SQL commands For class 12 Computer science CBSE
DDL commands of SQL
There are three DDL commands of SQL.
1. Create Table
This statement is used to create the structure of a table.
We can specify the various fields along wih their data types, size and range of values which can be stored in those fields.
We can also specify the constraints applied on fields.
Syntax:
| Create table <table_name>
( Column1 <data_type>(size) constraint, Column2 <data_type>(size) constraint, ……………………………………… ……………………………………… ); |
Here table_name is valid name of table.
Column1,Column2,…. are the names of various field of table.
data_type is the name of valid data type. It is used to specify the type of value which can be stored in a particular column.
size specifies the maximum number of digits or characters which can be stored in a particular field. It is optional in case of number data type.
constraint is the name of constraint that we want to apply on a particular field.
Example:
We have to create a table named Student with following structure:
| Field name | Data Type | Size | Constraint |
| roll | number | 3 | Primary Key |
| name | varchar | 20 | |
| dateofbirth | date | ||
| fees | number | 7,2 | |
| Class |
varchar |
20 |
SQL command for creating table with above structure is
| Create table student(roll number(3) primary key,
name varchar(20), dateofbirth date, fees number(7,2), Class varchar(20) ); |
2. Alter Table
This statement is used to modify the structure of a table. We can add or modify fields of an existing table.
We can also specify the data type, size, constraints and domain of fields which are to be added or modified in an existing table.
We can increase the size of any field at any time but we should be careful while decreasing size of any field as it may result in data loss.
Alter table command can be used in two ways
Syntax 1:
| Alter table <table_name>
add ( Column1 <data_type>(size) constraint, Column2 <data_type>(size) constraint, ……………………………………… ……………………………………… ); |
Here table_name is name of any existing table.
add specifies that new field is to be added to the table.
Column1,Column2,…. are the names of various field which are to be added to table.
data_type is the name of valid data type of Oracle. It is used to specify the type of value which can be stored in a particular column.
size specifies the maximum number of digits or characters which can be stored in a particular field. It is optional in case of number data type.
constraint is the name of constraint that we want to apply on a particular field.
Example 1:
| Alter table student add (address varchar2(50)); |
Above command will add a new field address to table named student.
Example 2:
| Alter table student add constraint u1 unique(address); |
Above command will add a unique constraint on address field of student table.
Syntax 2:
| Alter table <table_name>
modify( Column1 <data_type>(size) constraint, Column2 <data_type>(size) constraint, ……………………………………… ……………………………………… ); |
modify specifies that an existing field of a table is to be modified.
Example
| Alter table student modify(name varchar2(25)); |
Above command will increase the width of field named “name” to 25.
Syntax 3:
| Alter table <table_name>
drop column Column_name , ……………………………………… ……………………………………… ); |
drop column specifies that an existing field of a table is to be deleted.
Example:
| Alter table student drop column address; |
Above command will the delete the field address from the table student permanently.
3. Drop Table
This statement is used to delete the structure of a table as well as data stored in a particular table.
Syntax:
| Drop table <table_name> |
Here table_name is any valid name of Oracle.
Example
| drop table student; |
Above command will delete the table named student permanently.
DML Commands of SQL
There are four types of DML commands.
1. Insert
This command used to insert a new record in an existing table. We can insert a complete record or we can enter record values in specific fields also.
Syntax:
| Insert into table_name(field1,field2,……….)
values (datavalue1,datavalue2, ………..); |
Here table_name is the name of an existing table
field1,field2… represent the names of various fields of table
datavalue1,datavalue2, … represent the values which are to be inserted
into field1,field2,…….
Example 1:
| Insert into student values (101 ‘amit’, ‘2000–01-05’,5000.50); |
Above statement will insert a complete record in table named student.
101 will store in roll field, ‘amit’ in name field, ‘2000-01-05’ in dateofbirth field and 5000.50 in fees field.
Example 2:
| Insert into student(roll,name ) values(102, ‘sumit’) |
Above will only insert values in two fields named roll and name. 102 will be stored in roll field, ‘sumit’ in name field, dateofbirth and fees fields will get null.
2. update
This command is used to modify an exiting record . We can modify more than one field at a time and we can also modify those records which satisfy a particular condition.
Syntax: –
| Update <table_name>
set column_name1= expression1 , column_name2= expression2 , …….. where <condition >; |
Here table_name is the name of an existing table
column_name1, column_name2 represent the names of various fields of table whose contents are to be modified.
expression1, expression2 represent the values or expressions which are to be stored into fields specified by columnname1,columnname2.
condition is any relational or logical expression which is used to specify the condition according to which records should be modified.
Example 1:
| Update student set roll=106; |
Above statement ill make all the values in roll field to be 106.
Example 2:
| Update student set fees=fees+100; |
Above statement will increase the fees by 100 in all records.
Example 3:
| Update student set roll=roll+10 where fees>4500; |
Above statement will increase the roll by 10 in those records where fees>4500.
3. Delete
This command is used to delete all the records or an exiting record from a database table. Structure of table will not be deleted.
Syntax:
| Delete from<table_name>
where <condition >; |
Here table_name is the name of an existing table
condition is any relational or logical expression which is used to specify the condition as per which records should be deleted.
Example 1
| Delete from student; |
Above command will delete all the records from table named student.
Example 2
| Delete from student where fees>4000; |
Above command will delete those records from table named student which is having fees more than 4000.
4. Select
This command is used to view all the records or specific records from a database table depending on a particular condition.
Syntax:
| Select [*] / [column1,column2,………..]
from <table_name> where <condition >; |
Here table_name is the name of an existing table
* represents that all the records from the table should be shown on the screen.
column1,column2 represent the names of fields whose contents are to be shown.
condition is any relational or logical expression which is used to specify the condition as per which records should be deleted.
Example 1
| Select * from student; |
Above command will show all the records from table named student.
Example 2
| Select roll,name from student ; |
Above command will show contents of fields named roll and name for those records from table named student which have fees more than 4000.
Example 3
| Select * from student where fees>4000; |
Above command will show all those records from table named student which have fees more than 4000.
Clauses used with DML Commands
1. DISTINCT Clause
This clause is used with select command to view Unique values in a particular field.
Example
select distinct class from emp;
This command will show different values in the class field of student table avoiding duplicate values.
2. IN Clause
This clause is used to specify a range of values. This clause is used with select, delete , update statements.
Example
Select * from student where class in (‘xi’,’xii’);
OR
Select * from student where city =‘xi’ or city=’xii’;
This command is used to view those records where class field of student table contains either ‘xi’ or ‘xii’
3. BETWEEN Clause
The BETWEEN clause allows to check if an expression is within a range of two values. The values can be text, date, or numbers. The BETWEEN clause used in a condition will return the records where value of expression is within the range of value1 and value2.
Example:
Select * from student where fees between 1000 and 2000;
OR
Select * from student where fees>=1000 and fees<=2000;
In the above examples, those records from table student will be shown where value of fees is between 1000 and 2000. Both values 1000 and 2000 are included in the output.
4. LIKE Clause
The LIKE clause is used in a WHERE clause to search for a specified pattern in a column.
There are two wildcards used with LIKE clause:
- % : The percent sign represents zero or more characters.
- _ : The underscore represents a single character.
Examples
| LIKE Operator | Description |
| WHERE Name LIKE ‘A%’ | Finds values that start with “A” |
| WHERE Name LIKE ‘%s’ | Finds values that end with “s” |
| WHERE Name LIKE ‘%or%’ | Finds values that have “or” in any position |
| WHERE Name LIKE ‘_a%’ | Finds values that have “a” in the second position |
| WHERE Name LIKE ‘a_%’ | Finds values that start with “a” and are at least 2 characters in length |
| WHERE Name LIKE ‘a__%’ | Finds values that start with “a” and are at least 3 characters in length |
| WHERE Name LIKE ‘a%s’ | Finds any values that start with “a” and ends with “s” |
i. Select * from student where name like ‘A%’;
This command will show all those records from table named student where name field contains values starting with A for e.g. Arun, Aman, Anu , Amandeep etc.
ii. Select * from student where name like ‘%RU%’;
This command will show all those records from table student where name field contains values containing RU in it for e.g. ARUN, VARUN, KARUNA.
iii. Select * from student where name like ‘A_ _ _’;
This command will show all those records from table student where name field contains values starting with A and having four characters for e.g. Arun, Aman etc.
iv. Select * from student where name like ‘A_ _N’;
This command will show all those records from table student where name field contains values starting from A and ending with N and four characters in it for e.g. ARUN, AMAN etc.
5. IS NULL Clause
NULL means empty. It is neither Zero nor blank but it means “not defined”. This clause is used with select command to view those records which contain null values in a particular field i.e Field doesn’t contain any value in various records.
Example
i.. select * from student where fees is null;
This command will show all the records from student table where fees field contains null value.
ii.. select * from student where fees is not null;
This command will show all the records from student table where fees field does not contain null value.
6. ORDER BY Clause
This clause is basically used to sort the records as per a particular field. It is used with select command to view records alphabetically or numerically in ascending order or descending order with respect to a particular field.
We can user word “asc” to specify that records should be in ascending order. It is the default order when when we user order by clause. We use “desc” with order by clause to show the records in descending order of a particular field.
Example
i. select * from student order by name;
OR
select * from student order by name asc;
This command will show all the records from student table in ascending order of field “name”.
ii. select * from student order by name desc;
This command will show all the records from student table in descending order of field “name”.
7. GROUP BY Clause
This clause can be used with select statement. This clause is used to group records based on distinct values that exist for specified columns i.e. it creates a data set containing several sets of records grouped together based on a condition.
We can specify the condition for which records should be grouped by using “having” clause.
Example:
i. Select class, sum(fees) from student group by job ;
This command will show the classes along with sum of fees corresponding to various classes in the table student.
ii. Select class, sum(fees) from student group by job having count(class)>3;
This command will show the classes along with sum of fees corresponding to various classes where number of records corresponding to a particular class are more than 3.
SQL Functions
i. SUM
This function is used to find the sum of values stored in a numeric field of a table.
Syntax:
SUM(Field_Name)
Field_Name is the name of any numeric field on which we want to apply sum Function.
Example:
select sum(fees) from student;
This command will compute the sum of values stored in fees field of student table.
ii AVG:
This function is used to find the average of values stored in a numeric field of a table.
Syntax:
AVG(Field_Name)
Field_Name is the name of any numeric field on which we want to apply avg Function.
Example:
select avg(fees) from student;
This command will compute the average of values stored in fees field of student table.
iii. MIN
This function is used to find the minimum value among the values stored in a numeric field of a table.
Syntax:
MIN(Field_Name)
Field_Name is the name of any numeric field on which we want to apply MIN Function.
Example:
select MIN(fees) from student;
This command will find minimum fees from student table.
iv. MAX
This function is used to find the maximum value among the values stored in a numeric field of a table.
Syntax:
MAX(Field_Name)
Field_Name is the name of any numeric field on which we want to apply MIN Function.
Example:
select MAX(fees) from student;
This command will find maximum fees from student table.
v. COUNT
This function is used to count the number of records in a particular field or in complete table of Oracle.
Syntax
count(Expression/*)
Expression may be the name of any field of table or any expression based on a field of a table.
* is used when we want to know the number of records in a table.
Example
i. select count(fees) from student;
This command will show the number of non null values stored in fees field of student table.
ii. select count(*) from student;
This command will show the total number of records stored in student table.
SQL Joins
It is the way to combine records of more than one table in which there must exist one common field on which we create a relation between two tables using JOIN.
I. EQUI JOIN
This type of join is used to combine records from tables where the common field of both tables have same value.
Equi join is a special type of join in which we use only an equality (=) operator to select only those records having same value in common field.
Example:
Table: Foods
+---------+--------------+-----------+
| ITEM_ID | ITEM_NAME | COMPANY_ID|
+---------+--------------+-----------+
| 1 | Chex Mix | 16 |
| 6 | Cheez-It | 15 |
| 2 | BN Biscuit | 15 |
| 4 | Pot Rice | 15 |
+---------+--------------+-----------+
Table: Company
+------------+---------------+
| COMPANY_ID | COMPANY_NAME |
+------------+---------------+
| 15 | Jack Hill |
| 16 | Akas Foods |
| 17 | Foodies. |
| 19 | sip-n-Bite. |
+------------+---------------+
SELECT FOODS.ITEM_ID,FOOD.ITEM_NAME,
COMPANY.COMPANY_ID,COMPANY.COMPANY_NAME
FROM FOODS,COMPANY
WHERE FOODS.COMPANY_ID=COMPANY.COMPANY_ID;
OUTPUT
+---------+--------------+-----------+--------------+
| ITEM_ID | ITEM_NAME | COMPANY_ID| COMPANY_NAME |
+---------+--------------+-----------+--------------+
| 1 | Chex Mix | 16 | Akas Foods |
| 6 | Cheez-It | 15 | Jack Hill |
| 2 | BN Biscuit | 15 | Jack Hill |
| 4 | Pot Rice | 15 | Jack Hill |
+---------+--------------+-----------+--------------+
Output will show only those records from both the tables where company_id is same.
ii. NATURAL JOIN
A natural join is a type of equi join which occurs implicitly by comparing all the same names columns in both tables. The join result has only one column for each pair of equally named columns.
Natural Join has following conditions:
- The tables to be joined must have one or more columns having same name.
- The columns must be the same data type.
Syntax:
SELECT *
FROM table1
NATURAL JOIN table2;
Example:
Table: Foods
+---------+--------------+-----------+
| ITEM_ID | ITEM_NAME | COMPANY_ID|
+---------+--------------+-----------+
| 1 | Chex Mix | 16 |
| 6 | Cheez-It | 15 |
| 2 | BN Biscuit | 15 |
| 4 | Pot Rice | 15 |
+---------+--------------+-----------+
Table: Company
+------------+---------------+
| COMPANY_ID | COMPANY_NAME |
+------------+---------------+
| 15 | Jack Hill |
| 16 | Akas Foods |
| 17 | Foodies. |
| 19 | sip-n-Bite. |
+------------+---------------+
SELECT *
FROM foods NATURAL JOIN company;
OUTPUT
+---------+--------------+-----------+--------------+
| ITEM_ID | ITEM_NAME | COMPANY_ID| COMPANY_NAME |
+---------+--------------+-----------+--------------+
| 1 | Chex Mix | 16 | Akas Foods |
| 6 | Cheez-It | 15 | Jack Hill |
| 2 | BN Biscuit | 15 | Jack Hill |
| 4 | Pot Rice | 15 | Jack Hill |
+---------+--------------+-----------+--------------+
Output will show only those records from both the tables where company_id is same.