SQL Commands

Selection

SELECT name, salary
FROM employees
WHERE condition;

SELECT name AS employee_name
    salary AS annual_salary
FROM employees;

SELECT DISTINCT department, age
FROM employees;

SELECT *
FROM employees
WHERE department = 'IT'
OR department = 'HR';

SELECT *
FROM employees
WHERE NOT department = 'HR';

WHERE department IN ('IT', 'HR', 'Sales');  # Cleaner syntax

SELECT *
FROM employees
WHERE salary BETWEEN 60000 AND 80000;

Pattern Matching

WHERE name LIKE 'A%';
WHERE name LIKE 'A_i_e';

ORDER BY

SELECT *
FROM employees
ORDER BY salary DESC;

LIMIT

SELECT *
FROM employees
ORDER BY salary DESC
LIMIT 3;

AGGREGATE FUNCTIONS

SELECT COUNT(*)
FROM employees;

SELECT SUM(salary)
FROM employees;

SELECT AVG(salary)
FROM employees;

SELECT MIN(salary)
FROM employees;

SELECT MAX(salary)
FROM employees;

NULL

WHERE salary IS NULL;
WHERE salary IS NOT NULL;

GROUP BY

SELECT department, AVG(salary)
FROM employees
GROUP BY department;

SELECT department, AVG(salary)
FROM employees
GROUP BY department
HAVING AVG(salary) > 60000;

SELECT department, AVG(salary)
FROM employees
WHERE age > 25
GROUP BY department
HAVING AVG(salary) > 60000
ORDER BY AVG(salary) DESC;

CASE

SELECT name,
    salary,
    CASE
        when salary >= 75000 THEN 'High'
        when salary >= 60000 THEN 'Medium'
    END AS salary_category
FROM employees;

SELECT department,
    SUM(CASE
        WHEN salary > 60000 THEN 1
        ELSE 0
        END) AS high_salary_count
FROM employees
GROUP BY department;

Query skeleton

SELECT columns
FROM table_a AS a
INNER JOIN table_b AS b
    ON b.id = a.foreign_id
WHERE row_condition
GROUP BY grouping_columns
HAVING aggregate_condition
ORDER BY sorting_columns
LIMIT number;

Execution order

FROM / JOIN
WHERE
GROUP BY
HAVING
SELECT
DISTINCT
ORDER BY
LIMIT

Filtering

WHERE salary >= 70000
WHERE city IS NULL
WHERE city IS NOT NULL
WHERE category IN ('Memory', 'Storage')
WHERE salary BETWEEN 70000 AND 100000
WHERE employee_name LIKE 'A%'

Aggregation

SELECT department_id, COUNT(*) AS employee_count
FROM employees
GROUP BY department_id;

SELECT customer_id, COUNT(*) AS completed_orders
FROM orders
WHERE status = 'Completed'
GROUP BY customer_id
HAVING COUNT(*) >= 2;

Joins

SELECT o.order_id, c.customer_name
FROM orders AS o
INNER JOIN customers AS c
    ON c.customer_id = o.customer_id;

SELECT c.customer_name, o.order_id
FROM customers AS c
LEFT JOIN orders AS o
    ON o.customer_id = c.customer_id;

Missing relationship

SELECT c.*
FROM customers AS c
LEFT JOIN orders AS o
    ON o.customer_id = c.customer_id
WHERE o.order_id IS NULL;

Above average

SELECT employee_name, salary
FROM employees
WHERE salary > (
    SELECT AVG(salary)
    FROM employees
);

Conditional aggregation

SELECT
    customer_id,
    SUM(status = 'Completed') AS completed_orders,
    SUM(status = 'Cancelled') AS cancelled_orders
FROM orders
GROUP BY customer_id;

Portable version:

SUM(CASE WHEN status = 'Completed' THEN 1 ELSE 0 END)

Latest row per group

WITH ranked AS (
    SELECT
        o.*,
        ROW_NUMBER() OVER (
            PARTITION BY customer_id
            ORDER BY order_date DESC, order_id DESC
        ) AS rn
    FROM orders AS o
)
SELECT *
FROM ranked
WHERE rn = 1;

Second-highest distinct value

SELECT MAX(salary) AS second_highest_salary
FROM employees
WHERE salary < (
    SELECT MAX(salary)
    FROM employees
);

Alternative: Using LIMIT and OFFSET

SELECT DISTINCT salary
FROM employees
ORDER BY salary DESC
LIMIT 1 OFFSET 1;

MySQL dates

SELECT DATE_FORMAT(order_date, '%Y-%m')
FROM orders;

SELECT DATEDIFF(CURDATE(), order_date)
FROM orders;

SELECT DATE_ADD(order_date, INTERVAL 7 DAY)
FROM orders;

SELECT YEAR(order_date), MONTH(order_date)
FROM orders;

SELECT YEAR()
SELECT MONTH()
SELECT MONTHNAME()
SELECT DAY()
SELECT DAYNAME()
SELECT WEEKOFYEAR()

NULL handling

COALESCE(city, 'Unknown')
IFNULL(city, 'Unknown')
NULLIF(denominator, 0)

Ranking

DENSE_RANK() OVER (
    PARTITION BY department_id
    ORDER BY salary DESC
)

SELECT name,    
    department,
    salary,
    ROW_NUMBER() OVER (
    PARTITION BY department
    ORDER BY salary DESC
    ) AS rn
FROM employees;

• ROW_NUMBER(): 1, 2, 3, 4
• RANK(): 1, 2, 2, 4
• DENSE_RANK(): 1, 2, 2, 3

Common Table Expression

A named temporary result set that exists only for the duration of a single

query exection

WITH high_value_customers AS (
    SELECT customer_id, SUM(amount) AS spent
    FROM orders
    GROUP BY customer_id
    HAVING spent > 5000
    ),

    customer_details AS (
        SELECT id, name, email
        FROM customers
    )

SELECT c.name, c.email, h.spent
FROM high_value_customers h
JOIN customer_details c ON h.customer_id = c.id

Views

CREATE VIEW ActiveEmployees AS
SELECT EmployeeID, FirstName, LastName, Deparment
FROM Employees
WHERE Status = 'Active';

SELECT * FROM ActiveEmployees

Trigger

CREATE TRIGGER LogSalaryChange 
AFTER UPDATE ON Salaries
FOR EACH ROW
BEGIN
    INSERT INTO SalaryAuditLog (EmployeeID, OldSalary, NewSalary, ChangeDate)
    VALUES (NEW.EmployeeID, OLD.Salary, NEW.Salary, NOW());
END

Transactions

BEGIN TRANSACTION;

UPDATE ActiveEmployeesView
SET Department = 'Operations'
WHERE EmployeeID = 101;

ROLLBACK;