SQL (Structured Query Language) is the standard language used to interact with databases. It enables users to retrieve, update, delete, and manipulate data stored in a relational database management system (RDBMS). Over time, SQL has evolved into various types to address different needs, such as querying data, managing data, and ensuring database integrity. In this guide, we’ll explore the major types of SQL, their purposes, and how they are used. This exploration will also include examples to provide a practical understanding of each SQL type.
1. Data Definition Language (DDL)
DDL is responsible for defining and managing the structure of the database and its objects such as tables, indexes, and constraints. The main focus of DDL is to create, alter, and delete database objects.
Commands in DDL:
CREATE: Used to create new objects in the database, such as tables, indexes, and views.
Example:
CREATE TABLE Employees (
EmployeeID INT PRIMARY KEY,
Name VARCHAR(50),
Position VARCHAR(50),
HireDate DATE
);
ALTER: Used to modify existing objects within the database, like adding or deleting columns.
Example:
ALTER TABLE Employees ADD Salary DECIMAL(10, 2);
DROP: Used to delete objects from the database.
Example:
DROP TABLE Employees;
TRUNCATE: Deletes all records in a table but keeps the structure intact.
Example:
TRUNCATE TABLE Employees;
DDL commands are auto-committed, meaning that once they are executed, the changes are immediately saved and cannot be undone.
2. Data Manipulation Language (DML)
DML focuses on manipulating the data within the database, such as retrieving, inserting, updating, and deleting records. Unlike DDL, DML deals with the data stored in the database rather than the structure of the database.
Commands in DML:
SELECT: Used to retrieve data from the database. It is the most commonly used DML command.
Example:
SELECT Name, Position FROM Employees WHERE Salary > 50000;
INSERT: Used to add new records to a table.
Example:
INSERT INTO Employees (EmployeeID, Name, Position, HireDate, Salary)
VALUES (101, 'John Doe', 'Software Engineer', '2023-01-15', 65000);
UPDATE: Used to modify existing records.
Example:
UPDATE Employees SET Salary = 70000 WHERE EmployeeID = 101;
DELETE: Used to remove records from a table.
Example:
DELETE FROM Employees WHERE EmployeeID = 101;
Unlike DDL, DML commands are not auto-committed, meaning that changes can be rolled back if needed before committing them to the database.
3. Data Control Language (DCL)
DCL is used to control access to data within a database. It focuses on permissions and ensuring that the right users can access and manipulate the database in a controlled manner. DCL is vital for maintaining the security of a database.
Commands in DCL:
GRANT: Used to give specific privileges to a user or role.
Example:
GRANT SELECT, INSERT ON Employees TO user_name;
REVOKE: Used to remove privileges from a user or role.
Example:
REVOKE SELECT, INSERT ON Employees FROM user_name;
DCL commands are also auto-committed, meaning that the changes are saved immediately after execution.
4. Transaction Control Language (TCL)
TCL deals with managing transactions within a database. A transaction is a series of DML operations that must either all succeed or fail as a unit. TCL ensures that these operations are executed consistently and reliably.
Commands in TCL:
COMMIT: Saves all the changes made during the current transaction permanently to the database.
Example:
COMMIT;
ROLLBACK: Undoes all the changes made during the current transaction if something goes wrong.
Example:
ROLLBACK;
SAVEPOINT: Sets a point within a transaction that you can roll back to without affecting the entire transaction.
Example:
SAVEPOINT savepoint1;
SET TRANSACTION: Defines the properties of a transaction, such as its isolation level.
Example:
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
5. Query Language (QL)
Query language is specifically designed for querying and retrieving data from databases. Though the SELECT statement from DML is the primary query tool, query language is sometimes discussed as a separate type due to its importance. SQL queries allow for the combination of multiple tables, filtering of results, and aggregation of data.
Types of SQL Queries:
Basic Query: Retrieves data based on conditions.
Example:
SELECT * FROM Employees WHERE Position = 'Manager';
JOIN Query: Combines rows from two or more tables based on a related column between them.
Example:
SELECT e.Name, d.DepartmentName
FROM Employees e
JOIN Departments d ON e.DepartmentID = d.DepartmentID;
Aggregate Query: Uses functions like SUM, COUNT, AVG, MAX, and MIN to calculate values over a set of rows.
Example:
SELECT AVG(Salary) FROM Employees WHERE Position = 'Developer';
6. Data Query Language (DQL)
DQL is often considered a subset of DML, but it focuses purely on querying the data. SELECT is the main command in DQL, and its purpose is to retrieve data based on specific criteria. While this overlaps with DML, DQL is sometimes considered a separate category to emphasize the importance of querying in SQL.
Example of DQL:
SELECT Name, Salary FROM Employees WHERE Salary > 60000;
7. Embedded SQL
Embedded SQL refers to the integration of SQL within a host programming language like Java, Python, or C++. This allows developers to interact with databases using SQL commands directly from their code, making database operations dynamic and scalable.
Example of Embedded SQL in Python:
import sqlite3
# Connecting to the database
connection = sqlite3.connect('company.db')
# Creating a cursor object
cursor = connection.cursor()
# Executing an SQL query
cursor.execute("SELECT Name, Position FROM Employees WHERE Salary > 50000")
# Fetching the results
employees = cursor.fetchall()
# Closing the connection
connection.close()
8. Procedural Extensions
Many databases extend the functionality of SQL by offering procedural languages that allow users to write complex scripts combining SQL with control structures like loops and conditionals. Examples include PL/SQL (Procedural Language/SQL) in Oracle databases and T-SQL (Transact-SQL) in Microsoft SQL Server.
Example of a PL/SQL Procedure:
CREATE OR REPLACE PROCEDURE raise_salary(emp_id INT, raise_amount DECIMAL) AS
BEGIN
UPDATE Employees
SET Salary = Salary + raise_amount
WHERE EmployeeID = emp_id;
END;
9. NoSQL Extensions
Though SQL is traditionally used with relational databases, some NoSQL databases like MongoDB and Cassandra now offer SQL-like querying capabilities. These extensions allow users familiar with SQL to query non-relational data using familiar syntax.
Example of SQL-like Query in MongoDB:
db.Employees.find({ "Salary": { $gt: 50000 } });
Conclusion
SQL is a powerful and versatile language, used not only for querying databases but also for defining and manipulating their structures. Its various types—DDL, DML, DCL, TCL, and more—are essential for different aspects of database management, from creating tables to controlling access, and from running complex queries to managing transactions. Understanding these types of SQL is crucial for anyone involved in working with databases, whether they are developers, database administrators, or data analysts. Each type serves a specific purpose, ensuring that SQL remains efficient and reliable across various database operations.
