Detailed SQL Cheat Sheet with Examples
1. SQL Command Categories
SQL commands are grouped into categories:
• DDL (Data Definition Language): CREATE, ALTER, DROP, TRUNCATE
• DML (Data Manipulation Language): SELECT, INSERT, UPDATE, DELETE
• DCL (Data Control Language): GRANT, REVOKE
• TCL (Transaction Control Language): COMMIT, ROLLBACK, SAVEPOINT
Example (DDL):
CREATE TABLE employees (id INT PRIMARY KEY, name VARCHAR(50), salary INT);
2. CREATE TABLE – Detailed
Creating a table with constraints:
CREATE TABLE employees (
id INT PRIMARY KEY,
name VARCHAR(50) NOT NULL,
age INT CHECK(age > 0),
department VARCHAR(20) DEFAULT 'General'
);
3. INSERT – Single & Multiple Rows
INSERT INTO employees (id, name, age, department)
VALUES (1, 'Alice', 25, 'HR');
Insert multiple rows:
INSERT INTO employees VALUES
(2, 'Bob', 30, 'IT'),
(3, 'Carol', 28, 'Finance');
4. SELECT – All Important Patterns
Basic:
SELECT * FROM employees;
Specific Columns:
SELECT name, salary FROM employees;
Using Aliases:
SELECT name AS employee_name, salary AS monthly_salary FROM employees;
Using WHERE:
SELECT * FROM employees WHERE salary > 30000;
Pattern Matching (LIKE):
SELECT * FROM employees WHERE name LIKE 'A%';
5. ORDER BY / LIMIT / DISTINCT
Sort data:
SELECT * FROM employees ORDER BY salary DESC;
Distinct values:
SELECT DISTINCT department FROM employees;
Limit results:
SELECT * FROM employees LIMIT 5;
6. Aggregate Functions (Detailed)
Common aggregate functions:
• COUNT()
• SUM()
• AVG()
• MAX()
• MIN()
Example:
SELECT COUNT(*), AVG(salary) FROM employees;
GROUP BY + HAVING:
SELECT department, COUNT(*)
FROM employees
GROUP BY department
HAVING COUNT(*) > 1;
7. JOINS – All Types with Examples
INNER JOIN:
SELECT [Link], d.dept_name
FROM employees e
INNER JOIN departments d ON e.department_id = [Link];
LEFT JOIN:
SELECT [Link], departments.dept_name
FROM employees
LEFT JOIN departments ON employees.department_id = [Link];
RIGHT JOIN:
SELECT [Link], departments.dept_name
FROM employees
RIGHT JOIN departments ON employees.department_id = [Link];
FULL OUTER JOIN:
SELECT * FROM employees
FULL OUTER JOIN departments ON employees.department_id = [Link];
8. SUBQUERIES – Scalar, IN, EXISTS
Scalar Subquery:
SELECT name FROM employees WHERE salary = (SELECT MAX(salary) FROM employees);
IN Subquery:
SELECT name FROM employees WHERE id IN (SELECT emp_id FROM payroll WHERE paid =
'No');
EXISTS:
SELECT name FROM employees e
WHERE EXISTS (SELECT 1 FROM payroll p WHERE [Link] = p.emp_id);
9. SET Operators
Combine results:
UNION (removes duplicates)
UNION ALL (keeps duplicates)
INTERSECT
EXCEPT / MINUS
Example:
SELECT name FROM employees
UNION
SELECT vendor_name FROM vendors;
10. INDEXES – Detailed Explanation
Indexes speed up queries.
Create index:
CREATE INDEX idx_emp_name ON employees(name);
Drop index:
DROP INDEX idx_emp_name;
11. VIEWS – With Example
Create a view:
CREATE VIEW high_salary AS
SELECT name, salary FROM employees WHERE salary > 50000;
Use the view:
SELECT * FROM high_salary;
12. TRANSACTIONS
Ensures safe changes to DB.
START TRANSACTION;
UPDATE employees SET salary = salary + 5000 WHERE id = 1;
COMMIT; -- Save changes
ROLLBACK; -- Undo changes
13. Stored Procedure (MySQL Syntax)
CREATE PROCEDURE getEmployees()
BEGIN
SELECT * FROM employees;
END;
14. Normalization (Detailed Summary)
1NF: No repeating groups, atomic values.
2NF: No partial dependency.
3NF: No transitive dependency.
BCNF: Every determinant must be a key.
15. DELETE vs TRUNCATE vs DROP
DELETE → Removes rows (can be rolled back).
TRUNCATE → Removes all rows, faster (cannot be rolled back).
DROP → Deletes entire table structure.