SQL is divided into the following :-
- Data Definition Language (DDL)
- Data Manipulation Language (DML)
- Data Retrieval Language (DRL)
- Transaction Control Language (TCL)
- Data Control Language (DCL)
- DDL – Create, Alter, Drop, Truncate, Rename
- DML – Insert, Update, Delete
- DRL – Select
- TCL – Commit, Rollback, Savepoint
- DCL – Grant, Revoke
DRL COMMANDS
CREATE TABLE
Syntax:
Create table <table_name> (col1 datatype1, col2 datatype2 …coln datatypen);
Create table XXSTP_EMP_DETAILS
(
Cust_ID number primary Key,
Cust_Name varchar2(200),
Cust_join_date date
);
DML COMMANDS
INSERT
This will be used to insert the records into table.
We have two methods to insert.
- By value method
- By address method
- a) USING VALUE METHOD
Syntax:
insert into <table_name> values (value1, value2, value3 …. Valuen);
insert into XXSTP_EMP_DETAILS values (1,’Rajesh’,’01-Jan-2016’);
insert into XXSTP_EMP_DETAILS values (2,’Amit’,’01-Feb-2016’);
UPDATE
This can be used to modify the table data.
Syntax:
Update <table_name> set <col1> = value1, <col2> = value2 where <condition>;
Update XXSTP_EMP_DETAILS set Cust_Name=’Rajesh Singh’
Where cust_id=1;
DELETE
This can be used to delete the table data temporarily.
Syntax:
Delete <table_name> where <condition>;
Delete from XXSTP_EMP_DETAILS where cust_id=1;
Note: We have to explicitly commit the DML transactions
DDL COMMANDS
ALTER
This can be used to add or remove columns and to modify the precision of the datatype.
Syntax:
alter table <table_name> add <col datatype>;
alter table XXSTP_EMP_DETAILS add Cust_Father_Name varchar2(200);
TRUNCATE
This can be used to delete the entire table data permanently.
Syntax:
truncate table <table_name>;
Truncate table XXSTP_EMP_DETAILS;
DROP
This will be used to drop the database object;
Syntax:
Drop table <table_name>;
Drop table XXSTP_EMP_DETAILS;
RENAME
This will be used to rename the database object;
Syntax:
rename <old_table_name> to <new_table_name>;
Rename XXSTP_EMP_DETAILS to XXSTP_EMPLOYEES_DETAILS;
Note :- DDL statements are auto-commit. We don’t need to commit.
TCL COMMANDS
COMMIT
This will be used to save the work.
Syntax:
Commit;
ROLLBACK
This will undo the operation.
Syntax:
Rollback;
SAVEPOINT
You can use savepoints to rollback portions of your current set of transactions.
Syntax:
Savepoint <savepoint_name>;
DCL COMMANDS
DCL commands are used to granting and revoking the permissions.
GRANT
This is used to grant the privileges to other users.
Syntax:
Grant <privileges> on <object_name> to <user_name> [with grant option];
Grant all on XXSTP_EMPLOYEES_DETAILS to ORCL;
REVOKE
This is used to revoke the privileges from the users to which you granted the privileges.
Syntax:
Revoke <privileges> on <object_name> from <user_name>;
Revoke all on XXSTP_EMPLOYEES_DETAILS from ORCL;