What are the 5 Basic SQL Commands? (DDL, DML, DCL, TCL, DQL)

SQL Commands

SQL commands are a set of instructions used to interact with the database like SQL Server, MySql, Oracle, etc. SQL commands are responsible for creating and doing all the manipulation on the database. These are also responsible for giving/taking out access rights to a particular database. In short SQL commands, also known as SQL statements, provide us with complete control over the data in our database. Cool, right? SQL can help you manage user accounts, work with sales data, and run reports more efficiently.

So in this SQL Server Tutorial, We gonna explore basic SQL Commands including what are the 5 basic SQL commands, their syntaxes, and examples too.

Read More: SQL Server Interview Questions and Answers

SQL Commands Category/Types of SQL Commands

We have different SQL commands for different purposes. We can group Sql Commands into five major categories depending on their functionality.

1. Data Definition Language (DDL)

These SQL commands are used for creating a table, deleting a table, altering a table, and truncating the table. All the commands of DDL are auto-committed i.e. it permanently saves all the changes in the database.

In this category, we have four commands:

1. CREATE: It is used to create a new table in the database. Here, you need to specify the name of the table and the name of each column in the table.

   Syntax

   
   CREATE TABLE table_name (
     column_1 datatype, 
     column_2 datatype, 
     column_3 datatype
   );  

   Example

   
   CREATE TABLE Employee
   (
   EmployeeID int,
   EmployeeName varchar(200),
   PhoneNumber int,
   Email varchar(255),
   City varchar(255),
   Country varchar(255)
   );
   

2. ALTER: It changes the structure of a table by adding, deleting, or modifying columns in an existing table.

   To add a column

   Syntax

   
   ALTER TABLE TableName
   ADD ColumnName Datatype;
   

   To delete a column

   Syntax

   
   ALTER TABLE TableName
   DROP COLUMN ColumnName;
   

   To modify a column

   Syntax

   
   ALTER TABLE TableName
   ALTER COLUMN ColumnName Datatype;
   

   Example

   
   --ADD Column DOB:
   ALTER TABLE Employee
   ADD DOB varchar(10);
    
   --DROP Column DOB:
   ALTER TABLE Employee
   DROP COLUMN DOB;
   
   --Add a column DOB and change the data type to Date.
   ALTER TABLE Employee
   ADD DOB year;
    
   ALTER TABLE Employee
   ALTER DOB date;
   

3. DROP: This command is used to drop an existing table or a database.

   Syntax

   
   DROP DATABASE DatabaseName;
   

   Example

   
   DROP DATABASE Employee;
   

   Syntax

   
   DROP TABLE TableName;
   

   Example

   
   DROP Table Employee;
   

Read More: Drop all tables, stored procedures, views, and triggers

4. TRUNCATE: This command deletes the information present in the table or all the rows from the table but does not delete the table.

   Syntax

   
   TRUNCATE TABLE TableName;
   

   Example

   
   TRUNCATE Table Employee;
   

Note

1. Only with DDL commands, we need to write keywords (like table, procedure, view, index, function) with the syntax of the command.

2. These commands are used to create/modify the structure of the database object.

Read More - Top DBMS Interview Questions and Answers

2. Data Manipulation Language (DML)

These SQL commands modify the database. The commands of DML are not auto-committed i.e. it can't permanently save all the changes in the database. They can be rolled back.

In this category, we have three commands:

1. INSERT: It is used to insert new records into the table or data into the row of a table.

   Syntax

   
   INSERT INTO TableName (Column1, Column2, Column3, ...,ColumnN)
   VALUES (value1, value2, value, ...);
   or
   INSERT INTO TableName
   VALUES (Value1, Value2, Value3, ...);
   

   Example

   
   INSERT INTO Employee(EmployeeID, EmployeeName, PhoneNumber, Email, City, Country)
   VALUES ('01', 'Sourav', '9921321789', 'House No 12', 'Jharkhand', 'India');
    
   INSERT INTO Employee
   VALUES ('01', 'Sourav', '9921321789', 'House No 12', 'Jharkhand', 'India');
   

Read More: How to insert values to identity column in SQL Server

2. UPDATE: This command is used to edit rows or modify the records already present in the table.

   Syntax

   
   UPDATE TableName
   SET Column1 = Value1, Column2 = Value2, ...
   WHERE Condition;
   

   Example

   
   UPDATE Employee
   SET EmployeeNamw = 'Surabhi', City= 'Mumbai'
   WHERE EmployeeID = 7;
   

3. DELETE: It is used to remove one or more rows from a table.

   Syntax

   
   DELETE FROM TableName WHERE Condition;
   

   Example

   
   DELETE FROM Employee
   WHERE EmployeeName='Devanshi';
   

Read More:

• Delete Duplicate Rows in SQL Server From a Table
• SQL Server Insert, Retrieve, Update, Delete Operations using Stored Procedures

3. Data Query Language (DQL)

This SQL command is used to fetch/retrieve data from database tables.

In this category, we have only the SELECT command.

1. SELECT: These statements are used to fetch data from a database. The data returned is stored in a result table, called the result-set.

   Syntax

   
   SELECT Column1, Column2, ...ColumN
   FROM TableName;
   
   --(*) is used to select all from the table
   SELECT * FROM table_name;
   

   Example

   
   SELECT EmployeeID, EmployeeName
   FROM Employee;
    
   --(*) is used to select all from the table
   SELECT * FROM Employee;
   

4. Transaction Control Language (TCL)

These SQL commands are used to handle changes affecting the data in the database. We use these commands within the transaction or to make a stable point during changes in the database at which we can roll back the database state if required.

In this category, we have three commands:

1. SAVEPOINT: This command is used to save a transaction temporarily. You can roll the transaction back to a certain point without rolling back the entire transaction.

   Syntax

   
   SAVEPOINT SAVEPOINTNAME;
   

   Example

   
   INSERT INTO Employee VALUES(06, 'Pradnya');
   SAVEPOINT S2;
   

2. ROLLBACK: This command restores the database to the last committed state.

   Syntax

   
   ROLLBACK;
   

   Example

   
   ROLLBACK TO S2;
   SELECT * FROM Employee;
   

3. COMMIT: It saves all the transactions to the database.

   Syntax

   
   COMMIT;
   

   Example

   
   INSERT INTO Employee VALUES(05, 'Sakshi');
   COMMIT;
   

5. Data Control Language (DCL)

These SQL commands are used to implement security on database objects like tables, views, stored procedures, etc. It consists of commands which deal with the user permissions and controls of the database system.

In this category, we have two commands:

1. GRANT: This command permits specific users on specific database objects like table, view, etc.

   Syntax

   
   GRANT PrivilegeName
   ON ObjectName
   TO {UserName |PUBLIC |RoleName}
   [WITH GRANT OPTION];
   

   Here,

   • PrivilegeName: It is the privilege/right/access granted to the user.
   • ObjectName: Name of a database object like TABLE/VIEW/STORED PROC.
   • UserName: Name of the user who is given the access/rights/privileges.
   • PUBLIC: To grant access rights to all users.
   • RoleName: The name of a set of privileges grouped.
   • WITH GRANT OPTION: To give the user access to grant other users with rights.

   Example

   
   GRANT SELECT ON Employee TO user1;
   

2. REVOKE: This command is used to withdraw the user’s access privileges given by the GRANT command.

   Syntax

   
   REVOKE PrivilegeName 
   ON ObjectName 
   FROM {UserName |PUBLIC |RoleName}
   

   Example

   
   REVOKE SELECT ON Employee_Info TO user1; 
   

Summary

SQL (Structured Query Language) instructions control and manipulate data in relational databases. The key commands are SELECT to retrieve data, INSERT INTO to add records, UPDATE to modify existing data, and DELETE to remove records. Additionally, CREATE TABLE and ALTER TABLE assist build the database, whereas JOIN aggregates data from various tables. These instructions transform SQL into a formidable data management and organization tool. If you want to gain a practical understanding, you can enroll in our SQL Server Course.

Do you Know?

.NET is gaining popularity day by day, especially after the release of .NET 8. .NET 8 is not only a framework version but much more than that. It redefines the way software applications are built and deployed, enabling developers to meet the evolving demands of modern computing.

Therefore, if you want to upskill yourselves and stand out from others consider our following training programs on .NET.

• .NET Developer Training With Certification
• ASP.NET Core Certification Training
• ASP.NET Core Course
• .NET Solution Architect Certification Training
• Full-Stack .NET Developer Certification Training Program
• Advanced Full-Stack .NET Developer Certification Training

Take our Sqlserver skill challenge to evaluate yourself!

In less than 5 minutes, with our skill challenge, you can identify your knowledge gaps and strengths in a given skill.

GET FREE CHALLENGE