SQL — Complete Study Guide | Exam & Interview Ready

SQL
Structured Query Language
Complete Study Guide — From Beginner to Advanced
Queries • Joins • Aggregations • Window Functions • Indexes • Optimization • Interviews

Table of Contents
# Chapter Key Topics
1 SQL Fundamentals What is SQL, relational databases, RDBMS, data types,
NULL
2 DDL — Creating Structure CREATE, ALTER, DROP, TRUNCATE, constraints,
indexes
3 DML — Manipulating Data INSERT, UPDATE, DELETE, MERGE, transactions,
ACID
4 SELECT — Querying Data SELECT, FROM, WHERE, DISTINCT, ORDER BY,
LIMIT/TOP
5 Filtering & Operators WHERE operators, BETWEEN, IN, LIKE, IS NULL,
CASE
6 Aggregate Functions GROUP BY, HAVING, COUNT, SUM, AVG, MIN, MAX,
ROLLUP
7 Joins INNER, LEFT, RIGHT, FULL OUTER, CROSS, SELF
JOIN, set ops
8 Subqueries & CTEs Correlated subqueries, EXISTS, IN, WITH (CTE),
recursive CTE
9 Window Functions ROW_NUMBER, RANK, DENSE_RANK, LAG, LEAD,
OVER, PARTITION
10 String, Date & Math Functions String manipulation, date arithmetic, math functions
11 Views, Indexes & Performance Views, indexes, EXPLAIN/EXPLAIN PLAN, query
optimization
12 Database Design Normalization (1NF–3NF/BCNF), ERD, keys,
relationships

SQL Complete Guide | Exam & Interview Preparation

SQL — Complete Study Guide | Exam & Interview Ready

# Chapter Key Topics

13 Advanced SQL Stored procedures, triggers, cursors, JSON, pivoting
14 SQL Dialects MySQL, PostgreSQL, SQL Server, Oracle, SQLite
differences
15 Interview Questions & Answers 60+ questions with detailed answers and code examples

SQL Complete Guide | Exam & Interview Preparation

SQL — Complete Study Guide | Exam & Interview Ready

01 SQL Fundamentals
Relational databases, RDBMS, data types, NULL handling

Chapter 1: SQL Fundamentals

1.1 What is SQL?

SQL (Structured Query Language) is the standard language for interacting with relational
databases. It was developed at IBM in the 1970s based on E.F. Codd's relational model theory.
SQL is declarative — you describe WHAT data you want, not HOW to retrieve it. The database
engine figures out the execution plan.

SQL Category Abbr Commands Purpose

eviati
on
Data Definition Language DDL CREATE, ALTER, DROP, Define and modify
TRUNCATE, RENAME database
structure/schema
Data Manipulation DML SELECT, INSERT, Read and modify data
Language UPDATE, DELETE, MERGE within tables
Data Control Language DCL GRANT, REVOKE Control access
permissions to database
objects
Transaction Control TCL COMMIT, ROLLBACK, Manage database
Language SAVEPOINT transactions

1.2 Relational Database Concepts

Concept Definition
Table (Relation) A collection of data organized in rows (records/tuples) and columns
(attributes/fields)
Row (Record) A single data entry in a table — one instance of the entity
Column (Field) A named attribute of the table — stores one type of data for each row
Primary Key (PK) A column (or set of columns) that uniquely identifies each row —
cannot be NULL or duplicate
Foreign Key (FK) A column that references the Primary Key of another table —
enforces referential integrity

SQL Complete Guide | Exam & Interview Preparation

SQL — Complete Study Guide | Exam & Interview Ready

Concept Definition
Schema The logical structure/blueprint of a database — tables, columns,
types, constraints, relationships
Index A data structure that speeds up data retrieval — like a book's index
pointing to pages
Constraint A rule enforced on data in a column (NOT NULL, UNIQUE, CHECK,
DEFAULT, FK, PK)
Relationship An association between tables: One-to-One, One-to-Many, Many-to-
Many
NULL The absence of a value — NOT the same as zero or empty string.
NULL = unknown.

1.3 Common Data Types

Category Types Notes
Integer INT, INTEGER, BIGINT, BIGINT for very large IDs; TINYINT for
SMALLINT, TINYINT flags/small codes
Decimal DECIMAL(p,s), NUMERIC(p,s), Use DECIMAL for money — never FLOAT
FLOAT, REAL, DOUBLE (precision loss)
String VARCHAR(n), CHAR(n), TEXT, VARCHAR = variable length; CHAR = fixed
NVARCHAR(n), CLOB length; TEXT = unlimited
Date/Time DATE, TIME, DATETIME, TIMESTAMP includes timezone; DATE is
TIMESTAMP, INTERVAL date-only
Boolean BOOLEAN, BIT, TINYINT(1) MySQL uses TINYINT(1); PostgreSQL has
native BOOLEAN
Binary BLOB, BINARY, VARBINARY, Store files, images — avoid in most
BYTEA application design
JSON JSON, JSONB (PostgreSQL), JSONB in Postgres is binary-indexed —
JSON (MySQL 5.7+) faster for queries
UUID/GUID UUID (PostgreSQL), Good for distributed IDs — no sequential
UNIQUEIDENTIFIER (SQL risk
Server)

1.4 NULL — The Three-Valued Logic

NULL represents the absence of a known value. It is NOT zero, NOT empty string, NOT false. Any
arithmetic or comparison with NULL returns NULL (unknown). This is three-valued logic: TRUE,
FALSE, or UNKNOWN.

SQL Complete Guide | Exam & Interview Preparation

SQL — Complete Study Guide | Exam & Interview Ready

-- NULL comparisons ALWAYS return UNKNOWN (not TRUE or FALSE):

SELECT NULL = NULL; -- Returns NULL (not TRUE!)
SELECT NULL <> NULL; -- Returns NULL
SELECT NULL = 0; -- Returns NULL
SELECT 5 + NULL; -- Returns NULL (NULL 'poisons' arithmetic)

-- CORRECT way to check for NULL:

SELECT * FROM employees WHERE manager_id IS NULL;
SELECT * FROM employees WHERE manager_id IS NOT NULL;

-- COALESCE: returns first non-NULL value

SELECT COALESCE(middle_name, 'N/A') AS middle_name FROM employees;

-- NULLIF: returns NULL if two values are equal

SELECT NULLIF(budget, 0); -- Returns NULL instead of dividing by zero

-- ISNULL (SQL Server) / IFNULL (MySQL) / NVL (Oracle):

SELECT ISNULL(salary, 0) FROM employees; -- SQL Server
SELECT IFNULL(salary, 0) FROM employees; -- MySQL
SELECT COALESCE(salary, 0) FROM employees; -- Standard SQL (all dialects)

Critical NULL Rule

COUNT(*) counts ALL rows including NULLs. COUNT(column) counts only non-NULL values in
that column. SUM, AVG, MIN, MAX all IGNORE NULLs. This is a very common interview
question — always know how each aggregate handles NULLs.

SQL Complete Guide | Exam & Interview Preparation

SQL — Complete Study Guide | Exam & Interview Ready

02 DDL — Defining Structure

CREATE, ALTER, DROP, constraints, sequences

Chapter 2: DDL — Data Definition Language

2.1 CREATE TABLE

-- Basic table creation:
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) CHECK (salary > 0),
department_id INT,
manager_id INT,
FOREIGN KEY (department_id) REFERENCES departments(department_id),
FOREIGN KEY (manager_id) REFERENCES employees(employee_id) -- Self-
referencing FK
);

-- AUTO INCREMENT primary key (dialect-specific):

employee_id INT AUTO_INCREMENT PRIMARY KEY -- MySQL
employee_id SERIAL PRIMARY KEY -- PostgreSQL
employee_id INT IDENTITY(1,1) PRIMARY KEY -- SQL Server
employee_id INT GENERATED ALWAYS AS IDENTITY -- Standard SQL / Oracle 12c+

2.2 Constraints Reference

Constraint Purpose Example
PRIMARY KEY Uniquely identifies each row; employee_id INT PRIMARY KEY
implies NOT NULL + UNIQUE
FOREIGN KEY References PK of another table; FOREIGN KEY (dept_id)
enforces referential integrity REFERENCES
departments(dept_id)
NOT NULL Column cannot contain NULL first_name VARCHAR(50) NOT
values NULL
UNIQUE All values in column must be email VARCHAR(100) UNIQUE
distinct (NULLs may be allowed)
CHECK Values must satisfy a boolean CHECK (salary BETWEEN 0 AND

SQL Complete Guide | Exam & Interview Preparation

SQL — Complete Study Guide | Exam & Interview Ready

Constraint Purpose Example

expression 999999)
DEFAULT Provides a default value when no status VARCHAR(20) DEFAULT
value is supplied on INSERT 'active'
INDEX Not a constraint — speeds up CREATE INDEX idx_emp_dept ON
lookups at cost of write speed employees(department_id)

2.3 ALTER TABLE

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

-- Modify a column's data type:

ALTER TABLE employees MODIFY COLUMN salary DECIMAL(12,2); -- MySQL
ALTER TABLE employees ALTER COLUMN salary DECIMAL(12,2); -- SQL Server
ALTER TABLE employees ALTER COLUMN salary TYPE NUMERIC(12,2); -- PostgreSQL

-- Rename a column:
ALTER TABLE employees RENAME COLUMN phone TO phone_number; -- PostgreSQL /
MySQL 8+
EXEC sp_rename '[Link]', 'phone_number', 'COLUMN'; -- SQL Server

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

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

-- Drop a constraint:
ALTER TABLE employees DROP CONSTRAINT chk_salary;

2.4 DROP and TRUNCATE

-- DROP TABLE: removes the table structure AND all its data permanently
DROP TABLE employees;
DROP TABLE IF EXISTS employees; -- Safer: no error if table doesn't exist

-- TRUNCATE TABLE: removes ALL rows but keeps the table structure
TRUNCATE TABLE employees;
-- TRUNCATE is faster than DELETE (no row-by-row logging in most RDBMS)
-- TRUNCATE resets AUTO_INCREMENT/IDENTITY counters
-- TRUNCATE cannot be rolled back in MySQL; CAN be rolled back in
PostgreSQL/SQL Server

SQL Complete Guide | Exam & Interview Preparation

SQL — Complete Study Guide | Exam & Interview Ready

-- DROP DATABASE:
DROP DATABASE company_db;

-- DROP INDEX:
DROP INDEX idx_emp_dept ON employees; -- MySQL
DROP INDEX idx_emp_dept; -- PostgreSQL / SQL Server

DROP vs TRUNCATE vs DELETE

DELETE removes rows one at a time, is fully logged, can be rolled back, and supports WHERE
clauses. TRUNCATE removes all rows at once (deallocates pages), is minimally logged, resets
identity/auto-increment, and is much faster. DROP removes the entire table object. DELETE
fires triggers; TRUNCATE does NOT fire row-level triggers.

SQL Complete Guide | Exam & Interview Preparation

SQL — Complete Study Guide | Exam & Interview Ready

03 DML — Manipulating Data

INSERT, UPDATE, DELETE, MERGE, transactions, ACID

Chapter 3: DML — Data Manipulation Language

3.1 INSERT
-- Insert a single row (specify columns explicitly — best practice):
INSERT INTO employees (first_name, last_name, email, hire_date, salary,
department_id)
VALUES ('Alice', 'Johnson', 'alice@[Link]', '2024-01-15', 85000.00, 3);

-- Insert multiple rows at once:

INSERT INTO employees (first_name, last_name, email, hire_date, salary)
VALUES ('Bob', 'Smith', 'bob@[Link]', '2024-02-01', 72000),
('Carol', 'Davis', 'carol@[Link]', '2024-02-15', 91000),
('Dave', 'Wilson', 'dave@[Link]', '2024-03-01', 68000);

-- INSERT ... SELECT: insert results of a query into a table

INSERT INTO employee_archive (employee_id, full_name, termination_date)
SELECT employee_id, first_name || ' ' || last_name, CURRENT_DATE
FROM employees
WHERE status = 'terminated';

-- INSERT IGNORE (MySQL): skip rows that violate unique constraints

INSERT IGNORE INTO employees (email) VALUES ('existing@[Link]');

-- INSERT OR REPLACE / UPSERT (see MERGE section):

INSERT INTO settings (key, value)
VALUES ('theme', 'dark')
ON CONFLICT (key) DO UPDATE SET value = [Link]; -- PostgreSQL

3.2 UPDATE
-- Update specific rows (ALWAYS use WHERE — without it updates ALL rows!):
UPDATE employees
SET salary = salary * 1.10 -- 10% raise
WHERE department_id = 3 AND performance_rating = 'Excellent';

-- Update multiple columns:

UPDATE employees
SET salary = 90000,
job_title = 'Senior Developer',
updated_at = CURRENT_TIMESTAMP

SQL Complete Guide | Exam & Interview Preparation

SQL — Complete Study Guide | Exam & Interview Ready

WHERE employee_id = 42;

-- Update using a JOIN (SQL Server / MySQL):

UPDATE e
SET e.department_name = d.new_name
FROM employees e
JOIN departments d ON e.department_id = d.department_id
WHERE d.old_name = 'Engineering';

-- Update using subquery:

UPDATE employees
SET salary = (SELECT AVG(salary) FROM employees WHERE department_id = 3)
WHERE employee_id = 99;

3.3 DELETE
-- Delete specific rows (ALWAYS use WHERE!):
DELETE FROM employees WHERE status = 'inactive' AND hire_date < '2010-01-01';

-- Delete using subquery:

DELETE FROM order_items
WHERE order_id IN (
SELECT order_id FROM orders WHERE status = 'cancelled'
);

-- Delete with JOIN (MySQL syntax):

DELETE e FROM employees e
JOIN departments d ON e.department_id = d.department_id
WHERE d.department_name = 'Temp';

-- DANGER: Delete without WHERE deletes ALL rows (like TRUNCATE but slower):
DELETE FROM employees; -- Removes every row!

3.4 MERGE (UPSERT)

-- MERGE: insert if not exists, update if exists (SQL Server / Oracle
standard):
MERGE INTO employees AS target
USING staging_employees AS source
ON [Link] = [Link]
WHEN MATCHED THEN
UPDATE SET [Link] = [Link],
target.job_title = source.job_title
WHEN NOT MATCHED THEN
INSERT (first_name, last_name, email, salary)

SQL Complete Guide | Exam & Interview Preparation

SQL — Complete Study Guide | Exam & Interview Ready

VALUES (source.first_name, source.last_name, [Link], [Link]);

-- PostgreSQL equivalent (INSERT ... ON CONFLICT):

INSERT INTO employees (email, first_name, salary)
VALUES ('new@[Link]', 'New', 75000)
ON CONFLICT (email)
DO UPDATE SET salary = [Link], first_name = EXCLUDED.first_name;

-- MySQL equivalent (INSERT ... ON DUPLICATE KEY UPDATE):

INSERT INTO employees (email, salary) VALUES ('x@[Link]', 75000)
ON DUPLICATE KEY UPDATE salary = VALUES(salary);

3.5 Transactions & ACID Properties

-- A transaction groups statements into an atomic unit:
BEGIN TRANSACTION; -- or BEGIN; in PostgreSQL/MySQL

UPDATE accounts SET balance = balance - 500 WHERE account_id = 101;

UPDATE accounts SET balance = balance + 500 WHERE account_id = 202;

-- If both succeed:
COMMIT;

-- If something goes wrong, undo all changes:

ROLLBACK;

-- SAVEPOINT: partial rollback within a transaction

BEGIN;
INSERT INTO orders (...) VALUES (...);
SAVEPOINT after_insert;
UPDATE inventory SET qty = qty - 1 WHERE ...;
-- If update fails:
ROLLBACK TO SAVEPOINT after_insert;
COMMIT;

ACID Property Meaning Example

Atomicity All operations in a transaction Bank transfer: both debit AND credit,
succeed together or all fail together or neither
Consistency A transaction brings the database Foreign key constraints still satisfied
from one valid state to another valid after transaction
state
Isolation Concurrent transactions execute as if Two users booking the last seat —
they were sequential — one can't see only one succeeds
another's uncommitted changes

SQL Complete Guide | Exam & Interview Preparation

SQL — Complete Study Guide | Exam & Interview Ready

ACID Property Meaning Example

Durability Once committed, data persists even Power outage after COMMIT — data
in case of system failure is still saved on restart

SQL Complete Guide | Exam & Interview Preparation

SQL — Complete Study Guide | Exam & Interview Ready

04 SELECT — Querying Data

The complete SELECT anatomy, ORDER BY, LIMIT, DISTINCT

Chapter 4: SELECT — Querying Data

4.1 The Full SELECT Statement Anatomy

SELECT [DISTINCT] column_list -- What columns to return
FROM table_name -- Where to get the data
[JOIN other_table ON condition] -- Combine with other tables
[WHERE condition] -- Filter individual rows
[GROUP BY column(s)] -- Group rows for aggregation
[HAVING aggregate_condition] -- Filter groups (after GROUP BY)
[ORDER BY column [ASC|DESC]] -- Sort the result
[LIMIT n] / [TOP n] / [FETCH FIRST n] -- Limit result set size
[OFFSET n] -- Skip first n rows (pagination)

4.2 Logical Execution Order

SQL clauses are WRITTEN in one order but EXECUTED in a completely different order. This is
critical for understanding why certain things work or fail:

Execution Clause What Happens

Order
1 FROM + JOINs Identify source tables; apply join conditions to combine them
2 WHERE Filter individual rows from the joined result — no aggregates
allowed here
3 GROUP BY Group remaining rows by specified columns
4 HAVING Filter groups — aggregate functions allowed here (unlike
WHERE)
5 SELECT Evaluate expressions, compute aggregates, apply DISTINCT
6 ORDER BY Sort the final result — can reference SELECT aliases here
7 LIMIT / OFFSET Return only the specified rows from the sorted result

Why Execution Order Matters

You CANNOT use a SELECT alias in a WHERE clause because WHERE executes before
SELECT. You CAN use a SELECT alias in ORDER BY. You CANNOT use aggregate functions
in WHERE — use HAVING instead. CTEs and subqueries can work around these restrictions.

SQL Complete Guide | Exam & Interview Preparation

SQL — Complete Study Guide | Exam & Interview Ready

4.3 SELECT Examples

-- Select all columns (avoid in production — use explicit columns):
SELECT * FROM employees;

-- Select specific columns with alias:

SELECT
employee_id,
first_name || ' ' || last_name AS full_name, -- String concat
salary,
salary * 1.10 AS salary_with_raise,
UPPER(department_name) AS dept
FROM employees;

-- DISTINCT: remove duplicate rows from result:

SELECT DISTINCT department_id FROM employees;
SELECT DISTINCT country, city FROM customers; -- Distinct combination

-- ORDER BY: sort results (default ASC)

SELECT * FROM employees ORDER BY salary DESC, last_name ASC;

-- LIMIT / TOP / FETCH FIRST:

SELECT * FROM employees ORDER BY salary DESC LIMIT 10; -- MySQL,
PostgreSQL
SELECT TOP 10 * FROM employees ORDER BY salary DESC; -- SQL Server
SELECT * FROM employees ORDER BY salary DESC FETCH FIRST 10 ROWS ONLY; --
Oracle

-- Pagination with OFFSET:

SELECT * FROM products ORDER BY product_id LIMIT 20 OFFSET 40; -- Page 3 (20
per page)

SQL Complete Guide | Exam & Interview Preparation

SQL — Complete Study Guide | Exam & Interview Ready

05 Filtering & Operators

WHERE clause, comparison operators, LIKE, IN, BETWEEN, CASE

Chapter 5: Filtering & Operators

5.1 Comparison & Logical Operators

Operator Meaning Example
= Equal to WHERE status = 'active'
<> or != Not equal to WHERE status <> 'deleted'
> < Greater than / Less than WHERE salary > 50000
>= <= Greater/Less than or equal WHERE age >= 18 AND age <= 65
to
AND Both conditions must be WHERE dept_id = 3 AND salary > 60000
true
OR At least one condition WHERE dept_id = 3 OR dept_id = 5
must be true
NOT Negate a condition WHERE NOT status = 'inactive'
BETWEEN Inclusive range check WHERE salary BETWEEN 50000 AND
80000
IN Match any value in a list WHERE dept_id IN (1, 3, 5, 7)
NOT IN Not match any value in a WHERE country NOT IN ('US', 'CA')
list
LIKE Pattern match with WHERE email LIKE '%@[Link]'
wildcards
ILIKE Case-insensitive LIKE WHERE name ILIKE 'john%'
(PostgreSQL)
IS NULL Check for NULL value WHERE manager_id IS NULL
IS NOT NULL Check for non-NULL value WHERE email IS NOT NULL
EXISTS True if subquery returns WHERE EXISTS (SELECT 1 FROM orders
any rows WHERE ...)

5.2 LIKE Pattern Matching

SQL Complete Guide | Exam & Interview Preparation

SQL — Complete Study Guide | Exam & Interview Ready

-- Wildcards: % = any sequence of characters, _ = exactly one character

SELECT * FROM employees WHERE last_name LIKE 'S%'; -- Starts with S

SELECT * FROM employees WHERE last_name LIKE '%son'; -- Ends with son
SELECT * FROM employees WHERE email LIKE '%@[Link]'; -- Gmail addresses
SELECT * FROM products WHERE sku LIKE 'PRD-___-2024'; -- SKU with exact
format
SELECT * FROM employees WHERE phone LIKE '___-___-____'; -- Phone format: 123-
456-7890

-- Escape a wildcard character with ESCAPE:

SELECT * FROM products WHERE description LIKE '50\% off' ESCAPE '\';

-- NOT LIKE:
SELECT * FROM employees WHERE email NOT LIKE '%@[Link]';

-- Case sensitivity depends on database collation:

-- MySQL: case-insensitive by default for LIKE
-- PostgreSQL: case-sensitive; use ILIKE for case-insensitive
-- SQL Server: depends on collation (usually case-insensitive)

5.3 CASE Expression

-- Simple CASE (compare one value to many):
SELECT
employee_id,
salary,
CASE department_id
WHEN 1 THEN 'Engineering'
WHEN 2 THEN 'Marketing'
WHEN 3 THEN 'Sales'
ELSE 'Other'
END AS department_name
FROM employees;

-- Searched CASE (each condition is its own expression):

SELECT
employee_id,
salary,
CASE
WHEN salary >= 100000 THEN 'Senior'
WHEN salary >= 70000 THEN 'Mid-Level'
WHEN salary >= 50000 THEN 'Junior'
ELSE 'Entry Level'
END AS salary_band
FROM employees;

-- CASE in ORDER BY (custom sort order):

SQL Complete Guide | Exam & Interview Preparation

SQL — Complete Study Guide | Exam & Interview Ready

SELECT * FROM tasks

ORDER BY
CASE priority
WHEN 'Critical' THEN 1
WHEN 'High' THEN 2
WHEN 'Medium' THEN 3
ELSE 4
END;

-- CASE in aggregation (conditional count):

SELECT
COUNT(*) AS total_employees,
COUNT(CASE WHEN salary > 80000 THEN 1 END) AS high_earners,
SUM(CASE WHEN department_id = 3 THEN salary ELSE 0 END) AS sales_payroll
FROM employees;

SQL Complete Guide | Exam & Interview Preparation

SQL — Complete Study Guide | Exam & Interview Ready

06 Aggregate Functions & Grouping

GROUP BY, HAVING, COUNT, SUM, AVG, ROLLUP, CUBE

Chapter 6: Aggregate Functions & Grouping

6.1 Aggregate Functions

Function Description NULL Handling
COUNT(*) Count ALL rows including NULLs Counts NULL rows
COUNT(col) Count non-NULL values in a Ignores NULLs
column
COUNT(DISTINCT col) Count unique non-NULL values Ignores NULLs
SUM(col) Sum of all non-NULL numeric Ignores NULLs
values
AVG(col) Average of non-NULL values Ignores NULLs — may be
(sum/count of non-NULLs) misleading
MIN(col) Minimum value — works on Ignores NULLs
numbers, strings, dates
MAX(col) Maximum value — works on Ignores NULLs
numbers, strings, dates
GROUP_CONCAT / Concatenate values within a Ignores NULLs
STRING_AGG group into a string

6.2 GROUP BY
-- Count employees per department:
SELECT
department_id,
COUNT(*) AS employee_count,
AVG(salary) AS avg_salary,
MIN(salary) AS min_salary,
MAX(salary) AS max_salary,
SUM(salary) AS total_payroll
FROM employees
GROUP BY department_id;

-- GROUP BY multiple columns:

SELECT
department_id,

SQL Complete Guide | Exam & Interview Preparation

SQL — Complete Study Guide | Exam & Interview Ready

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

-- IMPORTANT: Every non-aggregated column in SELECT must be in GROUP BY:

-- This query is INVALID:
SELECT first_name, department_id, COUNT(*) FROM employees GROUP BY
department_id;
-- Fix: either aggregate first_name or add it to GROUP BY

6.3 HAVING — Filtering Groups

-- HAVING filters groups AFTER aggregation (unlike WHERE which filters rows):
SELECT
department_id,
COUNT(*) AS headcount,
AVG(salary) AS avg_salary
FROM employees
WHERE hire_date >= '2020-01-01' -- WHERE filters BEFORE grouping (rows)
GROUP BY department_id
HAVING COUNT(*) >= 5 -- HAVING filters AFTER grouping (groups)
AND AVG(salary) > 60000
ORDER BY avg_salary DESC;

-- You CANNOT use aggregate functions in WHERE:

-- WRONG: WHERE COUNT(*) > 5
-- CORRECT: HAVING COUNT(*) > 5

6.4 ROLLUP and CUBE (Advanced Grouping)

-- ROLLUP: creates subtotals and grand totals along a hierarchy:
SELECT
department_id,
YEAR(hire_date) AS hire_year,
COUNT(*) AS headcount
FROM employees
GROUP BY ROLLUP (department_id, YEAR(hire_date));
-- Result includes: dept+year rows, dept subtotals, grand total

-- CUBE: creates all possible combinations of subtotals:

SELECT department_id, job_title, COUNT(*)
FROM employees
GROUP BY CUBE (department_id, job_title);

SQL Complete Guide | Exam & Interview Preparation

SQL — Complete Study Guide | Exam & Interview Ready

-- GROUPING SETS: specify exactly which groups you want:

SELECT department_id, job_title, COUNT(*)
FROM employees
GROUP BY GROUPING SETS (
(department_id, job_title), -- detail level
(department_id), -- dept subtotal
() -- grand total
);

GROUP_CONCAT / STRING_AGG
MySQL: GROUP_CONCAT(col ORDER BY col SEPARATOR ', ') | PostgreSQL:
STRING_AGG(col, ', ' ORDER BY col) | SQL Server: STRING_AGG(col, ', ') WITHIN GROUP
(ORDER BY col) — Aggregates multiple row values into a comma-separated string within a
group.

SQL Complete Guide | Exam & Interview Preparation

SQL — Complete Study Guide | Exam & Interview Ready

07 Joins
INNER, LEFT, RIGHT, FULL OUTER, CROSS, SELF JOIN, set operations

Chapter 7: Joins

7.1 Visual Guide to Joins

Join Type Returns NULL Rows Use Case
INNER JOIN Only rows with matching None Get data that
keys in BOTH tables exists in both
tables
LEFT JOIN All rows from LEFT table + Right side NULLs for Keep all left rows,
matching rows from non-matches add right data
RIGHT where available
RIGHT JOIN All rows from RIGHT table Left side NULLs for Keep all right
+ matching rows from non-matches rows (equivalent
LEFT to swapping
LEFT JOIN)
FULL OUTER JOIN All rows from BOTH tables NULLs on both sides Find all
regardless of match for non-matches unmatched rows
in either table
CROSS JOIN Every combination of rows None — no join Generate all
(Cartesian product) condition possible
combinations
(e.g., size × color)
SELF JOIN A table joined to itself Depends on join type Hierarchy, org
charts, comparing
rows in same
table

7.2 INNER JOIN

-- Basic INNER JOIN syntax:
SELECT
e.employee_id,
e.first_name,
e.last_name,
d.department_name,
[Link]
FROM employees e

SQL Complete Guide | Exam & Interview Preparation

SQL — Complete Study Guide | Exam & Interview Ready

INNER JOIN departments d ON e.department_id = d.department_id;

-- JOIN keyword without INNER is also INNER JOIN (default):

FROM employees e JOIN departments d ON e.department_id = d.department_id

-- Multi-table JOIN:
SELECT e.first_name, d.department_name, [Link]
FROM employees e
JOIN departments d ON e.department_id = d.department_id
JOIN locations l ON d.location_id = l.location_id
WHERE [Link] > 80000;

7.3 LEFT JOIN (LEFT OUTER JOIN)

-- Find employees and their departments (include employees without a
department):
SELECT e.first_name, e.last_name, d.department_name
FROM employees e
LEFT JOIN departments d ON e.department_id = d.department_id;
-- Employees with no department: department_name = NULL

-- Find employees WITH NO DEPARTMENT (anti-join pattern):

SELECT e.first_name, e.last_name
FROM employees e
LEFT JOIN departments d ON e.department_id = d.department_id
WHERE d.department_id IS NULL; -- NULL on right = no match in departments

-- Count with LEFT JOIN (include departments with zero employees):

SELECT d.department_name, COUNT(e.employee_id) AS headcount
FROM departments d
LEFT JOIN employees e ON d.department_id = e.department_id
GROUP BY d.department_name
ORDER BY headcount DESC;
-- Use COUNT(e.employee_id) not COUNT(*) to count non-NULL employee refs

7.4 FULL OUTER JOIN

-- Get all employees AND all departments, showing unmatched on both sides:
SELECT e.first_name, d.department_name
FROM employees e
FULL OUTER JOIN departments d ON e.department_id = d.department_id;
-- Employees without dept: department_name = NULL
-- Departments without employees: first_name = NULL

-- MySQL does not have FULL OUTER JOIN — simulate with UNION:

SQL Complete Guide | Exam & Interview Preparation

SQL — Complete Study Guide | Exam & Interview Ready

SELECT e.first_name, d.department_name

FROM employees e LEFT JOIN departments d ON e.department_id = d.department_id
UNION
SELECT e.first_name, d.department_name
FROM employees e RIGHT JOIN departments d ON e.department_id =
d.department_id;

7.5 SELF JOIN

-- Manager hierarchy: each employee has a manager_id referencing the same
table
SELECT
e.first_name || ' ' || e.last_name AS employee,
m.first_name || ' ' || m.last_name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.employee_id;
-- LEFT JOIN ensures top-level employees (no manager) still appear

-- Find employees hired in the same year as a specific employee:

SELECT a.first_name, b.first_name AS same_year_colleague
FROM employees a
JOIN employees b ON YEAR(a.hire_date) = YEAR(b.hire_date)
WHERE a.employee_id <> b.employee_id -- Exclude self
AND a.employee_id = 42;

7.6 Set Operations: UNION, INTERSECT, EXCEPT

-- UNION: combine results of two queries, removes duplicates:
SELECT first_name, last_name FROM employees
UNION
SELECT first_name, last_name FROM contractors;

-- UNION ALL: combine WITHOUT removing duplicates (faster — no dedup step):

SELECT product_id FROM 2023_sales
UNION ALL
SELECT product_id FROM 2024_sales;

-- INTERSECT: rows that appear in BOTH result sets:

SELECT customer_id FROM 2023_customers
INTERSECT
SELECT customer_id FROM 2024_customers;
-- Returns customers who bought in BOTH 2023 and 2024

-- EXCEPT (MINUS in Oracle): rows in first set NOT in second:

SELECT customer_id FROM 2023_customers
EXCEPT

SQL Complete Guide | Exam & Interview Preparation

SQL — Complete Study Guide | Exam & Interview Ready

SELECT customer_id FROM 2024_customers;

-- Returns customers who bought in 2023 but NOT in 2024 (lapsed customers)

-- Rules: same number of columns, compatible data types, ORDER BY only at end

UNION vs UNION ALL

UNION removes duplicate rows by performing a sort/hash deduplication step — this adds
overhead. UNION ALL keeps all rows including duplicates and is always faster. Use UNION
ALL when you know there are no duplicates or when duplicates are acceptable. Always prefer
UNION ALL unless you specifically need deduplication.

SQL Complete Guide | Exam & Interview Preparation

SQL — Complete Study Guide | Exam & Interview Ready

08 Subqueries & CTEs

Correlated subqueries, EXISTS, WITH clause, recursive CTEs

Chapter 8: Subqueries & CTEs

8.1 Subqueries
-- Scalar subquery in SELECT (returns exactly one value):
SELECT
first_name,
salary,
(SELECT AVG(salary) FROM employees) AS company_avg,
salary - (SELECT AVG(salary) FROM employees) AS diff_from_avg
FROM employees;

-- Subquery in WHERE (returns a single value):

SELECT * FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

-- Subquery with IN (returns a list):

SELECT * FROM orders
WHERE customer_id IN (
SELECT customer_id FROM customers WHERE country = 'US'
);

-- Subquery in FROM (derived table / inline view):

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

8.2 Correlated Subqueries

-- A correlated subquery references the outer query — re-runs for EACH outer
row:

-- Find employees earning more than their department's average:

SELECT first_name, salary, department_id
FROM employees e_outer
WHERE salary > (
SELECT AVG(salary)

SQL Complete Guide | Exam & Interview Preparation

SQL — Complete Study Guide | Exam & Interview Ready

FROM employees e_inner

WHERE e_inner.department_id = e_outer.department_id -- References outer
row!
);

-- Find the most recent order for each customer:

SELECT c.customer_name, o.order_date, o.total_amount
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
WHERE o.order_date = (
SELECT MAX(order_date) FROM orders
WHERE customer_id = c.customer_id -- Correlated reference
);

8.3 EXISTS vs IN
-- EXISTS: returns TRUE if the subquery returns ANY rows (short-circuits on
first match):
SELECT * FROM customers c
WHERE EXISTS (
SELECT 1 -- Value doesn't matter — just checking existence
FROM orders o
WHERE o.customer_id = c.customer_id
);

-- NOT EXISTS: find customers with NO orders (anti-join):

SELECT * FROM customers c
WHERE NOT EXISTS (
SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id
);

-- IN vs EXISTS performance:
-- IN works well when subquery returns a small, static list
-- EXISTS is better when subquery references outer table (correlated) or large
sets
-- NOT IN has a dangerous edge case: if any value in the list is NULL,
-- it returns NO rows at all! NOT EXISTS is safer.

-- Dangerous NULL edge case with NOT IN:

SELECT * FROM A WHERE id NOT IN (SELECT id FROM B); -- Returns nothing if B
has ANY NULL!
-- Safe alternative:
SELECT * FROM A WHERE NOT EXISTS (SELECT 1 FROM B WHERE [Link] = [Link]);

8.4 CTEs — Common Table Expressions

SQL Complete Guide | Exam & Interview Preparation

SQL — Complete Study Guide | Exam & Interview Ready

-- CTE syntax (WITH clause) — like a named temporary result set:

WITH high_earners AS (
SELECT employee_id, first_name, salary, department_id
FROM employees
WHERE salary > 80000
)
SELECT h.first_name, [Link], d.department_name
FROM high_earners h
JOIN departments d ON h.department_id = d.department_id;

-- Multiple CTEs (chained):

WITH
dept_avg AS (
SELECT department_id, AVG(salary) AS avg_salary
FROM employees
GROUP BY department_id
),
above_avg_depts AS (
SELECT department_id FROM dept_avg WHERE avg_salary > 75000
)
SELECT e.first_name, [Link]
FROM employees e
WHERE e.department_id IN (SELECT department_id FROM above_avg_depts);

8.5 Recursive CTEs

-- Recursive CTE: traverse hierarchical data (org charts, BOMs, categories):
WITH RECURSIVE org_chart AS (
-- Anchor member: the root node (CEO with no manager)
SELECT employee_id, first_name, manager_id, 0 AS level
FROM employees
WHERE manager_id IS NULL

UNION ALL

-- Recursive member: join back to itself

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 employee_id, first_name, level,
REPEAT(' ', level) || first_name AS indented_name
FROM org_chart
ORDER BY level, employee_id;

CTE vs Subquery vs Temp Table

SQL Complete Guide | Exam & Interview Preparation

SQL — Complete Study Guide | Exam & Interview Ready

CTEs improve readability and allow referencing the same logic multiple times. Subqueries are
inline and can be harder to read when nested deeply. Temp tables (#table in SQL Server,
TEMP TABLE in PostgreSQL) physically store results — useful when the same intermediate
result is queried many times in complex stored procedures. CTEs are not necessarily
materialized — the optimizer may inline them.

SQL Complete Guide | Exam & Interview Preparation

SQL — Complete Study Guide | Exam & Interview Ready

09 Window Functions
ROW_NUMBER, RANK, DENSE_RANK, LAG, LEAD, OVER, PARTITION BY

Chapter 9: Window Functions

9.1 What Are Window Functions?

Window functions perform calculations across a set of rows that are related to the current row,
WITHOUT collapsing the rows into a group (unlike GROUP BY). They retain all rows while adding
computed values. They are executed AFTER WHERE, GROUP BY, and HAVING — but BEFORE
ORDER BY at the final output stage.

-- Anatomy of a window function:

function_name(expression) OVER (
[PARTITION BY column(s)] -- Divide rows into groups (like GROUP BY but
keeps all rows)
[ORDER BY column(s)] -- Define row order within each partition
[frame_clause] -- Define window frame (ROWS/RANGE BETWEEN ...)
)

9.2 Ranking Functions

SELECT
employee_id,
first_name,
salary,
department_id,

-- ROW_NUMBER: unique sequential number — no ties

ROW_NUMBER() OVER (ORDER BY salary DESC) AS row_num,

-- RANK: tied rows get same rank, next rank skips (1,2,2,4)
RANK() OVER (ORDER BY salary DESC) AS rank_val,

-- DENSE_RANK: tied rows get same rank, NO gaps (1,2,2,3)

DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rank_val,

-- NTILE: divide rows into N equal buckets

NTILE(4) OVER (ORDER BY salary DESC) AS salary_quartile,

-- PERCENT_RANK: relative rank as a percentage (0 to 1)

PERCENT_RANK() OVER (ORDER BY salary) AS pct_rank

SQL Complete Guide | Exam & Interview Preparation

SQL — Complete Study Guide | Exam & Interview Ready

FROM employees;

-- PARTITION BY: rank within each department independently:

SELECT
first_name, department_id, salary,
RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS dept_rank
FROM employees;
-- Returns rank 1 for highest earner in EACH department

9.3 Offset Functions: LAG, LEAD

-- LAG: access a value from a PREVIOUS row in the partition
-- LEAD: access a value from a NEXT row in the partition

SELECT
order_date,
total_amount,

-- Previous row's amount (month-over-month comparison):

LAG(total_amount, 1, 0) OVER (ORDER BY order_date) AS prev_amount,

-- Next row's amount:

LEAD(total_amount, 1) OVER (ORDER BY order_date) AS next_amount,

-- Month-over-month change:
total_amount - LAG(total_amount, 1, 0) OVER (ORDER BY order_date) AS
mom_change,

-- MoM growth %:
ROUND(
(total_amount - LAG(total_amount) OVER (ORDER BY order_date))
/ NULLIF(LAG(total_amount) OVER (ORDER BY order_date), 0) * 100, 2
) AS mom_growth_pct

FROM monthly_sales;

9.4 Aggregate Window Functions

-- Running total (cumulative sum):
SELECT
order_date,
daily_revenue,
SUM(daily_revenue) OVER (ORDER BY order_date) AS running_total
FROM daily_sales;

SQL Complete Guide | Exam & Interview Preparation

SQL — Complete Study Guide | Exam & Interview Ready

-- Running total within a partition (per department):

SELECT
department_id, employee_id, salary,
SUM(salary) OVER (PARTITION BY department_id ORDER BY hire_date) AS
running_dept_payroll
FROM employees;

-- Moving average (3-row rolling average):

SELECT
order_date,
daily_revenue,
AVG(daily_revenue) OVER (
ORDER BY order_date
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW -- current + 2 prev rows
) AS rolling_3day_avg
FROM daily_sales;

-- Compare to overall average without losing row detail:

SELECT
first_name, salary,
AVG(salary) OVER () AS company_avg,
salary - AVG(salary) OVER () AS diff_from_avg,
AVG(salary) OVER (PARTITION BY department_id) AS dept_avg
FROM employees;
-- OVER () with no clauses = entire result set as window

9.5 FIRST_VALUE, LAST_VALUE, NTH_VALUE

-- FIRST_VALUE and LAST_VALUE:
SELECT
employee_id, department_id, salary,
FIRST_VALUE(salary) OVER (PARTITION BY department_id ORDER BY salary DESC)
AS dept_highest_salary,
LAST_VALUE(salary) OVER (
PARTITION BY department_id ORDER BY salary DESC
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
) AS dept_lowest_salary
-- LAST_VALUE needs explicit frame — default frame only goes to current
row!
FROM employees;

-- Get top earner per department in one query:

SELECT * FROM (
SELECT *,
ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) AS
rn
FROM employees
) ranked
WHERE rn = 1; -- Filter to only rank 1 per department

SQL Complete Guide | Exam & Interview Preparation

SQL — Complete Study Guide | Exam & Interview Ready

Window Function Frame Clause

ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW = from start of partition
to current row (default for ORDER BY). ROWS BETWEEN UNBOUNDED PRECEDING AND
UNBOUNDED FOLLOWING = entire partition. ROWS BETWEEN 2 PRECEDING AND 2
FOLLOWING = 5-row rolling window. RANGE uses logical row position (handles ties), ROWS
uses physical position.

SQL Complete Guide | Exam & Interview Preparation

SQL — Complete Study Guide | Exam & Interview Ready

10 String, Date & Math Functions

Manipulation, parsing, date arithmetic, number functions

Chapter 10: String, Date & Math Functions

10.1 String Functions

-- Length:
SELECT LENGTH('Hello'); -- PostgreSQL/MySQL: 5
SELECT LEN('Hello'); -- SQL Server: 5

-- Case:
SELECT UPPER('hello'), LOWER('WORLD'), INITCAP('hello world'); -- INITCAP:
PostgreSQL

-- Trim whitespace:
SELECT TRIM(' hello '); -- 'hello'
SELECT LTRIM(' hello '); -- 'hello '
SELECT RTRIM(' hello '); -- ' hello'

-- Substring:
SELECT SUBSTRING('Hello World', 7, 5); -- 'World' (start 7, length 5) —
Standard SQL
SELECT SUBSTR('Hello World', 7, 5); -- Same, Oracle/MySQL alias
SELECT LEFT('Hello World', 5); -- 'Hello'
SELECT RIGHT('Hello World', 5); -- 'World'

-- Find position:
SELECT POSITION('World' IN 'Hello World'); -- 7
SELECT CHARINDEX('World', 'Hello World'); -- 7 (SQL Server)
SELECT INSTR('Hello World', 'World'); -- 7 (Oracle/MySQL)

-- Replace:
SELECT REPLACE('Hello World', 'World', 'SQL'); -- 'Hello SQL'

-- Concatenate:
SELECT 'Hello' || ' ' || 'World'; -- Standard SQL / PostgreSQL /
Oracle
SELECT CONCAT('Hello', ' ', 'World'); -- All dialects
SELECT CONCAT_WS(', ', 'Alice', 'Smith', 'NYC'); -- 'Alice, Smith, NYC'

-- Pad:
SELECT LPAD('42', 6, '0'); -- '000042' (left pad to length 6 with '0')
SELECT RPAD('ABC', 6, '-'); -- 'ABC---'

-- Repeat:

SQL Complete Guide | Exam & Interview Preparation

SQL — Complete Study Guide | Exam & Interview Ready

SELECT REPEAT('-', 20); -- '--------------------'

10.2 Date & Time Functions

-- Current date / time:
SELECT CURRENT_DATE; -- Date only (all dialects)
SELECT CURRENT_TIMESTAMP; -- Date + time (all dialects)
SELECT NOW(); -- PostgreSQL / MySQL alias
SELECT GETDATE(); -- SQL Server
SELECT SYSDATE; -- Oracle

-- Extract parts:
SELECT YEAR(hire_date), MONTH(hire_date), DAY(hire_date) FROM employees; --
MySQL/SQL Server
SELECT EXTRACT(YEAR FROM hire_date) FROM employees; -- PostgreSQL / Oracle
(standard)
SELECT DATE_PART('year', hire_date) FROM employees; -- PostgreSQL

-- Date arithmetic:
SELECT hire_date + INTERVAL '30 days' FROM employees; -- PostgreSQL
SELECT DATEADD(day, 30, hire_date) FROM employees; -- SQL Server
SELECT hire_date + 30 FROM employees; -- Oracle (adds days)

-- Date difference:
SELECT DATEDIFF(day, hire_date, CURRENT_DATE) AS days_employed FROM employees;
-- SQL Server
SELECT CURRENT_DATE - hire_date AS days_employed FROM employees;
-- PostgreSQL
SELECT TIMESTAMPDIFF(YEAR, birth_date, CURDATE()) AS age FROM persons;
-- MySQL

-- Format date as string:

SELECT TO_CHAR(hire_date, 'YYYY-MM-DD') FROM employees; -- PostgreSQL /
Oracle
SELECT FORMAT(hire_date, 'yyyy-MM-dd') FROM employees; -- SQL Server
SELECT DATE_FORMAT(hire_date, '%Y-%m-%d') FROM employees; -- MySQL

-- Truncate to start of period:

SELECT DATE_TRUNC('month', order_date) FROM orders; -- PostgreSQL
SELECT TRUNC(order_date, 'MM') FROM orders; -- Oracle
SELECT DATETRUNC(month, order_date) FROM orders; -- SQL Server 2022+

10.3 Math Functions

SELECT ABS(-15); -- 15 (absolute value)
SELECT CEIL(4.3), CEILING(4.3); -- 5 (round up)

SQL Complete Guide | Exam & Interview Preparation

SQL — Complete Study Guide | Exam & Interview Ready

SELECT FLOOR(4.9); -- 4 (round down)

SELECT ROUND(4.567, 2); -- 4.57 (round to 2 decimals)
SELECT ROUND(4.567, 0); -- 5 (round to integer)
SELECT TRUNCATE(4.567, 2); -- 4.56 (MySQL: truncate, no rounding)
SELECT TRUNC(4.567, 2); -- 4.56 (Oracle / PostgreSQL)
SELECT MOD(17, 5); -- 2 (modulo / remainder)
SELECT POWER(2, 10); -- 1024
SELECT SQRT(144); -- 12
SELECT LOG(100); -- log base 10 (MySQL: 2, PostgreSQL: ~4.6
natural)
SELECT LOG(10, 100); -- 2 (PostgreSQL: log base 10)
SELECT EXP(1); -- e = 2.718...
SELECT PI(); -- 3.14159...
SELECT SIGN(-42); -- -1 (returns -1, 0, or 1)
SELECT RAND(); -- Random float 0-1 (MySQL)
SELECT RANDOM(); -- Random float 0-1 (PostgreSQL)

SQL Complete Guide | Exam & Interview Preparation

SQL — Complete Study Guide | Exam & Interview Ready

11 Views, Indexes & Performance

Views, indexing strategies, EXPLAIN, optimization techniques

Chapter 11: Views, Indexes & Query Performance

11.1 Views
A view is a named, stored SELECT query. It behaves like a virtual table — you can SELECT from it,
JOIN it, and in some cases INSERT/UPDATE/DELETE through it. Views do not store data
themselves (unless materialized).

-- Create a view:
CREATE VIEW high_salary_employees AS
SELECT employee_id, first_name, last_name, salary, department_id
FROM employees
WHERE salary > 80000;

-- Query a view like a table:

SELECT * FROM high_salary_employees WHERE department_id = 3;

-- Update a view:
CREATE OR REPLACE VIEW high_salary_employees AS
SELECT employee_id, first_name, last_name, salary, department_id
FROM employees WHERE salary > 90000;

-- Drop a view:
DROP VIEW IF EXISTS high_salary_employees;

-- Materialized View (PostgreSQL) — physically stores results:

CREATE MATERIALIZED VIEW monthly_sales_summary AS
SELECT DATE_TRUNC('month', order_date) AS month, SUM(amount) AS total
FROM orders GROUP BY 1;

-- Refresh materialized view:

REFRESH MATERIALIZED VIEW monthly_sales_summary;

11.2 Indexes
An index is a data structure that the database engine uses to find rows faster — like the index at the
back of a book. They dramatically speed up SELECT queries at the cost of slower
INSERT/UPDATE/DELETE and more storage.

SQL Complete Guide | Exam & Interview Preparation

SQL — Complete Study Guide | Exam & Interview Ready

Index Type Description Best For

B-Tree (default) Balanced tree — supports =, <, >, Most general-purpose queries
BETWEEN, ORDER BY, LIKE 'val
%'
Hash Index Exact-match only — very fast for = Equality lookups only; not for
comparisons ranges
Composite Index Index on multiple columns — order Queries filtering on multiple
matters! columns together
Unique Index Enforces uniqueness + speeds up Email, username, any unique
lookups identifier
Partial Index Index only rows matching a Frequently queried subsets (e.g.,
(PostgreSQL) condition active records)
Full-Text Index Optimized for text search Document/text search
(CONTAINS, MATCH)
Covering Index Index includes all columns a query High-frequency read queries
needs — avoids table lookup
Clustered Index Table is physically sorted by this Primary key in SQL Server;
index (one per table) InnoDB always clusters on PK

-- Create a standard index:

CREATE INDEX idx_employees_dept ON employees (department_id);

-- Create a unique index:

CREATE UNIQUE INDEX idx_employees_email ON employees (email);

-- Composite index (order matters — filters left-to-right):

CREATE INDEX idx_orders_customer_date ON orders (customer_id, order_date);
-- This index helps queries filtering on: customer_id alone, OR customer_id +
order_date
-- It does NOT help queries filtering ONLY on order_date

-- Partial index (PostgreSQL):

CREATE INDEX idx_active_employees ON employees (department_id) WHERE status =
'active';

-- Drop an index:
DROP INDEX idx_employees_dept; -- PostgreSQL
DROP INDEX idx_employees_dept ON employees; -- MySQL

11.3 EXPLAIN — Reading Query Execution Plans

SQL Complete Guide | Exam & Interview Preparation

SQL — Complete Study Guide | Exam & Interview Ready

-- EXPLAIN shows how the database will execute a query WITHOUT running it:
EXPLAIN SELECT * FROM employees WHERE department_id = 3;

-- EXPLAIN ANALYZE actually runs the query and shows real timing:
EXPLAIN ANALYZE SELECT * FROM employees WHERE department_id = 3; --
PostgreSQL
EXPLAIN FORMAT=JSON SELECT * FROM employees WHERE department_id = 3; -- MySQL

-- Key things to look for in EXPLAIN output:

-- 'Seq Scan' = full table scan (bad for large tables — missing index?)
-- 'Index Scan' = using an index (usually good)
-- 'Index Only Scan' = covering index (best — no table access needed)
-- 'Nested Loop' = usually fine for small tables
-- 'Hash Join' / 'Merge Join' = efficient for larger datasets
-- 'rows' estimate = how many rows the optimizer thinks it will process
-- High 'cost' values or high actual vs estimated row difference = problem

11.4 Query Optimization Techniques

Technique Description
Use indexes on The most impactful optimization — index columns used in
JOIN/WHERE/ORDER columns filters and joins
Avoid SELECT * Only retrieve columns you need — reduces network
overhead and may enable covering indexes
Avoid functions on indexed WHERE YEAR(hire_date) = 2023 prevents index use; use
columns in WHERE WHERE hire_date BETWEEN '2023-01-01' AND '2023-12-
31'
Use EXISTS instead of COUNT for IF EXISTS (SELECT 1 ...) stops at first match; COUNT
existence checks scans all matching rows
Avoid DISTINCT when not needed DISTINCT adds a sort/hash dedup step — ensure it's
actually necessary
Prefer JOINs over correlated Correlated subqueries run once per outer row; JOINs are
subqueries typically optimized better
Paginate large result sets Always use LIMIT/OFFSET or keyset pagination for large
tables
Partition large tables Range/list/hash partitioning reduces scan scope for time-
series and segmented data
Use appropriate data types INT vs BIGINT vs VARCHAR(max) — smaller types =
faster comparisons and more cache efficiency
Avoid LIKE '%value%' leading Leading % prevents index use — consider full-text search
wildcard instead

SQL Complete Guide | Exam & Interview Preparation

SQL — Complete Study Guide | Exam & Interview Ready

Technique Description
Batch large updates/deletes Instead of DELETE 10M rows at once, loop 10,000 rows at
a time to avoid lock escalation
Analyze/Update statistics Outdated statistics cause the optimizer to choose bad
plans — ANALYZE TABLE / UPDATE STATISTICS

SQL Complete Guide | Exam & Interview Preparation

SQL — Complete Study Guide | Exam & Interview Ready

12 Database Design
Normalization, 1NF through BCNF, ERDs, keys, relationships

Chapter 12: Database Design & Normalization

12.1 Normalization Overview

Normalization is the process of organizing a relational database to reduce data redundancy and
improve data integrity. It involves decomposing tables into smaller, well-structured tables and
defining relationships between them. Each 'Normal Form' eliminates a specific type of anomaly.

Normal Form Rule Eliminates

1NF (First) Each column holds atomic (indivisible) Repeating groups, multi-valued
values. No repeating groups. Each row is cells, duplicate rows
unique.
2NF (Second) Meets 1NF + every non-key column is Partial dependencies — only
fully dependent on the ENTIRE primary relevant when PK is composite
key (no partial dependency)
3NF (Third) Meets 2NF + no non-key column depends Transitive dependencies —
on another non-key column (no transitive chain dependencies through
dependency) non-keys
BCNF (Boyce- Meets 3NF + every determinant must be Anomalies that 3NF misses
Codd) a candidate key (stricter version of 3NF) when multiple overlapping
candidate keys exist
4NF (Fourth) Meets BCNF + no multi-valued Independent multi-valued facts
dependencies stored in the same table
5NF (Fifth) Meets 4NF + no join dependencies not Rare — mostly theoretical;
implied by candidate keys applies to complex join
scenarios

12.2 Normalization Example: 1NF 3NF

-- UNNORMALIZED: multiple values in one cell + redundant data
-- OrderID | CustomerName | CustomerCity | Products | ProductPrice
-- 1001 | Alice Smith | New York | Widget, Gadget | 10.00, 25.00

-- 1NF: Atomic values, no repeating groups:

-- OrderItems: OrderID, CustomerName, CustomerCity, ProductName, ProductPrice
-- Problem: CustomerCity depends only on CustomerName (not full PK) = partial
dependency

SQL Complete Guide | Exam & Interview Preparation

SQL — Complete Study Guide | Exam & Interview Ready

-- 2NF: Remove partial dependencies (separate customer data):

-- Customers: CustomerID, CustomerName, CustomerCity
-- Orders: OrderID, CustomerID
-- Products: ProductID, ProductName, ProductPrice
-- OrderItems: OrderID, ProductID, Quantity
-- Now each non-key column depends on the WHOLE key

-- Check for transitive dependencies:

-- If CustomerCity depended on CustomerZip (non-key) = transitive dependency

-- 3NF: Remove transitive dependencies:

-- Customers: CustomerID, CustomerName, ZipCode
-- Locations: ZipCode, City, State -- ZipCode determines City/State
-- Now no non-key column determines another non-key column

12.3 Types of Database Relationships

Relationship Description Implementation
One-to-One (1:1) One row in table A FK in either table with UNIQUE
corresponds to exactly one row constraint
in table B
One-to-Many (1:M) One row in table A FK in the 'many' side table
corresponds to many rows in referencing PK of 'one' side
table B
Many-to-Many (M:M) Many rows in A correspond to Bridge/junction table with two FKs
many rows in B (one to each parent table)

-- Many-to-Many: Students and Courses (a student takes many courses; a course

has many students)

CREATE TABLE students (

student_id INT PRIMARY KEY,
student_name VARCHAR(100) NOT NULL
);

CREATE TABLE courses (

course_id INT PRIMARY KEY,
course_name VARCHAR(100) NOT NULL
);

-- Bridge/junction table:
CREATE TABLE enrollments (
student_id INT REFERENCES students(student_id),

SQL Complete Guide | Exam & Interview Preparation

SQL — Complete Study Guide | Exam & Interview Ready

course_id INT REFERENCES courses(course_id),

enrolled_at DATE DEFAULT CURRENT_DATE,
grade CHAR(2),
PRIMARY KEY (student_id, course_id) -- Composite PK prevents duplicate
enrollment
);

SQL Complete Guide | Exam & Interview Preparation

SQL — Complete Study Guide | Exam & Interview Ready

13 Advanced SQL
Stored procedures, triggers, cursors, JSON, dynamic SQL, pivoting

Chapter 13: Advanced SQL Features

13.1 Stored Procedures

-- PostgreSQL stored procedure (PL/pgSQL):
CREATE OR REPLACE PROCEDURE give_raise(dept_id INT, pct NUMERIC)
LANGUAGE plpgsql AS $$
BEGIN
UPDATE employees
SET salary = salary * (1 + pct / 100)
WHERE department_id = dept_id;
COMMIT;
END;
$$;

CALL give_raise(3, 10); -- Give 10% raise to dept 3

-- SQL Server stored procedure:

CREATE PROCEDURE GiveRaise
@DeptID INT,
@PctIncrease DECIMAL(5,2)
AS BEGIN
UPDATE employees
SET salary = salary * (1 + @PctIncrease / 100)
WHERE department_id = @DeptID;
END;

EXEC GiveRaise @DeptID = 3, @PctIncrease = 10;

13.2 Triggers
-- A trigger fires automatically BEFORE or AFTER a DML event on a table:

-- Example: Audit log trigger — record every salary change

CREATE TABLE salary_audit (
audit_id SERIAL PRIMARY KEY,
employee_id INT,
old_salary DECIMAL(10,2),
new_salary DECIMAL(10,2),
changed_by VARCHAR(100) DEFAULT CURRENT_USER,

SQL Complete Guide | Exam & Interview Preparation

SQL — Complete Study Guide | Exam & Interview Ready

changed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP

);

-- PostgreSQL trigger function:

CREATE OR REPLACE FUNCTION log_salary_change()
RETURNS TRIGGER LANGUAGE plpgsql AS $$
BEGIN
IF [Link] <> [Link] THEN
INSERT INTO salary_audit (employee_id, old_salary, new_salary)
VALUES (OLD.employee_id, [Link], [Link]);
END IF;
RETURN NEW;
END;
$$;

CREATE TRIGGER trg_salary_audit

AFTER UPDATE ON employees
FOR EACH ROW EXECUTE FUNCTION log_salary_change();

13.3 Pivoting Data

-- PIVOT: turn row values into column headers

-- Source data: sales by region and quarter

-- region | quarter | sales
-- North | Q1 | 100000
-- North | Q2 | 120000

-- Manual pivot using CASE (works in all dialects):

SELECT
region,
SUM(CASE WHEN quarter = 'Q1' THEN sales ELSE 0 END) AS Q1,
SUM(CASE WHEN quarter = 'Q2' THEN sales ELSE 0 END) AS Q2,
SUM(CASE WHEN quarter = 'Q3' THEN sales ELSE 0 END) AS Q3,
SUM(CASE WHEN quarter = 'Q4' THEN sales ELSE 0 END) AS Q4
FROM regional_sales
GROUP BY region;

-- SQL Server native PIVOT:

SELECT region, [Q1], [Q2], [Q3], [Q4]
FROM regional_sales
PIVOT (SUM(sales) FOR quarter IN ([Q1],[Q2],[Q3],[Q4])) AS pvt;

13.4 JSON in SQL

SQL Complete Guide | Exam & Interview Preparation

SQL — Complete Study Guide | Exam & Interview Ready

-- PostgreSQL JSON operations:

SELECT data->>'name' AS name, -- Extract text value
data->'address' AS address -- Extract JSON value
FROM customers
WHERE data->>'country' = 'US';

-- Query nested JSON:

SELECT data->'address'->>'city' AS city FROM customers;

-- MySQL JSON:
SELECT JSON_EXTRACT(data, '$.name') AS name FROM customers;
SELECT data->>'$.name' AS name FROM customers; -- Shorthand

-- SQL Server JSON:

SELECT JSON_VALUE(data, '$.name') AS name FROM customers;
SELECT * FROM customers WHERE JSON_VALUE(data, '$.country') = 'US';

-- Aggregate to JSON array (PostgreSQL):

SELECT department_id, JSON_AGG(first_name ORDER BY first_name) AS employees
FROM employees GROUP BY department_id;

SQL Complete Guide | Exam & Interview Preparation

SQL — Complete Study Guide | Exam & Interview Ready

14 SQL Dialects
MySQL, PostgreSQL, SQL Server, Oracle, SQLite comparison

Chapter 14: SQL Dialects — Key Differences

Feature MySQL PostgreSQ SQL Oracle SQLite
L Server
Auto-increment AUTO_INCR SERIAL / IDENTITY(1 GENERATE AUTOINCR
PK EMENT GENERATE ,1) D ALWAYS EMENT
D ALWAYS AS
IDENTITY
String CONCAT() || or + or || or || or ||
concatenation only CONCAT() CONCAT() CONCAT()
Limit rows LIMIT n LIMIT n TOP n / FETCH LIMIT n
FETCH FIRST n /
ROWNUM
Get current date CURDATE() CURRENT_ GETDATE() SYSDATE / date('now')
/ NOW() DATE / / CURRENT_
NOW() GETUTCDA DATE
TE()
String length LENGTH() LENGTH() / LEN() LENGTH() LENGTH()
CHAR_LEN
GTH()
If-null handling IFNULL(col, COALESCE( ISNULL(col, NVL(col, IFNULL(col,
val) col, val) val) val) val)
FULL OUTER Not Supported Supported Supported Not
JOIN supported supported
(use UNION)
Recursive CTE WITH WITH WITH WITH WITH
RECURSIVE RECURSIVE (recursive) (always RECURSIVE
(8.0+) recursive)
Window Supported Fully Fully Fully Supported
functions (8.0+) supported supported supported (3.25+)
JSON support JSON JSON / JSON JSON JSON
(5.7.8+) JSONB functions (12c+) (3.38+)
Case sensitivity Depends on Case- Case- Case- Case-
collation sensitive by insensitive sensitive insensitive
default
Transactions InnoDB only Full ACID Full ACID Full ACID Full ACID

SQL Complete Guide | Exam & Interview Preparation

SQL — Complete Study Guide | Exam & Interview Ready

Feature MySQL PostgreSQ SQL Oracle SQLite

L Server
(not support support support support
MyISAM)

Standard SQL vs. Dialects

ANSI/ISO SQL is the standard, but every RDBMS adds extensions and has slightly different
syntax for things like date functions, string handling, and DDL. When writing portable SQL, stick
to ANSI standard features: COALESCE, CASE, CAST, CURRENT_DATE,
CURRENT_TIMESTAMP, TRIM, SUBSTRING, and standard JOINs. Avoid vendor-specific
functions when portability matters.

SQL Complete Guide | Exam & Interview Preparation

SQL — Complete Study Guide | Exam & Interview Ready

15 Interview Questions & Answers

60+ questions with code examples across all SQL topics

Chapter 15: Interview Questions & Answers

Questions are organized by topic and difficulty. Each answer includes explanation and SQL code
where relevant.

Fundamentals

Q1: What is the difference between DELETE, TRUNCATE, and DROP?

DELETE removes specific rows matching a WHERE clause, is fully logged row by row, supports
transactions (fully rollback-able), and fires row-level triggers. Without a WHERE clause it
removes all rows. TRUNCATE removes ALL rows at once by deallocating data pages — it is
minimally logged, much faster, resets AUTO_INCREMENT/IDENTITY counters, and does NOT
fire row-level triggers. It can be rolled back in PostgreSQL and SQL Server but not MySQL.
DROP completely removes the table object including its structure, indexes, constraints, and all
data — it cannot be rolled back.

Q2: What is a PRIMARY KEY vs a UNIQUE KEY?

Both enforce uniqueness, but PRIMARY KEY additionally enforces NOT NULL — a primary key
column cannot contain NULL values. A table can have only ONE primary key (though it can be
composite — spanning multiple columns). A table can have multiple UNIQUE constraints.
UNIQUE columns CAN contain NULL values (and in most databases, multiple NULLs are
allowed in a UNIQUE column since NULL is not equal to NULL). The PRIMARY KEY is also
used as the default target for FOREIGN KEY references.

Q3: What is a FOREIGN KEY and what is referential integrity?

A FOREIGN KEY is a column (or set of columns) in one table that references the PRIMARY
KEY (or UNIQUE key) of another table. It establishes a link between the two tables. Referential
integrity means the database enforces that the FK value must either be NULL or match an
existing value in the referenced table — you cannot insert a row with a FK value that doesn't
exist in the parent table. ON DELETE CASCADE automatically deletes child rows when the
parent is deleted. ON DELETE SET NULL sets the FK to NULL. ON DELETE RESTRICT
(default) prevents deletion of a parent row that has child rows.

Q4: What is the difference between WHERE and HAVING?

SQL Complete Guide | Exam & Interview Preparation

SQL — Complete Study Guide | Exam & Interview Ready

WHERE filters individual rows BEFORE aggregation — it operates on raw row data and
CANNOT contain aggregate functions (SUM, COUNT, etc.). HAVING filters groups AFTER
aggregation — it operates on the result of GROUP BY and CAN contain aggregate functions.
Rule: if you're filtering a non-aggregated value, use WHERE (it runs first and is more efficient).
If you're filtering based on an aggregate result, use HAVING. Example: WHERE salary > 50000
(filter rows); HAVING COUNT(*) > 5 (filter groups).

Q5: What does NULL mean in SQL and how does it behave in comparisons?
NULL represents the absence of a known value — it is NOT zero, NOT empty string, NOT false.
SQL uses three-valued logic: TRUE, FALSE, and UNKNOWN. Any comparison with NULL
returns UNKNOWN (not TRUE or FALSE): NULL = NULL is UNKNOWN, NULL <> 5 is
UNKNOWN, NULL + 5 is NULL. This is why you must use IS NULL and IS NOT NULL to check
for NULL — never = NULL. Aggregates like SUM, AVG, MIN, MAX, and COUNT(column) all
IGNORE NULLs. COUNT(*) counts all rows including NULLs. NOT IN with a NULL in the list
returns no results — a critical gotcha.

Joins

Q6: What is the difference between INNER JOIN and LEFT JOIN?
INNER JOIN returns only rows where the join condition matches in BOTH tables — rows with
no match in either table are excluded. LEFT JOIN (LEFT OUTER JOIN) returns ALL rows from
the left table plus matching rows from the right table — where there is no match, right table
columns are NULL. Use LEFT JOIN when you want to keep all records from the primary table
regardless of whether a related record exists. The 'anti-join' pattern uses LEFT JOIN with
WHERE [Link] IS NULL to find rows in the left table with NO matching row in the right table.

Q7: What is a SELF JOIN and when would you use it?
A SELF JOIN joins a table to itself — you reference the same table twice with different aliases.
Use cases: (1) Employee-manager hierarchy where manager_id references employee_id in the
same table. (2) Finding pairs of rows with a relationship (employees hired in the same year). (3)
Comparing consecutive rows. (4) Bill of Materials — components that contain other components
from the same parts table. Example: SELECT [Link] AS employee, [Link] AS manager
FROM employees e LEFT JOIN employees m ON e.manager_id = m.employee_id.

Q8: What is the difference between UNION and UNION ALL?

UNION combines results from two queries and removes duplicate rows — it performs an implicit
DISTINCT sort/hash operation which adds overhead. UNION ALL combines results and keeps
all rows including duplicates — it is always faster because no deduplication step is needed.
Rules: both queries must return the same number of columns in the same order with compatible
data types. The column names come from the first query. Use UNION ALL when you know
there are no duplicates or when duplicates are acceptable. Use UNION only when deduplication

SQL Complete Guide | Exam & Interview Preparation

SQL — Complete Study Guide | Exam & Interview Ready

is specifically required.

Aggregations & Window Functions

Q9: What is the difference between COUNT(*), COUNT(column), and

COUNT(DISTINCT column)?
COUNT(*) counts every row including rows with NULLs — it counts rows, not values.
COUNT(column) counts only non-NULL values in that specific column — if 3 out of 100 rows
have NULL in that column, it returns 97. COUNT(DISTINCT column) counts unique non-NULL
values — if 50 rows have the same value and 50 have distinct values, it returns 51 (50 unique +
1 for the repeated). This is a very common interview question. Classic trap: COUNT(*) vs
COUNT(nullable_column) can return different numbers for the same table.

Q10: What is the difference between RANK(), DENSE_RANK(), and ROW_NUMBER()?

All three assign a number to rows in an ordered set. ROW_NUMBER() assigns a unique
sequential integer — no ties, always 1, 2, 3, 4, 5. RANK() handles ties by giving tied rows the
same rank and skipping the next rank(s) — e.g., 1, 2, 2, 4 (rank 3 is skipped). DENSE_RANK()
handles ties by giving tied rows the same rank but does NOT skip ranks — e.g., 1, 2, 2, 3 (no
gaps). Use ROW_NUMBER() when you need exactly one row per partition (e.g., latest record
per customer). Use RANK/DENSE_RANK when ties matter semantically.

Q11: How do you find the second highest salary without using LIMIT/TOP?
Multiple approaches: (1) Subquery: SELECT MAX(salary) FROM employees WHERE salary <
(SELECT MAX(salary) FROM employees). (2) Dense Rank: SELECT salary FROM (SELECT
salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS dr FROM employees) t WHERE
dr = 2. (3) NOT IN: SELECT MAX(salary) FROM employees WHERE salary NOT IN (SELECT
MAX(salary) FROM employees). The DENSE_RANK approach is the most generalizable —
change WHERE dr = 2 to dr = N for the Nth highest.

Q12: How would you calculate a running total in SQL?

Use a window function with SUM and ORDER BY: SELECT order_date, daily_sales,
SUM(daily_sales) OVER (ORDER BY order_date) AS running_total FROM daily_sales; The
OVER (ORDER BY order_date) defines a window that grows from the start of the result to the
current row. To reset the running total per group (e.g., per year): SUM(daily_sales) OVER
(PARTITION BY YEAR(order_date) ORDER BY order_date). Without window functions (older
SQL): use a correlated subquery: SELECT a.order_date, SUM(b.daily_sales) FROM
daily_sales b WHERE b.order_date <= a.order_date.

Q13: What is the difference between LAG and LEAD?

SQL Complete Guide | Exam & Interview Preparation

SQL — Complete Study Guide | Exam & Interview Ready

LAG(expression, offset, default) accesses a value from a PREVIOUS row within the window
partition. LEAD(expression, offset, default) accesses a value from a NEXT row. Both require
ORDER BY in the OVER clause. Default value is returned when the offset goes out of bounds
(first row's LAG or last row's LEAD). Classic use case: month-over-month comparison —
LAG(monthly_revenue, 1) OVER (ORDER BY month) gives last month's revenue, enabling:
current_month - LAG(monthly_revenue) OVER (ORDER BY month) as the change.

Subqueries & CTEs

Q14: What is a correlated subquery and why is it often slow?

A correlated subquery references columns from the outer (parent) query. Unlike a regular
subquery that executes once and returns a static result, a correlated subquery re-executes FOR
EACH ROW processed by the outer query. If the outer query processes 100,000 rows and the
subquery does a table scan each time, that's 100,000 table scans. Solutions: (1) Replace with a
JOIN — most correlated subqueries can be rewritten as JOINs. (2) Use a CTE or subquery to
pre-aggregate the data once. (3) Use a window function (AVG/SUM OVER PARTITION BY)
instead of a correlated subquery for per-group comparisons.

Q15: What is a CTE (Common Table Expression) and when would you use it over a
subquery?
A CTE (WITH clause) is a named temporary result set defined before the main query. Use
CTEs for: (1) Readability — break complex queries into named logical steps rather than deeply
nested subqueries. (2) Reuse — reference the same CTE multiple times in the query without
repeating code. (3) Recursive queries — traverse hierarchies (org charts, category trees) —
only CTEs support recursion in standard SQL. (4) Window function results — filter on window
function output (you can't filter on window function results in WHERE directly). CTEs are not
necessarily materialized — the optimizer may inline them like subqueries. Use temporary tables
instead when the same data is needed many times in a complex stored procedure.

Q16: What is the NOT IN / NULL gotcha and how do you avoid it?
If the subquery in NOT IN returns ANY NULL values, the entire NOT IN condition returns no
rows. This is because NOT IN uses <> comparisons under the hood, and any comparison with
NULL returns UNKNOWN, not TRUE — so no row satisfies the condition. Example: SELECT *
FROM A WHERE id NOT IN (SELECT id FROM B) — if B has even one NULL id, this returns
nothing. Fix: (1) Use NOT EXISTS instead: WHERE NOT EXISTS (SELECT 1 FROM B
WHERE [Link] = [Link]) — NULL-safe. (2) Filter NULLs in subquery: WHERE id NOT IN (SELECT
id FROM B WHERE id IS NOT NULL).

Database Design & Performance

SQL Complete Guide | Exam & Interview Preparation

SQL — Complete Study Guide | Exam & Interview Ready

Q17: What is database normalization and what are the first three normal forms?
Normalization organizes tables to reduce redundancy and prevent data anomalies (insert,
update, delete anomalies). 1NF: Atomic values (no multi-valued cells, no repeating groups),
each row uniquely identifiable. 2NF: Meets 1NF + every non-key column depends on the
ENTIRE primary key — eliminates partial dependencies (only applies when PK is composite).
3NF: Meets 2NF + no non-key column depends on another non-key column — eliminates
transitive dependencies. Practical rule: 'Every non-key column must depend on the key, the
whole key, and nothing but the key.'

Q18: What is a clustered vs non-clustered index?

A clustered index physically orders the table data on disk in the order of the index key. There
can only be ONE clustered index per table (because the data can only be sorted one way). In
SQL Server, the primary key is clustered by default. In MySQL InnoDB, the table is always
organized as a clustered index on the primary key. A non-clustered index is a separate
structure that contains the indexed column values with pointers back to the actual data rows. A
table can have many non-clustered indexes. Non-clustered indexes on columns frequently used
in WHERE, JOIN, or ORDER BY are the most common performance optimization.

Q19: Why should you avoid using a function on an indexed column in a WHERE
clause?
Using a function on an indexed column prevents the database from using the index: WHERE
YEAR(hire_date) = 2023 cannot use an index on hire_date because the database must
evaluate YEAR() for every row. This causes a full table scan. Solution: rewrite to a range that
the index can use: WHERE hire_date >= '2023-01-01' AND hire_date < '2024-01-01'. Similarly,
WHERE UPPER(email) = 'TEST@[Link]' can't use an index on email — instead,
store emails in a consistent case, or create a function-based index (PostgreSQL: CREATE
INDEX ON employees (UPPER(email))).

Q20: How do you find duplicate rows in a table?

Use GROUP BY with HAVING COUNT > 1: SELECT email, COUNT(*) AS occurrences FROM
employees GROUP BY email HAVING COUNT(*) > 1; To see the full duplicate rows: SELECT *
FROM employees WHERE email IN (SELECT email FROM employees GROUP BY email
HAVING COUNT(*) > 1) ORDER BY email; To delete duplicates keeping the row with the
lowest ID: DELETE FROM employees WHERE employee_id NOT IN (SELECT
MIN(employee_id) FROM employees GROUP BY email);

Advanced & Scenario Questions

Q21: Write a query to find employees earning more than their department average.

SQL Complete Guide | Exam & Interview Preparation

SQL — Complete Study Guide | Exam & Interview Ready

Option 1 — Window function (modern, clean): SELECT employee_id, first_name, salary,

department_id, AVG(salary) OVER (PARTITION BY department_id) AS dept_avg FROM
employees to get a derived table, then filter WHERE salary > dept_avg. Option 2 — Correlated
subquery: SELECT first_name, salary, department_id FROM employees e WHERE salary >
(SELECT AVG(salary) FROM employees WHERE department_id = e.department_id). Option 3
— JOIN to aggregated subquery: SELECT e.first_name, [Link] FROM employees e JOIN
(SELECT department_id, AVG(salary) AS avg_sal FROM employees GROUP BY
department_id) d ON e.department_id = d.department_id WHERE [Link] > d.avg_sal. The
window function approach is cleanest and typically most performant.

Q22: How would you pivot rows into columns without a native PIVOT operator?
Use conditional aggregation with CASE: SELECT region, SUM(CASE WHEN quarter = 'Q1'
THEN sales ELSE 0 END) AS Q1, SUM(CASE WHEN quarter = 'Q2' THEN sales ELSE 0 END)
AS Q2, SUM(CASE WHEN quarter = 'Q3' THEN sales ELSE 0 END) AS Q3, SUM(CASE
WHEN quarter = 'Q4' THEN sales ELSE 0 END) AS Q4 FROM regional_sales GROUP BY
region; This works in all SQL dialects. The CASE expression acts as a filter, and SUM/COUNT
aggregates only the matching values. SQL Server's PIVOT operator does the same thing with
cleaner syntax but is less portable.

Q23: Explain the SQL execution order and why it matters.

SQL clauses execute in this order: (1) FROM + JOINs — determine source tables and apply
join conditions. (2) WHERE — filter individual rows. (3) GROUP BY — group remaining rows.
(4) HAVING — filter groups. (5) SELECT — evaluate expressions, aggregates, DISTINCT. (6)
ORDER BY — sort results. (7) LIMIT/OFFSET — restrict output rows. Why it matters: (a) You
can't use a SELECT alias in WHERE (WHERE runs before SELECT). (b) You can't use
aggregate functions in WHERE (use HAVING). (c) You CAN use a SELECT alias in ORDER
BY. (d) Window functions execute in the SELECT phase — you can't filter on them in WHERE
or HAVING directly; wrap in a subquery or CTE.

Q24: What is the difference between an INNER JOIN and a WHERE clause join
(implicit join)?
Both produce the same results but the explicit JOIN syntax is strongly preferred. Implicit join
(comma syntax): SELECT [Link], [Link] FROM employees e, departments d WHERE
e.department_id = d.department_id. Explicit join: SELECT [Link], [Link] FROM employees e
INNER JOIN departments d ON e.department_id = d.department_id. With implicit syntax, if you
forget the WHERE condition you get a Cartesian product (every row × every row — potentially
millions of rows). With explicit JOIN syntax, the ON condition is required syntactically. Explicit
JOINs also clearly distinguish join conditions from filter conditions, improving readability.

Q25: How do you get the Nth row or Nth highest value?
Multiple approaches: (1) Window function — most modern and flexible: SELECT * FROM
(SELECT *, ROW_NUMBER() OVER (ORDER BY salary DESC) AS rn FROM employees) t

SQL Complete Guide | Exam & Interview Preparation

SQL — Complete Study Guide | Exam & Interview Ready

WHERE rn = 5 — gives the 5th highest salary row. (2) LIMIT/OFFSET (MySQL/PostgreSQL):
SELECT * FROM employees ORDER BY salary DESC LIMIT 1 OFFSET 4 — skip 4 rows, take
1. (3) Correlated subquery: SELECT * FROM employees e1 WHERE 4 = (SELECT
COUNT(DISTINCT salary) FROM employees e2 WHERE [Link] > [Link]) — finds row
with exactly 4 higher salaries. The window function approach is most reliable, handles ties
correctly, and works across all modern SQL dialects.

SQL Complete Guide | Exam & Interview Preparation

SQL — Complete Study Guide | Exam & Interview Ready

Quick Reference: SQL Cheat Sheet

Category Syntax Example / Notes
Basic SELECT SELECT col FROM tbl WHERE Always use explicit column list in
cond ORDER BY col LIMIT n production
Aggregate COUNT(*), SUM, AVG, MIN, COUNT(col) ignores NULLs;
MAX, COUNT(DISTINCT col) COUNT(*) does not
GROUP BY GROUP BY col HAVING HAVING filters groups; WHERE
aggregate_condition filters rows
JOIN FROM a INNER|LEFT|RIGHT| Omitting ON in CROSS JOIN gives
FULL JOIN b ON [Link] = [Link] Cartesian product
Subquery SELECT * FROM (SELECT ...) Inline view / derived table
AS sub WHERE ...
CTE WITH cte AS (SELECT ...) Multiple CTEs: WITH a AS (...), b AS
SELECT * FROM cte (...) SELECT ...
Window func() OVER (PARTITION BY col Does not collapse rows — adds a
ORDER BY col) column
ROW_NUMBER ROW_NUMBER() OVER Unique number per row; use for top-
(PARTITION BY x ORDER BY y) N per group
RANK/ RANK() skips ranks; Tied rows get same rank
DENSE_RANK DENSE_RANK() does not
LAG/LEAD LAG(col, 1, 0) OVER (ORDER Previous/next row value; 3rd arg =
BY date) default
Running total SUM(col) OVER (ORDER BY Default frame: start to current row
date)
Rolling average AVG(col) OVER (ORDER BY 3-row rolling average
date ROWS BETWEEN 2
PRECEDING AND CURRENT
ROW)
NULL check IS NULL / IS NOT NULL NEVER use = NULL
COALESCE COALESCE(col1, col2, 'default') First non-NULL value
CASE CASE WHEN cond THEN val Use in SELECT, ORDER BY,
ELSE other END aggregate
UNION / UNION query1 UNION ALL query2 UNION ALL is faster; UNION
ALL deduplicates
INTERSECT/ query1 INTERSECT query2 / Common rows / rows only in first
EXCEPT query1 EXCEPT query2 query

SQL Complete Guide | Exam & Interview Preparation

SQL — Complete Study Guide | Exam & Interview Ready

Category Syntax Example / Notes

LIKE wildcards % = any chars, _ = one char Leading % prevents index use
BETWEEN WHERE col BETWEEN low AND Inclusive on both ends
high
IN / NOT IN WHERE col IN (1,2,3) or IN NOT IN + NULL = no rows returned
(SELECT ...) — use NOT EXISTS
INSERT INSERT INTO tbl (cols) VALUES Always specify column list explicitly
(...)
UPSERT INSERT ... ON CONFLICT DO MySQL: ON DUPLICATE KEY
UPDATE (PostgreSQL) UPDATE
TRUNCATE TRUNCATE TABLE tbl Faster than DELETE; resets identity;
no triggers
INDEX CREATE INDEX idx ON tbl (col) Index WHERE/JOIN/ORDER BY
columns; composite: order matters
EXPLAIN EXPLAIN ANALYZE SELECT ... Seq Scan = full table scan; Index
Scan = using index
Normalization 1NF atomic; 2NF no partial deps; 'Key, whole key, nothing but the key'
3NF no transitive deps

Good luck with your SQL exam and interviews!

SQL Complete Guide • Covers all major dialects: MySQL, PostgreSQL, SQL Server, Oracle • Interview
Ready

SQL Complete Guide | Exam & Interview Preparation