SQL Commands Tutorial: DDL, DML, TCL and DQL Commands
Sequential Query Language or SQL is the most widely used programming language for managing data in a relational database management system. It is also used for stream processing in a relational data stream management system.
Oracle Database, MySQL, Microsoft SQL Server, PostgreSQL, and MariaDB are some of the most popular relational database management systems.
SQL commands are used for communicating with the database. Everything ranging from creating a table and adding data to modifying a table and setting user permissions is accomplished using Basic SQL commands.
So, learning SQL commands is the first step towards learning SQL. There are a total of 5 types of SQL commands, as described below:
1. DDL Commands - Data Definition Language
Data Definition Language or DDL commands are used for changing the structure of a table. In other words, DDL commands are capable of creating, deleting, and modifying data.
All DDL commands are auto-committed which means that changes made by them are automatically saved in the database. Following are the various DDL commands:
Used for altering the structure of a database. Typically, the ALTER command is used either to add a new attribute or modify the characteristics of some existing attribute.
For adding new columns to the table:
ALTER TABLE table_name ADD (column_name1 data_type (size), column_name2 data_type (size),….., column_nameN data_type (size));
ALTER TABLE Student ADD (Address varchar2(20)); ALTER TABLE Student ADD (Age number(2), Marks number(3));
For modifying an existing column in the table:
ALTER TABLE table_name MODIFY (column_name new_data_type(new_size));
ALTER TABLE Student MODIFY (Name varchar2(20));
The ALTER command can also be used for dropping a column from the table:
ALTER TABLE table_name DROP COLUMN column_name;
ALTER TABLE Student DROP COLUMN Age; </pre. Note: - It is not possible to do the following using the ALTER command:
- Change the name of a column
- Change the name of a table
- Decrease the size of a column
Used for creating a new table in the database. General Syntax:
CREATE TABLE table_name (column_name1 data_type(size), column_name2 data_type(size),…., column_nameN data_type(size));
CREATE TABLE Employee(Name varchar2(20), D.O.B. date, Salary number(6);
Used for deleting an entire table from the database and all the data stored in it.
DROP TABLE table_name;
DROP TABLE Student;
Used for renaming a table.
RENAME old_table_name TO new_table_name
RENAME Student TO Student_Details
Used for deleting all rows from a table and free the space containing the table.
TRUNCATE TABLE table_name;
TRUNCATE TABLE Student;
2. DML Commands - Data Manipulation Language
The DML or Data Manipulation Language commands help in modifying a relational database. These commands are not auto-committed, which simply means that all changes made to the database using DML commands aren’t automatically saved.
It is possible to rollback DML commands. Various DML commands are:
Used for removing one or more rows from a table.
DELETE FROM table_name; (deletes all rows from a table)
DELETE FROM table_name WHERE some_condition; (delete only the row(s) where the condition is true)
DELETE FROM Student; DELETE FROM Student WHERE Name = “Akhil”;
Used for inserting data into the row of a table.
INSERT INTO table_name (column_name1, column_name2,….,column_nameN) VALUES (value1, value2,….,valueN); OR INSERT INTO table_name VALUES (value1, value2,….,valueN);
INSERT INTO Student (Name, Age) VALUES (“Vijay”, “25”);
Insert command can also be used for inserting data into a table from another table.
INSERT INTO table_name1 SELECT column_name1, column_name2,….,column_nameN FROM table_name2;
INSERT INTO Student SELECT Id, Stream FROM Student_Subject_Details
Used to modify or update the value of a column in a table. It can update all rows or some selective rows in the table.
UPDATE table_name SET column_name1 = value1, column_name2 = value2,….,column_nameN = valueN (for updating all rows) UPDATE table_name SET column_name1 = value1, column_name2 = value2,….,column_nameN = valueN [WHERE CONDITION] (for updating particular rows)
UPDATE Student SET Name = “Akhil” WHERE Id = 22;
3. DCL Commands - Data Control Language
In order to protect the information in a table from unauthorized access, DCL commands are used. A DCL command can either enable or disable a user from accessing information from a database. List of user access privileges:
Used for granting user access privileges to a database.
GRANT object_privileges ON table_name TO user_name1, user_name2,….,user_nameN; GRANT object_privileges ON table_name TO user_name1, user_name2,….,user_nameN WITH GRANT OPTION; (allows the grantee to grant user access privileges to others)
GRANT SELECT, UPDATE ON Student TO Akhil Bhadwal
This will allow the user to run only SELECT and UPDATE operations on the Student table.
GRANT ALL ON Student TO Akhil Bhadwal WITH GRANT OPTION
Allows the user to run all commands on the table as well as grant access privileges to other users.
Used for taking back permission given to a user.
REVOKE object_privileges ON table_name FROM user1, user2,… userN;
REVOKE UPDATE ON Student FROM Akhil;
Note: - A user who is not the owner of a table but has been given the privilege to grant permissions to other users can also revoke permissions.
4. TCL Commands - Transaction Control Language
Transaction Control Language commands can only be used with DML commands. As these operations are auto-committed in the database, they can’t be used while creating or dropping tables. Various TCL commands are:
Used for saving all transactions made to a database. Ends the current transaction and makes all changes permanent that were made during the transaction. Releases all transaction locks acquired on tables.
DELETE FROM Student WHERE Age = 25; COMMIT;
Used to undo transactions that aren’t yet saved in the database. Ends the transaction and undoes all changes made during the transaction. Releases all transaction locks acquired on tables.
DELETE FROM Student WHERE Age = 25; ROLLBACK;
Used for rolling back to a certain state known as the savepoint. Savepoints need to be created first so that they can be used for rollbacking transactions partially.
Note: - An active savepoint is one that has been specified since the last COMMIT or ROLLBACK command.
5. DQL Commands - Data Query Language
DQL commands are used for fetching data from a relational database. There is only one command, which is the SELECT command.
Equivalent to the projection operation in relational algebra, SELECT command selects the attribute based on the condition described by the WHERE clause.
SELECT expressions FROM table_name WHERE condition1, condition2,…., conditionN;
Suppose we have a relational table called Student that has all the information regarding a student, such as name, roll no., stream, age, address, etc. and we need to fetch data regarding all student names who are less than 18 years of age, then we can use the SELECT command as follows:
SELECT student_name FROM student WHERE age < 18;
The result will be a list of all the student names who are less than 18 years of age.
The SELECT command can also be used for eliminating duplicates from a table.
SELECT DISTINCT column_name1, column_name2,…., column_nameN FROM table_name;
SELECT DISTINCT Name, Age FROM Student;
This command will scan through entire rows and will eliminate rows that are identical.
Rows retrieved using the SELECT command can be sorted in either ascending or descending order.
SELECT column_name1, column_name2,…., column_nameN FROM table_name ORDER BY column_name; (gives results in ascending order) SELECT column_name1, column_name2,…., column_nameN FROM table_name ORDER BY column_name DESC; (gives results in descending order)
SELECT Name, Age FROM Student ORDER BY Name; SELECT Name, Age FROM Student ORDER BY Name DESC;
Acquiring knowledge of commonly used commands is the first step to be able to understand and work with SQL databases. Mastering SQL commands demands a good amount of practice and we hope this article will help you get started on your journey!
Preparing for an SQL interview? Check out these most important SQL interview questions to seize the opportunity.
People are also reading: