SQL & Database Interview Preparation

A DBMS (Database Management System) is software that allows users and applications to create, store, retrieve, update, and manage data efficiently.

Instead of storing data in text files or Excel sheets, organizations use a DBMS because it provides:

• Data storage
• Data retrieval
• Data security
• Backup & recovery
• Concurrent access
• Data consistency

A DBMS acts as an interface between the application and the database.

Examples:

• MySQL
• Oracle
• PostgreSQL
• SQL Server
• MongoDB (NoSQL DBMS)

Example

Without DBMS:

Employee.txt

Searching thousands of records becomes slow and difficult.

With DBMS:

SELECT * FROM Employee WHERE id=10;

The DBMS quickly retrieves the record.

Real-world Example

An Amazon application stores:

• Customers
• Orders
• Products
• Payments

All these are managed using a DBMS.

Interview One-Liner: A DBMS is software that manages databases and provides secure, efficient, and concurrent access to data.

An RDBMS (Relational Database Management System) is a DBMS that stores data in the form of tables and maintains relationships using Primary Keys and Foreign Keys.

It follows the relational model proposed by E. F. Codd.

Features:

• Tables
• Relationships
• SQL support
• Constraints
• Transactions
• ACID compliance

Examples:

• MySQL
• PostgreSQL
• Oracle
• SQL Server

Example

Customer Table

Orders Table

CustomerID acts as Foreign Key.

Interview One-Liner: RDBMS stores data in related tables and maintains relationships using keys.

Interview Tip: If interviewer asks:

Is MySQL DBMS or RDBMS?

Answer:

MySQL is an RDBMS because it stores data in related tables and supports SQL, transactions, foreign keys, and ACID properties.

A relational database stores data in tables, where relationships between tables are established using:

• Primary Key
• Foreign Key

Instead of duplicating data, related information is connected through keys.

Example

Customer
--------
CustomerID
Name

Orders
--------
OrderID
CustomerID
Amount

CustomerID creates the relationship.

Advantages

• Less duplication
• Better consistency
• Easy joins
• Better integrity

Interview One-Liner: A relational database organizes data into related tables connected through keys.

A table is a collection of related data organized into rows and columns.

Example:

Employee

Each table represents one entity.

Examples:

• Employee
• Product
• Customer
• Orders

Interview One-Liner: A table stores data about one specific entity in rows and columns.

Row

A row represents one complete record.

Example

This entire record is one row.

Column

A column represents one attribute.

Example

Name column

John
Alice
Bob

Interview One-Liner: A row is a single record, while a column represents a specific attribute of all records.

A schema is the logical structure of a database.

It defines:

• Tables
• Views
• Indexes
• Constraints
• Stored Procedures
• Relationships

Think of schema as the blueprint of the database.

Example

Employee
---------
ID
Name
Salary

Department
-----------
DeptID
DeptName

Interview One-Liner: A schema defines how database objects are organized.

Metadata means data about data.

It describes:

• Table names
• Column names
• Data types
• Constraints
• Indexes

Example

Employee

ID INT
Name VARCHAR(100)
Salary DECIMAL

This information is metadata.

Interview One-Liner: Metadata describes the structure of stored data.

A database instance refers to the currently running database environment in memory, including the background processes and memory structures that manage access to the database.

• Database: Physical files stored on disk.
• Instance: Memory + processes currently accessing and managing those files.

For example, in Oracle, the instance includes the SGA (System Global Area) and background processes. In MySQL, the running mysqld server process is the database instance.

Example

When you start the MySQL server:

mysqld

The server process loads configurations, allocates memory, and starts handling client requests. This running process is the database instance.

Interview One-Liner: A database is the stored data on disk, while a database instance is the running database engine managing that data.

Normalization is the process of organizing database tables to reduce:

• Data redundancy
• Data inconsistency
• Duplicate data

It divides large tables into smaller related tables.

Before

John appears multiple times.

After

Student

Course

Enrollment

Interview One-Liner: Normalization minimizes redundancy and improves data integrity by organizing data into well-structured tables.

Reasons

• Eliminate duplicate data
• Improve consistency
• Avoid update anomalies
• Reduce storage
• Maintain integrity
• Easier maintenance

Production Example

Customer address stored once.

Instead of storing:

Delhi
Delhi
Delhi
Delhi

Store once and reference it using a Foreign Key.

Interview One-Liner: We normalize data to reduce redundancy, maintain consistency, and improve data integrity.

Advantages

• Less duplicate data
• Better consistency
• Easier updates
• Saves storage
• Prevents anomalies
• Stronger data integrity

Disadvantages

• More joins
• Slightly slower read queries
• More complex schema
• Increased query complexity

Interview Tip: Most OLTP systems (banking, e-commerce, ERP) use normalized schemas because they perform many inserts, updates, and deletes.

Denormalization is the process of intentionally adding redundant data to improve read performance by reducing the number of joins.

Example

Instead of:

Orders

CustomerID

Join with Customer table every time.

Store:

CustomerName

directly in Orders.

Reads become faster.

Interview One-Liner: Denormalization improves read performance by reducing joins at the cost of controlled redundancy.

Use denormalization when:

• Read-heavy applications
• Reporting systems
• Analytics
• Dashboards
• Data warehouse
• Frequently joined tables
• Performance-critical queries

Production Example

Amazon product page:

Instead of joining:

• Product
• Seller
• Category
• Rating
• Inventory

Frequently used information may be duplicated in a precomputed table or cache to serve pages faster.

When NOT to Use

• Banking
• Payment systems
• Inventory updates
• Frequent write operations

These systems prioritize consistency.

Interview One-Liner: Denormalization is preferred for read-intensive workloads where query performance is more important than minimizing redundancy.

1NF (First Normal Form)

Rule

• Atomic values only
• No repeating groups
• Each column contains a single value

❌

✔

2NF (Second Normal Form)

Rule

• Must satisfy 1NF
• No partial dependency
• Every non-key column depends on the entire primary key

Example:

Enrollment(StudentID, CourseID, StudentName)

StudentName depends only on StudentID, not on the full composite key (StudentID, CourseID). Move StudentName to a separate Student table.

3NF (Third Normal Form)

Rule

• Must satisfy 2NF
• No transitive dependency

❌

Employee

EmpID
DeptID
DeptName

DeptName depends on DeptID, not directly on EmpID.

✔

Employee

EmpID
DeptID

Department

DeptID
DeptName

BCNF (Boyce-Codd Normal Form)

Rule

Every determinant must be a candidate key.

BCNF is a stricter version of 3NF and resolves certain anomalies that 3NF cannot.

Interview Tip: Most production OLTP databases are normalized up to 3NF. BCNF is used only when required for more complex dependency scenarios.

Easy Memory Trick

1NF -> Atomic values

2NF -> Remove Partial Dependency

3NF -> Remove Transitive Dependency

BCNF -> Every determinant should be a Candidate Key

ACID properties ensure reliable and consistent transaction processing.

A – Atomicity

A transaction is treated as a single unit.

Either:

Everything succeeds

or

Everything fails

Example

Money transfer:

Debit ₹1000
Credit ₹1000

If the credit operation fails, the debit is also rolled back.

C – Consistency

A transaction must take the database from one valid state to another while enforcing all constraints.

Example

If account balance cannot be negative, the transaction must not violate this rule.

I – Isolation

Multiple transactions should not interfere with each other.

Even when users execute transactions simultaneously, intermediate changes from one transaction should not be visible to another based on the configured isolation level.

D – Durability

Once a transaction is committed, it remains permanent, even if the server crashes immediately afterward.

This is achieved using transaction logs and recovery mechanisms.

Production Example

UPI Payment

• Debit
• Credit
• Commit

Even if the application crashes after the commit, the transaction remains saved.

Interview One-Liner: ACID guarantees reliable, consistent, isolated, and durable transactions, making it essential for transactional systems like banking and e-commerce.

The CAP Theorem, proposed by Eric Brewer, states that in a distributed database, it is impossible to guarantee all three properties simultaneously:

• Consistency (C): Every read returns the latest committed data.
• Availability (A): Every request receives a response, even if some nodes are down.
• Partition Tolerance (P): The system continues operating despite network failures between nodes.

When a network partition occurs, a distributed system must choose between Consistency and Availability.

The Three Properties

1. Consistency (C)

Every client sees the same data at the same time.

Example:

You update your profile.

All users immediately see the updated profile.

2. Availability (A)

Every request receives a response.

The response may contain older (stale) data if consistency is sacrificed.

3. Partition Tolerance (P)

The system continues working even if communication between servers is interrupted.

This is essential because network failures are unavoidable in distributed environments.

CAP Combinations

CP (Consistency + Partition Tolerance)

• Prioritizes consistent data.
• May reject or delay requests during partitions.

Examples:

• HBase
• ZooKeeper

Use Cases:

• Banking
• Financial systems
• Distributed locking
• Inventory management

AP (Availability + Partition Tolerance)

• Prioritizes availability.
• May return stale data until replicas synchronize.

Examples:

• Cassandra
• DynamoDB
• CouchDB

Use Cases:

• Social media
• Product catalogs
• Recommendation systems
• News feeds

CA (Consistency + Availability)

Possible only when there is no network partition, typically in a single-node or tightly coupled environment. In distributed systems, partition tolerance is mandatory, so true CA is generally not achievable once partitions are considered.

Examples:

• Traditional single-node relational databases (before distribution)

Production Example

Banking Application

A balance must always be correct.

If replicas lose communication, it’s better to reject transactions than show inconsistent balances.

Choose: CP

Amazon Product Catalog

During high traffic, showing a slightly outdated product description is acceptable if the site remains responsive.

Choose: AP

CAP vs ACID

SQL (Structured Query Language) commands are broadly divided into five categories based on their purpose.

Easy Memory Trick

DDL → Structure

DML → Data

DQL → Query

DCL → Permission

TCL → Transaction

Production Example

Suppose you’re developing an E-Commerce application.

• CREATE Order table → DDL
• INSERT new order → DML
• SELECT order details → DQL
• GRANT access to reporting team → DCL
• COMMIT payment transaction → TCL

Interview One-Liner: SQL commands are categorized into DDL, DML, DQL, DCL, and TCL based on whether they define structures, manipulate data, retrieve data, control permissions, or manage transactions.

Interview Tip: Interviewer may ask:

Is SELECT DML?

Answer:

No.

SELECT belongs to DQL (Data Query Language) although many books include it under DML.

The CREATE command is a DDL command used to create database objects.

Examples:

• Database
• Table
• View
• Index

Syntax

CREATE TABLE Employee (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    salary DECIMAL(10,2)
);

Production Example

When a new microservice is deployed, Liquibase or Flyway executes CREATE scripts to create required tables.

Interview One-Liner: CREATE is used to create new database objects like tables, indexes, and views.

ALTER modifies the structure of an existing database object.

You can:

• Add columns
• Remove columns
• Rename columns
• Modify datatype
• Add constraints

Example

Add column

ALTER TABLE Employee
ADD email VARCHAR(100);

Modify datatype

ALTER TABLE Employee
MODIFY salary DECIMAL(12,2);

Production Example

Adding a new mobile_number field without recreating the table.

Interview One-Liner: ALTER changes the structure of an existing database object without deleting its data.

DROP permanently removes a database object.

It removes:

• Structure
• Data
• Constraints
• Indexes

Example

DROP TABLE Employee;

Important

Cannot be rolled back in most databases if auto-committed.

Interview One-Liner: DROP permanently deletes both the table structure and all its data.

TRUNCATE removes all records from a table while keeping the table structure intact.

Example

TRUNCATE TABLE Employee;

Characteristics

• Faster than DELETE
• Removes all rows
• Cannot use WHERE
• Resets identity/auto-increment in many databases (behavior is database-specific)

DELETE vs TRUNCATE

Production Example

Cleaning staging tables before loading fresh ETL data.

Interview One-Liner: TRUNCATE quickly removes all rows while preserving the table structure.

DELETE removes records from a table.

Delete all records

DELETE FROM Employee;

Delete specific records

DELETE FROM Employee
WHERE id = 10;

Features

• WHERE supported
• Row by row deletion
• Slower than TRUNCATE for large tables
• Fires DELETE triggers (if defined)

Interview One-Liner: DELETE removes one or more rows and supports filtering using the WHERE clause.

INSERT adds new records into a table.

Example

INSERT INTO Employee
(id, name, salary)
VALUES
(1,'John',50000);

Multiple Records

INSERT INTO Employee
VALUES
(1,'John',50000),
(2,'Alice',70000);

Production Example

Customer registration inserts a new row into the Customer table.

Interview One-Liner: INSERT is used to add new records into a table.

UPDATE modifies existing records.

Example

UPDATE Employee
SET salary = 70000
WHERE id = 10;

Important

Always use WHERE unless updating every row intentionally.

Without WHERE:

UPDATE Employee
SET salary = 70000;

Every employee’s salary will become ₹70,000.

Production Example

Updating order status from PENDING to DELIVERED.

Interview One-Liner: UPDATE modifies existing records in a table.

SELECT retrieves data from one or more tables.

Example

SELECT *
FROM Employee;

Specific columns

SELECT name, salary
FROM Employee;

Production Example

Displaying products on an e-commerce website.

Interview One-Liner: SELECT retrieves data from database tables.

DISTINCT removes duplicate values from the result set.

Example

Without DISTINCT

SELECT department
FROM Employee;

Output

IT

IT

HR

HR

With DISTINCT

SELECT DISTINCT department
FROM Employee;

Output

IT

HR

Interview One-Liner: DISTINCT returns only unique values.

WHERE filters rows before grouping or aggregation.

Example

SELECT *
FROM Employee
WHERE salary > 50000;

Operators

• =
• <
• =
• <=
• <>
• BETWEEN
• LIKE
• IN
• IS NULL

Production Example

Find all pending orders.

SELECT *
FROM Orders
WHERE status='PENDING';

Interview One-Liner: WHERE filters rows before any grouping or aggregation occurs.

ORDER BY sorts the result set.

Ascending

SELECT *
FROM Employee
ORDER BY salary;

Descending

SELECT *
FROM Employee
ORDER BY salary DESC;

Multiple Columns

ORDER BY department, salary DESC;

Interview One-Liner: ORDER BY sorts query results in ascending or descending order.

GROUP BY groups rows having the same values so aggregate functions can be applied.

Example

SELECT department,
COUNT(*)
FROM Employee
GROUP BY department;

Output

Common Aggregate Functions

• COUNT
• SUM
• AVG
• MAX
• MIN

Production Example

Count orders by status.

Interview One-Liner: GROUP BY groups rows to calculate aggregates such as COUNT, SUM, and AVG.

HAVING filters grouped data after GROUP BY.

Example

SELECT department,
COUNT(*)
FROM Employee
GROUP BY department
HAVING COUNT(*) > 10;

Only departments with more than 10 employees are returned.

Interview One-Liner: HAVING filters groups after aggregation.

LIMIT restricts the number of rows returned.

Example

SELECT *
FROM Employee
LIMIT 10;

Returns first 10 rows.

Production Example

Latest 20 orders.

SELECT *
FROM Orders
ORDER BY order_date DESC
LIMIT 20;

Interview One-Liner: LIMIT restricts the number of rows returned by a query.

OFFSET skips a specified number of rows before returning results.

Example

SELECT *
FROM Employee
LIMIT 10 OFFSET 20;

Returns rows 21–30.

Production Example

Pagination

Page Size = 20

Page 3

OFFSET = 40

LIMIT = 20

Interview One-Liner: OFFSET skips rows and is commonly used with LIMIT for pagination.

An Alias provides a temporary name to a column or table for the duration of a query.

Column Alias

SELECT salary AS EmployeeSalary
FROM Employee;

Table Alias

SELECT e.name
FROM Employee e;

Advantages

• Improves readability
• Simplifies joins
• Makes reports more user-friendly

Interview One-Liner: An alias is a temporary name assigned to a column or table within a query.

Many candidates write SQL correctly but don’t know the execution order. This is a favorite interview topic.

Query

SELECT department,
       COUNT(*) AS total
FROM Employee
WHERE salary > 50000
GROUP BY department
HAVING COUNT(*) > 5
ORDER BY total DESC
LIMIT 3;

Actual Execution Order

1. FROM

2. WHERE

3. GROUP BY

4. HAVING

5. SELECT

6. DISTINCT

7. ORDER BY

8. LIMIT / OFFSET

Easy Memory Trick

FROM

WHERE

GROUP BY

HAVING

SELECT

DISTINCT

ORDER BY

LIMIT

Interview Question

Why can’t we use an alias in the WHERE clause?

Example

SELECT salary * 12 AS annual_salary
FROM Employee
WHERE annual_salary > 600000;

❌ Invalid because WHERE is executed before SELECT, so the alias doesn’t exist yet.

Instead:

SELECT salary * 12 AS annual_salary
FROM Employee
HAVING annual_salary > 600000;

Or use a subquery (more portable across databases).

Interview One-Liner: SQL executes logically as FROM → WHERE → GROUP BY → HAVING → SELECT → DISTINCT → ORDER BY → LIMIT/OFFSET.

Example

WHERE

SELECT *
FROM Employee
WHERE salary > 50000;

Filters employees before grouping.

HAVING

SELECT department,
COUNT(*)
FROM Employee
GROUP BY department
HAVING COUNT(*) > 10;

Filters departments after aggregation.

Production Example

Find departments with more than 10 employees earning above ₹50,000.

SELECT department,
       COUNT(*)
FROM Employee
WHERE salary > 50000
GROUP BY department
HAVING COUNT(*) > 10;

Execution:

• WHERE filters employees with salary > ₹50,000.
• GROUP BY groups the remaining employees by department.
• HAVING returns only departments having more than 10 such employees.

Interview One-Liner: WHERE filters rows before grouping, whereas HAVING filters groups after aggregation.

Constraints are rules applied to table columns to ensure that only valid data is stored in the database.

They help maintain:

• Data integrity
• Data consistency
• Business rules
• Referential integrity

Common constraints are:

• PRIMARY KEY
• FOREIGN KEY
• UNIQUE
• NOT NULL
• CHECK
• DEFAULT

Production Example

In an E-Commerce application:

• Customer ID must be unique.
• Order must belong to an existing customer.
• Product price cannot be negative.
• Email should not be duplicated.

These rules are enforced using constraints.

Interview One-Liner: Constraints are database rules that ensure only valid and consistent data is stored.

A Primary Key uniquely identifies every row in a table.

Properties:

• Unique
• Cannot be NULL
• Only one Primary Key per table
• Automatically creates an index in most databases

Example

CREATE TABLE Employee (
    id INT PRIMARY KEY,
    name VARCHAR(100)
);

Data

Cannot insert:

ID = 1

again.

Production Example

Customer ID

Order ID

Employee ID

Product ID

Interview One-Liner: A Primary Key uniquely identifies each record and cannot contain NULL values.

A Foreign Key creates a relationship between two tables.

It ensures that the referenced value exists in the parent table.

Example

Customer

Orders

CustomerID in Orders is a Foreign Key.

SQL

CREATE TABLE Orders (
    order_id INT,
    customer_id INT,
    FOREIGN KEY(customer_id)
    REFERENCES Customer(customer_id)
);

Benefits

• Prevents orphan records
• Maintains referential integrity
• Creates relationships

Interview One-Liner: A Foreign Key maintains relationships by ensuring child records reference valid parent records.

UNIQUE ensures that all values in a column are different.

Unlike Primary Key:

• Multiple UNIQUE constraints allowed
• NULL handling depends on the database (many databases allow one or more NULLs)

Example

CREATE TABLE Employee (
    email VARCHAR(100) UNIQUE
);

Duplicate email is not allowed.

Production Example

• Aadhaar Number
• PAN Number
• Email
• Username

Interview One-Liner: UNIQUE prevents duplicate values while allowing database-specific handling of NULL values.

NOT NULL ensures that a column cannot contain NULL values.

Example

CREATE TABLE Employee (
    name VARCHAR(100) NOT NULL
);

Cannot insert:

NULL

Production Example

Mandatory fields

• Name
• Password
• Order Date

Interview One-Liner: NOT NULL ensures mandatory data is always provided.

CHECK validates data according to a specified condition.

Example

CREATE TABLE Employee (
    age INT CHECK(age>=18)
);

Cannot insert:

age = 15

Production Example

Salary > 0

Age >=18

Rating between 1 and 5

Interview One-Liner: CHECK enforces business rules by validating column values.

DEFAULT assigns a value automatically when no value is supplied.

Example

CREATE TABLE Employee (
    status VARCHAR(20)
    DEFAULT 'ACTIVE'
);

Insert

INSERT INTO Employee(id,name)
VALUES(1,'John');

Status automatically becomes

ACTIVE

Production Example

Order Status

PENDING

Account Status

ACTIVE

Created Date

CURRENT_TIMESTAMP

Interview One-Liner: DEFAULT automatically inserts a predefined value when none is provided.

A Composite Key consists of two or more columns that together uniquely identify a record.

Example

Student Course Enrollment

Neither column is unique individually.

Together they are unique.

SQL

PRIMARY KEY(StudentID, CourseID)

Production Example

Student Enrollment

Order Items

Flight Seat Booking

Interview One-Liner: A Composite Key uses multiple columns together to uniquely identify a record.

A Candidate Key is any column (or combination of columns) that can uniquely identify a row.

A table may have multiple Candidate Keys.

One of them becomes the Primary Key.

Example

Employee

Possible Candidate Keys

• EmpID
• Email
• PAN

Primary Key

EmpID

Interview One-Liner: A Candidate Key is any unique identifier eligible to become the Primary Key.

Alternate Keys are Candidate Keys that were not selected as the Primary Key.

Example

Candidate Keys

• EmpID
• Email
• PAN

Primary Key

EmpID

Alternate Keys

• Email
• PAN

Interview One-Liner: Alternate Keys are Candidate Keys that are not chosen as the Primary Key.

A Surrogate Key is an artificial key generated by the system.

It has no business meaning.

Usually:

• AUTO_INCREMENT
• IDENTITY
• SEQUENCE
• UUID

Example

CustomerID = 101

Generated automatically.

Production Example

Almost every modern application uses Surrogate Keys because they are stable even if business data changes.

Advantages

• Small
• Fast joins
• Never changes
• Better indexing

Interview One-Liner: A Surrogate Key is a system-generated identifier with no business meaning.

A Natural Key is derived from real business data.

Examples

• Email
• Aadhaar Number
• PAN
• Passport Number

Problem

Business values may change.

Email

abc@gmail.com

can become

xyz@gmail.com

Primary Keys should ideally remain stable.

Interview One-Liner: A Natural Key comes from real business data and may change over time.

Interview Tip: Can a table have multiple Primary Keys?

No.

Only one Primary Key.

But it may consist of multiple columns (Composite Primary Key).

Foreign Key cascading defines what happens to child records when the parent record is updated or deleted.

Common Options

ON DELETE CASCADE

Deleting parent automatically deletes child records.

Example

Delete Customer

↓

Delete Orders

ON DELETE SET NULL

Parent deleted

↓

Child Foreign Key becomes NULL.

ON DELETE RESTRICT / NO ACTION

Prevent parent deletion while child records exist.

ON UPDATE CASCADE

Updating parent key updates child keys automatically.

Production Example

Social Media

Delete User

↓

Delete User Posts

↓

Delete Comments

Interview One-Liner: Cascading automatically propagates updates or deletions from parent records to child records according to the configured action.

A JOIN combines rows from two or more tables based on a related column.

It is primarily used to retrieve related data stored in separate tables.

Example

Customer

Orders

JOIN combines these tables to show customer details along with orders.

Interview One-Liner: A JOIN retrieves related data by combining rows from multiple tables using a matching condition.

INNER JOIN returns only the rows that have matching values in both tables.

SQL

SELECT c.customer_id,
       c.name,
       o.order_id
FROM Customer c
INNER JOIN Orders o
ON c.customer_id = o.customer_id;

Result

Only customers having orders are returned.

Production Example

Show customers who placed at least one order.

Interview One-Liner: INNER JOIN returns only matching records from both tables.

LEFT JOIN returns:

• All rows from the left table
• Matching rows from the right table
• NULL when no match exists

Example

Customers

John

Alice

Orders

John only

Result

SQL

SELECT c.name,
       o.order_id
FROM Customer c
LEFT JOIN Orders o
ON c.customer_id = o.customer_id;

Production Example

Find customers who have not placed any orders.

SELECT c.*
FROM Customer c
LEFT JOIN Orders o
ON c.customer_id = o.customer_id
WHERE o.order_id IS NULL;

Interview One-Liner: LEFT JOIN returns all rows from the left table and matching rows from the right table.

RIGHT JOIN returns:

• All rows from the right table
• Matching rows from the left table

SQL

SELECT c.name,
       o.order_id
FROM Customer c
RIGHT JOIN Orders o
ON c.customer_id = o.customer_id;

Interview Tip: Many teams avoid RIGHT JOIN because the same result can usually be achieved with a LEFT JOIN by swapping the table order, improving readability.

Interview One-Liner: RIGHT JOIN returns all rows from the right table and matching rows from the left table.

FULL OUTER JOIN returns:

• All rows from the left table
• All rows from the right table
• Matching rows where possible
• NULL for non-matching rows

Result

Note

MySQL does not support FULL OUTER JOIN directly. It is commonly simulated using LEFT JOIN UNION RIGHT JOIN.

Interview One-Liner: FULL OUTER JOIN returns all records from both tables, matching where possible.

CROSS JOIN returns the Cartesian product.

Every row of the first table is combined with every row of the second table.

Example

3 Customers

2 Products

Result

3 × 2 = 6 rows

SQL

SELECT *
FROM Customer
CROSS JOIN Product;

Use Cases

• Generate combinations
• Calendar generation
• Testing

Interview One-Liner: CROSS JOIN produces every possible combination of rows from two tables.

A SELF JOIN joins a table with itself.

It is useful for hierarchical relationships.

Example

Employee

SQL

SELECT e.name AS employee,
       m.name AS manager
FROM Employee e
LEFT JOIN Employee m
ON e.manager_id = m.emp_id;

Production Example

Employee–Manager hierarchy

Category–Parent Category

Organization structures

Interview One-Liner: A SELF JOIN joins a table with itself using aliases.

Example

Customer

John

Alice

Orders

John only

INNER JOIN

John

LEFT JOIN

John

Alice

Interview One-Liner: INNER JOIN returns only matching records, while LEFT JOIN returns all rows from the left table even if no match exists.

SQL allows joining more than two tables in a single query.

Example

Customer

↓

Orders

↓

OrderItems

↓

Products

SELECT c.name,
       o.order_id,
       p.product_name
FROM Customer c
JOIN Orders o
  ON c.customer_id = o.customer_id
JOIN OrderItems oi
  ON o.order_id = oi.order_id
JOIN Product p
  ON oi.product_id = p.product_id;

Production Example

Amazon Order History

Customer

↓

Orders

↓

Order Items

↓

Products

↓

Payments

Interview Tip: Always join on indexed columns (typically primary and foreign keys) to improve performance.

Example

JOIN

SELECT e.name,
       d.department_name
FROM Employee e
JOIN Department d
ON e.department_id = d.department_id;

Subquery

SELECT name
FROM Employee
WHERE department_id =
(
SELECT department_id
FROM Department
WHERE department_name='IT'
);

Interview Tip: Modern optimizers can often rewrite subqueries into joins, so performance depends on the query and database engine. Always analyze execution plans rather than assuming one is always faster.

Interview One-Liner: Use JOIN to combine related tables and Subqueries when nested filtering or calculations make the query clearer.

JOIN performance depends on schema design, indexing, query structure, and the optimizer.

Best Practices

1. Index Join Columns

CustomerID

OrderID

ProductID

2. Filter Early

WHERE status='ACTIVE'

Reduce rows before joining.

3. Select Required Columns

Avoid

SELECT *

Use

SELECT name, salary

4. Join on Indexed Keys

Prefer Primary Keys and Foreign Keys.

5. Check Execution Plan

Use:

• EXPLAIN
• EXPLAIN ANALYZE (where supported)

to verify index usage, join order, and scan types.

6. Avoid Unnecessary Joins

Join only the tables needed for the result.

Production Example

An e-commerce order history query joining five tables can become slow if foreign key columns are not indexed. Adding indexes on customer_id, order_id, and product_id often reduces query time significantly.

Interview One-Liner: Efficient joins rely on proper indexing, early filtering, selecting only required columns, and validating the execution plan.

SQL Functions are built-in functions provided by the database to perform operations on data.

They help in:

• Calculations
• String manipulation
• Date operations
• NULL handling
• Data formatting

There are two major categories:

1. Aggregate Functions

2. Single Row (Scalar) Functions

Interview One-Liner: SQL functions simplify data manipulation, calculations, formatting, and reporting directly within SQL queries.

Aggregate Functions ⭐⭐⭐⭐⭐

Aggregate functions perform calculations on a set of rows and return a single result.

Common Aggregate Functions:

• COUNT
• SUM
• AVG
• MAX
• MIN

COUNT returns the number of rows.

Example

SELECT COUNT(*)
FROM Employee;

Returns

100

COUNT(column)

SELECT COUNT(email)
FROM Employee;

Counts only non-NULL email values.

COUNT(DISTINCT)

SELECT COUNT(DISTINCT department)
FROM Employee;

Counts unique departments.

Production Example

Total registered users

Total orders

Total products

Interview One-Liner: COUNT returns the number of rows or non-NULL values in a column.

SUM calculates the total of a numeric column.

Example

SELECT SUM(salary)
FROM Employee;

Output

₹55,00,000

Production Example

Total sales

Total revenue

Total inventory value

Interview One-Liner: SUM returns the total of numeric values.

AVG calculates the average value.

Example

SELECT AVG(salary)
FROM Employee;

Production Example

Average salary

Average order value

Average rating

Interview One-Liner: AVG returns the average of numeric values.

MAX returns the highest value.

Example

SELECT MAX(salary)
FROM Employee;

Production Example

Highest salary

Latest order date

Maximum marks

Interview One-Liner: MAX returns the largest value in a column.

MIN returns the smallest value.

Example

SELECT MIN(salary)
FROM Employee;

Production Example

Lowest salary

Earliest order date

Minimum product price

Interview One-Liner: MIN returns the smallest value in a column.

Aggregate Functions with GROUP BY ⭐⭐⭐⭐⭐

Example

SELECT department,
       COUNT(*) AS employees,
       AVG(salary) AS avg_salary,
       MAX(salary) AS highest_salary
FROM Employee
GROUP BY department;

Output

COALESCE returns the first non-NULL value from the list of expressions.

It is part of the SQL standard and is supported by most databases.

Example

SELECT COALESCE(phone, mobile, 'Not Available')
FROM Customer;

If

phone = NULL

mobile = 9876543210

Output

9876543210

Production Example

Display customer contact number.

Priority

Phone

↓

Mobile

↓

Default Value

Interview One-Liner: COALESCE returns the first non-NULL value from a list of expressions.

These functions replace NULL values with a specified value.

Database-specific functions:

MySQL

SELECT IFNULL(phone,'Not Available')
FROM Customer;

Oracle

SELECT NVL(phone,'Not Available')
FROM Customer;

Production Example

Show

Salary = 0

instead of

NULL

Interview Tip: COALESCE is portable across most databases, while IFNULL, NVL, and ISNULL are vendor-specific.

Interview One-Liner: IFNULL and NVL replace NULL values with a specified value, while COALESCE is the ANSI-standard alternative.

CASE works like an IF-ELSE statement inside SQL.

Example

SELECT name,
CASE
    WHEN salary > 100000 THEN 'High'
    WHEN salary > 50000 THEN 'Medium'
    ELSE 'Low'
END AS salary_category
FROM Employee;

Output

Production Example

Order Status

Paid

Pending

Cancelled

Customer Grade

Gold

Silver

Bronze

Interview One-Liner: CASE WHEN implements conditional logic in SQL, similar to IF-ELSE.

CONCAT joins multiple strings together.

Example

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

Output

John Smith

Production Example

Generate Full Name

Customer Address

Invoice Number

Interview One-Liner: CONCAT combines multiple strings into one.

Converts text to uppercase.

Example

SELECT UPPER(name)
FROM Employee;

Output

JOHN

Production Example

Case-insensitive reporting

Uniform display

Interview One-Liner: UPPER converts text to uppercase.

Converts text to lowercase.

Example

SELECT LOWER(email)
FROM Employee;

Production Example

Store emails in lowercase for consistent comparisons.

Interview One-Liner: LOWER converts text to lowercase.

Returns the number of characters in a string.

Note: In SQL Server, the equivalent function is LEN.

Example

SELECT LENGTH(name)
FROM Employee;

Output

John

4

Production Example

Validate password length

Username validation

Interview One-Liner: LENGTH returns the number of characters in a string.

ROUND rounds a numeric value to a specified number of decimal places.

Example

SELECT ROUND(123.4567,2);

Output

123.46

Production Example

Financial calculations

Tax calculation

GST reporting

Average ratings

Interview One-Liner: ROUND rounds numeric values to the required precision.

Date functions are extremely common in production applications.

Examples include:

• Current Date
• Current Timestamp
• Date Difference
• Date Addition
• Date Formatting
• Extracting Year, Month, Day

CURRENT_DATE

Returns today’s date.

SELECT CURRENT_DATE;

Output

2026-07-27

CURRENT_TIMESTAMP

Returns current date and time.

SELECT CURRENT_TIMESTAMP;

YEAR

SELECT YEAR(order_date)
FROM Orders;

MONTH

SELECT MONTH(order_date)
FROM Orders;

DAY

SELECT DAY(order_date)
FROM Orders;

DATE_ADD (MySQL)

SELECT DATE_ADD(CURRENT_DATE, INTERVAL 7 DAY);

Returns date after 7 days.

DATEDIFF

SELECT DATEDIFF(CURRENT_DATE, joining_date)
FROM Employee;

Returns the difference in days (syntax varies slightly by database).

Production Examples

Find today’s orders

SELECT *
FROM Orders
WHERE order_date = CURRENT_DATE;

Find employees joined this year

SELECT *
FROM Employee
WHERE YEAR(joining_date)=2026;

Find orders in the last 30 days

SELECT *
FROM Orders
WHERE order_date >= DATE_SUB(CURRENT_DATE, INTERVAL 30 DAY);

(MySQL syntax)

Interview Tip: Date functions vary across databases:

Interview One-Liner: Date functions help retrieve, manipulate, compare, and format date and time values for reporting and business logic.

Frequently Asked Production SQL Function Questions ⭐⭐⭐⭐⭐

Q1. Difference between COUNT(*) and COUNT(column)?

Q2. Difference between COALESCE and IFNULL?

Q3. Can Aggregate Functions be used in WHERE?

No.

❌ Incorrect

SELECT *
FROM Employee
WHERE COUNT(*) > 5;

✅ Correct

SELECT department,
       COUNT(*)
FROM Employee
GROUP BY department
HAVING COUNT(*) > 5;

Reason:

• WHERE executes before aggregation.
• HAVING executes after GROUP BY.

GROUP BY groups rows having the same values in one or more columns so that aggregate functions can be applied to each group.

It is commonly used with:

• COUNT
• SUM
• AVG
• MAX
• MIN

Example

Employee Table

Query

SELECT department,
       COUNT(*) AS total_employees
FROM Employee
GROUP BY department;

Output

Production Example

• Number of employees department-wise
• Orders city-wise
• Revenue month-wise
• Sales category-wise

Interview One-Liner: GROUP BY groups rows having the same values so aggregate functions can be applied.

HAVING filters grouped data after aggregation.

WHERE filters rows before grouping.

Example

SELECT department,
       COUNT(*) AS total
FROM Employee
GROUP BY department
HAVING COUNT(*) > 5;

Only departments having more than 5 employees are returned.

Execution Order

FROM

↓

WHERE

↓

GROUP BY

↓

HAVING

↓

SELECT

↓

ORDER BY

Interview One-Liner: HAVING filters grouped records after GROUP BY.

Aggregate queries summarize data using aggregate functions.

COUNT

SELECT COUNT(*)
FROM Employee;

SUM

SELECT SUM(salary)
FROM Employee;

AVG

SELECT AVG(salary)
FROM Employee;

MAX

SELECT MAX(salary)
FROM Employee;

MIN

SELECT MIN(salary)
FROM Employee;

Combined Example

SELECT department,
       COUNT(*) AS employees,
       SUM(salary) AS total_salary,
       AVG(salary) AS average_salary,
       MAX(salary) AS highest_salary,
       MIN(salary) AS lowest_salary
FROM Employee
GROUP BY department;

Production Example

Monthly revenue report

Department salary report

Product sales summary

Interview One-Liner: Aggregate queries summarize multiple rows into meaningful business information.

SQL allows grouping by multiple columns.

Example

Employee

Query

SELECT department,
       city,
       COUNT(*) AS employees
FROM Employee
GROUP BY department, city;

Output

Production Example

Sales report

Department + Branch

Product + Category

Year + Month

Interview One-Liner: Multiple GROUP BY columns create groups based on every unique combination of those columns.

DISTINCT

SELECT DISTINCT department
FROM Employee;

GROUP BY

SELECT department,
       COUNT(*)
FROM Employee
GROUP BY department;

Interview Tip: If you only need unique values, use DISTINCT.

If you need calculations like COUNT, SUM, or AVG, use GROUP BY.

Interview One-Liner: DISTINCT removes duplicate rows, whereas GROUP BY groups rows for aggregation.

This is one of the most common SQL interview questions.

Example

Employee

Query

SELECT email,
       COUNT(*) AS total
FROM Employee
GROUP BY email
HAVING COUNT(*) > 1;

Output

Production Example

Duplicate

• Email
• Phone Number
• Aadhaar
• PAN
• Username

Interview One-Liner: Find duplicates using GROUP BY and HAVING COUNT(*) > 1.

Very common interview question.

Query

SELECT department,
       MAX(salary) AS highest_salary
FROM Employee
GROUP BY department;

Output

To Get Employee Details

SELECT e.*
FROM Employee e
JOIN (
    SELECT department,
           MAX(salary) AS highest_salary
    FROM Employee
    GROUP BY department
) d
ON e.department = d.department
AND e.salary = d.highest_salary;

Production Example

Highest-paid employee in every department.

Interview One-Liner: Use GROUP BY with MAX to find the highest salary per department, and join back if full employee details are required.

One of the most frequently asked SQL interview questions.

Method 1 (Using Subquery)

SELECT MAX(salary)
FROM Employee
WHERE salary <
(
SELECT MAX(salary)
FROM Employee
);

Method 2 (Using DENSE_RANK – Recommended)

SELECT *
FROM
(
SELECT *,
       DENSE_RANK OVER
       (ORDER BY salary DESC) rnk
FROM Employee
) t
WHERE rnk = 2;

Why DENSE_RANK?

Suppose

Using

ROW_NUMBER

returns only one employee.

Using

DENSE_RANK

returns all employees having the second highest salary.

Production Interview Tip

For modern databases (MySQL 8+, PostgreSQL, SQL Server, Oracle), prefer window functions like DENSE_RANK because they are clearer and handle ties correctly.

Interview One-Liner: The second highest salary can be found using a subquery or, preferably in modern SQL, with DENSE_RANK.

A Subquery is a query written inside another SQL query.

It is enclosed within parentheses and its result is used by the outer query.

A subquery can be used in:

• SELECT
• WHERE
• HAVING
• FROM

Example

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

Employees earning above average salary.

Production Example

Find employees

• Above average salary
• Latest order
• Highest sales
• Best-performing branch

Interview One-Liner: A subquery is a query inside another query whose result is used by the outer query.

A Correlated Subquery depends on the outer query.

It executes once for every row processed by the outer query.

Example

SELECT e1.*
FROM Employee e1
WHERE salary >
(
SELECT AVG(salary)
FROM Employee e2
WHERE e1.department = e2.department
);

Returns employees earning more than the average salary of their own department.

Characteristics

• Uses outer query values
• Executes row by row
• Usually slower than non-correlated queries
• Can often be rewritten using joins or window functions

Interview One-Liner: A Correlated Subquery references columns from the outer query and executes once per outer row.

A Non-Correlated Subquery executes independently.

The inner query runs first.

The outer query then uses its result.

Example

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

Characteristics

• Independent
• Executes once
• Generally more efficient than correlated subqueries

Interview One-Liner: A Non-Correlated Subquery executes independently and provides a result to the outer query.

EXISTS checks whether a subquery returns at least one row.

It returns TRUE if the subquery returns any record.

Example

SELECT *
FROM Customer c
WHERE EXISTS
(
SELECT 1
FROM Orders o
WHERE o.customer_id = c.customer_id
);

Customers who have placed orders.

Advantages

• Stops searching after finding the first matching row.
• Efficient for existence checks, especially on indexed columns.

Interview One-Liner: EXISTS returns TRUE if the subquery returns at least one row.

NOT EXISTS returns rows where the subquery returns no rows.

Example

SELECT *
FROM Customer c
WHERE NOT EXISTS
(
SELECT 1
FROM Orders o
WHERE o.customer_id = c.customer_id
);

Customers without orders.

Production Example

Inactive users

Products never sold

Students without enrollments

Interview One-Liner: NOT EXISTS returns rows for which the subquery finds no matching records.

IN checks whether a value matches any value in a list or subquery.

Example

SELECT *
FROM Employee
WHERE department_id IN
(
SELECT department_id
FROM Department
WHERE location='Delhi'
);

Production Example

Employees

Products

Orders

Matching multiple IDs.

Interview One-Liner: IN checks whether a value exists within a list or subquery result.

NOT IN returns rows whose values are not present in the list or subquery.

Example

SELECT *
FROM Employee
WHERE department_id NOT IN
(
SELECT department_id
FROM ClosedDepartments
);

Interview Tip: Be careful with NULL values. If the subquery returns NULL, NOT IN may produce unexpected results because comparisons with NULL evaluate to UNKNOWN. NOT EXISTS is often the safer choice.

Interview One-Liner: NOT IN returns rows whose values are absent from the specified list or subquery.

ANY compares a value with any value returned by a subquery.

The condition is TRUE if it matches at least one returned value.

Example

SELECT *
FROM Employee
WHERE salary > ANY
(
SELECT salary
FROM Employee
WHERE department='HR'
);

Employees whose salary is greater than at least one HR employee.

Interview One-Liner: ANY returns TRUE if the comparison succeeds for at least one value from the subquery.

ALL compares a value with every value returned by the subquery.

The condition must be TRUE for all returned values.

Example

SELECT *
FROM Employee
WHERE salary > ALL
(
SELECT salary
FROM Employee
WHERE department='HR'
);

Employees earning more than every HR employee.

Interview One-Liner: ALL returns TRUE only if the comparison succeeds for every value from the subquery.

A Scalar Subquery returns exactly one row and one column (a single value).

It can be used anywhere a single value is expected.

Example

SELECT name,
       salary,
       (
         SELECT AVG(salary)
         FROM Employee
       ) AS company_average
FROM Employee;

Another Example

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

Production Example

Average salary

Highest price

Latest order date

Company revenue

Interview One-Liner: A Scalar Subquery returns a single value that can be used in expressions or comparisons.

Frequently Asked Production Interview Questions ⭐⭐⭐⭐⭐

Q1. Which is faster: JOIN or Subquery?

There is no universal answer.

• Modern query optimizers often transform subqueries into joins.
• For retrieving related data from multiple tables, JOIN is generally clearer and often more efficient.
• For simple existence checks, EXISTS is usually preferred.
• Always verify using EXPLAIN or EXPLAIN ANALYZE.

Q2. EXISTS vs IN

Q3. EXISTS vs NOT EXISTS vs NOT IN

• Use EXISTS to find matching records.
• Use NOT EXISTS to find missing related records.
• Use NOT IN carefully because NULL values in the subquery can change the result unexpectedly.

Set Operators combine the results of multiple SELECT queries into a single result set.

For Set Operators to work:

• Number of columns must be the same.
• Data types of corresponding columns should be compatible.
• Column order must match.

Example

Employee_2025

Employee_2026

These can be combined using UNION or UNION ALL.

Interview One-Liner: Set Operators combine the results of multiple SELECT statements into a single result set.

UNION combines the results of two queries and automatically removes duplicate rows.

Example

SELECT city
FROM Customer

UNION

SELECT city
FROM Supplier;

Output

Delhi

Mumbai

Pune

Duplicate cities appear only once.

Characteristics

• Removes duplicates.
• Performs sorting or hashing internally to eliminate duplicates.
• Slightly slower than UNION ALL because of duplicate removal.

Production Example

Combine customers from two regions without duplicates.

Interview One-Liner: UNION combines results and removes duplicate rows.

UNION ALL combines results without removing duplicates.

Example

SELECT city
FROM Customer

UNION ALL

SELECT city
FROM Supplier;

Output

Delhi

Mumbai

Delhi

Pune

Duplicates are preserved.

Advantages

• Faster
• No duplicate elimination
• Better for large datasets

Production Example

Merge transaction logs from multiple servers where every record is required.

Interview One-Liner: UNION ALL combines result sets while preserving duplicate rows.

INTERSECT returns only the rows that are common to both queries.

Example

Customer Cities

Delhi

Mumbai

Pune

Supplier Cities

Delhi

Pune

Chennai

Query

SELECT city
FROM Customer

INTERSECT

SELECT city
FROM Supplier;

Output

Delhi

Pune

Production Example

Find customers who are also suppliers.

Interview Tip: MySQL does not support INTERSECT directly. It can be simulated using an INNER JOIN or IN clause.

Interview One-Liner: INTERSECT returns only the common rows from both queries.

MINUS (Oracle) or EXCEPT (SQL Server, PostgreSQL) returns rows from the first query that are not present in the second query.

Example

Customer Cities

Delhi

Mumbai

Pune

Supplier Cities

Delhi

Query

SELECT city
FROM Customer

EXCEPT

SELECT city
FROM Supplier;

Output

Mumbai

Pune

Production Example

Customers who never became suppliers.

Employees who never logged in.

Products never sold.

Interview Tip: – Oracle uses MINUS.

• PostgreSQL and SQL Server use EXCEPT.
• MySQL can simulate this using LEFT JOIN … IS NULL or NOT EXISTS.

Interview One-Liner: EXCEPT (or MINUS) returns rows from the first query that do not exist in the second query.

Interview Question

Which one is faster?

Answer:

UNION ALL

Because it does not remove duplicates.

Production Tip

Always use UNION ALL unless duplicate removal is explicitly required.

Interview One-Liner: UNION removes duplicates, while UNION ALL preserves duplicates and is generally faster.

Window Functions perform calculations across a set of related rows (called a window) while retaining every row in the output.

Unlike GROUP BY:

• GROUP BY returns one row per group.
• Window Functions return every row with additional calculated values.

Example

Employee

Window Function

SELECT name,
       department,
       salary,
       AVG(salary)
       OVER(PARTITION BY department) AS dept_avg
FROM Employee;

Every employee row is returned along with the average salary of that department.

Interview One-Liner: Window Functions perform calculations across related rows without reducing the number of rows returned.

OVER defines the window on which the function operates.

It can include:

• PARTITION BY
• ORDER BY
• Window frame (ROWS/RANGE)

Example

SELECT name,
       salary,
       AVG(salary)
       OVER
FROM Employee;

Calculates the company-wide average salary for every row.

With PARTITION BY

SELECT name,
       department,
       AVG(salary)
       OVER(PARTITION BY department)
FROM Employee;

Calculates the average salary within each department.

Interview One-Liner: OVER defines the window of rows used by a window function.

ROW_NUMBER assigns a unique sequential number to each row.

Even if values are tied, numbers remain unique.

Example

SELECT name,
       salary,
       ROW_NUMBER
       OVER(ORDER BY salary DESC) AS row_num
FROM Employee;

Output

Production Example

Pagination

Top N Records

Latest Orders

Interview One-Liner: ROW_NUMBER assigns a unique sequence number to each row.

RANK assigns the same rank to tied values but skips the next rank.

Example

Salary

100000

100000

90000

Ranks

1

1

3

Rank 2 is skipped.

SQL

SELECT name,
       salary,
       RANK
       OVER(ORDER BY salary DESC) AS rnk
FROM Employee;

Interview One-Liner: RANK assigns equal ranks to ties and leaves gaps in the ranking sequence.

DENSE_RANK assigns the same rank to tied values but does not skip subsequent ranks.

Example

Salary

100000

100000

90000

Ranks

1

1

2

Production Example

Second highest salary

Top 3 employees

Leaderboards

Interview One-Liner: DENSE_RANK assigns equal ranks to ties without leaving gaps.

LEAD retrieves the value from the next row without requiring a self join.

Example

SELECT employee_name,
       salary,
       LEAD(salary)
       OVER(ORDER BY salary) AS next_salary
FROM Employee;

Production Example

Compare current month’s sales with the next month.

Interview One-Liner: LEAD returns a value from the following row in the window.

LAG retrieves the value from the previous row.

Example

SELECT employee_name,
       salary,
       LAG(salary)
       OVER(ORDER BY salary) AS previous_salary
FROM Employee;

Production Example

Compare today’s stock price with yesterday’s.

Compare current salary with previous salary.

Interview One-Liner: LAG returns a value from the previous row in the window.

FIRST_VALUE returns the first value within the window.

Example

SELECT employee_name,
       salary,
       FIRST_VALUE(salary)
       OVER(ORDER BY salary DESC) AS highest_salary
FROM Employee;

Every row displays the highest salary in the ordered window.

Production Example

Top performer

Highest sale

Earliest order

Interview One-Liner: FIRST_VALUE returns the first value in the ordered window.

LAST_VALUE returns the last value in the window.

Example

SELECT employee_name,
       salary,
       LAST_VALUE(salary)
       OVER(
           ORDER BY salary
           ROWS BETWEEN UNBOUNDED PRECEDING
                    AND UNBOUNDED FOLLOWING
       ) AS lowest_salary
FROM Employee;

Interview Tip: Without explicitly specifying the window frame, LAST_VALUE may return unexpected results because the default frame often ends at the current row.

Interview One-Liner: LAST_VALUE returns the last value within the defined window frame.

NTILE divides rows into a specified number of nearly equal-sized groups.

Example

SELECT employee_name,
       salary,
       NTILE(4)
       OVER(ORDER BY salary DESC) AS quartile
FROM Employee;

Divides employees into four quartiles.

Production Example

Customer segmentation

Salary bands

Performance buckets

Interview One-Liner: NTILE divides rows into approximately equal groups.

One of the most commonly asked window function questions.

Example

SELECT order_date,
       amount,
       SUM(amount)
       OVER(
           ORDER BY order_date
       ) AS running_total
FROM Orders;

Output

Production Example

Bank account balance

Sales dashboard

Monthly revenue

Inventory movement

Interview One-Liner: A running total is a cumulative sum calculated using a window function.

Frequently asked in product companies.

Example

3-day Moving Average

SELECT order_date,
       amount,
       AVG(amount)
       OVER(
           ORDER BY order_date
           ROWS BETWEEN 2 PRECEDING
                    AND CURRENT ROW
       ) AS moving_average
FROM Orders;

Production Example

Stock prices

Sales trends

Website traffic

Temperature monitoring

Interview One-Liner: A moving average calculates the average over a sliding window of rows.

ROW_NUMBER vs RANK vs DENSE_RANK ⭐⭐⭐⭐⭐

Interview Tip: – ROW_NUMBER → Unique numbering (pagination, unique ranking)

• RANK → Competition ranking (gaps after ties)
• DENSE_RANK → Ranking without gaps (commonly used for “Nth highest salary”)

Frequently Asked Production Interview Questions ⭐⭐⭐⭐⭐

Q1. GROUP BY vs Window Function

Q2. When should you use ROW_NUMBER?

• Pagination
• Remove duplicate rows while keeping one record
• Top-N queries
• Latest record per group

Q3. How do you find the second highest salary?

Preferred answer:

SELECT *
FROM (
    SELECT *,
           DENSE_RANK OVER (ORDER BY salary DESC) AS rnk
    FROM Employee
) t
WHERE rnk = 2;

Q4. When do you use LEAD and LAG?

• Month-over-month sales comparison
• Previous and next transaction analysis
• Trend detection
• Time-series analytics

A CTE (Common Table Expression) is a temporary named result set that exists only during the execution of a single SQL statement.

It is defined using the WITH clause.

Unlike a temporary table, a CTE is not physically stored in the database.

Syntax

WITH EmployeeCTE AS
(
    SELECT *
    FROM Employee
    WHERE salary > 50000
)

SELECT *
FROM EmployeeCTE;

Characteristics

• Improves readability
• Simplifies complex queries
• Can be referenced multiple times within the same statement
• Exists only during query execution

Production Example

Instead of writing the same complex query multiple times, define it once using a CTE.

Example:

• Monthly Sales
• Active Customers
• Top Products

Interview One-Liner: A CTE is a temporary named result set created using the WITH clause to simplify complex SQL queries.

A Recursive CTE references itself and is used to solve hierarchical or recursive problems.

Common use cases:

• Employee hierarchy
• Folder structure
• Category tree
• Organization chart
• Bill of Materials (BOM)

Syntax

WITH RECURSIVE EmployeeHierarchy AS
(
    -- Anchor Query
    SELECT emp_id,
           manager_id,
           name
    FROM Employee
    WHERE manager_id IS NULL

    UNION ALL

    -- Recursive Query
    SELECT e.emp_id,
           e.manager_id,
           e.name
    FROM Employee e
    JOIN EmployeeHierarchy h
      ON e.manager_id = h.emp_id
)

SELECT *
FROM EmployeeHierarchy;

Example

Employee Table

Output

CEO

↓

Manager

↓

Employee

Production Example

Company hierarchy

Product categories

Menu hierarchy

Folder hierarchy

Interview Tip: A Recursive CTE consists of:

1. Anchor Query

2. Recursive Query

3. Termination Condition (when no more rows are produced)

Interview One-Liner: A Recursive CTE repeatedly executes until no new rows are generated, making it ideal for hierarchical data.

Example

Subquery

SELECT *
FROM
(
    SELECT *
    FROM Employee
) e;

CTE

WITH EmployeeCTE AS
(
    SELECT *
    FROM Employee
)

SELECT *
FROM EmployeeCTE;

Interview Tip: Modern query optimizers often generate similar execution plans for equivalent CTEs and subqueries. Choose the one that makes the query easier to understand unless performance testing indicates otherwise.

Interview One-Liner: CTEs improve readability and support recursion, while subqueries are better suited for small, simple nested queries.

A query can define multiple CTEs separated by commas.

They can reference previously defined CTEs in the same WITH clause.

Example

WITH HighSalary AS
(
    SELECT *
    FROM Employee
    WHERE salary > 100000
),

ITEmployees AS
(
    SELECT *
    FROM HighSalary
    WHERE department='IT'
)

SELECT *
FROM ITEmployees;

Production Example

Sales Report

↓

Top Customers

↓

High Revenue Orders

↓

Final Dashboard

Each step can be represented by a separate CTE.

Advantages

• Modular code
• Better readability
• Easier maintenance
• Easier debugging

Interview One-Liner: Multiple CTEs break complex business logic into smaller, reusable query blocks.

Frequently Asked CTE Interview Questions ⭐⭐⭐⭐⭐

Q1. Is a CTE stored permanently?

No.

It exists only for the duration of the SQL statement.

Q2. Can we update data through a CTE?

Yes, in many databases (such as SQL Server and PostgreSQL), if the CTE references an updatable base table and satisfies the database’s update rules. Support and restrictions vary by database.

Q3. Is CTE faster than a Subquery?

Not necessarily.

Performance depends on the database optimizer and the query. Choose CTEs primarily for readability and maintainability, and verify performance using execution plans.

A View is a virtual table created from the result of one or more SQL queries.

Unlike a normal table:

• It usually does not store data itself.
• It stores only the SQL definition.
• Data is retrieved from the underlying tables when the view is queried.

Syntax

CREATE VIEW EmployeeView AS

SELECT emp_id,
       name,
       salary
FROM Employee;

Query

SELECT *
FROM EmployeeView;

Production Example

HR employees should see:

• Name
• Department

But not:

• Salary
• Bank Account
• Aadhaar

A view exposes only the required columns.

Interview One-Liner: A View is a virtual table based on a stored SQL query.

A Simple View is based on a single table and typically does not contain:

• GROUP BY
• Aggregate Functions
• DISTINCT
• Complex Joins

Simple Views are often updatable, subject to database-specific rules.

Example

CREATE VIEW EmployeeView AS

SELECT emp_id,
       name,
       department
FROM Employee;

Advantages

• Easy to maintain
• Can often be updated
• Faster to understand

Interview One-Liner: A Simple View is created from a single table without complex SQL operations.

A Complex View is created using:

• Multiple tables
• Joins
• Aggregate Functions
• GROUP BY
• UNION

Complex Views are often read-only, depending on the database and query.

Example

CREATE VIEW DepartmentSalary AS

SELECT department,
       AVG(salary) average_salary
FROM Employee
GROUP BY department;

Production Example

Monthly Dashboard

Sales Report

Revenue Analytics

Interview One-Liner: A Complex View combines multiple tables or advanced SQL operations.

A Materialized View stores the query result physically on disk.

Unlike a normal View:

• Data is stored.
• Query execution is faster.
• Requires refresh to reflect changes in base tables.

Normal View

Query runs every time

Materialized View

Precomputed result stored on disk

Example

Monthly Sales Report

Instead of calculating every time,

Store

Monthly Revenue

and refresh it periodically.

Production Example

• BI dashboards
• Analytics
• Data Warehouse
• Reporting

Interview Tip: Support for Materialized Views varies by database. Oracle and PostgreSQL support them. MySQL does not provide native materialized views, although similar behavior can be implemented using tables and scheduled refresh jobs.

Interview One-Liner: A Materialized View stores query results physically to improve read performance.

An Updatable View allows INSERT, UPDATE, or DELETE operations through the view.

Generally, it should:

• Be based on a single table.
• Avoid aggregate functions.
• Avoid GROUP BY.
• Avoid DISTINCT.
• Avoid UNION.

The exact rules depend on the database engine.

Example

CREATE VIEW EmployeeView AS

SELECT emp_id,
       name,
       department
FROM Employee;

Update

UPDATE EmployeeView
SET department='HR'
WHERE emp_id=1;

The underlying Employee table is updated.

Interview One-Liner: An Updatable View allows modifications to the underlying base table when database rules permit.

1. Security

Hide sensitive columns.

Example

Hide

• Salary
• Password
• Aadhaar

2. Simplicity

Complex joins become simple.

Instead of writing:

5 JOINs

Use:

SELECT *
FROM EmployeeDashboard;

3. Reusability

Write the query once.

Use it everywhere.

4. Data Abstraction

Users don’t need to know table structures.

5. Maintainability

Modify the view definition instead of updating every application query.

Production Example

Banking

Different departments see different information using different views.

Interview One-Liner: Views improve security, simplify queries, promote reuse, and provide data abstraction.

Interview Question

Does a View store data?

Answer:

A standard view typically stores only the SQL definition.

Exception:

Materialized Views store data physically.

Frequently Asked Production Interview Questions ⭐⭐⭐⭐⭐

Q1. Why use Views instead of Tables?

• Hide sensitive data
• Simplify queries
• Improve maintainability
• Reuse common business logic

Q2. Are Views faster than Tables?

Not necessarily.

A standard view executes its underlying query each time it is accessed. Performance depends on the query and indexes. Materialized Views are faster for repeated reads because they store precomputed results.

Q3. Can we insert data into a View?

Yes, if it is an updatable view and satisfies the database’s update rules.

Q4. Materialized View vs View

An Index is a database object that improves the speed of data retrieval operations.

It works similarly to the index of a book.

Instead of scanning every row, the database uses the index to directly locate the required data.

Without Index

Book

Page 1

Page 2

Page 3

...

Page 900

Need to search page by page.

With Index

Java

→ Page 560

Direct access.

SQL

CREATE INDEX idx_employee_name
ON Employee(name);

Production Example

Amazon Product Search

Instead of searching millions of products,

Index on

product_name

helps find results quickly.

Interview One-Liner: An Index is a special data structure that speeds up data retrieval by avoiding full table scans.

Indexes improve query performance.

Without an index,

Database performs

Full Table Scan

With an index,

Database performs

Index Scan

↓

Row Lookup

Advantages

✔ Faster SELECT

✔ Faster WHERE

✔ Faster ORDER BY

✔ Faster JOIN

✔ Faster GROUP BY

Production Example

SELECT *
FROM Customer
WHERE email='abc@gmail.com';

Without Index

Search every customer.

With Index

Direct lookup.

Interview One-Liner: Indexes reduce query execution time by minimizing the number of rows the database must scan.

A Clustered Index determines the physical order of data in the table.

Since data can be physically arranged only one way,

A table can have only one Clustered Index.

Example

Employee Table

Clustered on

EmployeeID

Physical Storage

1

2

3

4

5

Characteristics

• Data stored in sorted order
• Only one per table
• Fast range queries
• Commonly created automatically on the Primary Key in SQL Server (implementation differs by database)

Production Example

Transaction History

Order History

Customer ID

Interview One-Liner: A Clustered Index defines the physical storage order of table data.

A Non-Clustered Index stores a separate structure containing indexed values and pointers (row locators) to the actual table rows.

Multiple Non-Clustered Indexes can exist on a table.

Example

Employee Table

Index

Name

↓

Row Pointer

↓

Employee Record

Characteristics

• Separate structure
• Multiple allowed
• Uses pointers

Production Example

Index

Email

Phone

PAN

Username

Interview One-Liner: A Non-Clustered Index stores indexed values separately with references to the actual rows.

A Composite Index consists of multiple columns.

Example

CREATE INDEX idx_dept_salary

ON Employee(department, salary);

Useful Query

SELECT *
FROM Employee
WHERE department='IT'
AND salary>50000;

Leftmost Prefix Rule

A Composite Index on:

(department, salary)

Can efficiently support:

✔ department

✔ department + salary

But generally not salary alone.

Production Example

Orders

(customer_id,

order_date)

Interview One-Liner: A Composite Index indexes multiple columns and is most effective when queries follow the leftmost indexed columns.

A Covering Index contains all the columns required to satisfy a query.

The database can answer the query directly from the index without accessing the table.

Example

Index

(department,

salary)

Query

SELECT department,
       salary
FROM Employee
WHERE department='IT';

No table lookup required.

Benefits

• Very fast
• Less I/O
• Better performance

Production Example

Reporting queries

Dashboards

Read-heavy applications

Interview One-Liner: A Covering Index contains all required columns, allowing the query to be answered using only the index.

A Unique Index ensures duplicate values cannot exist.

Example

CREATE UNIQUE INDEX idx_email

ON Customer(email);

Duplicate email

❌ Not allowed

Production Example

• Email
• Aadhaar
• PAN
• Passport

Interview One-Liner: A Unique Index enforces uniqueness while improving search performance.

A Primary Index is built on the Primary Key.

It uniquely identifies records.

Many databases automatically create an index for the Primary Key.

Example

PRIMARY KEY(EmployeeID)

Production Example

Employee ID

Order ID

Product ID

Interview One-Liner: A Primary Index is created on the Primary Key to enable fast record retrieval.

A Secondary Index is created on columns other than the Primary Key.

Example

Index on

Email

while Primary Key is

EmployeeID

Production Example

Search

• Email
• Mobile
• Username

Interview One-Liner: A Secondary Index improves queries on non-primary-key columns.

A Bitmap Index stores bitmaps instead of row pointers.

Best suited for columns with low cardinality (few distinct values).

Example

Gender

Male

Female

Status

ACTIVE

INACTIVE

Production Example

Data Warehouse

Analytics

BI Reports

Interview Tip: Bitmap indexes are common in Oracle and analytical workloads. They are generally not recommended for high-concurrency OLTP systems because frequent updates can be expensive.

Interview One-Liner: A Bitmap Index is optimized for low-cardinality columns and analytical queries.

A B-Tree (Balanced Tree) Index is the default index type in most relational databases.

It stores keys in sorted order.

Structure

          50
       /      \\
     20        80
   /   \\      /   \\
 10   30    60    90

Search

80

Only a few comparisons are needed.

Advantages

• Fast search
• Fast insert
• Fast delete
• Fast range queries

Production Example

Customer ID

Employee ID

Order Date

Email

Interview One-Liner: A B-Tree Index stores data in a balanced tree, enabling efficient searches, inserts, deletes, and range queries.

A Hash Index uses a hash function to locate records.

Best For

Equality Search

WHERE id=10

Not Good For

WHERE salary>50000

Range queries

ORDER BY

Production Example

Session lookup

Cache lookup

Key-value storage

Interview One-Liner: A Hash Index is optimized for exact-match lookups but not for range searches.

When a query executes,

Without Index

Query

↓

Full Table Scan

↓

Read Every Row

↓

Find Match

With Index

Query

↓

Index Lookup

↓

Row Locator

↓

Required Record

Example

SELECT *
FROM Employee
WHERE email='abc@gmail.com';

Database

• Searches the index.
• Finds the row locator.
• Fetches the matching row.

Interview Tip: The database optimizer chooses whether to use an index based on factors like table size, selectivity, available indexes, and estimated cost.

Interview One-Liner: An index allows the optimizer to navigate directly to matching rows instead of scanning the entire table.

Indexes improve reads but add overhead to writes.

Every INSERT, UPDATE, or DELETE may require index maintenance.

Example

Insert

Employee

Database updates

• Table
• Index 1
• Index 2
• Index 3

More indexes

↓

More work

↓

Slower writes

Situations

• Heavy INSERT workloads
• Frequent UPDATEs
• Frequent DELETEs
• Bulk loads
• Columns with low selectivity (e.g., boolean flags) where indexes may not help

Production Example

Logging tables

Kafka event tables

Audit tables

Usually have fewer indexes.

Interview One-Liner: Indexes improve reads but can slow writes because every data modification may also update the indexes.

Every index consumes:

• Disk space
• Memory
• CPU
• Maintenance time

Problems

• Slower INSERT
• Slower UPDATE
• Slower DELETE
• Longer backups
• Increased storage

Example

Table

10 Columns

Indexes

9 Indexes

Each insert updates almost every index.

Interview One-Liner: Too many indexes increase storage requirements and degrade write performance.

Start by identifying slow queries, then analyze how they access data.

Techniques

1. Execution Plan

Use

EXPLAIN

or

EXPLAIN ANALYZE

to determine whether the query performs an index scan or a full table scan.

2. Monitor Slow Queries

Check:

• Slow query logs
• Query performance dashboards
• Database monitoring tools

3. Analyze Frequent Filters

Columns frequently used in:

• WHERE
• JOIN
• ORDER BY
• GROUP BY

may benefit from indexes.

4. Database Recommendations

Many databases provide tuning recommendations, such as:

• SQL Server Database Engine Tuning Advisor
• SQL Server Missing Index DMVs
• MySQL Performance Schema and EXPLAIN
• PostgreSQL EXPLAIN ANALYZE and extensions like pg_stat_statements

Recommendations should always be reviewed before implementation.

Production Example

Slow Query

SELECT *
FROM Orders
WHERE customer_id=100;

No index

↓

Full Table Scan

Create

CREATE INDEX idx_customer
ON Orders(customer_id);

↓

Index Scan

↓

Significant improvement in execution time.

Interview One-Liner: Use execution plans, query analysis, and workload patterns to identify useful indexes.

Clustered vs Non-Clustered Index ⭐⭐⭐⭐⭐

B-Tree vs Hash Index ⭐⭐⭐⭐⭐

Frequently Asked Production Interview Questions ⭐⭐⭐⭐⭐

Q1. Why doesn’t the database use an index every time?

Because the optimizer estimates cost. If a query returns a large percentage of the table, a full table scan may be cheaper than using an index.

Q2. Which columns should be indexed?

• Primary Keys
• Foreign Keys (often beneficial, especially for joins)
• Frequently used WHERE columns
• JOIN columns
• ORDER BY columns
• GROUP BY columns

Avoid indexing every column indiscriminately.

Q3. Why isn’t indexing a boolean column usually beneficial?

Columns with very few distinct values (low cardinality), such as true/false, often match a large portion of the table, so the optimizer may prefer a full scan.

Q4. How do you optimize a slow SQL query?

1. Analyze the execution plan.

2. Eliminate unnecessary SELECT *.

3. Add or improve indexes where appropriate.

4. Rewrite inefficient joins or subqueries.

5. Filter rows as early as possible.

6. Verify improvements with EXPLAIN ANALYZE.

Query Optimization is the process of improving SQL query performance so that it executes faster while consuming fewer resources such as CPU, memory, disk I/O, and network bandwidth.

The database optimizer automatically chooses the most efficient execution plan, but developers can write better queries and design appropriate indexes.

Goals

• Reduce execution time
• Reduce CPU usage
• Reduce Disk I/O
• Reduce Memory usage
• Improve scalability

Production Example

Suppose an Orders table has 100 million records.

SELECT *
FROM Orders
WHERE customer_id = 100;

Without an index:

• Full table scan
• Reads 100 million rows

With an index:

• Reads only matching rows
• Much faster execution

Interview One-Liner: Query optimization improves SQL performance by reducing the amount of work required to execute a query.

A query can become slow due to poor SQL design, missing indexes, large data volumes, or inefficient execution plans.

Common Reasons

1. Missing Indexes

SELECT *
FROM Employee
WHERE email='abc@gmail.com';

Without an index:

Full Table Scan

2. SELECT *

SELECT *
FROM Employee;

Instead use

SELECT name,
       department
FROM Employee;

3. Unnecessary Joins

Joining many large tables increases execution time.

4. Functions on Indexed Columns

Bad

SELECT *
FROM Employee
WHERE UPPER(name)='JOHN';

The database may not be able to use a normal index on name.

5. Large Result Sets

Returning millions of rows unnecessarily.

6. Poor JOIN Conditions

Missing join predicates can create huge intermediate results.

7. Too Many Subqueries

Repeated nested queries instead of joins or CTEs.

8. Sorting Large Data

ORDER BY salary

Sorting millions of rows is expensive.

Production Example

Dashboard loading slowly because every request scans the Orders table.

Interview One-Liner: Slow queries are usually caused by poor indexing, inefficient SQL, excessive data scanning, or expensive operations like sorting and joining.

An Execution Plan shows how the database intends to execute a SQL query.

It includes:

• Table Scan
• Index Scan
• Index Seek
• Join Methods
• Sort Operations
• Estimated Cost

MySQL

EXPLAIN

SELECT *
FROM Employee
WHERE department='IT';

PostgreSQL

EXPLAIN ANALYZE

SELECT *
FROM Employee;

Production Use

Whenever a query is slow,

First check the Execution Plan.

Interview One-Liner: The Execution Plan shows how the optimizer will execute a query and where most of the cost is spent.

A Full Table Scan means the database reads every row in the table.

Example

SELECT *
FROM Employee
WHERE name='John';

Without an index,

Database scans every row.

When It Happens

• No index
• Small tables (optimizer may choose a scan because it’s cheaper)
• Low-selectivity conditions
• Query returns most rows

Drawbacks

• High Disk I/O
• Slow
• High CPU

Interview One-Liner: A Full Table Scan reads every row in the table to find matching data.

An Index Scan reads the index sequentially.

It is commonly chosen when many rows match the query.

Example

WHERE department='IT'

If many employees belong to IT,

Database may scan the index.

Characteristics

• Reads index pages
• Better than full table scan in many cases
• May still read many rows

Interview One-Liner: An Index Scan reads through index entries when a large portion of the index is needed.

An Index Seek directly navigates to the required index entry.

This is generally the fastest access method for highly selective queries.

Example

SELECT *
FROM Employee
WHERE employee_id=100;

Primary Key lookup

↓

Index Seek

Advantages

• Minimal I/O
• Very Fast
• Reads only matching rows

Interview One-Liner: An Index Seek navigates directly to matching index entries instead of scanning the entire index.

The Optimizer decides the best way to execute a SQL query.

It evaluates multiple execution plans and chooses the one with the lowest estimated cost.

Optimizer Considers

• Available indexes
• Table size
• Statistics
• Join order
• Selectivity
• Estimated cost

Interview One-Liner: The optimizer selects the most efficient execution plan based on statistics and estimated cost.

Predicate Pushdown means applying filter conditions as early as possible so fewer rows are processed later.

Example

Instead of

Read 10 Million Rows

↓

Filter

↓

100 Rows

Use

Filter First

↓

Read 100 Rows

SQL Example

SELECT *
FROM Orders
WHERE customer_id=100;

The filter is pushed down to the storage engine.

Production Example

Big Data

Snowflake

Spark

Hive

Cloud Databases

Interview One-Liner: Predicate Pushdown reduces processing by filtering rows as early as possible.

A Cost Based Optimizer chooses the execution plan with the lowest estimated cost.

Cost is estimated using:

• Table statistics
• Index statistics
• Number of rows
• CPU cost
• I/O cost
• Memory usage

Example

Optimizer compares

Plan A

Full Table Scan

Cost

150

Plan B

Index Seek

Cost

10

Optimizer chooses Plan B.

Interview One-Liner: A Cost Based Optimizer evaluates multiple execution plans and selects the least expensive one.

Best Practices

Use Appropriate Indexes

Avoid SELECT *

Retrieve Only Required Columns

Filter Early

Avoid Functions on Indexed Columns

Replace Unnecessary Subqueries with JOINs or CTEs

Use EXISTS instead of IN where appropriate

Avoid Returning Large Result Sets

Analyze Execution Plan

Update Statistics Regularly

Production Example

Bad

SELECT *
FROM Orders;

Good

SELECT order_id,
       amount
FROM Orders
WHERE customer_id=100;

Interview One-Liner: Query tuning combines efficient SQL writing, proper indexing, and execution plan analysis to improve performance.

A Covering Index contains all columns needed by a query.

The database can satisfy the query using only the index.

Example

Index

(customer_id,
 order_date,
 amount)

Query

SELECT customer_id,
       order_date,
       amount
FROM Orders
WHERE customer_id=100;

No table lookup required.

Benefits

• Less Disk I/O
• Faster Queries
• Better Performance

Production Example

Reporting Dashboard

Read-heavy systems

Analytics

Interview One-Liner: A Covering Index eliminates extra table lookups because all required columns exist in the index.

Query Optimization Summary

SQL & Database Interview Questions

A Transaction is a sequence of one or more SQL operations executed as a single logical unit of work.

A transaction must either:

• Complete successfully (COMMIT), or
• Fail completely (ROLLBACK)

This guarantees data consistency.

Banking Example

Transfer ₹1,000

Account A

↓

Debit ₹1,000

↓

Credit ₹1,000

↓

Commit

If the credit fails,

Rollback

No money is lost.

SQL Example

BEGIN;

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

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

COMMIT;

Interview One-Liner: A transaction is a logical unit of work that either completes entirely or is rolled back completely.

The most frequently asked transaction question.

A – Atomicity

All or Nothing

Either every operation succeeds or none does.

Example

Money Transfer

Debit succeeds

Credit fails

↓

Rollback

C – Consistency

The database moves from one valid state to another.

Rules and constraints remain valid.

Example

Bank Balance

Before

₹10,000 + ₹20,000 = ₹30,000

After Transfer

₹9,000 + ₹21,000 = ₹30,000

I – Isolation

Concurrent transactions should not interfere with each other.

Example

Two users booking the last movie ticket simultaneously should not both succeed.

D – Durability

Once committed,

Data survives

• Power failure
• Server crash
• Database restart

Interview One-Liner: ACID ensures transactions are reliable, consistent, isolated, and durable.

COMMIT permanently saves all changes made during the transaction.

Example

BEGIN;

UPDATE Employee
SET salary = 70000
WHERE id = 1;

COMMIT;

Result

Changes become permanent.

Interview One-Liner: COMMIT permanently saves the transaction.

ROLLBACK undoes all uncommitted changes.

Example

BEGIN;

UPDATE Employee
SET salary = 70000
WHERE id = 1;

ROLLBACK;

Salary remains unchanged.

Production Example

Payment failed.

Undo inventory deduction.

Interview One-Liner: ROLLBACK cancels all changes made during the current transaction.

A SAVEPOINT creates a checkpoint inside a transaction.

Instead of rolling back the entire transaction, you can roll back only to the savepoint.

Example

BEGIN;

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

SAVEPOINT after_debit;

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

ROLLBACK TO SAVEPOINT after_debit;

COMMIT;

Production Example

Multi-step order processing where only the last step fails.

Interview One-Liner: SAVEPOINT allows partial rollback within a transaction.

Auto Commit automatically commits every SQL statement after successful execution.

Most databases enable Auto Commit by default for standalone statements, though behavior varies by client and driver.

Example

UPDATE Employee
SET salary = 50000
WHERE id = 1;

Automatically committed if auto-commit is enabled.

Disable Auto Commit (Example)

SET autocommit = 0;

(MySQL syntax)

Interview One-Liner: Auto Commit commits each successful statement automatically unless disabled.

Steps

BEGIN

↓

Execute SQL Statements

↓

Success?

↓

YES → COMMIT

↓

NO → ROLLBACK

Production Example

Online Shopping

Order

↓

Payment

↓

Inventory

↓

Invoice

↓

Commit

If any step fails,

Rollback

Interview One-Liner: A transaction begins, performs operations, and ends with either COMMIT or ROLLBACK.

Common States

Active

Transaction is executing statements.

Partially Committed

All statements have executed successfully, but the final commit has not yet completed.

Committed

Changes are permanently stored.

Failed

An error occurs during execution.

Aborted (Rolled Back)

All changes are undone.

Terminated

The transaction finishes after commit or rollback, and resources are released.

Lifecycle Diagram

Active
   │
   ▼
Partially Committed
   │
 ┌─┴─────────────┐
 ▼               ▼
Committed      Failed
                 │
                 ▼
             Aborted
                 │
                 ▼
            Terminated

Interview One-Liner: Transactions move through states such as Active, Partially Committed, Committed, Failed, Aborted, and Terminated.

Frequently Asked Production Interview Questions ⭐⭐⭐⭐⭐

Q1. Why are transactions important?

They prevent partial updates and ensure data integrity, especially in banking, e-commerce, inventory, and payment systems.

Q2. What happens if the database crashes after COMMIT?

Because of Durability, committed changes are recovered using mechanisms such as transaction logs (e.g., write-ahead logging or redo logs), depending on the database.

Q3. Can a transaction contain multiple SQL statements?

Yes. A transaction can include multiple INSERT, UPDATE, DELETE, and other statements, all treated as one logical unit.

Q4. When should you use SAVEPOINT?

When a transaction has multiple stages and you want the ability to roll back only part of the work instead of the entire transaction.

Transactions Summary

Top Transaction Interview Questions ⭐⭐⭐⭐⭐

1. Explain the ACID properties with a banking example.

2. What is the difference between COMMIT, ROLLBACK, and SAVEPOINT?

3. What happens if a server crashes immediately after a transaction is committed?

4. When should Auto Commit be disabled?

5. How would you design a money transfer system to ensure data consistency?

6. Why are transactions critical in e-commerce order processing?

7. Explain the complete transaction lifecycle and its states.

Concurrency Control is a mechanism used by a DBMS to ensure that multiple transactions execute simultaneously without violating data consistency.

Its goals are:

• Maintain data integrity
• Prevent conflicts
• Maximize concurrency
• Ensure ACID properties

Example

Two users attempt to book the last movie ticket simultaneously.

Without concurrency control:

• User A books it.
• User B also books it.

Result:

❌ Same seat sold twice.

With concurrency control:

Only one transaction succeeds.

Production Examples

• Banking
• Ticket Booking
• E-commerce Inventory
• Online Payments

Interview One-Liner: Concurrency Control ensures multiple transactions execute safely while maintaining data consistency.

A Dirty Read occurs when one transaction reads data that has been modified but not yet committed by another transaction.

If the first transaction rolls back, the second transaction has read invalid data.

Example

Transaction A

BEGIN;

UPDATE Account
SET balance = 9000
WHERE id = 1;

(Not committed)

Transaction B

SELECT balance
FROM Account
WHERE id = 1;

Reads

9000

Transaction A

ROLLBACK;

Actual balance becomes

10000

Transaction B read incorrect data.

Interview One-Liner: A Dirty Read occurs when a transaction reads uncommitted data from another transaction.

A Non-Repeatable Read occurs when the same row is read twice within a transaction, but another committed transaction modifies the row between the two reads.

Example

Transaction A

SELECT salary
FROM Employee
WHERE id = 1;

Returns

50000

Transaction B

UPDATE Employee
SET salary = 60000
WHERE id = 1;

COMMIT;

Transaction A

SELECT salary
FROM Employee
WHERE id = 1;

Returns

60000

Same query

Different result

Interview One-Liner: A Non-Repeatable Read occurs when the same row returns different values within one transaction.

A Phantom Read occurs when a transaction re-executes a query and finds additional or missing rows because another transaction inserted or deleted matching records.

Example

Transaction A

SELECT *
FROM Employee
WHERE department='IT';

Returns

10 Employees

Transaction B

INSERT INTO Employee
VALUES (...,'IT');
COMMIT;

Transaction A

SELECT *
FROM Employee
WHERE department='IT';

Returns

11 Employees

Interview One-Liner: A Phantom Read occurs when rows appear or disappear between repeated executions of the same query.

A Lost Update occurs when two transactions update the same row, and one update overwrites the other.

Example

Balance

₹10,000

Transaction A

Withdraw ₹1000

Balance

₹9000

Transaction B

Deposit ₹500

Reads old balance

₹10,000

Updates

₹10,500

Correct balance should be

₹9500

The withdrawal is lost.

Interview One-Liner: A Lost Update occurs when one transaction overwrites another transaction’s changes.

Write Skew occurs when two concurrent transactions read the same data, update different rows, and together violate a business rule.

This anomaly can occur under Snapshot Isolation.

Production Example

Hospital

Rule:

At least one doctor must remain on duty.

Transaction A

Doctor A marks themselves unavailable.

Transaction B

Doctor B marks themselves unavailable.

Each transaction sees the other doctor as still available.

After both commit:

❌ No doctor is on duty.

Interview One-Liner: Write Skew occurs when concurrent transactions update different rows and collectively violate a business constraint.

Isolation Levels control how visible one transaction’s changes are to another.

Higher isolation improves consistency but usually reduces concurrency.

Lowest isolation level.

Transactions can read uncommitted changes.

Fastest

Least safe

Problems

Dirty Read

Non-Repeatable Read

Phantom Read

Interview One-Liner: Read Uncommitted allows reading uncommitted changes from other transactions.

A transaction only sees committed data.

Dirty Reads are prevented.

Problems

Non-Repeatable Read

Phantom Read

can still occur.

Used By

Oracle (default)

SQL Server (default)

PostgreSQL (default)

Interview One-Liner: Read Committed prevents Dirty Reads but allows other concurrency anomalies.

Rows already read cannot change during the transaction.

Prevents

Dirty Read

Non-Repeatable Read

May Allow

Phantom Read depending on the database implementation.

MySQL InnoDB uses next-key locking to prevent many phantom scenarios.

Interview One-Liner: Repeatable Read guarantees that previously read rows remain unchanged during the transaction.

Highest isolation level.

Transactions behave as if executed one after another.

Safest

Slowest

Prevents

• Dirty Read
• Non-Repeatable Read
• Phantom Read

Production Example

Banking

Financial Trading

Interview One-Liner: Serializable provides complete isolation by making concurrent transactions appear sequential.

Snapshot Isolation allows a transaction to read a consistent snapshot of committed data without being blocked by concurrent writers.

Readers do not block writers, and writers generally do not block readers (subject to database implementation).

Advantages

• High concurrency
• Consistent reads
• Fewer read locks

Limitation

May still allow Write Skew.

Interview One-Liner: Snapshot Isolation provides consistent reads from a snapshot while improving concurrency.

MVCC allows multiple versions of a row to exist.

Readers access an older committed version while writers create a new version.

This avoids many read-write blocking situations.

Example

Version 1

Salary

50000

Writer updates

↓

Version 2

Salary

60000

Reader still sees

50000

until appropriate according to the isolation level.

Databases Using MVCC

• PostgreSQL
• MySQL InnoDB
• Oracle (uses a versioning mechanism for consistent reads)

Interview One-Liner: MVCC maintains multiple row versions so readers and writers can operate concurrently with minimal blocking.

Concurrency Control Summary

SQL & Database Interview Questions

A Lock is a mechanism that temporarily restricts access to data so concurrent transactions do not interfere with each other.

Production Example

Ticket Booking

Seat A1

↓

Locked

↓

Other users must wait or receive a conflict.

Interview One-Liner: A lock controls concurrent access to data to maintain consistency.

A Shared Lock allows multiple transactions to read the same data simultaneously.

Updates are not allowed while a Shared Lock is held.

Example

Transaction A

SELECT *
FROM Employee;

Transaction B

SELECT *
FROM Employee;

Both succeed.

Transaction C

UPDATE Employee
SET salary=50000;

Must wait (depending on the database and isolation level).

Interview One-Liner: Shared Locks allow concurrent reads but prevent conflicting writes.

An Exclusive Lock is acquired when data is modified.

No other transaction can read or modify the locked data in many locking implementations, although MVCC databases may still allow readers to see older committed versions.

Example

UPDATE Employee
SET salary=70000
WHERE id=1;

Exclusive Lock acquired.

Interview One-Liner: Exclusive Locks protect data during updates by preventing conflicting access.

Only the affected row is locked.

Concurrency remains high.

Example

Updating

Employee

ID=5

Only that row is locked.

Advantages

• Better concurrency
• Less blocking

Interview One-Liner: Row-level locking locks only the affected row, allowing other rows to remain accessible.

The entire table is locked.

Other transactions must wait.

Production Example

Bulk updates

Maintenance operations

Interview One-Liner: Table Locks lock the whole table and reduce concurrency.

Optimistic Locking assumes conflicts are rare.

No lock is held while reading.

Before updating, the application verifies that the row has not changed, often using a version number or timestamp.

Example

Version = 5

Update

WHERE version = 5

If another transaction already updated the row,

Version becomes

6

Update fails.

Used In

Hibernate/JPA

@Version

Interview One-Liner: Optimistic Locking detects conflicts during update rather than preventing them with long-held locks.

Pessimistic Locking assumes conflicts are likely.

Data is locked before modification.

Example

SELECT *
FROM Account
WHERE id=1
FOR UPDATE;

Other updates must wait.

Production Example

Banking

Inventory

Payments

Interview One-Liner: Pessimistic Locking acquires locks early to prevent concurrent modifications.

A Deadlock occurs when two or more transactions wait indefinitely for each other to release locks.

Example

Transaction A

Locks

Table A

Needs

Table B

Transaction B

Locks

Table B

Needs

Table A

Neither can continue.

Interview One-Liner: A Deadlock occurs when transactions wait on each other’s locks indefinitely.

Most databases automatically detect deadlocks using a wait-for graph or similar algorithm.

When a deadlock is detected, the database selects one transaction as the victim, rolls it back, and allows the others to proceed.

Interview One-Liner: Deadlock detection identifies circular waits and resolves them by aborting one transaction.

Best Practices

• Access tables in the same order.
• Keep transactions short.
• Commit quickly.
• Avoid unnecessary locks.
• Index frequently accessed rows to reduce lock duration.
• Retry transactions that are rolled back due to deadlocks.

Interview One-Liner: Deadlocks are prevented by consistent lock ordering, short transactions, and minimizing lock contention.

Lock Escalation occurs when a database replaces many fine-grained locks (such as row locks) with a coarser-grained lock (such as a table lock) to reduce lock-management overhead.

Support and behavior vary across databases.

Example

10000 Row Locks

↓

1 Table Lock

Advantages

• Lower lock-management overhead

Disadvantages

• Reduced concurrency

Interview One-Liner: Lock Escalation combines many small locks into a larger lock to reduce overhead.

Shared vs Exclusive Lock

Optimistic vs Pessimistic Locking