SQL
commands /SQL statements
1.DDL (Data Definition Language) Statements: {Dr.CAT}
i. Create : It is used for creating any database object in SQL
ii. Alter : It is used for modifying the structure of the existing database object
iii.Drop: It is used for removing an any database object permanently from database
iv.Truncate:
· All the records in the table
permanently deleted. We can’t rollback again
· So, If we want to delete specific
rows/records in a table then we can use delete statement
2.DML (Data Manipulation Language):
i.Insert : By using this we can add values into table
ii. Delete : To remove any record from existing table we can use this.
·Temorarily deletion it is, we can able to rollback again this
· But we commit wecan’t rollback iii. Update: It is used to modify existing data in database object.
3.DRL (Data Retrieval Language) or DQL (Data Query Language):
i.Select: we can retrive the data
4.TCL (Transaction Control Language):
i.Commit: commit the changes
ii.Rollback: rollback entire data
iii.Save point : savepoints are used to roll back transactions to a specified point.
5.DCL (Data Control Language):
i.Grant: It is specifically used to provide privileges to database objects for a user. This command also allows users to grant permissions to other users too.
ii.Revoke: Revoke command withdraw user privileges on database objects if any granted.
DDL (Data Definition Language) Statements:
1. Create: It is used for creating any database object in SQL
To create table, database, index,views and procedures will use "create" statement.
Syntax:
Create table <tablename>
(column1 datatype(size),
column2 datatype(size),
|
columnN datatype(size));
Example:
Create table
Employee(e_no number(2), e_name varchar2(20));
To
view:
Desc employee;
Syntax:
Alter table tablename add(col1 datatype(size),col2
datatype(size), -----);
Example:
Alter table employee add(sal
number(10,2), doj date);
· It is used to modify the size
· It is used for the modifying the
precision value of a column
· If we want to decrease or increase
column size we can use this
Syntax:
Alter table tablename modify colname
datatype(size);
Example 1:
Alter table employee modify e_no
number(4); //Increasing the size
Example 2:
Alter table employee modify e_name
varchar2(10); //ename existing values is greater than 10 so it’s not work
Syntax:
Alter table tablename drop column
<colname>;
Example:
Alter table employee drop column doj;
4. To rename a column:
Syntax:
Alter table tablename rename
column<oldcolname> to <newcolname>;
Example:
Alter table employee rename column
sal to salary;
· All the records in the table permanently deleted. We can’t rollback again
· So, If we want to delete specific rows/records in a table then we can use delete statement
Syntax:
Truncate table <tablename>;
Example:
Truncate table employee;
Syntax:
Rename <oldname> to
<newname>;
Example:
Rename employee to emp;
5. Drop: It is used for removing an any database object permanently from database
Syntax:
Drop table <tablename>;
Example:
Drop table emp;
DML
Statements in SQL
Insert:
By using insert statement, we can add
values into a table
Methods
of adding values in tables:
1.
By using value method:
Method-1:
Syntax:
Insert into
<tablename> values(val1,val2,………valN);
Example:
Insert into
emp values(1,’tvs’,25000);
Method-2:
Syntax:
Insert into
<tablename> (col1,col2,………..colN) values(val1,val2,……….valN);
Example-1:
Insert into
emp(e_name,e_no,sal) values('vijay',2,3000);
Example-2:
Here we omitted
the sal
Insert into
emp(e_no,e_name) values(3,’ram’);
Example-3: we can pass null values in
implicitly also
Insert into
emp(e_no,e_name,sal) values(4,null,null);
2.
By using Address method(&):
Method-1:
Inserting the values into all columns
Syntax:
Insert into <tablename> values(&e_no,’&e_name’,&sal);
Example:
Insert into emp values(&e_no,’&e_name’,&sal);
Method-2:
Inserting the values into specific columns
Syntax:
Insert into <tablename> (col1,col2,………)
values(&val1,&val2…..);
Example:
Insert into emp(e_no,e_name) values (&empno,’&empname’);
Delete
Statement:
· Temporarily deletion it is, by using rollback we can rollback the deleted things again
· But we commit after deleted ,we cann’t rollback it again
1.To
delete all records:
Syntax:
Delete <tablename>;
Example-1:
Delete emp;
Rollback;
Example-2:
Delete emp;
Commit;
2.To
delete specific Records:
Syntax:
Delete <tablename> where
<condition>;
Example:
Delete emp where e_no =5;
3.To
delete records permanently:
By using From keyword we can
permanently deleted without commit & rollback.
Syntax:
Delete from <tablename>;
Update Statement:
It is used
to modify existing data in database object
Syntax:
Update <tablename> set col1 =
val1, col2=val2,…………..where<condition>;
Example-1:
If you want to
update entire table
Update emp set sal=50000;
Example-2:
Update emp set sal=60000 where
e_no=3;
Example-3:
Update emp set
e_name=’gopal’,sal=30000 where e_no=4;
DRL
Statement
Syntax:
Select column names from tablename;
0r
Select *from tablename;
Example:
Select e_name, e_no from emp;
Select distinct:
Syntax:
Select distinct e_name from emp;
Where clause:
Syntax:
Select columnnames from tablename where condition;
Example:
Select * from emp where e_no=2;