SQL Components

DQL consists of commands that retrieve data from a database, mainly using SELECT.

SELECT column1, column2 FROM table_name WHERE condition;

SELECT * FROM table_name;

It filters records based on a condition.

Example:

SELECT * FROM Employees WHERE Department = 'HR';

It sorts query results in ascending (ASC) or descending (DESC) order.

SELECT * FROM Employees ORDER BY Salary DESC;

• DISTINCT removes duplicate values.
• GROUP BY groups rows with the same values.

Example:

SELECT DISTINCT Department FROM Employees;  
SELECT Department, COUNT(*) FROM Employees GROUP BY Department;

It restricts the number of rows returned.

SELECT * FROM Employees LIMIT 5;  -- MySQL, PostgreSQL  
SELECT TOP 5 * FROM Employees;  -- SQL Server  

HAVING filters after GROUP BY.

SELECT Department, COUNT(*) FROM Employees GROUP BY Department HAVING COUNT(*) > 5;

It performs pattern matching in string searches.

SELECT * FROM Customers WHERE Name LIKE 'A%';  -- Names starting with A

It filters values within a range.

SELECT * FROM Orders WHERE OrderDate BETWEEN '2023-01-01' AND '2023-12-31';

DDL consists of commands that define and modify database schema, like CREATE, ALTER, DROP, and TRUNCATE.

CREATE TABLE Employees (
    ID INT PRIMARY KEY,
    Name VARCHAR(50),
    Salary DECIMAL(10,2),
    Department VARCHAR(30)
);

Add a column:

ALTER TABLE Employees ADD Age INT;

Modify a column:

ALTER TABLE Employees MODIFY COLUMN Age SMALLINT;

• DROP TABLE table_name; removes the entire table.
• TRUNCATE TABLE table_name; deletes all records but keeps the table structure.

ALTER TABLE Employees RENAME TO Staff;

• PRIMARY KEY: Uniquely identifies each row and cannot be NULL.
• UNIQUE: Ensures uniqueness but allows one NULL value.

CREATE TABLE Orders (
    OrderID INT PRIMARY KEY,
    CustomerID INT,
    FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
);

ALTER TABLE Employees DROP COLUMN Age;

DESCRIBE Employees;  -- MySQL  
EXEC sp_help Employees;  -- SQL Server  

CREATE INDEX idx_name ON Employees(Name);

DML modifies data using INSERT, UPDATE, and DELETE.

INSERT INTO Employees (ID, Name, Salary, Department) VALUES (1, 'Rahul', 50000, 'HR');

UPDATE Employees SET Salary = 55000 WHERE ID = 1;

DELETE FROM Employees WHERE ID = 1;

• DELETE removes specific rows.
• TRUNCATE removes all rows and resets auto-increment counters

DCL manages user permissions using GRANT and REVOKE.

GRANT SELECT, INSERT ON Employees TO 'user1'@'localhost';

REVOKE INSERT ON Employees FROM 'user1'@'localhost';

SHOW GRANTS FOR 'user1'@'localhost';

TCL manages transactions using COMMIT, ROLLBACK, and SAVEPOINT.

START TRANSACTION;

COMMIT;

ROLLBACK;

It allows partial rollbacks within a transaction.

SAVEPOINT sp1;
ROLLBACK TO sp1;

• COMMIT saves changes.
• ROLLBACK undoes changes.

SELECT @@tx_isolation;  -- MySQL  
DBCC USEROPTIONS;  -- SQL Server