• DDL (Data Definition Language) — defines schema: CREATE, ALTER, DROP, TRUNCATE.
• DML (Data Manipulation Language) — manipulates data: SELECT, INSERT, UPDATE, DELETE.
• DCL (Data Control Language) — controls access: GRANT, REVOKE.
• TCL (Transaction Control Language) — manages transactions: COMMIT, ROLLBACK, SAVEPOINT.

WHERE filters rows before grouping; it cannot use aggregate functions. HAVING filters groups after GROUP BY; it can use aggregate functions.

-- WHERE: filter before grouping
SELECT department, COUNT(*) AS emp_count
FROM employees
WHERE salary > 50000
GROUP BY department
HAVING emp_count > 5;

• INNER JOIN — returns rows with matching values in both tables.
• LEFT JOIN — all rows from the left table; NULLs for non-matching right rows.
• RIGHT JOIN — all rows from the right table; NULLs for non-matching left rows.
• FULL OUTER JOIN — all rows from both tables; NULLs where no match.
• CROSS JOIN — Cartesian product of both tables.
• SELF JOIN — a table joined with itself.

SELECT e.name, d.department_name
FROM employees e
INNER JOIN departments d ON e.dept_id = d.id;

-- Left join to find employees without a department
SELECT e.name, d.department_name
FROM employees e
LEFT JOIN departments d ON e.dept_id = d.id
WHERE d.id IS NULL;

• Subquery — executes once independently; its result is used by the outer query.
• Correlated subquery — references columns from the outer query; executes once per row of the outer query (slower).

-- Subquery (independent)
SELECT name FROM employees
WHERE dept_id IN (SELECT id FROM departments WHERE location = "NYC");

-- Correlated subquery
SELECT name, salary FROM employees e
WHERE salary > (SELECT AVG(salary) FROM employees WHERE dept_id = e.dept_id);

• Clustered index — determines the physical order of data in the table. Only one per table (usually the primary key).
• Non-clustered index — a separate structure with pointers to the actual data rows. Multiple allowed per table.

-- Create a non-clustered index
CREATE INDEX idx_email ON users(email);

-- Composite index
CREATE INDEX idx_name_dept ON employees(last_name, dept_id);

EXPLAIN shows the query execution plan — which indexes are used, join types, estimated rows scanned, and whether a full table scan occurs. Use it to identify and fix slow queries.

EXPLAIN SELECT * FROM orders
WHERE customer_id = 42 AND status = "pending";
-- Look for: type=ref (good), type=ALL (bad - full scan)

• 1NF — atomic values; no repeating groups; each column has a single value.
• 2NF — 1NF + no partial dependencies (non-key columns depend on the whole primary key).
• 3NF — 2NF + no transitive dependencies (non-key columns depend only on the primary key).
• BCNF — stricter 3NF; every determinant is a candidate key.

• Primary key — uniquely identifies each row; cannot be NULL; only one per table.
• Foreign key — references the primary key of another table; enforces referential integrity.
• Unique key — ensures all values in a column are distinct; allows one NULL; multiple per table.

CREATE TABLE orders (
  id         INT PRIMARY KEY AUTO_INCREMENT,
  customer_id INT NOT NULL,
  order_num  VARCHAR(20) UNIQUE,
  FOREIGN KEY (customer_id) REFERENCES customers(id)
);

NULL represents an unknown or missing value. Comparisons with NULL using = or != always return NULL (unknown). Use IS NULL / IS NOT NULL to check for NULL. COALESCE() and IFNULL() provide default values.

SELECT * FROM users WHERE phone IS NULL;

-- COALESCE returns first non-NULL value
SELECT name, COALESCE(phone, "N/A") AS phone FROM users;

-- IFNULL (MySQL-specific)
SELECT IFNULL(discount, 0) FROM orders;

• COUNT() — number of rows (COUNT(*) includes NULLs; COUNT(col) excludes NULLs).
• SUM() — total of numeric values.
• AVG() — average of numeric values.
• MIN() / MAX() — minimum / maximum value.
• GROUP_CONCAT() — concatenates values within a group (MySQL).

SELECT
  department,
  COUNT(*)        AS total,
  AVG(salary)     AS avg_salary,
  MAX(salary)     AS max_salary,
  MIN(hire_date)  AS earliest_hire
FROM employees
GROUP BY department;

Window functions perform calculations across a set of rows related to the current row without collapsing them into groups. They use the OVER() clause.

SELECT
  name,
  salary,
  department,
  ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rank_in_dept,
  RANK()       OVER (ORDER BY salary DESC) AS overall_rank,
  LAG(salary)  OVER (ORDER BY hire_date)   AS prev_salary,
  LEAD(salary) OVER (ORDER BY hire_date)   AS next_salary
FROM employees;

• ROW_NUMBER() — assigns a unique sequential number to each row; no ties.
• RANK() — assigns the same rank to ties; skips the next rank(s) after a tie.
• DENSE_RANK() — assigns the same rank to ties; does not skip ranks.

-- Scores: 100, 90, 90, 80
-- ROW_NUMBER:  1, 2, 3, 4
-- RANK:        1, 2, 2, 4
-- DENSE_RANK:  1, 2, 2, 3

A stored procedure is a precompiled set of SQL statements stored in the database. It can accept parameters, contain logic (IF/LOOP), and be called repeatedly, reducing network overhead.

DELIMITER //
CREATE PROCEDURE GetUserOrders(IN userId INT)
BEGIN
  SELECT o.id, o.total, o.created_at
  FROM orders o
  WHERE o.customer_id = userId
  ORDER BY o.created_at DESC;
END //
DELIMITER ;

CALL GetUserOrders(42);

A trigger is a stored procedure that automatically executes in response to INSERT, UPDATE, or DELETE events on a table. Triggers can be BEFORE or AFTER the event.

CREATE TRIGGER before_order_insert
BEFORE INSERT ON orders
FOR EACH ROW
BEGIN
  IF NEW.total < 0 THEN
    SIGNAL SQLSTATE "45000"
      SET MESSAGE_TEXT = "Order total cannot be negative";
  END IF;
END;

A view is a virtual table based on a SELECT query. It simplifies complex queries, provides a security layer (hiding sensitive columns), and can be queried like a regular table.

CREATE VIEW active_customers AS
SELECT id, name, email
FROM customers
WHERE status = "active" AND last_order_date > DATE_SUB(NOW(), INTERVAL 1 YEAR);

SELECT * FROM active_customers WHERE name LIKE "A%";

• Atomicity — all operations in a transaction succeed or all are rolled back.
• Consistency — a transaction brings the database from one valid state to another.
• Isolation — concurrent transactions do not interfere with each other.
• Durability — committed transactions persist even after system failure.

START TRANSACTION;
UPDATE accounts SET balance = balance - 500 WHERE id = 1;
UPDATE accounts SET balance = balance + 500 WHERE id = 2;
COMMIT;  -- or ROLLBACK on error

• READ UNCOMMITTED — can read uncommitted changes (dirty reads).
• READ COMMITTED — only reads committed data; prevents dirty reads.
• REPEATABLE READ — same query returns same results within a transaction (MySQL default); prevents dirty and non-repeatable reads.
• SERIALIZABLE — fully isolated; transactions execute as if serial; prevents all anomalies.

• UNION — combines results of two SELECT statements and removes duplicate rows.
• UNION ALL — combines results and keeps all rows including duplicates; faster than UNION.

SELECT name FROM customers_2023
UNION
SELECT name FROM customers_2024;  -- removes duplicates

SELECT name FROM customers_2023
UNION ALL
SELECT name FROM customers_2024;  -- keeps duplicates, faster

• DELETE — DML; removes specific rows (with WHERE) or all rows; can be rolled back; fires triggers; slow for large tables.
• TRUNCATE — DDL; removes all rows; cannot be rolled back (in most DBs); resets AUTO_INCREMENT; faster than DELETE.
• DROP — DDL; removes the entire table structure and data permanently.

DELETE FROM logs WHERE created_at < "2023-01-01";
TRUNCATE TABLE temp_data;  -- removes all rows, resets auto_increment
DROP TABLE old_table;      -- removes table entirely

• MySQL — faster for read-heavy workloads; simpler setup; widely used in web apps (LAMP stack).
• PostgreSQL — more standards-compliant; supports advanced features (JSONB, arrays, CTEs, full-text search, custom types); better for complex queries.
• PostgreSQL supports partial indexes, materialized views, and more advanced window functions.

A CTE is a temporary named result set defined with the WITH clause. It improves readability and can be referenced multiple times in the same query. Recursive CTEs handle hierarchical data.

WITH dept_avg AS (
  SELECT dept_id, AVG(salary) AS avg_sal
  FROM employees
  GROUP BY dept_id
)
SELECT e.name, e.salary, d.avg_sal
FROM employees e
JOIN dept_avg d ON e.dept_id = d.dept_id
WHERE e.salary > d.avg_sal;

ORDER BY sorts the result set by one or more columns. By default, NULLs sort first in ascending order in MySQL. Use NULLS FIRST / NULLS LAST (PostgreSQL) or ISNULL() (MySQL) to control NULL ordering.

SELECT name, salary FROM employees
ORDER BY salary DESC, name ASC;

-- MySQL: put NULLs last in ascending sort
SELECT name, salary FROM employees
ORDER BY ISNULL(salary), salary ASC;

• CHAR(n) — fixed-length; always stores n characters (padded with spaces); faster for fixed-size data like country codes.
• VARCHAR(n) — variable-length; stores only the actual characters plus 1-2 bytes for length; more space-efficient for variable data.

• InnoDB — default engine; supports ACID transactions, foreign keys, row-level locking, and crash recovery.
• MyISAM — no transactions or foreign keys; table-level locking; faster for read-heavy workloads.
• MEMORY — stores data in RAM; very fast; data lost on restart.

GROUP BY groups rows with the same values in specified columns into summary rows. It is used with aggregate functions. In MySQL, non-aggregated columns in SELECT must appear in GROUP BY (strict mode).

SELECT
  YEAR(order_date) AS year,
  MONTH(order_date) AS month,
  COUNT(*) AS order_count,
  SUM(total) AS revenue
FROM orders
GROUP BY YEAR(order_date), MONTH(order_date)
ORDER BY year, month;