There are five types of SQL commands: DDL, DML, DCL, TCL, and DQL
1. DDL - Data Definition Language.
This includes changes to the structure of the table like creation of table,
altering table, deleting a table etc. All DDL commands are auto-committed that
means it saves all the changes permanently in the database. Here are some
commands that come under DDL:
-
CREATE : It is used to create objects (tables, views) in the database
| Syntax |
CREATE TABLE TABLE_NAME (COLUMN_NAME DATATYPES[,....]);
|
| Example |
CREATE TABLE EMPLOYEE ( ID NUMBER, Name VARCHAR2(20), Email
VARCHAR2(100),DOB DATE);
|
-
ALTER : It is used to alter the structure of the database. This
change could be either to modify the characteristics of an existing
attribute or probably to add a new attribute.
| Syntax |
"To add a new column in the table use" ALTER TABLE table_name ADD
column_name COLUMN-definition;
"To modify existing column in the table use" ALTER TABLE MODIFY(COLUMN
DEFINITION....);
|
| Example |
ALTER TABLE EMPLOYEE ADD(ADDRESS VARCHAR2(20));
ALTER TABLE EMPLOYEE MODIFY (NAME VARCHAR2(100)):
|
-
DROP : It is used to delete database objects (It will invalidate the
dependent objects ,it also drops indexes, triggers and referential integrity
constraints).
| Syntax |
DROP TABLE TABLE_NAME; |
| Example |
DROP TABLE EMPLOYEE; |
-
TRUNCATE : Remove all records from a table, including all spaces
allocated for the records are removed (It is fast as compared to Delete and
does not generate undo information as Delete does. It performs an implicit
commit as it is a DDL. It resets the high water mark) .
| Syntax |
TRUNCATE TABLE table_name; |
| Example |
TRUNCATE TABLE EMPLOYEE; |
2. DML - Data Manipulation Language.
DML commands are used for manipulating the data stored in the table and not
the table itself. DML commands are not auto-committed. It means changes are
not permanent to database, they can be rolled back:
-
INSERT : The INSERT statement is a SQL query. It is used to insert
data into the row of a table.
| Syntax |
INSERT INTO TABLE_NAME (col1, col2, col3,.... col N) VALUES (value1,
value2, value3, .... valueN);
INSERT INTO TABLE_NAME VALUES (value1, value2, value3, .... valueN);
|
| Example |
INSERT INTO employee (id, name) VALUES (1, 'admin');
|
-
UPDATE : This command is used to update or modify the value of a
column in the table.
| Syntax |
UPDATE table_name SET [column_name1= value1,...column_nameN = valueN]
[WHERE CONDITION]
|
| Example |
UPDATE employee SET name = 'admin' WHERE id = '1';
|
- DELETE : It is used to remove one or more row from a table.
| Syntax |
DELETE FROM table_name [WHERE condition];
|
| Example |
DELETE FROM employee WHERE id = 1; |
3. DCL - Data Control Language.
Data control language are the commands to grant and take back authority from
any database user.
-
GRANT : It is used to give user access privileges to a database.
| Syntax |
GRANT SELECT, UPDATE ON TABLE_NAME TO USER_NAME, ANOTHER_USER_NAME;
|
| Example |
GRANT SELECT, UPDATE ON EMPLOYEE TO HR, APPS;
|
- REVOKE : It is used to take back permissions from the user.
| Syntax |
REVOKE SELECT, UPDATE ON TABLE_NAME FROM USER1, USER2;
|
| Example |
REVOKE SELECT, UPDATE ON EMPLOYEE FROM HR, APPS;
|
4. TCL - Transaction Control Language.
TCL commands can only use with DML commands like INSERT, DELETE and UPDATE
only. These operations are automatically committed in the database that's why
they cannot be used while creating tables or dropping them.
-
COMMIT : Commit command is used to save all the transactions to the
database.
| Syntax |
COMMIT; |
| Example |
DELETE FROM EMPLOYEE WHERE ID = 1;
COMMIT;
|
-
ROLLBACK : Rollback command is used to undo transactions that have
not already been saved to the database.
| Syntax |
ROLLBACK; |
| Example |
DELETE FROM EMPLOYEE WHERE ID = 1;
ROLLBACK;
|
-
SAVEPOINT : It is used to roll the transaction back to a certain
point without rolling back the entire transaction.
| Syntax |
SAVEPOINT SAVEPOINT_NAME; |
5. DQL - Data Query Language.
Data query language is used to fetch data from tables based on conditions that
we can easily apply.
-
SELECT : This is the same as the projection operation of relational
algebra. It is used to select the attribute based on the condition described
by WHERE clause.
| Syntax |
SELECT expressions FROM TABLE(S)_NAME WHERE conditions;
|
| Example |
SELECT * FROM EMPLOYEE WHERE ID = 1; |
