SQL commands are instructions to the database to perform a specific operation. For example, you can use SELECT command to read the data from database, you can use UPDATE command to update data in database. There are several commands that are available in SQL for various type of tasks and these commands are divided in following categories:
- DDL (Data Definition Language) – Commands such as CREATE, DROP, ALTER.
- DML (Data Manipulation Language) – Commands such as INSERT, UPDATE, DELETE.
- DCL (Data Control Language) – Commands such as GRANT, REVOKE.
- TCL (Transaction Control Language) – Commands such as COMMIT, ROLLBACK.
- DQL (Data Query Language) – Commands such as SELECT.
1. DDL (Data Definition Language)
- DDL commands are used to create, drop and alter the databases, aliases, indexes, tables etc.
- Once a DDL statement is executed, it takes effect immediately in the database. Which means any changes done to the database using DDL command is permanent.
- Popular DDL commands are: CREATE, DROP, ALTER and TRUNCATE.
a) CREATE: This command is used to create database and tables inside a database.
Syntax for creating database:
CREATE DATABASE database_name;
Syntax for creating table:
CREATE TABLE table_name ( column1 datatype, column2 datatype, column3 datatype, .... );
b) DROP: This command is used to drop database and tables.
Syntax for dropping database:
DROP DATABASE database_name;
Syntax for dropping table:
DROP TABLE table_name;
c) ALTER: This command is used to add, delete, or modify columns in an existing table.
Syntax: To add a new column in the existing table
ALTER TABLE table_name ADD column_name datatype;
Example: The following ALTER TABLE statement will add a new column
Address to the existing table
ALTER TABLE EMPLOYEES ADD Address VARCHAR2(200);
d) TRUNCATE: This statement deletes all the rows from the table. This is different from the DROP command, the DROP command deletes the entire table along with the table schema, however TRUNCATE just deletes all the rows and leaves an empty table.
TRUNCATE TABLE table_name;
Example: Deletes all the rows from the table
TRUNCATE TABLE EMPLOYEES;
2. DML (Data Manipulation Language)
SQL commands that deals with the manipulation of data comes under DML category. The popular commands that come under DML are:
- INSERT − Insert data into a table.
- UPDATE − Update existing data in the specified table.
- DELETE − Delete records from the specified table in database.
a) INSERT: This command is used to insert records in a table
Syntax: When you are not inserting the data for all the columns and leaving some columns empty. In that case specify the column name and corresponding value.
INSERT INTO table_name (column1, column2, column3, ...) VALUES (value1, value2, value3, ...);
Syntax: When inserting the data for all the columns. No need to specify column name.
INSERT INTO table_name VALUES (value1, value2, value3, ...);
Example: Adding an employee record into the table
Employee. However adding the values for only two columns
age into the table.
INSERT INTO EMPLOYEE (name, age) VALUES ("Chaitanya", "35");
a) UPDATE: This is used to modify the existing records in a table.
UPDATE table_name SET column1 = value1, column2 = value2, ... WHERE condition;
Example: Updating the
city of the employee with employee id “1001”. This will search all the records in the table where
Employee_id is equal to
1001 then it will update the
name field to “Chaitanya” and
city field to “Noida” for all the filtered records.
UPDATE EMPLOYEE SET name = 'Chaitanya', city = 'Noida' WHERE Employee_id = 1001;
a) DELETE: It is used to delete the existing records from the table.
DELETE FROM table_name WHERE condition;
Example: The following SQL query will delete all the rows from the table
name is equal to
DELETE FROM EMPLOYEES WHERE name="Chaitanya";
3. DCL (Data Control Language)
DCL commands are used to set permissions on the table. This is the command that is responsible for the security of database. By setting up the permission, user can prevent unauthorised access to the database.
DCL commands are:
a) Grant: It is used to give user access privileges to a database.
Example: The following command will grant users
USER2, the select, update and delete access to the table
GRANT SELECT, UPDATE, DELETE ON TABLE1 TO USER1, USER2;
b) Revoke: It revokes the given access to the user.
Example: Let’s revoke the access from
USER2 given above using
REVOKE SELECT, UPDATE, DELETE ON TABLE1 FROM USER1, USER2;
4. TCL (Transaction Control Language)
The purpose of TCL commands is to maintain the consistency of the database. These commands are generally used along with the DML commands such as INSERT, UPDATE and DELETE. The changes made by DML commands are either committed or rolled back by TCL commands. There is another TCL command that can place a save point in the transactions which makes it possible to rollback all the transaction till the last save point.
TCL commands are:
a) Commit: Commit command make the changes made to the database permanent.
Example: The following update command changes the name in the
STUDENT table from
"Chaitanya Pratap Singh". By using
COMMIT just after the update statement save these changes permanently in the table.
UPDATE STUDENT SET NAME = ‘Chaitanya Pratap Singh’ WHERE NAME = ‘CPS’; COMMIT;
b) Rollback: Rollback command is used to undo the changes that have been made to the database temporarily. The important point to note here is that the changes saved using
COMMIT command cannot be undone using
Example: The following commands would have deleted all the students from the
STUDENT table who have age greater than 15. Since we have used the
ROLLBACK command after the DELETE statement, these changes were undone and no record deleted from the table.
The important point to note here is that
ROLLBACK are two different commands, they are not written together. To understand this, let’s say the user mistakenly written the following DELETE command but as soon as he realises his mistake, he use ROLLBACK command to undo the changes that were made unintentionally.
DELETE FROM STUDENT WHERE AGE > 15; ROLLBACK;
c) SAVEPOINT: This command helps in roll backing the transactions till a certain point. For example, a transaction consists of several DML commands and we want to undo the changes till a certain point and not completely, this can be achieved by using
INSERT into EMPLOYEE (name, age) VALUES ('Chaitanya', 35); Commit; UPDATE EMPLOYEE SET name = ‘Ajeet’ WHERE age = 49; SAVEPOINT A; UPDATE EMPLOYEE SET name = ‘Hari’ WHERE age = 51; Savepoint B; UPDATE EMPLOYEE SET name = ‘Steve’ WHERE age = 38; Savepoint C;
Now if we want to roll back the certain DML commands, we can do so by using Rollback like this:
This will rollback the transaction till save point A:
Rollback to A;
This will rollback the transaction till save point B:
Rollback to B;
5. DQL (Data Query Language)
DQL command is used to fetch data from the database. There is only one DQL command:
a) SELECT: SELECT command fetches the records from the specified table that matches the given condition, if no condition is provided, it fetches all the records from the table.
SELECT expressions FROM TABLE_NAME WHERE conditions;
Example: The following query will fetch all the student names from the
STUDENT table where the student age is greater than 15.
SELECT name FROM STUDENT WHERE age > 15;