■ Complete SQL Notes

Beginner to Advanced

Database Fundamentals CRUD Operations All JOIN Types

Aggregations & GroupBy Indexes & Optimization Transactions & ACID

Comprehensive Study Guide & Reference Handbook

Covers syntax · examples · diagrams · practice questions · cheat sheet
Table of Contents
1. Database Fundamentals
1.1 What is a Database?
1.2 RDBMS Concepts
1.3 SQL Overview
1.4 Data Types

2. DDL – Data Definition

2.1 CREATE TABLE
2.2 ALTER TABLE
2.3 DROP & TRUNCATE
2.4 Constraints

3. DML – CRUD Operations

3.1 INSERT
3.2 SELECT
3.3 UPDATE
3.4 DELETE

4. Advanced SELECT
4.1 WHERE & Operators
4.2 ORDER BY & LIMIT
4.3 DISTINCT & ALIAS
4.4 CASE Expressions

5. Joins
5.1 INNER JOIN
5.2 LEFT / RIGHT JOIN
5.3 FULL OUTER JOIN
5.4 CROSS JOIN
5.5 SELF JOIN

6. Aggregations & GROUP BY

6.1 Aggregate Functions
6.2 GROUP BY
6.3 HAVING
6.4 Window Functions

7. Subqueries
7.1 Scalar Subqueries
7.2 Correlated Subqueries
7.3 EXISTS / IN
7.4 CTEs

8. Indexes & Optimization

8.1 What is an Index?
 8.2 Types of Indexes
8.3 Query Execution Plan
8.4 Optimization Tips

9. Normalization
9.1 1NF through 3NF
9.2 BCNF
9.3 Denormalization

10. Transactions
10.1 ACID Properties
10.2 BEGIN/COMMIT/ROLLBACK
10.3 Isolation Levels
10.4 Deadlocks

11. Practice Questions

Beginner · Intermediate · Advanced

12. SQL Cheat Sheet

Quick Reference
Chapter 1: Database Fundamentals

1.1 What is a Database?

■ Definition: A database is an organised collection of structured data stored electronically. A Database
Management System (DBMS) is software that manages databases.
Relational databases organise data into tables (relations) made of rows and columns. Examples:
PostgreSQL, MySQL, SQL Server, Oracle, SQLite.

1.2 RDBMS Key Concepts

Term Definition

Table (Relation) Grid of data with rows and columns

Row (Record/Tuple) A single data entry in a table

Column (Attribute) A field representing one property of the data

Primary Key Uniquely identifies each row; cannot be NULL

Foreign Key Column(s) that reference a Primary Key in another table

Schema Structure/blueprint of the database (tables, columns, types)

Index Data structure that speeds up data retrieval

View Virtual table based on a SELECT query

Stored Procedure Pre-compiled SQL code stored in the database

Trigger SQL code that runs automatically on INSERT/UPDATE/DELETE

1.3 SQL Overview

SQL (Structured Query Language) is the standard language for interacting with relational databases. It is
divided into sub-languages:

Sub-language Stands For Commands

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 BEGIN, COMMIT, ROLLBACK, SAVEPOINT

1.4 SQL Data Types

Category Type Description Example

Numeric INT / INTEGER Whole numbers 42, -7

Numeric BIGINT Large whole numbers 9876543210

Numeric DECIMAL(p,s) Exact decimal (p digits, s scale) 123.45

Numeric FLOAT / REAL Approximate decimal 3.14159

String VARCHAR(n) Variable-length string up to n 'Alice'

String CHAR(n) Fixed-length string 'US'

String TEXT Unlimited length string Long descriptions

Date/Time DATE Date only 2024-01-15

Date/Time TIME Time only 14:30:00

DATETIME / 2024-01-15
Date/Time Date and time
TIMESTAMP 14:30:00

Boolean BOOLEAN True/False (1/0) TRUE

Other NULL Absence of value NULL

Chapter 2: DDL – Data Definition
Language

2.1 CREATE TABLE

■ CREATE TABLE Syntax
-- Basic CREATE TABLE
CREATE TABLE employees (
employee_id INT PRIMARY KEY,
first_name VARCHAR(50) NOT NULL,
last_name VARCHAR(50) NOT NULL,
email VARCHAR(100) UNIQUE NOT NULL,
hire_date DATE NOT NULL,
salary DECIMAL(10,2) DEFAULT 0.00,
department_id INT,
FOREIGN KEY (department_id) REFERENCES departments(dept_id)
);

-- CREATE TABLE with composite PK

CREATE TABLE order_items (
order_id INT,
product_id INT,
quantity INT NOT NULL,
unit_price DECIMAL(8,2) NOT NULL,
PRIMARY KEY (order_id, product_id)
);

-- CREATE TABLE from SELECT (copy structure + data)

CREATE TABLE emp_backup AS
SELECT * FROM employees;

-- CREATE TABLE IF NOT EXISTS

CREATE TABLE IF NOT EXISTS logs (
log_id INT AUTO_INCREMENT PRIMARY KEY,
message TEXT,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

2.2 ALTER TABLE

■ ALTER TABLE Operations
-- Add a column
ALTER TABLE employees
ADD COLUMN phone VARCHAR(20);

-- Modify a column type

ALTER TABLE employees
 MODIFY COLUMN phone VARCHAR(30) NOT NULL; -- MySQL
-- ALTER COLUMN phone TYPE VARCHAR(30); -- PostgreSQL

-- Rename a column
ALTER TABLE employees
RENAME COLUMN phone TO phone_number;

-- Drop a column
ALTER TABLE employees
DROP COLUMN phone_number;

-- Add constraint
ALTER TABLE employees
ADD CONSTRAINT chk_salary CHECK (salary >= 0);

-- Drop constraint
ALTER TABLE employees
DROP CONSTRAINT chk_salary;

2.3 DROP & TRUNCATE

■ DROP vs TRUNCATE
-- DROP TABLE — removes table and all data permanently
DROP TABLE IF EXISTS employees;

-- TRUNCATE TABLE — removes all rows, keeps structure

-- Faster than DELETE; cannot be rolled back in most DBs
TRUNCATE TABLE employees;

-- DROP DATABASE
DROP DATABASE IF EXISTS company_db;

2.4 Constraints
Constraint Purpose Example

PRIMARY KEY Unique + NOT NULL identifier employee_id INT PRIMARY KEY

FOREIGN KEY Links to another table's PK dept_id INT REFERENCES dept(id)

UNIQUE No duplicate values in column email VARCHAR UNIQUE

NOT NULL Column must have a value name VARCHAR NOT NULL

CHECK Custom rule for valid values CHECK (age >= 18)

DEFAULT Value used if none provided salary DECIMAL DEFAULT 0

Chapter 3: DML – CRUD Operations

3.1 INSERT
■ INSERT Syntax
-- Insert single row
INSERT INTO employees (first_name, last_name, email, hire_date, salary, department_id)
VALUES ('Alice', 'Smith', 'alice@[Link]', '2023-01-15', 75000.00, 2);

-- Insert multiple rows

INSERT INTO employees (first_name, last_name, email, hire_date, salary, department_id)
VALUES
('Bob', 'Jones', 'bob@[Link]', '2023-02-01', 65000.00, 1),
('Carol', 'White', 'carol@[Link]', '2023-03-10', 85000.00, 3),
('Dave', 'Brown', 'dave@[Link]', '2022-11-05', 72000.00, 2);

-- Insert from SELECT

INSERT INTO emp_backup
SELECT * FROM employees WHERE department_id = 2;

-- INSERT ... ON CONFLICT (upsert) — PostgreSQL

INSERT INTO employees (employee_id, first_name, salary)
VALUES (1, 'Alice', 80000)
ON CONFLICT (employee_id)
DO UPDATE SET salary = [Link];

-- INSERT IGNORE (MySQL) — skip on duplicate key error

INSERT IGNORE INTO employees VALUES (1, 'Alice', ...);

3.2 SELECT
■ SELECT Syntax
-- All columns
SELECT * FROM employees;

-- Specific columns
SELECT first_name, last_name, salary FROM employees;

-- With alias
SELECT first_name AS "First Name",
salary * 12 AS "Annual Salary"
FROM employees;

-- WHERE clause
SELECT * FROM employees
WHERE department_id = 2
AND salary > 60000;

-- Comparison operators
 -- = != <> > < >= <=
-- BETWEEN ... AND ...
-- LIKE '%pattern%' (% = any chars, _ = one char)
-- IN (val1, val2, ...)
-- IS NULL / IS NOT NULL

SELECT * FROM employees WHERE salary BETWEEN 50000 AND 80000;

SELECT * FROM employees WHERE last_name LIKE 'S%';
SELECT * FROM employees WHERE department_id IN (1, 2, 3);
SELECT * FROM employees WHERE email IS NOT NULL;

-- ORDER BY
SELECT * FROM employees ORDER BY salary DESC;
SELECT * FROM employees ORDER BY department_id ASC, salary DESC;

-- LIMIT / TOP
SELECT * FROM employees ORDER BY salary DESC LIMIT 5; -- MySQL/PG
SELECT TOP 5 * FROM employees ORDER BY salary DESC; -- SQL Server

-- DISTINCT
SELECT DISTINCT department_id FROM employees;

3.3 UPDATE
■ UPDATE Syntax
-- Update specific rows
UPDATE employees
SET salary = salary * 1.10,
email = 'newemail@[Link]'
WHERE employee_id = 1;

-- Update multiple rows

UPDATE employees
SET department_id = 3
WHERE department_id = 1 AND salary < 50000;

-- Update using subquery

UPDATE employees
SET salary = (SELECT AVG(salary) FROM employees)
WHERE department_id = (
SELECT dept_id FROM departments WHERE name = 'HR'
);

■ Common Mistake: Always use WHERE with UPDATE and DELETE. Omitting WHERE affects ALL rows!

3.4 DELETE
■ DELETE Syntax
-- Delete specific row(s)
DELETE FROM employees
WHERE employee_id = 5;
-- Delete with condition
DELETE FROM employees
WHERE hire_date < '2020-01-01' AND salary < 40000;

-- Delete all rows (use TRUNCATE for performance)

DELETE FROM employees;

-- Delete with JOIN (MySQL syntax)

DELETE e FROM employees e
JOIN departments d ON e.department_id = d.dept_id
WHERE [Link] = 'Deprecated';
Chapter 4: Advanced SELECT

4.1 CASE Expressions

■ CASE Expression
-- Simple CASE
SELECT first_name,
CASE department_id
WHEN 1 THEN 'HR'
WHEN 2 THEN 'Engineering'
WHEN 3 THEN 'Finance'
ELSE 'Other'
END AS dept_name
FROM employees;

-- Searched CASE
SELECT first_name, salary,
CASE
WHEN salary >= 90000 THEN 'Senior'
WHEN salary >= 70000 THEN 'Mid-level'
WHEN salary >= 50000 THEN 'Junior'
ELSE 'Entry-level'
END AS level
FROM employees
ORDER BY salary DESC;

-- CASE in aggregate
SELECT
COUNT(CASE WHEN salary >= 80000 THEN 1 END) AS high_earners,
COUNT(CASE WHEN salary < 50000 THEN 1 END) AS low_earners
FROM employees;

4.2 String & Date Functions

■ Useful Functions
-- String functions
SELECT
UPPER(first_name), -- 'ALICE'
LOWER(last_name), -- 'smith'
CONCAT(first_name,' ',last_name) AS full_name,
LENGTH(email), -- character count
TRIM(' hello '), -- 'hello'
SUBSTRING(email, 1, 5), -- first 5 chars
REPLACE(email,'@','[at]'),
COALESCE(phone, 'N/A') -- first non-NULL value
FROM employees;

-- Date functions
SELECT
 CURRENT_DATE, -- today's date
CURRENT_TIMESTAMP, -- current date+time
YEAR(hire_date), -- extract year
MONTH(hire_date),
DATEDIFF(CURRENT_DATE, hire_date) AS days_employed,
DATE_ADD(hire_date, INTERVAL 1 YEAR) AS anniversary
FROM employees;

-- Numeric functions
SELECT
ROUND(salary, 2),
FLOOR(salary),
CEIL(salary),
ABS(-100),
MOD(salary, 1000)
FROM employees;
Chapter 5: Joins
■ Definition: A JOIN combines rows from two or more tables based on a related column. Understanding
all JOIN types is critical for SQL mastery.

JOIN Types — Visual Summary

Tables: A (left) and B (right). Common column: id

JOIN Type Returns Visual (A ■ B)

INNER JOIN Rows matching in BOTH tables A ∩ B (intersection only)

All rows from A + matching from B (NULL if

LEFT JOIN All of A + matching B
no match)

All rows from B + matching from A (NULL if

RIGHT JOIN All of B + matching A
no match)

FULL OUTER JOIN All rows from both; NULL where no match A ∪ B (entire union)

Every combination of rows (Cartesian

CROSS JOIN A×B
product)

SELF JOIN Table joined with itself Table A1 JOIN Table A2

5.1 INNER JOIN

■ Definition: Returns only the rows where there is a match in BOTH tables. Non-matching rows are
excluded.

■ INNER JOIN
-- Basic INNER JOIN
SELECT
e.first_name,
e.last_name,
d.department_name,
[Link]
FROM employees e
INNER JOIN departments d
ON e.department_id = d.dept_id;

-- JOIN with WHERE filter

SELECT e.first_name, d.department_name
FROM employees e
INNER JOIN departments d ON e.department_id = d.dept_id
WHERE d.department_name = 'Engineering'
ORDER BY [Link] DESC;

-- Multi-table JOIN
SELECT
e.first_name,
 d.department_name,
[Link],
j.job_title
FROM employees e
JOIN departments d ON e.department_id = d.dept_id
JOIN locations l ON d.location_id = l.location_id
JOIN jobs j ON e.job_id = j.job_id;

5.2 LEFT JOIN (LEFT OUTER JOIN)

■ Definition: Returns ALL rows from the left table, and the matched rows from the right table. If no
match, NULLs fill the right side.

■ LEFT JOIN
-- All employees, including those with no department
SELECT
e.first_name,
e.last_name,
d.department_name -- NULL if no department
FROM employees e
LEFT JOIN departments d ON e.department_id = d.dept_id;

-- Find employees WITHOUT a department (anti-join)

SELECT e.first_name, e.last_name
FROM employees e
LEFT JOIN departments d ON e.department_id = d.dept_id
WHERE d.dept_id IS NULL;

-- RIGHT JOIN is symmetric (rarely used; swap tables instead)

-- The above is equivalent to:
SELECT e.first_name, e.last_name
FROM departments d
RIGHT JOIN employees e ON e.department_id = d.dept_id
WHERE d.dept_id IS NULL;

5.3 FULL OUTER JOIN

■ FULL OUTER JOIN
-- All employees and all departments
-- NULLs where no match on either side
SELECT
e.first_name,
d.department_name
FROM employees e
FULL OUTER JOIN departments d ON e.department_id = d.dept_id;

-- Emulate FULL OUTER in MySQL (not natively supported)

SELECT e.first_name, d.department_name
FROM employees e LEFT JOIN departments d ON e.department_id = d.dept_id
UNION
SELECT e.first_name, d.department_name
FROM employees e RIGHT JOIN departments d ON e.department_id = d.dept_id;
5.4 CROSS JOIN & SELF JOIN
■ CROSS JOIN and SELF JOIN
-- CROSS JOIN — every combination (Cartesian product)
-- Use case: generate all combinations (e.g. sizes x colors)
SELECT s.size_name, c.color_name
FROM sizes s
CROSS JOIN colors c;
-- If sizes has 3 rows and colors has 4, result = 12 rows

-- SELF JOIN — employee and their manager

SELECT
e.first_name AS employee,
m.first_name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.employee_id;

-- Find employees in same department (pairs)

SELECT e1.first_name, e2.first_name, e1.department_id
FROM employees e1
JOIN employees e2
ON e1.department_id = e2.department_id
AND e1.employee_id < e2.employee_id; -- avoid duplicates
Chapter 6: Aggregations & GROUP
BY

6.1 Aggregate Functions

Function Description Example

COUNT(*) Total number of rows COUNT(*) → 100

COUNT(col) Rows where col is NOT NULL COUNT(salary) → 98

SUM(col) Sum of numeric column SUM(salary) → 7500000

AVG(col) Average of numeric column AVG(salary) → 75000

MIN(col) Minimum value MIN(hire_date)

MAX(col) Maximum value MAX(salary)

GROUP_CONCAT /
Concatenate values in group GROUP_CONCAT(name)
STRING_AGG

6.2 GROUP BY
■ Definition: GROUP BY groups rows that have the same value in specified columns, allowing
aggregate functions to be applied to each group.

■ GROUP BY Examples
-- Count employees per department
SELECT department_id, COUNT(*) AS employee_count
FROM employees
GROUP BY department_id;

-- Average salary per department

SELECT
d.department_name,
COUNT(e.employee_id) AS headcount,
AVG([Link]) AS avg_salary,
MAX([Link]) AS max_salary,
MIN([Link]) AS min_salary,
SUM([Link]) AS total_salary
FROM employees e
JOIN departments d ON e.department_id = d.dept_id
GROUP BY d.department_name
ORDER BY avg_salary DESC;

-- GROUP BY multiple columns

SELECT department_id, YEAR(hire_date) AS hire_year,
COUNT(*) AS hires
FROM employees
 GROUP BY department_id, YEAR(hire_date)
ORDER BY department_id, hire_year;

6.3 HAVING
■ Definition: HAVING filters groups (after GROUP BY), similar to WHERE which filters rows (before
GROUP BY). HAVING can use aggregate functions; WHERE cannot.

■ HAVING Examples
-- Departments with more than 5 employees
SELECT department_id, COUNT(*) AS cnt
FROM employees
GROUP BY department_id
HAVING COUNT(*) > 5;

-- Departments with avg salary above threshold

SELECT
d.department_name,
AVG([Link]) AS avg_sal
FROM employees e
JOIN departments d ON e.department_id = d.dept_id
GROUP BY d.department_name
HAVING AVG([Link]) > 70000
ORDER BY avg_sal DESC;

-- WHERE filters rows BEFORE grouping

-- HAVING filters groups AFTER grouping
SELECT department_id, COUNT(*) AS cnt
FROM employees
WHERE salary > 50000 -- filter rows first
GROUP BY department_id
HAVING COUNT(*) >= 3; -- then filter groups

6.4 Window Functions

■ Definition: Window functions perform calculations across a set of related rows (a 'window') without
collapsing them into a single group. Added in SQL:2003 standard.

■ Window Functions
-- Syntax: function() OVER (PARTITION BY ... ORDER BY ...)

-- ROW_NUMBER — unique sequential number within partition

SELECT
first_name,
department_id,
salary,
ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) AS row_num
FROM employees;

-- RANK and DENSE_RANK

SELECT
first_name, salary,
 RANK() OVER (ORDER BY salary DESC) AS rank_with_gaps,
DENSE_RANK() OVER (ORDER BY salary DESC) AS rank_no_gaps
FROM employees;

-- Running total
SELECT
hire_date, salary,
SUM(salary) OVER (ORDER BY hire_date) AS running_total
FROM employees;

-- LAG / LEAD — access previous/next row

SELECT
first_name, hire_date,
LAG(hire_date) OVER (ORDER BY hire_date) AS prev_hire,
LEAD(hire_date) OVER (ORDER BY hire_date) AS next_hire
FROM employees;

-- NTILE — divide rows into N buckets

SELECT first_name, salary,
NTILE(4) OVER (ORDER BY salary) AS quartile
FROM employees;
Chapter 7: Subqueries & CTEs

7.1 Subqueries
■ Definition: A subquery (inner query) is a query nested inside another query. It can appear in SELECT,
FROM, WHERE, or HAVING clauses.

■ Subquery Examples
-- Scalar subquery in WHERE
SELECT first_name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

-- Subquery in FROM (derived table)

SELECT dept, avg_sal
FROM (
SELECT department_id AS dept,
AVG(salary) AS avg_sal
FROM employees
GROUP BY department_id
) AS dept_stats
WHERE avg_sal > 70000;

-- Subquery in SELECT
SELECT
first_name,
salary,
(SELECT AVG(salary) FROM employees) AS company_avg,
salary - (SELECT AVG(salary) FROM employees) AS diff_from_avg
FROM employees;

-- IN subquery
SELECT first_name
FROM employees
WHERE department_id IN (
SELECT dept_id
FROM departments
WHERE location = 'New York'
);

-- NOT IN subquery
SELECT first_name
FROM employees
WHERE department_id NOT IN (
SELECT dept_id FROM departments WHERE budget < 100000
);

7.2 EXISTS & Correlated Subqueries

■ EXISTS and Correlated Subqueries
-- EXISTS — returns TRUE if subquery returns any rows
SELECT d.department_name
FROM departments d
WHERE EXISTS (
SELECT 1
FROM employees e
WHERE e.department_id = d.dept_id
AND [Link] > 100000
);

-- NOT EXISTS — find departments with no employees

SELECT d.department_name
FROM departments d
WHERE NOT EXISTS (
SELECT 1 FROM employees e
WHERE e.department_id = d.dept_id
);

-- Correlated subquery — references outer query

-- Find employees earning more than dept average
SELECT first_name, salary, department_id
FROM employees e1
WHERE salary > (
SELECT AVG(salary)
FROM employees e2
WHERE e2.department_id = e1.department_id -- correlation
);

7.3 CTEs – Common Table Expressions

■ Definition: A CTE (WITH clause) creates a named temporary result set that can be referenced in the
main query. Improves readability and enables recursive queries.

■ CTEs
-- Basic CTE
WITH high_earners AS (
SELECT employee_id, first_name, salary
FROM employees
WHERE salary > 80000
)
SELECT * FROM high_earners ORDER BY salary DESC;

-- Multiple CTEs
WITH
dept_stats AS (
SELECT department_id,
AVG(salary) AS avg_sal,
COUNT(*) AS headcount
FROM employees
GROUP BY department_id
),
top_depts AS (
 SELECT department_id
FROM dept_stats
WHERE headcount >= 10
)
SELECT e.first_name, [Link], ds.avg_sal
FROM employees e
JOIN dept_stats ds ON e.department_id = ds.department_id
WHERE e.department_id IN (SELECT department_id FROM top_depts);

-- Recursive CTE — org chart (manager hierarchy)

WITH RECURSIVE org_chart AS (
-- anchor: top-level (CEO)
SELECT employee_id, first_name, manager_id, 1 AS level
FROM employees
WHERE manager_id IS NULL

UNION ALL

-- recursive: employees reporting to someone in prior result

SELECT e.employee_id, e.first_name, e.manager_id, [Link] + 1
FROM employees e
JOIN org_chart oc ON e.manager_id = oc.employee_id
)
SELECT level, first_name FROM org_chart ORDER BY level;
Chapter 8: Indexes & Query
Optimization

8.1 What is an Index?

■ Definition: An index is a data structure (usually a B-tree) that speeds up data retrieval at the cost of
additional storage and slower write operations.

■ Creating Indexes
-- Single-column index
CREATE INDEX idx_emp_email ON employees(email);

-- Unique index (enforces uniqueness too)

CREATE UNIQUE INDEX idx_emp_email_unique ON employees(email);

-- Composite index
CREATE INDEX idx_dept_salary ON employees(department_id, salary);

-- Full-text index (for text search)

CREATE FULLTEXT INDEX idx_ft_name ON employees(first_name, last_name);

-- Drop index
DROP INDEX idx_emp_email ON employees; -- MySQL
DROP INDEX idx_emp_email; -- PostgreSQL

-- List indexes (MySQL)

SHOW INDEX FROM employees;

-- List indexes (PostgreSQL)

SELECT indexname, indexdef FROM pg_indexes WHERE tablename = 'employees';

8.2 Query Execution Plan

■ Definition: EXPLAIN shows how the database engine executes a query — which indexes are used,
join order, estimated rows scanned. Essential for optimization.

■ EXPLAIN
-- MySQL
EXPLAIN SELECT * FROM employees WHERE department_id = 2;
EXPLAIN ANALYZE SELECT e.first_name, d.department_name
FROM employees e JOIN departments d ON e.department_id = d.dept_id
WHERE [Link] > 70000;

-- Key columns to check:

-- type: const > eq_ref > ref > range > index > ALL
-- (ALL = full table scan — bad!)
-- key: which index is being used
-- rows: estimated rows examined
-- Extra: Using index, Using filesort, Using temporary

8.3 Optimization Tips

Tip Explanation

Use indexes on
Primary candidate for indexing
WHERE/JOIN/ORDER columns

Avoid SELECT * Fetch only needed columns; reduces I/O

Use LIMIT Avoid scanning entire result set

Avoid functions on indexed columns WHERE YEAR(date) = 2023 → can't use date index

Use covering indexes Index includes all columns needed by query

Avoid OR on different columns Use UNION instead; each part can use its index

Batch large inserts/updates Reduces transaction overhead

Use EXPLAIN before optimizing Always measure before guessing

Normalize but denormalize for reads Balance write integrity vs read speed
Chapter 9: Normalization
■ Definition: Normalization is the process of organising a database to reduce data redundancy and
improve data integrity through a series of normal forms (NF).

First Normal Form (1NF)

Rules: (1) Each column contains atomic (indivisible) values. (2) Each row is unique (has a PK). (3) No
repeating groups or arrays.

■ 1NF Violation and Fix

-- VIOLATION: multiple phones in one column
-- orders(order_id, customer, products) -- 'apple,banana,cherry'

-- FIX: separate row per product

-- orders(order_id, customer_id, product_id)

Second Normal Form (2NF)

Prerequisite: Must be in 1NF. Rule: Every non-key attribute is fully dependent on the entire primary key
(no partial dependency). Applies when PK is composite.

■ 2NF Violation and Fix

-- VIOLATION: composite PK (order_id, product_id)
-- order_items(order_id, product_id, product_name, quantity)
-- product_name depends only on product_id (partial dependency!)

-- FIX: split into two tables

-- order_items(order_id, product_id, quantity)
-- products(product_id, product_name, price)

Third Normal Form (3NF)

Prerequisite: Must be in 2NF. Rule: No transitive dependencies (non-key attribute depends on another
non-key attribute).

■ 3NF Violation and Fix

-- VIOLATION:
-- employees(emp_id, name, dept_id, dept_name, dept_location)
-- dept_name and dept_location depend on dept_id, not emp_id
-- (transitive: emp_id → dept_id → dept_name)

-- FIX:
-- employees(emp_id, name, dept_id)
-- departments(dept_id, dept_name, dept_location)

Normal Form Removes Rule

1NF Repeating groups Atomic values, unique rows

Normal Form Removes Rule

2NF Partial dependencies Full dependency on entire PK

3NF Transitive dependencies Non-key attributes depend only on PK

BCNF Anomalies in 3NF Every determinant is a candidate key

Chapter 10: Transactions

10.1 ACID Properties

Property Meaning Example

All operations succeed or ALL are

Atomicity Transfer: debit + credit both happen or neither
rolled back

DB transitions from one valid

Consistency Constraints (FK, CHECK) always satisfied
state to another

Concurrent transactions don't Two bank transfers don't see each other's partial
Isolation
interfere state

Committed changes survive

Durability Written to persistent storage (WAL, redo log)
crashes

10.2 BEGIN / COMMIT / ROLLBACK

■ Transaction Commands
-- Transfer money between accounts
BEGIN; -- or START TRANSACTION;

UPDATE accounts SET balance = balance - 500 WHERE acct_id = 1;

UPDATE accounts SET balance = balance + 500 WHERE acct_id = 2;

-- Check if both updates went through

-- If OK:
COMMIT;

-- If error occurred:
-- ROLLBACK;

-- SAVEPOINT — partial rollback

BEGIN;
INSERT INTO orders VALUES (1001, 'Alice', 200);
SAVEPOINT after_order;

INSERT INTO order_items VALUES (1001, 'Widget', 5);

-- Something went wrong with items:
ROLLBACK TO SAVEPOINT after_order;

-- Order is still there; items are rolled back

COMMIT;

10.3 Isolation Levels

Isolation Level Dirty Read Non-Repeatable Read Phantom Read Performance

READ UNCOMMITTED Yes Yes Yes Fastest

READ COMMITTED No Yes Yes Fast

REPEATABLE READ No No Yes Moderate

SERIALIZABLE No No No Slowest

■ Setting Isolation Level

-- MySQL
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
START TRANSACTION;
-- ... your queries ...
COMMIT;

-- PostgreSQL
BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
-- ... your queries ...
COMMIT;
Chapter 11: Practice Questions

Beginner Level
Q1. Write a query to list all employees sorted by salary descending.
Answer: SELECT * FROM employees ORDER BY salary DESC;

Q2. Find the number of employees in each department.

Answer: SELECT department_id, COUNT(*) FROM employees GROUP BY department_id;

Q3. List all employees whose last name starts with 'S'.
Answer: SELECT * FROM employees WHERE last_name LIKE 'S%';

Q4. Find employees hired after January 1, 2022.

Answer: SELECT * FROM employees WHERE hire_date > '2022-01-01';

Q5. What is the highest salary in the company?

Answer: SELECT MAX(salary) FROM employees;

Intermediate Level
Q6. List department names and average salary, only for departments with average salary above
70,000.
Answer: SELECT d.department_name, AVG([Link]) AS avg_sal FROM employees e JOIN
departments d ON e.department_id=d.dept_id GROUP BY d.department_name HAVING
AVG([Link])>70000;

Q7. Find employees who earn more than the average salary of their own department.
Answer: SELECT * FROM employees e WHERE salary > (SELECT AVG(salary) FROM employees
WHERE department_id=e.department_id);

Q8. List all departments that have NO employees.

Answer: SELECT d.department_name FROM departments d LEFT JOIN employees e ON
d.dept_id=e.department_id WHERE e.employee_id IS NULL;

Q9. Rank employees by salary using DENSE_RANK within each department.

Answer: SELECT first_name, department_id, salary, DENSE_RANK() OVER (PARTITION BY
department_id ORDER BY salary DESC) AS rnk FROM employees;

Q10. Write a query to find the second highest salary.

Answer: SELECT MAX(salary) FROM employees WHERE salary < (SELECT MAX(salary) FROM
employees);
Advanced Level
Q11. Write a recursive CTE to find all subordinates of a given manager (org chart traversal).
Answer: Use WITH RECURSIVE with anchor = direct reports, recursive = their reports.

Q12. Using a window function, compute a 3-month rolling average of sales.

Answer: AVG(sales) OVER (ORDER BY month ROWS BETWEEN 2 PRECEDING AND CURRENT
ROW)

Q13. Write a query that pivots rows to columns — show each department's total salary in a single
row.
Answer: Use SUM(CASE WHEN dept='HR' THEN salary ELSE 0 END) AS HR, ... pattern.

Q14. Explain how to optimise a slow query that performs a full table scan on a 10M-row table.
Answer: Check EXPLAIN, add appropriate index on WHERE/JOIN column, avoid SELECT *, use
LIMIT.

Q15. Design tables for a many-to-many relationship between students and courses with an
enrollment date.
Answer: students(id, name), courses(id, title), enrollments(student_id, course_id, enrolled_at, PK
composite)
Chapter 12: SQL Cheat Sheet

SELECT Statement Anatomy

■ Full SELECT Order
SELECT [DISTINCT] columns -- 5. select columns
FROM table1 -- 1. specify source
[JOIN table2 ON condition] -- 2. join tables
WHERE row_condition -- 3. filter rows
GROUP BY grouping_columns -- 4. group rows
HAVING group_condition -- 6. filter groups
ORDER BY sort_columns [ASC|DESC] -- 7. sort output
LIMIT n OFFSET m; -- 8. paginate

-- Execution order: FROM → JOIN → WHERE → GROUP BY

-- → HAVING → SELECT → DISTINCT → ORDER → LIMIT

JOIN Quick Reference

JOIN Type Syntax Returns

INNER JOIN FROM a JOIN b ON [Link]=[Link] Matching rows only

LEFT JOIN FROM a LEFT JOIN b ON [Link]=[Link] All of a + matching b

RIGHT JOIN FROM a RIGHT JOIN b ON [Link]=[Link] All of b + matching a

FULL OUTER FROM a FULL OUTER JOIN b ON [Link]=[Link] All rows both sides

CROSS JOIN FROM a CROSS JOIN b Every combination

SELF JOIN FROM a e1 JOIN a e2 ON condition Same table, different alias

Aggregate Functions
Function Description NULL Handling

COUNT(*) Count all rows Includes NULLs

COUNT(col) Count non-NULL values Excludes NULLs

SUM(col) Total of values Ignores NULLs

AVG(col) Mean of values Ignores NULLs

MIN/MAX(col) Smallest/largest Ignores NULLs

Window Functions Quick Reference

 Function Purpose

ROW_NUMBER() Sequential row number within partition (no ties)

RANK() Rank with gaps (tied rows get same rank, next rank skips)

DENSE_RANK() Rank without gaps (tied rows get same rank)

NTILE(n) Divide rows into n buckets

LAG(col, n) Value from n rows before current

LEAD(col, n) Value from n rows after current

SUM(col) OVER(...) Running/window total

AVG(col) OVER(...) Running/window average

FIRST_VALUE(col) First value in window

LAST_VALUE(col) Last value in window

Constraints Quick Reference

Constraint Effect Inline Syntax

PRIMARY KEY Unique + NOT NULL id INT PRIMARY KEY

FOREIGN KEY Referential integrity REFERENCES table(col)

UNIQUE No duplicates email VARCHAR UNIQUE

NOT NULL Mandatory field name VARCHAR NOT NULL

CHECK Custom rule CHECK (age >= 0)

DEFAULT Default value salary DECIMAL DEFAULT 0