SQL Commands (DDL, DML, DCL, TCL, DQL): Types, Syntax, and Examples

Overview

SQL, which stands for Structured Query Language, is a powerful language used for managing and manipulating relational databases. In this comprehensive guide, we will delve into SQL commands, their types, syntax, and practical examples to empower you with the knowledge to interact with databases effectively.

What is SQL?

SQL, or Structured Query Language, is a domain-specific language designed for managing and querying relational databases. It provides a standardized way to interact with databases, making it an essential tool for anyone working with data.

Understanding SQL Commands

Categorization of SQL Commands

SQL commands can be categorized into four main types:

Data Definition Language (DDL) Commands

What is DDL?

DDL, or Data Definition Language, is a subset of SQL used to define and manage the structure of database objects. DDL commands are typically executed once to set up the database schema.

DDL commands are used to define, modify, and manage the structure of database objects, such as tables, indexes, and constraints. Some common DDL commands include:

• CREATE TABLE: Used to create a new table.
• ALTER TABLE: Used to modify an existing table’s structure.
• DROP TABLE: Used to delete a table.
• CREATE INDEX: Used to create an index on a table, improving query performance.

DDL commands play a crucial role in defining the database schema.

Data Manipulation Language (DML) Commands

DML commands are used to retrieve, insert, update, and delete data in the database. Common DML commands include:

• SELECT: Used to retrieve data from one or more tables.
• INSERT: Used to add new records to a table.
• UPDATE: Used to modify existing records in a table.
• DELETE: Used to remove records from a table.

DML commands are essential for managing the data stored in a database.

Data Control Language (DCL) Commands

DCL commands are used to manage database security and access control. The two primary DCL commands are:

• GRANT: Used to grant specific privileges to database users or roles.
• REVOKE: Used to revoke previously granted privileges.

DCL commands ensure that only authorized users can access and modify the database.

Transaction Control Language (TCL) Commands

TCL commands are used to manage database transactions, ensuring data integrity. Key TCL commands include:

• COMMIT: Commits a transaction, saving changes permanently.
• ROLLBACK: Undoes changes made during a transaction.
• SAVEPOINT: Sets a point within a transaction to which you can later roll back.

TCL commands are vital for maintaining the consistency of data in a database.

Common DDL Commands

CREATE TABLE

The CREATE TABLE command is used to define a new table in the database. Here’s an example:

CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    FirstName VARCHAR(50),
    LastName VARCHAR(50),
    ...
);

This command defines a table called “Employees” with columns for employee ID, first name, last name, and more.

ALTER TABLE

The ALTER TABLE command allows you to modify an existing table. For instance, you can add a new column or modify the data type of an existing column:

ALTER TABLE Employees
ADD Email VARCHAR(100);

This adds an “Email” column to the “Employees” table.

DROP TABLE

The DROP TABLE command removes a table from the database:

DROP TABLE Employees;

This deletes the “Employees” table and all its data.

CREATE INDEX

The CREATE INDEX command is used to create an index on one or more columns of a table, improving query performance:

CREATE INDEX idx_LastName ON Employees(LastName);

This creates an index on the “LastName” column of the “Employees” table.

DDL Commands in SQL with Examples

Here are code snippets and their corresponding outputs for DDL commands:

SQL Command	Code Snippet	Output
CREATE TABLE	CREATE TABLE Employees ( EmployeeID INT PRIMARY KEY, FirstName VARCHAR(50), LastName VARCHAR(50), Department VARCHAR(50) );	New “Employees” table created with specified columns.
ALTER TABLE	ALTER TABLE Employees ADD Email VARCHAR(100);	“Email” column added to the “Employees” table.
DROP TABLE	DROP TABLE Employees;	“Employees” table and its data deleted.

Data Manipulation Language (DML) Commands

What is DML?

DML, or Data Manipulation Language, is a subset of SQL used to retrieve, insert, update, and delete data in a database. DML commands are fundamental for working with the data stored in tables.

Common DML Commands

SELECT

The SELECT statement retrieves data from one or more tables based on specified criteria:

SELECT FirstName, LastName FROM Employees WHERE Department = 'Sales';

This query selects the first and last names of employees in the “Sales” department.

INSERT

The INSERT statement adds new records to a table:

INSERT INTO Employees (FirstName, LastName, Department) VALUES ('John', 'Doe', 'HR');

This inserts a new employee record into the “Employees” table.

UPDATE

The UPDATE statement modifies existing records in a table:

UPDATE Employees SET Salary = Salary * 1.1 WHERE Department = ‘Engineering’;

This increases the salary of employees in the “Engineering” department by 10%.

DELETE

The DELETE statement removes records from a table:

DELETE FROM Employees WHERE Department = 'Finance';

This deletes employees from the “Finance” department.

DML Commands in SQL with Examples

Here are code snippets and their corresponding outputs for DML commands:

SQL Command	Code Snippet	Output
SELECT	SELECT FirstName, LastName FROM Employees WHERE Department = 'Sales';	Retrieves the first and last names of employees in the “Sales” department.
INSERT	INSERT INTO Employees (FirstName, LastName, Department) VALUES ('John', 'Doe', 'HR');	New employee record added to the “Employees” table.
UPDATE	UPDATE Employees SET Salary = Salary * 1.1 WHERE Department = 'Engineering';	Salary of employees in the “Engineering” department increased by 10%.
DELETE	DELETE FROM Employees WHERE Department = 'Finance';	Employees in the “Finance” department deleted.

Data Control Language (DCL) Commands

What is DCL?

DCL, or Data Control Language, is a subset of SQL used to manage database security and access control. DCL commands determine who can access the database and what actions they can perform.

Common DCL Commands

GRANT

The GRANT command is used to grant specific privileges to database users or roles:

GRANT SELECT, INSERT ON Employees TO HR_Manager;

This grants the “HR_Manager” role the privileges to select and insert data into the “Employees” table.

REVOKE

The REVOKE command is used to revoke previously granted privileges:

REVOKE DELETE ON Customers FROM Sales_Team;

This revokes the privilege to delete data from the “Customers” table from the “Sales_Team” role.

DCL Commands in SQL with Examples

Here are code snippets and their corresponding real-value outputs for DCL commands:

SQL Command	Code Snippet	Output (Real Value Example)
GRANT	GRANT SELECT, INSERT ON Employees TO HR_Manager;	“HR_Manager” role granted privileges to select and insert data in the “Employees” table.
REVOKE	REVOKE DELETE ON Customers FROM Sales_Team;	Privilege to delete data from the “Customers” table revoked from the “Sales_Team” role.

Transaction Control Language (TCL) Commands

What is TCL?

TCL, or Transaction Control Language, is a subset of SQL used to manage database transactions. TCL commands ensure data integrity by allowing you to control when changes to the database are saved permanently or rolled back.

Common TCL Commands

COMMIT

The COMMIT command is used to save changes made during a transaction to the database permanently:

BEGIN;
-- SQL statements
COMMIT;

This example begins a transaction, performs SQL statements, and then commits the changes to the database.

ROLLBACK

The ROLLBACK command is used to undo changes made during a transaction:

BEGIN;
-- SQL statements
ROLLBACK;

This example begins a transaction, performs SQL statements, and then rolls back the changes, restoring the database to its previous state.

SAVEPOINT

The SAVEPOINT command allows you to set a point within a transaction to which you can later roll back:

BEGIN;
-- SQL statements
SAVEPOINT my_savepoint;
-- More SQL statements
ROLLBACK TO my_savepoint;

This example creates a savepoint and later rolls back to that point, undoing some of the transaction’s changes.

TCL Commands in SQL with Examples

Here are code snippets and their corresponding outputs for TCL commands:

SQL Command	Code Snippet	Output
COMMIT	BEGIN; -- SQL statements COMMIT;	Changes made in the transaction saved permanently.
ROLLBACK	BEGIN; -- SQL statements ROLLBACK;	Changes made in the transaction rolled back.
SAVEPOINT	BEGIN; -- SQL statements SAVEPOINT my_savepoint; -- More SQL statements ROLLBACK TO my_savepoint;	Savepoint created and later used to roll back to a specific point in the transaction.

Data Query Language (DQL) Commands

What is DQL?

Data Query Language (DQL) is a critical subset of SQL (Structured Query Language) used primarily for querying and retrieving data from a database. While SQL encompasses a range of commands for data manipulation, DQL commands are focused exclusively on data retrieval.

Data Query Language (DQL) forms the foundation of SQL and is indispensable for retrieving and analyzing data from relational databases. With a solid understanding of DQL commands and concepts, you can extract valuable insights and generate reports that drive informed decision-making. Whether you’re a database administrator, data analyst, or software developer, mastering DQL is essential for effectively working with databases.

Purpose of DQL

The primary purpose of DQL is to allow users to extract meaningful information from a database. Whether you need to retrieve specific records, filter data based on certain conditions, or aggregate and sort results, DQL provides the tools to do so efficiently. DQL plays a crucial role in various database-related tasks, including:

• Generating reports
• Extracting statistical information
• Displaying data to users
• Answering complex business queries

Common DQL Commands

SELECT Statement

The SELECT statement is the cornerstone of DQL. It allows you to retrieve data from one or more tables in a database. Here’s the basic syntax of the SELECT statement:

SELECT column1, column2, ...FROM table_nameWHERE condition;

• column1, column2, …: The columns you want to retrieve from the table.
• table_name: The name of the table from which you want to retrieve data.
• condition (optional): The condition that specifies which rows to retrieve. If omitted, all rows will be retrieved.

Example: Retrieving Specific Columns

SELECT FirstName, LastNameFROM Employees;

This query retrieves the first and last names of all employees from the “Employees” table.

Example: Filtering Data with a Condition

SELECT ProductName, UnitPriceFROM ProductsWHERE UnitPrice > 50;

This query retrieves the names and unit prices of products from the “Products” table where the unit price is greater than 50.

DISTINCT Keyword

The DISTINCT keyword is used in conjunction with the SELECT statement to eliminate duplicate rows from the result set. It ensures that only unique values are returned.

Example: Using DISTINCT

SELECT DISTINCT CountryFROM Customers;

This query retrieves a list of unique countries from the “Customers” table, eliminating duplicate entries.

ORDER BY Clause

The ORDER BY clause is used to sort the result set based on one or more columns in ascending or descending order.

Example: Sorting Results

SELECT ProductName, UnitPriceFROM ProductsORDER BY UnitPrice DESC;

This query retrieves product names and unit prices from the “Products” table and sorts them in descending order of unit price.

Aggregate Functions

DQL supports various aggregate functions that allow you to perform calculations on groups of rows and return single values. Common aggregate functions include COUNT, SUM, AVG, MIN, and MAX.

Example: Using Aggregate Functions

SELECT AVG(UnitPrice) AS AveragePriceFROM Products;

This query calculates the average unit price of products in the “Products” table.

JOIN Operations

DQL enables you to combine data from multiple tables using JOIN operations. INNER JOIN, LEFT JOIN, RIGHT JOIN, and FULL OUTER JOIN are common types of joins.

Example: Using INNER JOIN

SELECT Orders.OrderID, Customers.CustomerNameFROM OrdersINNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID;

This query retrieves order IDs and customer names by joining the “Orders” and “Customers” tables based on the “CustomerID” column.

Grouping Data with GROUP BY

The GROUP BY clause allows you to group rows that share a common value in one or more columns. You can then apply aggregate functions to each group.

Example: Grouping and Aggregating Data

SELECT Country, COUNT(*) AS CustomerCountFROM CustomersGROUP BY Country;

This query groups customers by country and calculates the count of customers in each country.

Advanced DQL Concepts

Subqueries

Subqueries, also known as nested queries, are queries embedded within other queries. They can be used to retrieve values that will be used in the main query.

Example: Using a Subquery

SELECT ProductNameFROM ProductsWHERE CategoryID IN (SELECT CategoryID FROM Categories WHERE CategoryName = 'Beverages');

This query retrieves the names of products in the “Beverages” category using a subquery to find the category ID.

Views

Views are virtual tables created by defining a query in SQL. They allow you to simplify complex queries and provide a consistent interface to users.

Example: Creating a View

CREATE VIEW ExpensiveProducts ASSELECT ProductName, UnitPriceFROM ProductsWHERE UnitPrice > 100;

This query creates a view called “ExpensiveProducts” that includes product names and unit prices for products with a unit price greater than 100.

Window Functions

Window functions are used to perform calculations across a set of rows related to the current row within the result set. They are often used for tasks like calculating cumulative sums and ranking rows.

Example: Using a Window Function

SELECT OrderID, ProductID, UnitPrice,SUM(UnitPrice) OVER (PARTITION BY OrderID) AS TotalPricePerOrderFROM OrderDetails;

This query calculates the total price per order using a window function to partition the data by order.

SQL Command Categories

Overview of SQL Command Categories

SQL commands can be categorized into five primary types, each serving a distinct purpose in database management. Understanding these categories is essential for efficient and effective database operations:

1. Data Definition Language (DDL) Commands

DDL commands are used to define, modify, and manage the structure of database objects. These commands are primarily concerned with the database schema—the way data is organized within the database. Common DDL commands include:

• CREATE TABLE: Used to create new tables in the database.
• ALTER TABLE: Enables modifications to the structure of an existing table.
• DROP TABLE: Removes a table and all its data from the database.
• CREATE INDEX: Creates an index on one or more columns to improve query performance.
• CREATE VIEW: Defines a virtual table based on the result of a query.

DDL commands play a pivotal role in shaping the database’s architecture and schema.

2. Data Manipulation Language (DML) Commands

DML commands are instrumental in retrieving, inserting, updating, and deleting data within database tables. These commands focus on manipulating the actual data stored in the database. Common DML commands include:

• SELECT: Retrieves data from one or more tables based on specified criteria.
• INSERT: Adds new records or rows to a table.
• UPDATE: Modifies existing records in a table.
• DELETE: Removes one or more rows from a table.

DML commands are crucial for managing the contents of the database.

3. Data Control Language (DCL) Commands

DCL commands are responsible for managing database security and controlling access to database objects. They determine which users or roles can access the database and what actions they can perform. Common DCL commands include:

• GRANT: Provides specific privileges to users or roles, allowing them to perform certain actions on database objects.
• REVOKE: Withdraws previously granted privileges, restricting users or roles from performing specific actions.

DCL commands are pivotal for enforcing security policies and access controls within the database.

4. Transaction Control Language (TCL) Commands

TCL commands are indispensable for managing database transactions, ensuring data integrity and consistency. These commands allow you to control when changes made to the database are saved permanently or rolled back. Key TCL commands include:

• COMMIT: Saves changes made during a transaction to the database permanently.
• ROLLBACK: Undoes changes made during a transaction, reverting the database to its previous state.
• SAVEPOINT: Establishes a point within a transaction to which you can later roll back.

TCL commands are essential for maintaining the accuracy and reliability of data.

5. Data Query Language (DQL) Commands

DQL commands focus exclusively on retrieving data from the database. While the SELECT statement is the most prominent DQL command, it plays a critical role in extracting and presenting data from one or more tables based on specific criteria. DQL commands enable you to obtain valuable insights from the stored data.

SQL commands encompass a diverse set of categories, each tailored to a specific aspect of database management. Whether you’re defining database structures (DDL), manipulating data (DML), controlling access (DCL), managing transactions (TCL), or querying for information (DQL), SQL provides the tools you need to interact with relational databases effectively. Understanding these categories empowers you to choose the right SQL command for the task at hand, making you a more proficient database professional.

Differentiating DDL, DML, DCL, TCL and DQL Commands

Each category of SQL commands serves a specific purpose:

• DDL commands define and manage the database structure.
• DML commands manipulate data within the database.
• DCL commands control access and security.
• TCL commands manage transactions and data integrity.
• DQL commands are dedicated to retrieving data from the database.

Basic SQL Queries

Introduction to Basic SQL Queries

Basic SQL queries are essential for retrieving and displaying data from a database. They form the foundation of many complex database operations.

Examples of Basic SQL Queries

SELECT Statement

The SELECT statement is used to retrieve data from one or more tables. Here’s a simple example:

SELECT * FROM Customers;

This query retrieves all columns from the “Customers” table.

Filtering Data with WHERE

You can filter data using the WHERE clause.

SELECT * FROM Employees WHERE Department = 'Sales';

This query retrieves all employees from the “Employees” table who work in the “Sales” department.

Sorting Data with ORDER BY

The ORDER BY clause is used to sort the result set.

SELECT * FROM Products ORDER BY Price DESC;

This query retrieves all products from the “Products” table and sorts them in descending order of price.

Aggregating Data with GROUP BY

You can aggregate data using the GROUP BY clause.

SELECT Department, AVG(Salary) AS AvgSalary FROM Employees GROUP BY Department;

This query calculates the average salary for each department in the “Employees” table.

Combining Conditions with AND/OR

You can combine conditions using AND and OR.

SELECT * FROM Orders WHERE (CustomerID = 1 AND OrderDate >= '2023-01-01') OR TotalAmount > 1000;

This query retrieves orders where either the customer ID is 1, and the order date is on or after January 1, 2023, or the total amount is greater than 1000.

Limiting Results with LIMIT

The LIMIT clause is used to limit the number of rows returned.

SELECT * FROM Products LIMIT 10;

This query retrieves the first 10 rows from the “Products” table.

Combining Tables with JOIN

You can combine data from multiple tables using JOIN.

SELECT Customers.CustomerName, Orders.OrderDate FROM Customers INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID;

This query retrieves the customer names and order dates for customers who have placed orders by joining the “Customers” and “Orders” tables on the CustomerID.

These examples of basic SQL queries cover common scenarios when working with a relational database. SQL queries can be customized and extended to suit the specific needs of your database application.

SQL Cheat Sheet

A SQL cheat sheet provides a quick reference for essential SQL commands, syntax, and usage. It’s a handy tool for both beginners and experienced SQL users. It can be a handy tool for SQL developers and database administrators to access SQL syntax and examples quickly.

Here’s a complete SQL cheat sheet, which includes common SQL commands and their explanations:

SQL Command	Description	Example
SELECT	Retrieves data from a table.	SELECT FirstName, LastName FROM Employees;
FILTERING with WHERE	Filters rows based on a specified condition.	SELECT ProductName, Price FROM Products WHERE Price > 50;
SORTING with ORDER BY	Sorts the result set in ascending (ASC) or descending (DESC) order.	SELECT ProductName, Price FROM Products ORDER BY Price DESC;
AGGREGATION with GROUP BY	Groups rows with the same values into summary rows and applies aggregate functions.	SELECT Department, AVG(Salary) AS AvgSalary FROM Employees GROUP BY Department;
COMBINING CONDITIONS	Combines conditions using AND and OR operators.	SELECT * FROM Orders WHERE (CustomerID = 1 AND OrderDate >= '2023-01-01') OR TotalAmount > 1000;
LIMITING RESULTS	Limits the number of rows returned with LIMIT and skips rows with OFFSET.	SELECT * FROM Products LIMIT 10 OFFSET 20;
JOINING TABLES with JOIN	Combines data from multiple tables using JOIN.	SELECT Customers.CustomerName, Orders.OrderDate FROM Customers INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID;
INSERT INTO	Inserts new records into a table.	INSERT INTO Employees (FirstName, LastName, Department) VALUES ('John', 'Doe', 'HR');
UPDATE	Modifies existing records in a table.	UPDATE Employees SET Salary = Salary * 1.1 WHERE Department = 'Engineering';
DELETE	Removes records from a table.	DELETE FROM Employees WHERE Department = 'Finance';
GRANT	Grants privileges to users or roles.	GRANT SELECT, INSERT ON Employees TO HR_Manager;
REVOKE	Revokes previously granted privileges.	REVOKE DELETE ON Customers FROM Sales_Team;
BEGIN, COMMIT, ROLLBACK	Manages transactions: BEGIN starts, COMMIT saves changes permanently, and ROLLBACK undoes changes and rolls back.	BEGIN; -- SQL statements COMMIT;

SQL Language Types and Subsets

Exploring SQL Language Types and Subsets

SQL is a versatile language with various types and subsets designed for specific database systems and use cases. Understanding these distinctions can help you choose the right SQL variant for your needs.

Embedded SQL in DBMS

Understanding Embedded SQL and its Usage

Embedded SQL allows SQL statements to be embedded within programming languages like Java, C++, or Python. This integration facilitates database interactions within application code.

SQL Examples and Practice

More SQL Query Examples for Practice

Practicing SQL with real-world examples is crucial for mastering the language. Our article onSQL Examples and Practice offers additional SQL queries and exercises to sharpen your skills.

SQL Documentation and Resources

Where to Find Comprehensive SQL Documentation and Resources

To continue your SQL learning journey, it’s essential to know where to find reliable documentation and resources. Our guide directs you to valuable SQL learning materials.

Conclusion

In conclusion, SQL commands are the foundation of effective database management. Whether you’re defining database structures, manipulating data, controlling access, or managing transactions, SQL provides the tools you need. With this comprehensive guide, you’ve gained a deep understanding of SQL commands, their categories, syntax, and practical examples.

Glossary

• SQL: Structured Query Language, a domain-specific language for managing relational databases.
• DDL: Data Definition Language, a subset of SQL for defining and managing database structures.
• DML: Data Manipulation Language, a subset of SQL for retrieving, inserting, updating, and deleting data.
• DCL: Data Control Language, a subset of SQL for managing database security and access control.
• TCL: Transaction Control Language, a subset of SQL for managing database transactions.
• DQL: Data Query Language, a subset of SQL focused solely on retrieving and querying data from the database.

References

For further reading and in-depth exploration of specific SQL topics, please refer to the following references:

Source: GreatLearning Blog