SQL Complete Guide: Beginner to Advanced + Interview Questions

How to use this resource: start from the beginning if you are new to SQL. If you are preparing for interviews, focus heavily on joins, GROUP BY, subqueries, CTEs, window functions, indexes, transactions, isolation levels and query optimization.

1. What is a Database?

A database is an organized collection of data designed so applications can store, retrieve, update and manage information efficiently.

Stores structured information permanently.
Makes searching and filtering large amounts of data efficient.
Allows multiple applications and users to work with the same data.
Supports relationships between different entities.
Can enforce data correctness using constraints.
Provides transactions, recovery, security and concurrency control.

Example data

Users

+----+--------+----------------------+-----+--------+
| id | name   | email                | age | city   |
+----+--------+----------------------+-----+--------+
| 1  | Rahul  | [email protected]    | 25  | Delhi  |
| 2  | Priya  | [email protected]    | 24  | Pune   |
| 3  | Aman   | [email protected]     | 28  | Noida  |
+----+--------+----------------------+-----+--------+

2. What is a DBMS?

A Database Management System is the software layer responsible for interacting with the physical database and providing controlled access to applications.

Stores and retrieves data.
Executes SQL queries.
Manages transactions.
Controls concurrent access.
Provides authentication and authorization.
Maintains indexes.
Supports backup and recovery.
Maintains integrity constraints.

DBMS architecture

Client / Application
        |
        v
+--------------------+
|        DBMS        |
|--------------------|
| SQL Parser         |
| Query Optimizer    |
| Execution Engine   |
| Transaction Manager|
| Buffer Manager     |
| Storage Manager    |
+--------------------+
        |
        v
+--------------------+
| Database Storage   |
+--------------------+

3. What is an RDBMS?

A Relational Database Management System stores data inside tables and represents relationships using keys.

Data is organized into tables.
Each table contains rows and columns.
Primary keys uniquely identify rows.
Foreign keys create relationships.
SQL is normally used to query relational databases.
Relationships can be one-to-one, one-to-many or many-to-many.

Relationship example

users
+----+--------+
| id | name   |
+----+--------+
| 1  | Rahul  |
| 2  | Priya  |
+----+--------+

orders
+------+---------+--------+
| id   | user_id | amount |
+------+---------+--------+
| 1001 | 1       | 799.00 |
| 1002 | 1       | 499.00 |
| 1003 | 2       | 999.00 |
+------+---------+--------+

users.id
    |
    +----------< orders.user_id

4. SQL vs MySQL vs PostgreSQL

SQL is a language. MySQL, PostgreSQL, Oracle Database and SQL Server are database management systems that implement SQL.

SQL = Structured Query Language.
MySQL = relational database system.
PostgreSQL = relational/object-relational database system.
SQL Server = Microsoft's relational database system.
Oracle Database = enterprise relational database system.
SQLite = embedded relational database engine.
Syntax and advanced features can differ slightly between database engines.

Relationship

SQL
 |
 +-- MySQL
 |
 +-- PostgreSQL
 |
 +-- SQL Server
 |
 +-- Oracle Database
 |
 +-- SQLite

5. Important Database Terminology

Understanding database terminology makes schema design and SQL discussions much easier.

Database: organized collection of data.
Schema: logical structure describing database objects.
Table: collection of related rows.
Row / Tuple / Record: one complete entry.
Column / Attribute: one property of an entity.
Constraint: rule enforced on stored data.
Query: instruction for reading or manipulating data.
Primary Key: uniquely identifies a row.
Foreign Key: references a key in another table.
Candidate Key: column or column set capable of uniquely identifying rows.
Super Key: any column set capable of uniquely identifying rows.
Alternate Key: candidate key not selected as primary key.
Composite Key: key made from multiple columns.
Natural Key: real-world value used as a key.
Surrogate Key: artificial key such as an auto-generated ID.

Example

CREATE TABLE users (
    id BIGINT PRIMARY KEY,
    email VARCHAR(255) UNIQUE NOT NULL,
    name VARCHAR(100) NOT NULL
);

6. SQL Data Types

Selecting appropriate data types improves storage efficiency, validation and query performance.

INT / INTEGER: regular integer values.
BIGINT: very large integer values.
SMALLINT: smaller integer range.
DECIMAL / NUMERIC: exact decimal arithmetic.
FLOAT / DOUBLE: approximate floating-point values.
CHAR: fixed-length strings.
VARCHAR: variable-length strings.
TEXT: long textual content.
DATE: calendar date.
TIME: time of day.
TIMESTAMP / DATETIME: date and time.
BOOLEAN: true/false values.
JSON / JSONB: semi-structured JSON data where supported.

Data type example

CREATE TABLE products (
    id BIGINT PRIMARY KEY,
    name VARCHAR(200) NOT NULL,
    description TEXT,
    price DECIMAL(10, 2) NOT NULL,
    stock INT DEFAULT 0,
    active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

7. Creating Databases and Tables

DDL commands such as CREATE define the database structure.

CREATE DATABASE creates a database.
CREATE TABLE creates a table.
Columns require names and data types.
Constraints can be defined during table creation.
Defaults can automatically assign values.

Create database and table

CREATE DATABASE targettrek;

CREATE TABLE users (
    id BIGINT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(255) UNIQUE NOT NULL,
    age INT,
    city VARCHAR(100),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

8. SQL Command Categories

SQL commands are commonly categorized according to what they do.

DDL — Data Definition Language: CREATE, ALTER, DROP, TRUNCATE.
DML — Data Manipulation Language: INSERT, UPDATE, DELETE.
DQL — Data Query Language: SELECT.
DCL — Data Control Language: GRANT, REVOKE.
TCL — Transaction Control Language: COMMIT, ROLLBACK, SAVEPOINT.

SQL categories

SQL
├── DDL
│   ├── CREATE
│   ├── ALTER
│   ├── DROP
│   └── TRUNCATE
│
├── DML
│   ├── INSERT
│   ├── UPDATE
│   └── DELETE
│
├── DQL
│   └── SELECT
│
├── DCL
│   ├── GRANT
│   └── REVOKE
│
└── TCL
    ├── COMMIT
    ├── ROLLBACK
    └── SAVEPOINT

9. INSERT

INSERT adds new rows to a table.

Specify the columns explicitly whenever possible.
Multiple rows can be inserted in one statement.
Columns with defaults may be omitted.
Required NOT NULL columns must receive values unless defaults exist.
Unique constraints may reject duplicate values.

INSERT examples

INSERT INTO users (
    id,
    name,
    email,
    age,
    city
)
VALUES (
    1,
    'Rahul',
    '[email protected]',
    25,
    'Delhi'
);

INSERT INTO users (id, name, email, age, city)
VALUES
    (2, 'Priya', '[email protected]', 24, 'Pune'),
    (3, 'Aman', '[email protected]', 28, 'Noida');

10. SELECT

SELECT retrieves data from one or more tables.

SELECT * returns all columns.
Selecting only required columns usually reduces unnecessary data transfer.
Aliases make query output easier to understand.
SELECT expressions can perform calculations.

SELECT examples

SELECT *
FROM users;

SELECT name, email
FROM users;

SELECT
    name AS user_name,
    email AS user_email
FROM users;

SELECT
    name,
    age,
    age + 1 AS age_next_year
FROM users;

11. WHERE Clause

WHERE filters rows before they are returned or modified.

= means equal.
<> or != means not equal.
> and < compare values.
>= and <= include equality.
AND requires all conditions to be true.
OR requires at least one condition to be true.
NOT reverses a condition.

Filtering data

SELECT *
FROM users
WHERE age > 25;

SELECT *
FROM users
WHERE age >= 18
  AND city = 'Delhi';

SELECT *
FROM users
WHERE city = 'Delhi'
   OR city = 'Pune';

SELECT *
FROM users
WHERE NOT city = 'Delhi';

12. SQL Operator Precedence

AND normally has higher precedence than OR, which can create unexpected results.

Use parentheses when combining AND and OR.
Do not depend on readers remembering operator precedence.
Explicit conditions make production queries safer.

Precedence example

-- Potentially confusing
SELECT *
FROM users
WHERE city = 'Delhi'
   OR city = 'Mumbai'
  AND age > 25;

-- Clear intention
SELECT *
FROM users
WHERE (
        city = 'Delhi'
        OR city = 'Mumbai'
      )
  AND age > 25;

13. IN and NOT IN

IN compares a value against a list of possible values.

IN is cleaner than writing many OR conditions.
NOT IN excludes values.
NOT IN can behave unexpectedly when the subquery contains NULL.
NOT EXISTS is often safer for anti-join logic.

IN examples

SELECT *
FROM users
WHERE city IN ('Delhi', 'Pune', 'Mumbai');

SELECT *
FROM users
WHERE city NOT IN ('Delhi', 'Pune');

14. BETWEEN

BETWEEN checks whether a value falls inside an inclusive range.

The lower boundary is included.
The upper boundary is included.
Can be used with numbers, dates and comparable values.

BETWEEN example

SELECT *
FROM products
WHERE price BETWEEN 500 AND 1000;

-- Equivalent
SELECT *
FROM products
WHERE price >= 500
  AND price <= 1000;

15. LIKE and Pattern Matching

LIKE performs simple text pattern matching.

% matches zero or more characters.
_ matches exactly one character.
Prefix searches may use indexes more effectively than patterns beginning with %.

LIKE examples

-- Starts with A
SELECT *
FROM users
WHERE name LIKE 'A%';

-- Ends with a
SELECT *
FROM users
WHERE name LIKE '%a';

-- Contains dev
SELECT *
FROM users
WHERE name LIKE '%dev%';

-- Exactly one wildcard character
SELECT *
FROM users
WHERE name LIKE 'R_hul';

16. NULL and Three-Valued Logic

NULL represents an unknown or missing value. It is not the same as zero or an empty string.

Use IS NULL instead of = NULL.
Use IS NOT NULL instead of != NULL.
Comparisons with NULL usually produce UNKNOWN.
SQL conditions operate using TRUE, FALSE and UNKNOWN.
UNKNOWN rows are normally excluded by WHERE.

NULL examples

-- Wrong
SELECT *
FROM users
WHERE phone = NULL;

-- Correct
SELECT *
FROM users
WHERE phone IS NULL;

SELECT *
FROM users
WHERE phone IS NOT NULL;

-- Useful replacement
SELECT
    name,
    COALESCE(phone, 'Not provided') AS phone
FROM users;

17. DISTINCT

DISTINCT removes duplicate rows from a result set.

Can be used with one or multiple selected columns.
Removing duplicates may require sorting or hashing.
Do not use DISTINCT merely to hide incorrect joins.

DISTINCT example

SELECT DISTINCT city
FROM users;

SELECT DISTINCT city, age
FROM users;

18. ORDER BY

ORDER BY controls the sorting of the final result.

ASC sorts ascending.
DESC sorts descending.
Multiple columns can participate in sorting.
A stable secondary sort is useful for pagination.

Sorting examples

SELECT *
FROM users
ORDER BY age ASC;

SELECT *
FROM users
ORDER BY age DESC;

SELECT *
FROM users
ORDER BY city ASC, age DESC;

SELECT *
FROM orders
ORDER BY created_at DESC, id DESC;

19. LIMIT, OFFSET and Keyset Pagination

LIMIT restricts result size while OFFSET skips rows. For very large datasets, cursor/keyset pagination usually scales better.

LIMIT controls page size.
OFFSET skips a number of rows.
Large offsets may become expensive because rows still need to be located or scanned.
Keyset pagination uses the last seen indexed value.
Keyset pagination also handles frequently changing data more predictably.

Pagination examples

-- OFFSET pagination
SELECT *
FROM products
ORDER BY id
LIMIT 20 OFFSET 40;

-- Keyset pagination
SELECT *
FROM products
WHERE id > 500
ORDER BY id
LIMIT 20;

-- Composite cursor
SELECT *
FROM orders
WHERE
    created_at < '2026-09-30 10:00:00'
    OR (
        created_at = '2026-09-30 10:00:00'
        AND id < 5000
    )
ORDER BY created_at DESC, id DESC
LIMIT 20;

20. UPDATE

UPDATE modifies existing rows.

Use WHERE to control which rows are updated.
Multiple columns can be modified together.
A missing WHERE condition can update every row.
Critical updates should often run inside a transaction.

UPDATE examples

UPDATE users
SET age = 26
WHERE id = 1;

UPDATE products
SET
    price = 899.00,
    updated_at = CURRENT_TIMESTAMP
WHERE id = 100;

-- Dangerous: updates every row
UPDATE users
SET active = FALSE;

21. DELETE

DELETE removes selected rows from a table.

WHERE determines which rows are deleted.
Without WHERE, every row can be removed.
Foreign keys may block deletion or perform cascading actions.
Soft deletion is sometimes used when historical records must remain available.

DELETE examples

DELETE FROM users
WHERE id = 5;

DELETE FROM sessions
WHERE expires_at < CURRENT_TIMESTAMP;

-- Dangerous
DELETE FROM users;

22. DELETE vs TRUNCATE vs DROP

These commands all remove something, but they operate at different levels.

DELETE removes rows and can normally use WHERE.
TRUNCATE quickly removes all table rows while keeping the table structure.
DROP removes the table object itself.
Locking, identity reset and transaction behaviour can vary by database engine.

Examples

DELETE FROM users
WHERE active = FALSE;

TRUNCATE TABLE temp_imports;

DROP TABLE old_logs;

23. ALTER TABLE

ALTER changes an existing table structure.

Add columns.
Drop columns.
Rename columns.
Change data types.
Add or remove constraints.
Large schema changes must be planned carefully on production tables.

ALTER examples

ALTER TABLE users
ADD COLUMN phone VARCHAR(20);

ALTER TABLE users
ADD COLUMN active BOOLEAN DEFAULT TRUE;

ALTER TABLE users
DROP COLUMN phone;

ALTER TABLE users
ADD CONSTRAINT users_email_unique
UNIQUE (email);

24. SQL Constraints

Constraints protect the correctness and integrity of stored data.

NOT NULL prevents missing values.
UNIQUE prevents duplicate values.
PRIMARY KEY uniquely identifies every row.
FOREIGN KEY maintains relationships.
CHECK validates a condition.
DEFAULT supplies a value when none is provided.

Constraint example

CREATE TABLE employees (
    id BIGINT PRIMARY KEY,

    email VARCHAR(255)
        UNIQUE
        NOT NULL,

    name VARCHAR(100)
        NOT NULL,

    age INT
        CHECK (age >= 18),

    status VARCHAR(20)
        DEFAULT 'ACTIVE',

    department_id BIGINT,

    CONSTRAINT fk_department
        FOREIGN KEY (department_id)
        REFERENCES departments(id)
);

25. Aggregate Functions

Aggregate functions calculate one result from a set of rows.

COUNT(*) counts rows.
COUNT(column) does not count NULL values.
SUM calculates the total.
AVG calculates the arithmetic mean.
MIN and MAX return smallest/largest values.
DISTINCT can be used inside aggregates.

SQL

SELECT COUNT(*) FROM orders;

SELECT SUM(amount) FROM orders;

SELECT AVG(amount) FROM orders;

SELECT MIN(amount) FROM orders;

SELECT MAX(amount) FROM orders;

SELECT COUNT(DISTINCT user_id)
FROM orders;

26. GROUP BY and HAVING

GROUP BY creates groups of rows so aggregate calculations can be performed per group.

WHERE filters rows before grouping.
GROUP BY creates groups.
HAVING filters groups after aggregation.
Columns selected without aggregation generally need to appear in GROUP BY.

SQL

SELECT
    city,
    COUNT(*) AS total_users
FROM users
GROUP BY city;

SELECT
    user_id,
    COUNT(*) AS order_count,
    SUM(amount) AS total_spent
FROM orders
GROUP BY user_id;

SELECT
    user_id,
    SUM(amount) AS total_spent
FROM orders
GROUP BY user_id
HAVING SUM(amount) > 10000;

27. Logical SQL Query Execution Order

SQL is written in one order but conceptually processed in another logical order.

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

SQL

SELECT        -- 5
    department_id,
    COUNT(*) AS total
FROM employees -- 1
WHERE active = TRUE -- 2
GROUP BY department_id -- 3
HAVING COUNT(*) > 5 -- 4
ORDER BY total DESC -- 6
LIMIT 10; -- 7

28. SQL Joins

Joins combine rows from multiple tables using related columns.

INNER JOIN returns only matching rows.
LEFT JOIN returns all rows from the left table plus matches.
RIGHT JOIN returns all rows from the right table plus matches.
FULL OUTER JOIN returns matched and unmatched rows from both sides.
CROSS JOIN returns every possible combination.
SELF JOIN joins a table with itself.

SQL

-- INNER JOIN
SELECT
    u.name,
    o.id AS order_id,
    o.amount
FROM users u
INNER JOIN orders o
    ON o.user_id = u.id;

-- LEFT JOIN
SELECT
    u.name,
    o.id AS order_id
FROM users u
LEFT JOIN orders o
    ON o.user_id = u.id;

-- Find users with no orders
SELECT
    u.id,
    u.name
FROM users u
LEFT JOIN orders o
    ON o.user_id = u.id
WHERE o.id IS NULL;

29. Self Join

A self join is useful when rows in the same table relate to one another, such as employees and managers.

The same table appears more than once.
Aliases distinguish each logical copy.
Common use cases include hierarchical relationships.

SQL

CREATE TABLE employees (
    id BIGINT PRIMARY KEY,
    name VARCHAR(100),
    manager_id BIGINT
);

SELECT
    employee.name AS employee,
    manager.name AS manager
FROM employees employee
LEFT JOIN employees manager
    ON employee.manager_id = manager.id;

30. CROSS JOIN

CROSS JOIN creates the Cartesian product of two tables.

If one table contains 4 rows and another contains 3, the result contains 12 rows.
Useful for generating combinations.
Accidental Cartesian products can create huge result sets.

SQL

SELECT
    colors.name,
    sizes.name
FROM colors
CROSS JOIN sizes;

31. UNION, UNION ALL, INTERSECT and EXCEPT

Set operators combine the result sets of independent SELECT statements.

UNION removes duplicates.
UNION ALL keeps duplicates and is usually cheaper.
INTERSECT returns values present in both results.
EXCEPT returns values found in the first query but not the second.
Database support and syntax can vary.

SQL

SELECT email
FROM customers

UNION

SELECT email
FROM newsletter_users;

SELECT email
FROM customers

UNION ALL

SELECT email
FROM newsletter_users;

SELECT email FROM customers
INTERSECT
SELECT email FROM newsletter_users;

SELECT email FROM customers
EXCEPT
SELECT email FROM newsletter_users;

32. Subqueries

A subquery is a query embedded inside another SQL statement.

Scalar subqueries return a single value.
Multi-row subqueries return multiple values.
Subqueries can appear in SELECT, FROM, WHERE and HAVING depending on the database.
A join may sometimes be easier to optimize or understand.

SQL

SELECT *
FROM products
WHERE price > (
    SELECT AVG(price)
    FROM products
);

SELECT *
FROM users
WHERE id IN (
    SELECT user_id
    FROM orders
    WHERE amount > 5000
);

33. Correlated Subqueries

A correlated subquery references columns from the outer query.

The inner query depends on the current outer row.
EXISTS is frequently used with correlated subqueries.
Indexes on correlation columns are important.

SQL

SELECT
    u.id,
    u.name
FROM users u
WHERE EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.user_id = u.id
      AND o.amount > 5000
);

34. EXISTS and NOT EXISTS

EXISTS checks whether a subquery returns at least one matching row.

EXISTS is useful when only existence matters.
NOT EXISTS is commonly used for anti-joins.
It avoids several NULL-related problems associated with NOT IN.

SQL

SELECT *
FROM users u
WHERE EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.user_id = u.id
);

SELECT *
FROM users u
WHERE NOT EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.user_id = u.id
);

35. Common Table Expressions — CTE

A CTE creates a named temporary result that can make complex queries easier to read.

Created using WITH.
Improves readability.
Can break complex queries into logical stages.
Optimizer behaviour differs between database engines and versions.

SQL

WITH high_value_orders AS (
    SELECT
        id,
        user_id,
        amount
    FROM orders
    WHERE amount >= 5000
)
SELECT
    u.name,
    h.amount
FROM high_value_orders h
JOIN users u
    ON u.id = h.user_id;

36. Recursive CTE

Recursive CTEs can traverse hierarchical data such as employee hierarchies, categories or folder trees.

Contains an anchor query.
Contains a recursive query.
Recursion stops when no additional rows are produced.
Useful for tree-like structures.

SQL

WITH RECURSIVE employee_tree AS (
    SELECT
        id,
        name,
        manager_id,
        1 AS level
    FROM employees
    WHERE manager_id IS NULL

    UNION ALL

    SELECT
        e.id,
        e.name,
        e.manager_id,
        tree.level + 1
    FROM employees e
    JOIN employee_tree tree
        ON e.manager_id = tree.id
)
SELECT *
FROM employee_tree;

37. CASE Expression

CASE provides conditional logic inside SQL queries.

Comparable to if/else logic.
Can be used in SELECT, ORDER BY and other expressions.
Useful for categorization and conditional aggregation.

SQL

SELECT
    name,
    price,
    CASE
        WHEN price >= 5000 THEN 'Premium'
        WHEN price >= 1000 THEN 'Mid Range'
        ELSE 'Budget'
    END AS price_category
FROM products;

38. String Functions

SQL databases provide functions for manipulating textual values.

UPPER / LOWER change case.
LENGTH returns string length.
CONCAT combines strings.
TRIM removes unwanted whitespace.
SUBSTRING extracts part of a string.
Exact names can differ by database engine.

SQL

SELECT UPPER(name)
FROM users;

SELECT LOWER(email)
FROM users;

SELECT LENGTH(name)
FROM users;

SELECT CONCAT(first_name, ' ', last_name)
FROM users;

SELECT TRIM(name)
FROM users;

SELECT SUBSTRING(name, 1, 3)
FROM users;

39. Numeric Functions

Numeric functions perform calculations and transformations.

ROUND controls decimal precision.
ABS returns absolute value.
CEIL rounds upward.
FLOOR rounds downward.
POWER performs exponentiation.

SQL

SELECT ROUND(99.876, 2);

SELECT ABS(-100);

SELECT CEIL(10.2);

SELECT FLOOR(10.9);

SELECT POWER(2, 10);

40. Date and Time

Date functions are essential for analytics, reports and application logic.

Store timestamps consistently.
Timezone strategy should be defined at the application level.
Date syntax varies more between SQL engines than basic SELECT syntax.

SQL

SELECT CURRENT_DATE;

SELECT CURRENT_TIMESTAMP;

-- PostgreSQL example
SELECT NOW();

SELECT *
FROM orders
WHERE created_at >= CURRENT_DATE;

-- PostgreSQL example
SELECT
    created_at,
    created_at + INTERVAL '7 days'
FROM orders;

41. COALESCE and NULL Handling

COALESCE returns the first non-NULL expression.

Useful for default presentation values.
Accepts multiple arguments.
Stops at the first non-NULL value.

SQL

SELECT
    name,
    COALESCE(phone, alternate_phone, 'No phone') AS phone
FROM users;

SELECT
    product_id,
    COALESCE(discount, 0) AS discount
FROM products;

42. Window Functions

Window functions calculate values across related rows without collapsing them into a single grouped row.

OVER defines the window.
PARTITION BY creates logical groups.
ORDER BY inside OVER determines calculation order.
Unlike GROUP BY, individual rows remain visible.

SQL

SELECT
    employee_id,
    department_id,
    salary,
    AVG(salary) OVER (
        PARTITION BY department_id
    ) AS department_avg_salary
FROM employees;

43. ROW_NUMBER, RANK and DENSE_RANK

Ranking window functions are among the most important SQL interview topics.

ROW_NUMBER always produces unique sequential numbers.
RANK gives equal values the same rank and leaves gaps.
DENSE_RANK gives equal values the same rank without gaps.

SQL

SELECT
    employee_id,
    department_id,
    salary,

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

    RANK() OVER (
        PARTITION BY department_id
        ORDER BY salary DESC
    ) AS salary_rank,

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

FROM employees;

44. LAG and LEAD

LAG accesses a previous row and LEAD accesses a following row in a window.

Useful for growth calculations.
Useful for comparing current and previous records.
Avoids many self-joins.

SQL

SELECT
    order_date,
    revenue,

    LAG(revenue) OVER (
        ORDER BY order_date
    ) AS previous_revenue,

    LEAD(revenue) OVER (
        ORDER BY order_date
    ) AS next_revenue

FROM daily_revenue;

45. Running Totals

Window frames can calculate cumulative totals.

Common in financial and analytics workloads.
The window frame controls which rows participate.

SQL

SELECT
    order_date,
    amount,

    SUM(amount) OVER (
        ORDER BY order_date
        ROWS BETWEEN UNBOUNDED PRECEDING
        AND CURRENT ROW
    ) AS running_total

FROM orders;

46. Database Normalization

Normalization organizes relational data to reduce redundancy and prevent data anomalies.

Form	Requirement	Main Goal
1NF	Atomic values, no repeating groups	Remove multi-valued columns
2NF	1NF + no partial dependency	Remove dependency on part of a composite key
3NF	2NF + no transitive dependency	Non-key fields depend only on the key
BCNF	Every determinant is a candidate key	Stronger dependency correctness

Bad design

orders
----------------------------------------------------------
order_id | customer_name | customer_phone | products
----------------------------------------------------------
1        | Rahul         | 9999999999     | Book, Pen, Bag

Problems:
- products contains multiple values
- customer data repeats
- updates may create inconsistencies

Normalized design

customers
---------
id
name
phone

orders
---------
id
customer_id
created_at

products
---------
id
name
price

order_items
---------
order_id
product_id
quantity
price

Normalization improves consistency, but highly read-heavy systems sometimes intentionally denormalize selected data to reduce expensive joins.

47. Database Relationships

Relational databases model how entities are connected.

One-to-One

User → UserProfile

One-to-Many

User → Orders

Many-to-Many

Students ↔ Courses

Many-to-many design

CREATE TABLE students (
    id BIGINT PRIMARY KEY,
    name VARCHAR(100)
);

CREATE TABLE courses (
    id BIGINT PRIMARY KEY,
    title VARCHAR(150)
);

CREATE TABLE student_courses (
    student_id BIGINT,
    course_id BIGINT,

    PRIMARY KEY (student_id, course_id),

    FOREIGN KEY (student_id)
        REFERENCES students(id),

    FOREIGN KEY (course_id)
        REFERENCES courses(id)
);

48. Foreign Key Actions

Foreign keys can define what happens when referenced rows change.

ON DELETE CASCADE — automatically deletes dependent rows.
ON DELETE SET NULL — replaces the foreign key with NULL.
RESTRICT / NO ACTION — blocks deletion when dependent rows exist.
ON UPDATE CASCADE — propagates referenced key updates.
Cascade rules should be used carefully in production systems.

SQL

CREATE TABLE orders (
    id BIGINT PRIMARY KEY,
    user_id BIGINT,

    CONSTRAINT fk_order_user
        FOREIGN KEY (user_id)
        REFERENCES users(id)
        ON DELETE RESTRICT
        ON UPDATE CASCADE
);

49. Views and Materialized Views

A view stores a query definition. A materialized view stores the query result physically where supported.

Feature	View	Materialized View
Stores query	Yes	Yes
Stores result data	Usually no	Yes
Fresh data	Generally current	Needs refresh
Read speed	Depends on base query	Often faster
Use case	Abstraction / security	Expensive analytics

SQL

CREATE VIEW active_users AS
SELECT
    id,
    name,
    email
FROM users
WHERE active = TRUE;

SELECT *
FROM active_users;

-- PostgreSQL example
CREATE MATERIALIZED VIEW monthly_sales AS
SELECT
    DATE_TRUNC('month', created_at) AS month,
    SUM(amount) AS revenue
FROM orders
GROUP BY DATE_TRUNC('month', created_at);

50. Database Indexes

An index is an additional data structure that helps the database locate rows without scanning the entire table.

Think of an index like the index at the back of a book: instead of reading every page, you first locate where the desired information exists.

Index example

CREATE INDEX idx_users_email
ON users(email);

SELECT *
FROM users
WHERE email = '[email protected]';

Index Type	Typical Use	Notes
B-Tree	Equality, range, ORDER BY	Most common general-purpose index
Hash	Equality lookup	Database support and limitations vary
Composite	Multiple-column filters	Column order matters
Unique	Uniqueness + lookup	Prevents duplicate indexed values
Partial / Filtered	Subset of table	Useful for highly targeted queries
Full-text	Text search	Better suited to natural text search
GIN / GiST	PostgreSQL specialized workloads	Arrays, JSONB, full-text, geometric data and more

Indexes speed up many reads but are not free. They consume storage and add work to INSERT, UPDATE and DELETE because index entries must also be maintained.

51. Composite Index and Column Order

A composite index contains multiple columns. Choosing the right column order is critical.

SQL

CREATE INDEX idx_orders_user_status_created
ON orders(user_id, status, created_at);

-- Excellent candidate
SELECT *
FROM orders
WHERE user_id = 100
  AND status = 'SUCCESS'
ORDER BY created_at DESC;

-- May use only part of the index
SELECT *
FROM orders
WHERE user_id = 100;

-- Often cannot efficiently use the leading structure
SELECT *
FROM orders
WHERE status = 'SUCCESS';

Column order matters.
Equality filters often appear before range filters.
Indexes should reflect real application query patterns.
Do not blindly add every filtered column to an index.
A covering index contains enough columns to answer a query without reading the base table in some database engines.

52. EXPLAIN and Query Execution Plans

The query optimizer decides how to execute a SQL statement. EXPLAIN allows developers to inspect that plan.

SQL

EXPLAIN
SELECT *
FROM orders
WHERE user_id = 100;

-- PostgreSQL
EXPLAIN ANALYZE
SELECT *
FROM orders
WHERE user_id = 100;

Check whether the database performs a sequential/table scan.
Check which indexes are used.
Inspect join algorithms.
Compare estimated rows with actual rows where supported.
Look for unnecessary sorts.
Look for repeated expensive operations.
Use execution plans before assuming an index will help.

53. Transactions

A transaction groups operations into a single logical unit of work.

Money transfer

BEGIN;

UPDATE accounts
SET balance = balance - 1000
WHERE id = 1;

UPDATE accounts
SET balance = balance + 1000
WHERE id = 2;

COMMIT;

Rollback

BEGIN;

UPDATE inventory
SET stock = stock - 1
WHERE product_id = 500
  AND stock > 0;

-- Something fails

ROLLBACK;

Savepoint

BEGIN;

INSERT INTO orders (...);

SAVEPOINT order_created;

INSERT INTO payment_attempts (...);

ROLLBACK TO SAVEPOINT order_created;

COMMIT;

54. ACID Properties

ACID describes important guarantees expected from reliable database transactions.

Atomicity

All operations succeed together or the transaction is rolled back.

Consistency

A transaction moves the database from one valid state to another valid state.

Isolation

Concurrent transactions should not incorrectly interfere with one another.

Durability

Committed data should survive failures according to the database's durability guarantees.

55. Transaction Isolation Levels

Isolation levels control how much concurrent transactions can observe each other's intermediate effects.

Dirty Read

Transaction reads data written by another transaction that has not committed.

Non-repeatable Read

The same row returns a different committed value when read again.

Phantom Read

Repeating a range query returns additional or missing rows.

Isolation Level	Dirty Read	Non-repeatable Read	Phantom Read
Read Uncommitted	Possible	Possible	Possible
Read Committed	Prevented	Possible	Possible
Repeatable Read	Prevented	Prevented*	Engine dependent
Serializable	Prevented	Prevented	Prevented

* Exact guarantees and implementation details can vary across database engines, particularly because of MVCC.

56. Database Locking

Locks coordinate concurrent access to data and help protect correctness.

Shared/read locks allow compatible readers depending on the engine.
Exclusive/write locks protect modifications.
Row-level locks affect specific records.
Table-level locks affect an entire table.
Lock granularity influences concurrency.
MVCC-based databases may avoid many read/write conflicts using row versions.

Pessimistic locking

BEGIN;

SELECT *
FROM inventory
WHERE product_id = 100
FOR UPDATE;

UPDATE inventory
SET stock = stock - 1
WHERE product_id = 100;

COMMIT;

57. Optimistic vs Pessimistic Locking

Applications can handle concurrent updates using different strategies.

Feature	Optimistic	Pessimistic
Assumption	Conflicts are uncommon	Conflicts may occur
Lock early	Usually no	Yes
Typical mechanism	Version column	SELECT FOR UPDATE
Concurrency	High	Potentially lower
Conflict handling	Detect and retry	Wait/block

Optimistic locking

UPDATE products
SET
    stock = stock - 1,
    version = version + 1
WHERE id = 100
  AND version = 7;

-- If affected rows = 0,
-- another transaction changed the record first.

58. Database Deadlocks

A deadlock occurs when transactions wait on resources held by one another and neither can continue.

Deadlock scenario

Transaction A
-------------
Locks Row 1
Needs Row 2

Transaction B
-------------
Locks Row 2
Needs Row 1

A waits for B
B waits for A

=> Deadlock

Access rows in a consistent order.
Keep transactions short.
Avoid user interaction inside open transactions.
Use appropriate indexes so fewer rows remain locked.
Retry transactions that are selected as deadlock victims.
Avoid unnecessarily broad locking.

59. Stored Procedures and Database Functions

Stored procedures/functions allow logic to execute inside the database. Exact syntax is database-specific.

Can encapsulate repeated database operations.
Can reduce application-to-database round trips in some cases.
May improve security by limiting direct table access.
Heavy business logic inside the database can make application portability and testing harder.
Syntax differs significantly between PostgreSQL, MySQL, SQL Server and Oracle.

60. Database Triggers

A trigger automatically executes database logic after or before specified events.

Can execute on INSERT.
Can execute on UPDATE.
Can execute on DELETE.
Useful for auditing and enforcing specialized rules.
Can create hidden behaviour if overused.
Debugging systems with many triggers can become difficult.

Prefer explicit application/domain logic when the operation should remain visible to application developers. Use triggers deliberately.

61. Table Partitioning

Partitioning divides one logical table into smaller physical pieces.

Range Partitioning

Example: orders separated by month or year.

List Partitioning

Example: records separated by region.

Hash Partitioning

Rows distributed using a hash function.

Partitioning is not the same as sharding. Partitioning normally divides data within one database system, while sharding distributes data across separate database nodes or instances.

62. SQL Injection and Security

SQL injection happens when untrusted input is interpreted as part of a SQL statement.

Unsafe approach

// Never build SQL by directly joining user input.

const sql =
  "SELECT * FROM users WHERE email = '" +
  email +
  "'";

Parameterized approach

const sql =
  "SELECT * FROM users WHERE email = ?";

db.execute(sql, [email]);

Use prepared statements or parameterized queries.
Never concatenate raw user input into SQL.
Validate input.
Use least-privilege database accounts.
Do not expose database error details to end users.
Use ORM parameterization correctly rather than assuming every ORM method is automatically safe.

63. SQL Query Optimization

Performance tuning begins with measuring the actual query and examining its execution plan.

Select only required columns.

Filter rows as early as logically possible.

Create indexes for important access patterns.

Avoid unnecessary DISTINCT.

Avoid unnecessary sorts.

Inspect execution plans.

Index foreign keys used heavily in joins when appropriate.

Avoid applying functions to indexed columns when it prevents efficient index use.

Avoid leading wildcard searches for large tables when possible.

Use correct data types.

Avoid N+1 application queries.

Batch related database work.

Keep transactions short.

Use keyset pagination for very large datasets.

Archive or partition extremely large historical tables where appropriate.

Monitor slow-query logs.

64. Common SQL Performance Mistakes

Several query patterns repeatedly cause avoidable performance issues.

Examples

-- 1. Fetching unnecessary columns
SELECT *
FROM large_orders;

-- Better
SELECT id, user_id, amount
FROM large_orders;


-- 2. Function over indexed column
WHERE LOWER(email) = '[email protected]'


-- 3. Leading wildcard
WHERE name LIKE '%developer%'


-- 4. Huge OFFSET
LIMIT 20 OFFSET 500000


-- 5. Accidental Cartesian product
SELECT *
FROM users, orders;

65. Real E-commerce Schema Design

A practical relational design showing how SQL concepts come together.

Schema

users
-----
id PK
name
email UNIQUE
created_at

products
--------
id PK
name
price
stock
active

orders
------
id PK
user_id FK -> users.id
status
total_amount
created_at

order_items
-----------
id PK
order_id FK -> orders.id
product_id FK -> products.id
quantity
unit_price

payments
--------
id PK
order_id FK -> orders.id
provider
status
amount
transaction_id
created_at

reviews
-------
id PK
user_id FK -> users.id
product_id FK -> products.id
rating
comment
created_at

Example production query

SELECT
    o.id AS order_id,
    u.name AS customer,
    o.status,
    o.total_amount,
    COUNT(oi.id) AS total_items
FROM orders o
JOIN users u
    ON u.id = o.user_id
JOIN order_items oi
    ON oi.order_id = o.id
WHERE o.created_at >= CURRENT_DATE
GROUP BY
    o.id,
    u.name,
    o.status,
    o.total_amount
ORDER BY o.created_at DESC;

66. Important SQL Interview Queries

These patterns appear repeatedly in backend, data and software engineering interviews.

Second highest salary

SQL

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

Nth highest salary

SQL

WITH ranked AS (
    SELECT
        salary,
        DENSE_RANK() OVER (
            ORDER BY salary DESC
        ) AS rank_number
    FROM employees
)
SELECT DISTINCT salary
FROM ranked
WHERE rank_number = 3;

Duplicate emails

SQL

SELECT
    email,
    COUNT(*) AS occurrences
FROM users
GROUP BY email
HAVING COUNT(*) > 1;

Employees earning more than manager

SQL

SELECT
    employee.name AS employee,
    employee.salary,
    manager.name AS manager,
    manager.salary AS manager_salary
FROM employees employee
JOIN employees manager
    ON employee.manager_id = manager.id
WHERE employee.salary > manager.salary;

Top 3 salaries from each department

SQL

WITH ranked AS (
    SELECT
        id,
        name,
        department_id,
        salary,
        DENSE_RANK() OVER (
            PARTITION BY department_id
            ORDER BY salary DESC
        ) AS salary_rank
    FROM employees
)
SELECT *
FROM ranked
WHERE salary_rank <= 3;

Users with no orders

SQL

SELECT
    u.id,
    u.name
FROM users u
WHERE NOT EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.user_id = u.id
);

Latest order for every user

SQL

WITH ranked_orders AS (
    SELECT
        o.*,
        ROW_NUMBER() OVER (
            PARTITION BY user_id
            ORDER BY created_at DESC, id DESC
        ) AS row_num
    FROM orders o
)
SELECT *
FROM ranked_orders
WHERE row_num = 1;

Monthly revenue

SQL

-- PostgreSQL example
SELECT
    DATE_TRUNC('month', created_at) AS month,
    SUM(amount) AS revenue
FROM orders
WHERE status = 'SUCCESS'
GROUP BY DATE_TRUNC('month', created_at)
ORDER BY month;

67. SQL Practice Problems

Try solving these without looking at the examples first.

1Find the second-highest salary.

2Find the Nth-highest salary.

3Find duplicate emails.

4Delete duplicate rows while keeping one.

5Find employees earning more than their manager.

6Find the top 3 salaries in every department.

7Find users who never placed an order.

8Find customers who placed more than 5 orders.

9Calculate monthly revenue.

10Calculate month-over-month revenue growth.

11Find the most frequently purchased product.

12Find consecutive login dates.

13Calculate a running total.

14Find records that appear three consecutive times.

15Find the latest order for every customer.

16Find the first order for every customer.

17Find products priced above their category average.

18Find departments whose average salary is above company average.

68. SQL Interview Questions Checklist

Use this checklist before backend, SDE, data engineering or SQL-heavy interviews.

What is SQL and how is it different from MySQL?

What is a primary key?

Primary key vs unique key?

What is a foreign key?

What is a composite key?

What is a candidate key?

DELETE vs TRUNCATE vs DROP?

WHERE vs HAVING?

UNION vs UNION ALL?

INNER JOIN vs LEFT JOIN?

What is a CROSS JOIN?

What is a SELF JOIN?

What is normalization?

Explain 1NF, 2NF, 3NF and BCNF.

What is denormalization?

What is an index?

How does a B-Tree index work?

When should you not create an index?

What is a composite index?

What is the leftmost-prefix concept?

What is a covering index?

Clustered vs non-clustered index?

What is a transaction?

Explain ACID.

What is a dirty read?

What is a non-repeatable read?

What is a phantom read?

Explain transaction isolation levels.

Optimistic vs pessimistic locking?

What is a database deadlock?

How do you prevent deadlocks?

What is a CTE?

CTE vs subquery?

What is a recursive CTE?

What are window functions?

ROW_NUMBER vs RANK vs DENSE_RANK?

What are LAG and LEAD?

What is a correlated subquery?

IN vs EXISTS?

NOT IN vs NOT EXISTS?

What is a view?

View vs materialized view?

What does EXPLAIN do?

What is a query execution plan?

Why can SELECT * be inefficient?

OFFSET vs cursor pagination?

What is SQL injection?

How do prepared statements prevent SQL injection?

Partitioning vs sharding?

SQL vs NoSQL — when would you choose each?

69. SQL Quick Revision Cheat Sheet

A compact revision block for commonly used SQL syntax.

SQL

-- SELECT
SELECT column1, column2
FROM table_name;

-- FILTER
SELECT *
FROM users
WHERE age >= 18;

-- SORT
SELECT *
FROM users
ORDER BY created_at DESC;

-- LIMIT
SELECT *
FROM users
LIMIT 10;

-- GROUP
SELECT city, COUNT(*)
FROM users
GROUP BY city;

-- GROUP FILTER
SELECT city, COUNT(*)
FROM users
GROUP BY city
HAVING COUNT(*) > 10;

-- JOIN
SELECT *
FROM users u
JOIN orders o
    ON o.user_id = u.id;

-- LEFT JOIN
SELECT *
FROM users u
LEFT JOIN orders o
    ON o.user_id = u.id;

-- SUBQUERY
SELECT *
FROM products
WHERE price > (
    SELECT AVG(price)
    FROM products
);

-- CTE
WITH data AS (
    SELECT *
    FROM orders
)
SELECT *
FROM data;

-- WINDOW FUNCTION
SELECT
    name,
    salary,
    DENSE_RANK() OVER (
        ORDER BY salary DESC
    ) AS salary_rank
FROM employees;

-- INDEX
CREATE INDEX idx_users_email
ON users(email);

-- TRANSACTION
BEGIN;

UPDATE accounts
SET balance = balance - 100
WHERE id = 1;

COMMIT;

-- EXISTENCE
SELECT *
FROM users u
WHERE EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.user_id = u.id
);

-- NULL
SELECT *
FROM users
WHERE phone IS NULL;

-- CASE
SELECT
    name,
    CASE
        WHEN age >= 18 THEN 'Adult'
        ELSE 'Minor'
    END AS category
FROM users;

What should you master for interviews?

Do not stop after learning SELECT and JOIN. For backend and SDE interviews, you should be able to explain how the database behaves internally and why a particular query or schema performs well.

Joins

GROUP BY + HAVING

Subqueries

CTEs

Window Functions

Normalization

Indexes

Composite Indexes

EXPLAIN

Transactions

ACID

Isolation Levels

Locking

Deadlocks

Pagination

Query Optimization

SQL Injection

Schema Design

Recommended learning path