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. Types of SQL Commands are as follows:

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 six 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, do 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 the following 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.

1. SELECT

The SELECT command is used to retrieve data from a database. You can fetch all columns or specific columns from one or more tables.

SELECT column1, column2, ... FROM table_name;

2. SELECT DISTINCT

Retrieves unique values from a column or a combination of columns, eliminating duplicate entries.

SELECT DISTINCT column1, column2, ... FROM table_name;

3. WHERE

Used to filter rows based on specified conditions. Only rows that satisfy the condition(s) are included in the result.

SELECT column1, column2, ... FROM table_name WHERE condition;

4. ORDER BY

Sorts the result set based on one or more columns in ascending (ASC) or descending (DESC) order.

SELECT column1, column2, ... FROM table_name ORDER BY column1 [ASC|DESC];

5. GROUP BY

Groups rows sharing the same values in specified columns and allows aggregate functions (e.g., SUM, AVG, COUNT) to be applied to each group.

SELECT column1, aggregate_function(column2) FROM table_name GROUP BY column1;

6. HAVING

Filters grouped data based on specified conditions, often used with GROUP BY.

SELECT column1, aggregate_function(column2) FROM table_name GROUP BY column1 HAVING condition;

7. LIMIT

Restricts the number of rows returned by a query.

SELECT column1, column2, ... FROM table_name LIMIT number_of_rows;

8. JOIN

Combines rows from two or more tables based on a related column, enabling retrieval of data across multiple tables.

SELECT table1.column1, table2.column2, ... FROM table1 JOIN table2 ON table1.column_name = table2.column_name;

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 main 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 main 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