In this comprehensive guide, we’ll delve into the different types of SQL statements and explore their applications with real-world examples. Let’s get started.
Introduction: Different Types of SQL Statements
SQL (Structured Query Language) is a powerful tool for managing and manipulating data in relational databases. It offers various types of statements, each serving a specific purpose in database management.
Let’s now look into various types of SQL Statements.
Data Query Language (DQL)
Data Query Language (DQL) is a fundamental component of SQL that focuses on retrieving data from one or more database tables.
SELECT Statement
The SELECT statement is the cornerstone of DQL and is primarily used for retrieving data from one or more database tables. It allows you to specify the columns you want to retrieve and apply filters to fetch specific records.
Example:
-- Retrieve all customer names from the 'Customers' table
SELECT CustomerName FROM Customers;
Read More : Select Query in SQL with Examples
Data Manipulation Language (DML)
DML statements are crucial for modifying data within a database. They include:
INSERT Statement
The INSERT statement is used to add new records into a table.
Example:
-- Insert a new employee into the 'Employees' table
INSERT INTO Employees (EmployeeID, FirstName, LastName) VALUES (101, 'John', 'Doe');
UPDATE Statement
The UPDATE statement allows you to modify existing records in a table.
Example:
-- Update the salary of an employee in the 'Employees' table
UPDATE Employees SET Salary = 60000 WHERE EmployeeID = 101;
DELETE Statement
The DELETE statement is used to remove records from a table.
Example:
-- Delete an employee from the 'Employees' table
DELETE FROM Employees WHERE EmployeeID = 101;
Data Definition Language (DDL)
DDL statements are employed to define the structure of the database, including tables, indexes, and constraints. They include:
CREATE Statement
The CREATE statement is used to create database objects like tables, indexes, or views.
Example:
-- Create a new 'Products' table
CREATE TABLE Products (
ProductID INT PRIMARY KEY,
ProductName VARCHAR(255),
Price DECIMAL(10, 2)
);
ALTER Statement
The ALTER statement allows you to modify existing database objects, such as adding or removing columns from a table.
Example:
-- Add a 'Description' column to the 'Products' table
ALTER TABLE Products ADD Description TEXT;
DROP Statement
The DROP statement is used to delete database objects, such as tables or indexes.
Example:
-- Delete the 'Products' table
DROP TABLE Products;
TRUNCATE Statement
The TRUNCATE statement removes all rows from a table while retaining the table structure.
Example:
-- Remove all records from the 'Products' table
TRUNCATE TABLE Products;
Data Control Language (DCL)
DCL statements are responsible for controlling data visibility and access rights. They include:
GRANT Statement
The GRANT statement provides specific privileges to users or roles, allowing them to perform certain actions on database objects.
Example:
-- Grant SELECT privilege on the 'Orders' table to the 'SalesTeam' role
GRANT SELECT ON Orders TO SalesTeam;
REVOKE Statement
The REVOKE statement revokes previously granted privileges from users or roles.
Example:
-- Revoke INSERT privilege on the 'Customers' table from user 'Alice'
REVOKE INSERT ON Customers FROM Alice;
Transaction Control Language (TCL)
TCL statements are used to manage transactions within a database. They include:
COMMIT Statement
The COMMIT statement is used to permanently save the changes made during a transaction.
Example:
-- Commit the changes made during the current transaction
COMMIT;
ROLLBACK Statement
The ROLLBACK statement is used to undo the changes made during a transaction.
Example:
-- Roll back the current transaction and discard changes
ROLLBACK;
SAVEPOINT Statement
The SAVEPOINT statement sets a point within a transaction to which you can later roll back.
Example:
-- Create a savepoint 'before_update' within the current transaction
SAVEPOINT before_update;
Conclusion: Different Types of SQL Statements
SQL statements play a pivotal role in managing and manipulating data in relational databases. Understanding the different types of SQL statements and their applications is essential for efficient database management
Related Articles :