Types Of SQL Commands

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.