Subject: Class 10, Information Technology

Updated by: Ms. Ramya

Tuple , Attribute , Degree, Cardinality &

SQL commands

All Rights Reserved @ Bharatiya Vidya Bhavan Middle East

Employee Table
ID Name Age Salary
1 Adam 34 13000
2 Alex 28 15000
3 Stuart 20 18000
4 Ross 42 19020

All Rights Reserved @ Bharatiya Vidya Bhavan

Middle East
Tuple
A single entry in a table is called a Tuple or Record or Row.
A tuple in a table represents a set of related data.
For example, the above Employee table has 4
tuples/records/rows.

Following is an example of single record or tuple.

1 Adam 34 13000

All Rights Reserved @ Bharatiya Vidya Bhavan

Middle East
Attribute
A table consists of several records(row), each record can be
broken down into several smaller parts of data known
as Attributes. The above Employee table consist of four
attributes, ID, Name, Age and Salary.

All Rights Reserved @ Bharatiya Vidya Bhavan

Middle East
Degree
Degree is the number of attributes in a relation (number of
columns).
Therefore, the degree of the above table is 4.

All Rights Reserved @ Bharatiya Vidya Bhavan

Middle East
Cardinality
Cardinality is the number of tuples in a relation (number
of rows).
The cardinality of the above table Employee is 4.

All Rights Reserved @ Bharatiya Vidya Bhavan

Middle East
S Q L Commands

All Rights Reserved @ Bharatiya Vidya Bhavan

Middle East
DDL Command(Data Definition
Language)
Eg: create, Alter etc.

All Rights Reserved @ Bharatiya Vidya Bhavan

Middle East
SQL: create command
create is a DDL S QL command used to create a table or a
database in relational database management system.

Creating a Database
To create a database in RD BMS, create command is used.
Following is the syntax,
CREATE DATABASE <DB_NAME>;
Example for creating Database
CREATE DATABASE Test;
The above command will create a database named Test,
which will be an empty schema without any table.

All Rights Reserved @ Bharatiya Vidya Bhavan

Middle East
To create tables in this newly created database, we can again use the create
command.
Creating a Table
create command can also be used to create tables. Now when we create a table, we have
to specify the details of the columns of the tables too. We can specify the names and
datatypes of various columns in the create command itself.

Following is the syntax,

CREATE TABLE <TABLE_NAME>

(
column_name1 datatype1,
column_name2 datatype2,
column_name3 datatype3,
column_name4 datatype4
);
create table command will tell the database system to create a new table with the given
table name and column information.

All Rights Reserved @ Bharatiya Vidya Bhavan

Middle East
Example for creating Table
CREATE TABLE Student (
student_id INT,
name VARCHAR (100),
age INT);
The above command will create a new table with name
Student in the current database with 3 columns, namely
student_id, name and age. Where the column student_id
will only store integer, name will hold upto 100 characters
and age will again store only integer value.

All Rights Reserved @ Bharatiya Vidya Bhavan

Middle East
SQL: ALTER command
alter command is used for altering the table structure,
such as,

• to add a column to existing table

• to rename any existing column
• to change datatype of any column or to modify its size.
• to drop a column from the table.

All Rights Reserved @ Bharatiya Vidya Bhavan

Middle East
ALTER Command: Add a new Column

Using ALTER command we can add a column to any existing table. Following is the syntax,

ALTER TABLE table_name ADD(

column_name datatype);
Here is an Example for this,

ALTER TABLE student ADD(

address VARCHAR(200)
);
The above command will add a new column address to the table student, which will hold
data of type varchar which is nothing but string, of length 200.

All Rights Reserved @ Bharatiya Vidya Bhavan

Middle East
ALTER Command: Add multiple new
Columns
Using ALTER command we can even add multiple new columns to any existing table.
Following is the syntax,

ALTER TABLE table_name ADD(

column_name1 datatype1,
column-name2 datatype2,
column-name3 datatype3);
Here is an Example for this,

ALTER TABLE student ADD(

father_name VARCHAR(60),
mother_name VARCHAR(60),
dob DATE);
The above command will add three new columns to the student table.

All Rights Reserved @ Bharatiya Vidya Bhavan

Middle East
ALTER Command: Add Column with
default value
ALTER command can add a new column to an existing table with a default value
too. The default value is used when no value is inserted in the column. Following
is the syntax,

ALTER TABLE table_name ADD(

column-name1 datatype1 DEFAULT some_value
);
Here is an Example for this,

ALTER TABLE student ADD(

dob DATE DEFAULT '01-Jan-99'
);
The above command will add a new column with a preset default value to the table
student.
All Rights Reserved @ Bharatiya Vidya Bhavan
Middle East
ALTER Command: Modify an existing
Column
ALTER command can also be used to modify data type of any existing column.
Following is the syntax,

ALTER TABLE table_name modify(

column_name datatype
);
Here is an Example for this,

ALTER TABLE student MODIFY(

address varchar(300));
Remember we added a new column address in the beginning? The above command
will modify the address column of the student table, to now hold upto 300
characters.

All Rights Reserved @ Bharatiya Vidya Bhavan

Middle East
ALTER Command: Rename a Column

Using ALTER command you can rename an existing column.

Following is the syntax,

ALTER TABLE table_name RENAME

old_column_name TO new_column_name;
Here is an example for this,

ALTER TABLE student RENAME

address TO location;
The above command will rename address column to location.

All Rights Reserved @ Bharatiya Vidya Bhavan

Middle East
ALTER Command: Drop a Column

ALTER command can also be used to drop or remove columns.

Following is the syntax,

ALTER TABLE table_name DROP(

column_name);
Here is an example for this,

ALTER TABLE student DROP(

address);
The above command will drop the address column from the
table student.
All Rights Reserved @ Bharatiya Vidya Bhavan
Middle East
DML Command (Data
Manipulation Language)
Using INSERT SQL command
Data Manipulation Language (DML) statements are used
for managing data in database. DML commands are not
auto committed. It means changes made by DML
command is not permanent to database, it can be rolled
back.

Talking about the Insert command, whenever we post a

Tweet on Twitter, the text is stored in some table, and as
we post a new tweet, a new record gets inserted in that
table.
All Rights Reserved @ Bharatiya Vidya Bhavan
Middle East
Insert command is used to insert data
into a table. Following is its general
syntax,
INSERT INTO table_name VALUES(data1, data2, ...)
Lets see an example,
Consider a table student with the following fields.
s_id name age

INSERT INTO student VALUES(101, 'Adam', 15);

The above command will insert a new record into student
t able.
s_id name age
101 Adam 15
All Rights Reserved @ Bharatiya Vidya Bhavan
Middle East
Insert value into only specific
columns
We can use the INSERT command to insert values for only
some specific columns of a row. We can specify the column
names along with the values to be inserted like this,

INSERT INTO student(id, name) values(102, 'Alex');

The above SQL query will only insert id and name values in
the newly inserted record.

All Rights Reserved @ Bharatiya Vidya Bhavan

Middle East
Insert NULL value to a column

Both the statements below will insert NULL value into age
column of the student table.

INSERT INTO student(id, name) values(102, 'Alex');

Or,
INSERT INTO Student VALUES(102,'Alex', null);
The above command will insert only two column values
and the other column is set to null.

All Rights Reserved @ Bharatiya Vidya Bhavan

Middle East
S_id S_Name age

101 Adam 15

102 Alex null

All Rights Reserved @ Bharatiya Vidya Bhavan

Middle East
Insert Default value to a column

INSERT INTO Student VALUES(103,'Chris', default)

S_id S_Name age
101 Adam 15
102 Alex null
103 chris 14
Suppose the column age in our table has a default value of 14.

Also, if you run the below query, it will insert default value into
the age column, whatever the default value may be.

INSERT INTO Student VALUES(103,'Chris')

All Rights Reserved @ Bharatiya Vidya Bhavan
Middle East
Using UPDATE SQL command
Let's take an example of a real-world problem. These days, Facebook
provides an option for Editing your status update, how do you think it
works? Yes, using the Update SQL command.

Let's learn about the syntax and usage of the UPDATE command.

UPDATE command
UPDATE command is used to update any record of data in a table.
Following is its general syntax,

UPDATE table_name SET column_name = new_value WHERE

some_condition;
WHERE clause is used to add a condition to any SQL query.

All Rights Reserved @ Bharatiya Vidya Bhavan

Middle East
Consider the following table student.

student_id name age

101 Adam 15
102 Alex null
103 chris 14

All Rights Reserved @ Bharatiya Vidya Bhavan

Middle East
UPDATE student SET age=18 WHERE
student_id=102;

S_id S_Name age

101 Adam 15
102 Alex 18
103 chris 14

In the above statement, if we do not use the WHERE clause, then our update query will
update age for all the columns of the table to 18.

All Rights Reserved @ Bharatiya Vidya Bhavan

Middle East
Updating Multiple Columns

We can also update values of multiple columns using a single

UPDATE statement.

UPDATE student SET name='Abhi', age=17 where s_id=103;

The above command will update two columns of the record
which has s_id 103.

s_id nameage
101 Adam 15
102 Alex 18
103 Abhi 17

All Rights Reserved @ Bharatiya Vidya Bhavan

Middle East
UPDATE Command: Incrementing
Integer Value
When we have to update any integer value in a table, then we can
fetch and update the value in the table in a single statement.

For example, if we have to update the age column of student table

every year for every student, then we can simply run the following
UPDATE statement to perform the following operation:

UPDATE student SET age = age+1;

As you can see, we have used age = age + 1 to increment the value of
age by 1.

NOTE: This style only works for integer values.

All Rights Reserved @ Bharatiya Vidya Bhavan

Middle East
Using DELETE SQL command

When you ask any question in any Forum it gets saved

into a table. And using the Delete option, you can even
delete a question asked by you. How do you think that
works? Yes, using the Delete DML command.

Let's study about the syntax and the usage of the Delete
command.

All Rights Reserved @ Bharatiya Vidya Bhavan

Middle East
DELETE command

DELETE command is used to delete data from a table.

Following is its general syntax,

DELETE FROM table_name;

Let's take a sample table student:

s_id name age

101 Adam 15
102 Alex 18
103 Abhi 17

All Rights Reserved @ Bharatiya Vidya Bhavan

Middle East
Delete all Records from a Table

DELETE FROM student;

The above command will delete all the records from the
table student.

All Rights Reserved @ Bharatiya Vidya Bhavan

Middle East
Delete a particular Record from a
Table
In our student table if we want to delete a single record, we can
use the WHERE clause to provide a condition in our DELETE
statement.

DELETE FROM student WHERE s_id=103;

The above command will delete the record where s_id is 103
from the table student.

S_id S_Name age

101 Adam 15
102 Alex 18

All Rights Reserved @ Bharatiya Vidya Bhavan

Middle East
DCL Command(Data Control
Language)
Eg:Select query
SELECT SQL Query
SELECT query is used to retrieve data from a table. It is
the most used SQL query. We can retrieve complete table
data, or partial by specifying conditions using the WHERE
clause.

All Rights Reserved @ Bharatiya Vidya Bhavan

Middle East
Syntax of SELECT query
SELECT query is used to retieve records from a table. We can specify
the names of the columns which we want in the resultset.

SELECT
column_name1,
column_name2,
column_name3,
...
column_nameN
FROM table_name;

All Rights Reserved @ Bharatiya Vidya Bhavan

Middle East
Consider the following student table,

s_id name age address

101 Adam 15 Chennai
102 Alex 18 Delhi
103 Abhi 17 Banglore
104 Ankit 22 Mumbai
SELECT s_id, name, age FROM student;
The above query will fetch information of s_id, name and age columns of the student table and display them,

s_id name age

101 Adam 15
102 Alex 18
103 Abhi 17
104 Ankit 22
As you can see the data from address column is absent, because we did not specify it in our SELECT query.

All Rights Reserved @ Bharatiya Vidya Bhavan

Middle East
Now we will use the SELECT statement to display data of the table, based on a condition, which
we will add to our SELECT query using WHERE clause.

Let's write a simple SQL query to display the record for student with s_id as 101.

SELECT s_id,
name,
age,
address
FROM student WHERE s_id = 101;
Following will be the result of the above query.

s_id name age address

101 Adam 15 Noida

All Rights Reserved @ Bharatiya Vidya Bhavan

Middle East
Applying condition on Text Fields
In the above example we have applied a condition to an integer value field, but
what if we want to apply the condition on name field. In that case we must
enclose the value in single quote ' '. Some databases even accept double
quotes, but single quotes is accepted by all.

SELECT s_id,
name,
age,
address
FROM student WHERE name = 'Adam';
Following will be the result of the above query.

s_id name age address

101 Adam 15 Noida
All Rights Reserved @ Bharatiya Vidya Bhavan
Middle East
S Q L LIKE clause
LIKE clause is used in the condition in SQL query with the WHERE clause. LIKE clause compares data with an expression
using wildcard operators to match pattern given in the condition.

Wildcard operators
There are two wildcard operators that are used in LIKE clause.

Percent sign %: represents zero, one or more than one character.

Underscore sign _: represents only a single character.
Example of LIKE clause
Consider the following Student table.

s_id s_Name age

101 Adam 15
102 Alex 18
103 Abhi 17

All Rights Reserved @ Bharatiya Vidya Bhavan

Middle East
SELECT * FROM Student WHERE s_name LIKE 'A%';
The above query will return all records where s_name
starts with character 'A'.

s_id s_Name age

101 Adam 15
102 Alex 18
103 Abhi 17

All Rights Reserved @ Bharatiya Vidya Bhavan

Middle East
Using _ and %
SELECT * FROM Student WHERE s_name LIKE '_d%';
The above query will return all records from Student table where s_name contain 'd' as
second character.

s_id s_Name age

101 Adam 15
Using % only
SELECT * FROM Student WHERE s_name LIKE '%x';
The above query will return all records from Student table where s_name contain 'x' as last
character.

s_id s_Name age

102 Alex 18

All Rights Reserved @ Bharatiya Vidya Bhavan

Middle East
ORDER BY Clause
Order by clause is used with SELECT statement for
arranging retrieved data in sorted order. The Order by
clause by default sorts the retrieved data in ascending
order. To sort the data in descending order D E S C keyword
is used with Order by clause.

SELECT * FROM Emp ORDER BY salary;

SELECT * FROM Emp ORDER BY salary D E S C ;

All Rights Reserved @ Bharatiya Vidya Bhavan

Middle East