SQL commands are used in SQL to perform various functions. These functions include building database objects, manipulating objects, populating database tables with data, updating existing data in tables, deleting data, performing database queries, controlling database access, and overall database administration.
The main categories of SQL Commands are,
- Data Definition Language (DDL)
- Data Manipulation Language (DML)
- Data Query Language (DQL)
- Data Control Language (DCL)
- Transaction Control Language (TCL)
Data Definition Language (DDL)
Data Definition Language (DDL) is a set of SQL commands used to define a database's structure, including tables, indexes, and constraints.DDL commands are used to create, modify, and delete database objects. Here are some common DDL commands:
CREATE
This command creates new database objects, such as tables, views, indexes, and constraints.
Example
CREATE TABLE Employee (
EmployeeID INT PRIMARY KEY,
FirstName VARCHAR(50),
LastName VARCHAR(50),
Email VARCHAR(100),
HireDate DATE,
Salary DECIMAL(10, 2),
DepartmentID INT
);
CREATE VIEW RecentOrders AS
SELECT OrderID, OrderDate, CustomerID, TotalAmount
FROM Orders
CREATE INDEX idx_Employee_DepartmentID ON Employee (DepartmentID);
CREATE PROCEDURE InsertOrder
@OrderDate DATE,
@CustomerID INT,
@TotalAmount DECIMAL(10,2)
AS
BEGIN
INSERT INTO Orders (OrderDate, CustomerID, TotalAmount)
VALUES (@OrderDate, @CustomerID, @TotalAmount)
END;
CREATE FUNCTION GetYearsWithCompany (@EmployeeID INT)
RETURNS INT
AS
BEGIN
DECLARE @YearsWithCompany INT;
SELECT @YearsWithCompany = DATEDIFF(YEAR, HireDate, GETDATE())
FROM Employees
WHERE EmployeeID = @EmployeeID;
RETURN @YearsWithCompany;
END;
CREATE TRIGGER OrderAuditTrigger
ON Orders
AFTER INSERT
AS
BEGIN
INSERT INTO OrderAudit (OrderID, OrderDate, CustomerID, TotalAmount)
SELECT OrderID, OrderDate, CustomerID, TotalAmount
FROM inserted;
END;
ALTER
This command is used to modify the structure of existing database objects, such as adding or removing columns from a table or changing the data type of a column.
Examples
ALTER TABLE Employees
ADD EmailAddress varchar(100);
ALTER TABLE Employees
DROP COLUMN EmailAddress;
ALTER TABLE Employees
ALTER COLUMN Salary decimal(10, 2);
ALTER TABLE Employees
ADD CONSTRAINT PK_Employees PRIMARY KEY (EmployeeID);
ALTER VIEW SalesData
AS SELECT ProductID, ProductName, QuantitySold
FROM Sales
WHERE SaleDate BETWEEN '2022-01-01' AND '2022-12-31';
ALTER PROCEDURE GetEmployeesByDepartment
@DepartmentID int
AS
BEGIN
SELECT * FROM Employees WHERE DepartmentID = @DepartmentID;
END;
ALTER INDEX idx_Employees_LastName
ON Employees(LastName, FirstName)
INCLUDE (Email);
ALTER FUNCTION GetTotalSales
(@StartDate DATE, @EndDate DATE)
RETURNS MONEY
AS
BEGIN
DECLARE @TotalSales MONEY;
SELECT @TotalSales = SUM(TotalAmount)
FROM Sales
WHERE SaleDate BETWEEN @StartDate AND @EndDate;
RETURN @TotalSales;
END;
ALTER TRIGGER trg_Employees_Insert
ON Employees
AFTER INSERT
AS
BEGIN
INSERT INTO EmployeeAudit(EmployeeID, AuditDate, EventType)
SELECT EmployeeID, GETDATE(), 'INSERT'
FROM inserted;
END;
DROP
This command deletes an existing database object, such as a table, view, or index.
DROP TABLE Employee;
DROP VIEW Get_EmployeeDetail;
DROP INDEX idx_Employees_Name;
DROP PROCEDURE GetEmployeesByDepartment;
DROP FUNCTION my_function;
DROP TRIGGER my_trigger ON my_table;
TRUNCATE
This command deletes all data from a table but keeps the table structure intact.
TRUNCATE TABLE suppliers;
RENAME
This command renames an existing database object, such as a table or column.
EXEC sp_rename 'old_table_name', 'new_table_name';
EXEC sp_rename 'table_name.old_column_name', 'new_column_name', 'COLUMN';
Note: SQL Server doesn't support the RENAME keyword in the ALTER TABLE statement. Instead, you can use the sp_rename system stored procedure to rename a table and table column.
COMMENT
This command adds comments to a database object, such as a table or column, to provide additional information about the object.
Single-line comments: These comments start with two hyphens "--" and continue until the end of the line. For example:
SELECT * FROM customers -- This is a comment
Multi-line comments: These comments start with "/" and end with "/". They can span multiple lines. For example:
/* This is a
multi-line comment */
In short, DDL commands are used for creating and modifying the structure of a database.
Data Manipulation Language (DML)
Data Manipulation Language (DML) is a set of SQL commands used to manipulate data stored in a database. DML commands retrieve, insert, update, and delete data in tables. Here are some common DML commands:
SELECT
This command retrieves data from one or more tables in a database.
SELECT column1, column2, ..., columnN FROM table_name;
SELECT name, email FROM customers;
SELECT * FROM customers;
INSERT
This command is used to insert new data into a table.
INSERT INTO customers VALUES ('RAJ', '[email protected]', '7019160263');
INSERT INTO customers (name, email, phone) VALUES ('RAJ', '[email protected]', '7019160263');
INSERT INTO OrderDetail (CustomerName, City, Country)
SELECT Name, City, Country FROM Customers;
UPDATE
This command is used to modify existing data in a table.
UPDATE customers
SET email = '[email protected]', first_name = 'Rj'
WHERE id=1
DELETE
This command is used to delete data from a table.
DELETE FROM customers; -- delete all data
DELETE FROM customers -- delete record from customers which id is 5
WHERE id = 5;
MERGE
This command performs insert, update, or delete operations on a target table based on the data in a source table.
-- Insert Merge
MERGE employees AS target
USING employees_new AS source
ON (target.id = source.id)
WHEN NOT MATCHED THEN
INSERT (id, name, salary)
VALUES (source.id, source.name, source.salary);
-- Update Merge
MERGE INTO customers c
USING (
SELECT id, phone, address
FROM customers
WHERE email IN ('[email protected]', '[email protected]', '[email protected]')
) s
ON (c.id = s.id)
WHEN MATCHED THEN
UPDATE SET c.phone = s.phone, c.address = s.address;
-- Delete Merge
MERGE INTO orders o
USING (
SELECT order_id
FROM orders
WHERE order_date < '2022-01-01'
) s
ON (o.order_id = s.order_id)
WHEN MATCHED THEN DELETE;
DML commands are essential for managing the data stored in a database. By using DML commands, users can add, update, or delete data in a table, which is crucial for maintaining the data's accuracy and integrity.
Data Query Language (DQL)
Data Query Language (DQL) is a subset of SQL commands used to retrieve data from one or more tables in a database. DQL commands are also known as data retrieval commands.
Here are some common DQL commands,
SELECT
This command retrieves data from one or more tables in a database.
SELECT column1, column2, ..., columnN FROM table_name;
SELECT name, email FROM Employees;
SELECT * FROM Employees;
DISTINCT
This command is used to retrieve unique values from a column in a table.
SELECT DISTINCT category
FROM products;
WHERE
This command is used to filter data based on specific criteria.
SELECT *
FROM customers
WHERE age > 30;
UPDATE customers
SET email = '[email protected]'
WHERE id = 1;
DELETE
FROM customers
WHERE age > 30 AND email LIKE '%@yahoo.com';
ORDER BY
This command is used to sort data in ascending or descending order.
SELECT *
FROM customers
ORDER BY age DESC;
UPDATE customers
SET age = age + 1
WHERE id IN (
SELECT id
FROM customers
ORDER BY age ASC
);
DELETE FROM customers
WHERE age > 50
ORDER BY age DESC;
GROUP BY
This command is used to group data based on one or more columns.
SELECT product, SUM(quantity * price) as total_sales
FROM sales
GROUP BY product;
UPDATE sales
SET price = (
SELECT AVG(price)
FROM sales
WHERE product = sales.product
)
WHERE product IN (
SELECT product
FROM sales
GROUP BY product
);
DELETE FROM sales
WHERE product IN (
SELECT product
FROM sales
GROUP BY product
HAVING COUNT(*) = 1
);
JOIN
This command combines data from two or more tables into a single result set.
SELECT orders.id, customers.name, orders.product, orders.quantity, orders.price
FROM orders
INNER JOIN customers ON orders.customer_id = customers.id;
SELECT departments.name AS department_name, employees.name AS employee_name
FROM departments
RIGHT JOIN employees ON departments.id = employees.department_id;
SELECT employees.name AS employee_name, departments.name AS department_name
FROM employees
LEFT JOIN departments ON employees.department_id = departments.id;
SELECT employees.name AS employee_name, departments.name AS department_name
FROM employees
FULL JOIN departments ON employees.department_id = departments.id;
SELECT A.EmployeeName AS EmployeeName1, B.EmployeeName AS EmployeeName2, A.City
FROM Employee A, Employee B
WHERE A.EmployeeID <> B.EmployeeID
AND A.City = B.City
ORDER BY A.City;
DQL commands are essential for retrieving data from a database. Using DQL commands, users can filter, sort, and group data based on specific criteria, which is crucial for analyzing and interpreting the data stored in the database.
Data Control Language (DCL)
Data Control Language (DCL) is a set of SQL commands used to control access to a database. DCL commands are used to grant or revoke permissions to users and roles.
Here are some common DCL commands:
GRANT
This command is used to grant permissions to a user or a role.
GRANT SELECT ON mydatabase.mytable TO myuser;
GRANT SELECT, INSERT, UPDATE ON mydatabase.mytable TO myuser;
GRANT SELECT, INSERT ON mydatabase.* TO myuser;
REVOKE
This command is used to revoke permissions from a user or a role.
REVOKE SELECT ON mydatabase.mytable FROM myuser;
REVOKE SELECT, INSERT, UPDATE ON mydatabase.mytable FROM myuser;
REVOKE ALL PRIVILEGES ON mydatabase.* FROM myuser;
REVOKE SELECT, INSERT ON mydatabase.* FROM myuser;
DENY
This command is used to deny permissions to a user or a role.
DENY SELECT ON mydatabase.mytable TO myuser;
DENY SELECT, INSERT, UPDATE ON mydatabase.mytable TO myuser;
DCL commands are essential for managing access to a database. Using DCL commands, database administrators can control who has access to the database and what actions they can perform on the data stored in the database.
This is critical for maintaining the security and integrity of the data stored in the database.
Transaction Control Language (TCL)
Transaction Control Language (TCL) is a set of SQL commands used to manage transactions in a database. A transaction is a sequence of one or more SQL statements treated as a single unit of work. TCL commands are used to commit or rollback transactions. Here are some common TCL commands:
COMMIT
This command permanently saves the changes made by a transaction to the database.
CREATE PROCEDURE update_employee_salary
@employee_id INT,
@new_salary DECIMAL(10, 2)
AS
BEGIN
BEGIN TRANSACTION;
UPDATE company.employees
SET salary = @new_salary
WHERE id = @employee_id;
COMMIT;
END
ROLLBACK
This command is used to undo the changes made by a transaction and restore the database to its previous state.
CREATE PROCEDURE update_employee_salary
@employee_id INT,
@new_salary DECIMAL(10, 2)
AS
BEGIN
BEGIN TRANSACTION;
UPDATE company.employees
SET salary = @new_salary
WHERE id = @employee_id;
IF @@ERROR <> 0
BEGIN
ROLLBACK;
RETURN;
END;
COMMIT;
END
SAVEPOINT
This command is used to set a savepoint within a transaction, which allows you to roll back to a specific point in the transaction.
CREATE PROCEDURE transfer_funds
@from_account INT,
@to_account INT,
@amount DECIMAL(10, 2)
AS
BEGIN
BEGIN TRANSACTION;
-- Savepoint
SAVE TRANSACTION transfer_start;
UPDATE bank.accounts
SET balance = balance - @amount
WHERE id = @from_account;
IF @@ERROR <> 0
BEGIN
-- Rollback to savepoint
ROLLBACK TRANSACTION transfer_start;
RETURN;
END;
UPDATE bank.accounts
SET balance = balance + @amount
WHERE id = @to_account;
IF @@ERROR <> 0
BEGIN
-- Rollback entire transaction
ROLLBACK;
RETURN;
END;
COMMIT;
END
RELEASE SAVEPOINT
This command is used to remove a savepoint within a transaction.
CREATE PROCEDURE example_procedure
AS
BEGIN
BEGIN TRANSACTION;
-- Perform some operations
INSERT INTO myTable (column1, column2) VALUES (1, 'A');
INSERT INTO myTable (column1, column2) VALUES (2, 'B');
INSERT INTO myTable (column1, column2) VALUES (3, 'C');
-- Set a savepoint
SAVE TRANSACTION mySavepoint;
-- More operations
INSERT INTO myTable (column1, column2) VALUES (4, 'D');
INSERT INTO myTable (column1, column2) VALUES (5, 'E');
-- Check for errors
IF @@ERROR <> 0
BEGIN
-- Rollback to savepoint
ROLLBACK TRANSACTION mySavepoint;
RETURN;
END;
-- Mark the savepoint as complete
RELEASE SAVEPOINT mySavepoint;
COMMIT;
END;
TCL commands are essential for managing transactions in a database. Using TCL commands, users can ensure that changes made to the database are consistent and reliable, even if there are errors or failures during the transaction. This is critical for maintaining the integrity of the data stored in the database.
Note
it's generally a good practice to use transactions in stored procedures to ensure data integrity and prevent data corruption. Using transactions and committing or rolling back changes as needed can help ensure that your database remains consistent and reliable.
Conclusion
I hope the blog has helped you understand SQL commands.