SQL Interview Questions
Master your SQL interviews with 60+ questions covering basics, joins, indexes, normalization, window functions, CTEs, query problems, optimization, transactions, and security for all experience levels.
I. Beginner Level
1. What is SQL and why is it used?
SQL stands for Structured Query Language. It is the standard language used to communicate with relational databases - creating structures, inserting data, retrieving records, updating values, and controlling access permissions.
Developed by IBM in the 1970s and standardised by ANSI in 1986, SQL's core syntax works across MySQL, PostgreSQL, Oracle, and SQL Server. Every backend developer and data analyst is expected to know it because data almost always lives in a relational database somewhere.
2. What is the difference between SQL and NoSQL?
SQL databases store data in structured tables with a fixed schema. NoSQL databases use flexible models - documents, key-value pairs, graphs - and do not require a predefined schema.
Schema: SQL enforces a defined schema before inserting data. NoSQL is schema-flexible.
Scaling: SQL scales vertically (bigger server). NoSQL scales horizontally (more servers).
Consistency: SQL guarantees ACID. Many NoSQL systems follow BASE (eventual consistency).
Use cases: SQL for structured relational data (banking, ERP). NoSQL for unstructured data at scale (social feeds, IoT).
Examples: SQL - MySQL, PostgreSQL, Oracle. NoSQL - MongoDB, Cassandra, Redis.
3. What is an RDBMS? Name some popular ones.
RDBMS (Relational Database Management System) is software that stores data in tables, enforces relationships between them using keys, and lets you query that data using SQL. It is based on E.F. Codd's relational model from 1970.
Popular examples: MySQL (open-source, most widely used in web apps), PostgreSQL (advanced open-source, supports JSON and complex queries), Oracle Database (enterprise, banking and finance), Microsoft SQL Server (Windows ecosystem), and SQLite (lightweight, embedded in mobile apps).
4. What are the common data types in SQL?
Data types define what kind of value a column can hold. Choosing the right type affects storage size, query performance, and data integrity.
1CREATE TABLE products (
2 id INT PRIMARY KEY AUTO_INCREMENT,
3 name VARCHAR(200) NOT NULL, -- variable-length string up to 200 chars
4 description TEXT, -- large text, no length limit
5 price DECIMAL(10, 2) NOT NULL, -- exact number: 10 digits, 2 decimal places
6 stock INT DEFAULT 0,
7 is_active BOOLEAN DEFAULT TRUE,
8 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
9);
105. What are the different types of SQL commands (DDL, DML, DCL, TCL, DQL)?
SQL commands are grouped by their purpose. This classification is a very common fresher interview question.
1-- DDL (Data Definition Language) - defines structure
2CREATE TABLE employees (id INT, name VARCHAR(100));
3ALTER TABLE employees ADD COLUMN salary DECIMAL(10,2);
4DROP TABLE employees;
5TRUNCATE TABLE employees;
6
7-- DML (Data Manipulation Language) - manipulates data
8INSERT INTO employees (id, name) VALUES (1, 'Alice');
9UPDATE employees SET salary = 75000 WHERE id = 1;
10DELETE FROM employees WHERE id = 1;
11
12-- DQL (Data Query Language) - retrieves data
13SELECT * FROM employees WHERE salary > 50000;
14
15-- DCL (Data Control Language) - controls access
16GRANT SELECT ON employees TO 'hr_user';
17REVOKE SELECT ON employees FROM 'hr_user';
18
19-- TCL (Transaction Control Language) - manages transactions
20BEGIN;
21UPDATE accounts SET balance = balance - 500 WHERE id = 1;
22COMMIT; -- save changes
23ROLLBACK; -- undo changes
246. What is a Primary Key?
A Primary Key is a column (or combination of columns) that uniquely identifies every row in a table. It enforces two rules: the value must be unique and it cannot be NULL. The database automatically creates a clustered index on it, making lookups by PK very fast.
1-- Single-column primary key
2CREATE TABLE users (
3 id INT PRIMARY KEY AUTO_INCREMENT,
4 email VARCHAR(255) NOT NULL
5);
6
7-- Composite primary key (combination of two columns must be unique)
8CREATE TABLE order_items (
9 order_id INT NOT NULL,
10 product_id INT NOT NULL,
11 quantity INT NOT NULL,
12 PRIMARY KEY (order_id, product_id)
13);
147. What is a Foreign Key and how does it work?
A Foreign Key is a column in one table that references the Primary Key of another table. It enforces referential integrity - you cannot insert a value that does not exist in the referenced table, and you cannot delete a parent row while child rows still reference it (unless CASCADE is set).
1CREATE TABLE orders (
2 id INT PRIMARY KEY AUTO_INCREMENT,
3 customer_id INT NOT NULL,
4 total DECIMAL(10, 2),
5 FOREIGN KEY (customer_id)
6 REFERENCES customers(id)
7 ON DELETE CASCADE -- deleting a customer also deletes their orders
8 ON UPDATE CASCADE
9);
10
11-- ✓ Works - customer_id 1 exists
12INSERT INTO orders (customer_id, total) VALUES (1, 1500.00);
13
14-- ✗ Fails - customer_id 999 doesn't exist
15INSERT INTO orders (customer_id, total) VALUES (999, 500.00);
168. What is the difference between a Primary Key and a Unique Key?
Both enforce uniqueness but differ in two ways: a table can only have one Primary Key but multiple Unique Keys, and the Primary Key cannot contain NULLs while a Unique Key column can (multiple NULLs are allowed because NULL ≠ NULL).
1CREATE TABLE users (
2 id INT PRIMARY KEY AUTO_INCREMENT, -- one PK, no NULLs
3 email VARCHAR(255) UNIQUE NOT NULL, -- unique, not null
4 phone VARCHAR(20) UNIQUE, -- unique, NULLs allowed
5 username VARCHAR(100) UNIQUE NOT NULL
6);
79. What are SQL constraints? List the common ones.
Constraints are rules enforced at the database level to ensure data accuracy and integrity. They catch invalid data even when the application layer makes a mistake.
1CREATE TABLE employees (
2 id INT PRIMARY KEY AUTO_INCREMENT, -- PRIMARY KEY
3 name VARCHAR(200) NOT NULL, -- NOT NULL
4 email VARCHAR(255) UNIQUE NOT NULL, -- UNIQUE
5 department_id INT,
6 age INT CHECK (age >= 18), -- CHECK
7 status VARCHAR(20) DEFAULT 'active', -- DEFAULT
8 FOREIGN KEY (department_id) REFERENCES departments(id) -- FOREIGN KEY
9);
1010. How does the SELECT statement work in SQL?
SELECT retrieves data from one or more tables. A key interview point is knowing the logical execution order, which is different from the writing order: FROM → JOIN → WHERE → GROUP BY → HAVING → SELECT → DISTINCT → ORDER BY → LIMIT.
1SELECT
2 d.name AS department,
3 COUNT(e.id) AS headcount,
4 AVG(e.salary) AS avg_salary
5FROM employees e
6JOIN departments d ON e.department_id = d.id
7WHERE e.status = 'active'
8GROUP BY d.name
9HAVING COUNT(e.id) > 5
10ORDER BY avg_salary DESC
11LIMIT 10;
1211. What is the difference between WHERE and HAVING?
WHERE filters individual rows before grouping. HAVING filters groups after GROUP BY has been applied. You cannot use an aggregate function like SUM() or COUNT() in a WHERE clause - that is what HAVING is for.
1-- WHERE filters rows BEFORE grouping
2SELECT department, COUNT(*) AS cnt
3FROM employees
4WHERE salary > 30000 -- individual row filter
5GROUP BY department;
6
7-- HAVING filters groups AFTER grouping
8SELECT department, AVG(salary) AS avg_sal
9FROM employees
10GROUP BY department
11HAVING AVG(salary) > 50000; -- group filter
12
13-- ✗ WRONG - aggregate not allowed in WHERE
14SELECT department FROM employees
15WHERE AVG(salary) > 50000 GROUP BY department;
1612. What is the difference between ORDER BY and GROUP BY?
GROUP BY collapses multiple rows into one summary row per group - it changes the row count. ORDER BY simply sorts the existing rows - it does not change the row count at all. They serve completely different purposes and are often used together.
1-- GROUP BY collapses rows (many rows → one per department)
2SELECT department, SUM(salary) AS total
3FROM employees
4GROUP BY department;
5
6-- ORDER BY sorts rows (row count stays the same)
7SELECT name, salary FROM employees ORDER BY salary DESC;
8
9-- Used together: group first, then sort the groups
10SELECT department, AVG(salary) AS avg_sal
11FROM employees
12GROUP BY department
13ORDER BY avg_sal DESC;
1413. What are JOINs in SQL? List the types.
A JOIN combines rows from two or more tables based on a related column. JOINs are the cornerstone of relational databases - they let you reassemble normalized data at query time.
1-- INNER JOIN - only rows with a match in both tables
2SELECT e.name, d.name AS dept
3FROM employees e INNER JOIN departments d ON e.department_id = d.id;
4
5-- LEFT JOIN - all rows from left + matched rows from right (NULL if no match)
6SELECT e.name, d.name AS dept
7FROM employees e LEFT JOIN departments d ON e.department_id = d.id;
8
9-- RIGHT JOIN - all rows from right + matched rows from left
10SELECT e.name, d.name AS dept
11FROM employees e RIGHT JOIN departments d ON e.department_id = d.id;
12
13-- FULL OUTER JOIN - all rows from both tables
14SELECT e.name, d.name AS dept
15FROM employees e FULL OUTER JOIN departments d ON e.department_id = d.id;
16
17-- CROSS JOIN - every combination of rows (Cartesian product)
18SELECT s.size, c.color FROM sizes s CROSS JOIN colors c;
1914. What is the difference between INNER JOIN and OUTER JOIN?
INNER JOIN returns only rows where there is a match in both tables - unmatched rows are excluded. OUTER JOIN (LEFT, RIGHT, or FULL) also includes rows that have no match on one or both sides, using NULL for the missing columns.
1-- customers: Alice(1), Bob(2), Charlie(3)
2-- orders: order for 1, order for 1, order for 2 (Charlie has no orders)
3
4-- INNER JOIN - Charlie excluded
5SELECT c.name, o.id AS order_id
6FROM customers c INNER JOIN orders o ON c.id = o.customer_id;
7-- Result: Alice|1, Alice|2, Bob|3
8
9-- LEFT JOIN - Charlie included with NULL order
10SELECT c.name, o.id AS order_id
11FROM customers c LEFT JOIN orders o ON c.id = o.customer_id;
12-- Result: Alice|1, Alice|2, Bob|3, Charlie|NULL
13
14-- Find customers with NO orders (classic pattern)
15SELECT c.name FROM customers c
16LEFT JOIN orders o ON c.id = o.customer_id
17WHERE o.id IS NULL;
1815. What is NULL in SQL and how is it handled?
NULL represents the absence of a value - not zero, not an empty string. You cannot compare NULL using = or != because NULL is not equal to anything, not even itself. Use IS NULL / IS NOT NULL instead.
1-- ✗ WRONG - returns no rows
2SELECT * FROM employees WHERE phone = NULL;
3
4-- ✓ CORRECT
5SELECT * FROM employees WHERE phone IS NULL;
6
7-- COALESCE - returns first non-NULL value
8SELECT name, COALESCE(phone, email, 'No contact') AS contact
9FROM employees;
10
11-- NULLIF - returns NULL if both arguments are equal
12-- Prevents division-by-zero errors
13SELECT revenue / NULLIF(orders, 0) AS avg_order FROM summary;
14
15-- COUNT(*) counts all rows; COUNT(column) ignores NULLs
16SELECT COUNT(*) AS total, COUNT(phone) AS has_phone FROM employees;
1716. What does the DISTINCT keyword do?
DISTINCT removes duplicate rows from the result set. It applies to the combination of all selected columns. NULLs are treated as identical, so only one NULL appears in a DISTINCT result.
1-- Unique departments
2SELECT DISTINCT department FROM employees;
3
4-- Unique (city, country) pairs
5SELECT DISTINCT city, country FROM customers;
6
7-- Count unique values
8SELECT COUNT(DISTINCT department) AS unique_depts FROM employees;
917. What is the LIKE operator and how are wildcards used?
LIKE searches for a pattern in a string. It uses two wildcards: % matches any sequence of zero or more characters, and _ matches exactly one character.
1SELECT * FROM customers WHERE name LIKE 'A%'; -- starts with A
2SELECT * FROM customers WHERE name LIKE '%son'; -- ends with 'son'
3SELECT * FROM customers WHERE email LIKE '%@gmail.com'; -- gmail users
4SELECT * FROM products WHERE code LIKE 'A_1'; -- A, any one char, 1
5SELECT * FROM emails WHERE address NOT LIKE '%@spam.com';
618. What are aggregate functions in SQL?
Aggregate functions perform calculations on a set of rows and return a single value. They ignore NULL values (except COUNT(*)). They are almost always used with GROUP BY.
1SELECT
2 COUNT(*) AS total_employees,
3 COUNT(DISTINCT department) AS unique_depts,
4 SUM(salary) AS total_payroll,
5 AVG(salary) AS avg_salary,
6 MIN(salary) AS lowest,
7 MAX(salary) AS highest
8FROM employees WHERE status = 'active';
9
10-- With GROUP BY
11SELECT department, COUNT(*) AS headcount, AVG(salary) AS avg_sal
12FROM employees
13GROUP BY department
14ORDER BY avg_sal DESC;
1519. What is an alias in SQL and how is it used?
An alias is a temporary name for a column or table using the AS keyword. Column aliases make output readable. Table aliases (especially in JOINs) make queries shorter. Aliases exist only for the duration of the query.
1-- Column alias
2SELECT salary * 12 AS annual_salary, CONCAT(first_name,' ',last_name) AS full_name
3FROM employees;
4
5-- Table alias (required when joining same table or for readability)
6SELECT e.name, d.name AS department
7FROM employees e JOIN departments d ON e.department_id = d.id;
8
9-- Subquery alias (mandatory - subqueries must have an alias)
10SELECT * FROM (
11 SELECT department, AVG(salary) AS avg_sal FROM employees GROUP BY department
12) AS dept_avg
13WHERE avg_sal > 60000;
1420. What is the difference between BETWEEN and IN operators?
BETWEEN filters a continuous range (inclusive of both endpoints) - works for numbers, dates, and strings. IN matches against a discrete list of values.
1-- BETWEEN - inclusive range
2SELECT * FROM employees WHERE salary BETWEEN 40000 AND 80000;
3SELECT * FROM orders WHERE order_date BETWEEN '2025-01-01' AND '2025-12-31';
4
5-- IN - matches a list of values
6SELECT * FROM employees WHERE department IN ('Engineering', 'Design', 'Product');
7SELECT * FROM orders WHERE status NOT IN ('cancelled', 'refunded');
8
9-- IN with a subquery
10SELECT * FROM employees
11WHERE department_id IN (SELECT id FROM departments WHERE location = 'Mumbai');
12II. Intermediate Level
1. What are indexes in SQL and why are they used?
An index is a separate data structure (usually a B-Tree) that lets the database jump directly to matching rows instead of scanning the entire table. Without an index, every query does a full table scan. The trade-off: indexes speed up SELECT but slow down INSERT, UPDATE, and DELETE because the index also needs updating.
1-- Single-column index
2CREATE INDEX idx_email ON users(email);
3
4-- Composite index (column order matters - filters left-to-right)
5CREATE INDEX idx_status_date ON orders(status, created_at);
6
7-- Unique index
8CREATE UNIQUE INDEX idx_unique_email ON users(email);
9
10-- ✓ Index used - filtering on indexed column
11SELECT * FROM users WHERE email = 'alice@example.com';
12
13-- ✗ Index NOT used - function on indexed column defeats it
14SELECT * FROM users WHERE UPPER(email) = 'ALICE@EXAMPLE.COM';
15
16-- View indexes on a table (MySQL)
17SHOW INDEX FROM orders;
182. What is the difference between clustered and non-clustered indexes?
A clustered index determines the physical storage order of rows in the table - only one per table (the Primary Key in MySQL/InnoDB). A non-clustered index is a separate structure holding the key values and a pointer to the actual row - a table can have many of them.
1-- Primary key = clustered index (data physically sorted by id)
2CREATE TABLE orders (
3 id INT PRIMARY KEY, -- clustered
4 customer_id INT,
5 status VARCHAR(20)
6);
7
8-- Non-clustered indexes (separate structures)
9CREATE INDEX idx_customer ON orders(customer_id);
10CREATE INDEX idx_status ON orders(status);
11
12-- Covering index: includes all columns the query needs
13-- so the engine never reads the actual table row
14CREATE INDEX idx_covering ON orders(status, created_at, customer_id);
15SELECT customer_id, created_at FROM orders WHERE status = 'pending';
16-- ↑ Fully answered from index alone - no table lookup
173. What are views in SQL and what are their advantages?
A view is a saved SELECT query stored under a name. It is a virtual table - no data is duplicated. Every time you query the view, the underlying SELECT runs against the live tables. Views simplify complex queries, enforce security (hiding sensitive columns), and present a stable interface even if the underlying tables change.
1CREATE VIEW active_employees AS
2SELECT e.id, e.name, e.salary, d.name AS department
3FROM employees e
4JOIN departments d ON e.department_id = d.id
5WHERE e.status = 'active';
6
7-- Query the view just like a table
8SELECT name, salary FROM active_employees WHERE department = 'Engineering';
9
10-- Security view - hides salary from a reporting role
11CREATE VIEW employee_directory AS
12SELECT id, name, email, department_id FROM employees;
13GRANT SELECT ON employee_directory TO 'readonly_user';
14
15-- Drop a view
16DROP VIEW IF EXISTS active_employees;
174. What are stored procedures and how are they different from functions?
A stored procedure is a precompiled block of SQL stored in the database, called with CALL/EXEC. It can perform INSERT/UPDATE/DELETE and return multiple result sets, but doesn't have to return a value. A function must return a single value (or table) and can be used inside a SELECT statement - procedures cannot.
1-- Stored Procedure
2DELIMITER //
3CREATE PROCEDURE get_dept_employees(IN dept_name VARCHAR(100))
4BEGIN
5 SELECT e.name, e.salary
6 FROM employees e JOIN departments d ON e.department_id = d.id
7 WHERE d.name = dept_name
8 ORDER BY e.salary DESC;
9END //
10DELIMITER ;
11
12CALL get_dept_employees('Engineering');
13
14-- User-Defined Function (must return a value, usable in SELECT)
15DELIMITER //
16CREATE FUNCTION annual_salary(monthly DECIMAL(10,2))
17RETURNS DECIMAL(12,2) DETERMINISTIC
18BEGIN
19 RETURN monthly * 12;
20END //
21DELIMITER ;
22
23SELECT name, annual_salary(salary) AS yearly FROM employees;
245. What are triggers in SQL?
A trigger is a stored procedure that automatically executes in response to an INSERT, UPDATE, or DELETE on a table. Triggers cannot be called manually. They are used for audit logging, enforcing business rules, and maintaining derived data.
1-- Audit trigger: log every salary change
2DELIMITER //
3CREATE TRIGGER trg_salary_audit
4AFTER UPDATE ON employees
5FOR EACH ROW
6BEGIN
7 IF OLD.salary <> NEW.salary THEN
8 INSERT INTO salary_audit (employee_id, old_salary, new_salary, changed_at)
9 VALUES (NEW.id, OLD.salary, NEW.salary, NOW());
10 END IF;
11END //
12DELIMITER ;
13
14-- BEFORE INSERT trigger: normalise email before saving
15DELIMITER //
16CREATE TRIGGER trg_normalise_email
17BEFORE INSERT ON users
18FOR EACH ROW
19BEGIN
20 SET NEW.email = LOWER(TRIM(NEW.email));
21END //
22DELIMITER ;
236. What is normalization? Explain 1NF, 2NF, and 3NF.
Normalization is the process of structuring a database to reduce data redundancy and improve integrity. Each normal form builds on the previous one.
1-- ✗ UNNORMALISED - products stored as a comma list in one cell
2-- order_id | customer | products
3-- 1 | Alice | 'Laptop, Mouse'
4
5-- ✓ 1NF: atomic values + unique rows (one product per row)
6-- order_id | customer | product
7-- 1 | Alice | Laptop
8-- 1 | Alice | Mouse
9
10-- ✓ 2NF: no partial dependency on a composite key
11-- (customer depends only on order_id, not on the full PK order_id+product)
12-- Move customer to an orders table; keep order_items separate
13CREATE TABLE orders (id INT PRIMARY KEY, customer_id INT);
14CREATE TABLE order_items (order_id INT, product_id INT, qty INT,
15 PRIMARY KEY (order_id, product_id));
16
17-- ✓ 3NF: no transitive dependency (non-key column depending on another non-key column)
18-- ✗ employees: emp_id | zip_code | city (city depends on zip, not on emp_id)
19-- ✓ Extract zip→city to its own table
20CREATE TABLE zip_codes (zip VARCHAR(10) PRIMARY KEY, city VARCHAR(100));
217. What is denormalization and when would you use it?
Denormalization is the deliberate introduction of redundancy to improve read performance. You combine tables or duplicate data to avoid expensive JOINs on frequently executed queries. It is common in data warehouses and reporting databases where reads vastly outnumber writes.
1-- Normalised: 4-table JOIN just to show an order summary
2SELECT o.id, c.name, p.name, cat.name
3FROM orders o
4JOIN customers c ON o.customer_id = c.id
5JOIN order_items oi ON o.id = oi.order_id
6JOIN products p ON oi.product_id = p.id
7JOIN categories cat ON p.category_id = cat.id;
8
9-- Denormalised summary table: all data in one place for fast reporting
10CREATE TABLE order_summary (
11 order_id INT,
12 customer_name VARCHAR(200), -- duplicated from customers
13 product_name VARCHAR(200), -- duplicated from products
14 category_name VARCHAR(100), -- duplicated from categories
15 quantity INT,
16 revenue DECIMAL(10,2),
17 order_date DATE
18);
19-- Reporting query now: simple scan, zero JOINs
20SELECT customer_name, SUM(revenue) FROM order_summary GROUP BY customer_name;
218. What are ACID properties in SQL?
ACID properties guarantee that database transactions are processed reliably. They are what makes SQL databases trustworthy for financial and mission-critical data.
Atomicity: All operations in a transaction succeed together or all are rolled back. A bank transfer cannot debit one account without crediting the other.
Consistency: A transaction takes the database from one valid state to another. Constraints (like CHECK balance >= 0) ensure no invalid data sneaks in.
Isolation: Concurrent transactions behave as if they ran one at a time. Controlled by isolation levels (READ COMMITTED, SERIALIZABLE, etc.).
Durability: Once COMMIT is confirmed, the change survives crashes. The database writes to a WAL (Write-Ahead Log) before acknowledging the commit.
9. What are transactions in SQL and how do you use them?
A transaction groups multiple SQL operations into a single unit. Either all succeed (COMMIT) or all are undone (ROLLBACK). Essential whenever multiple related writes must succeed or fail together.
1BEGIN;
2 INSERT INTO orders (customer_id, total) VALUES (1, 2500.00);
3 SET @oid = LAST_INSERT_ID();
4 INSERT INTO order_items (order_id, product_id, qty) VALUES (@oid, 5, 2);
5 UPDATE inventory SET stock = stock - 2 WHERE product_id = 5;
6COMMIT; -- all three changes land together
7
8-- With error handling
9BEGIN;
10 UPDATE accounts SET balance = balance - 10000 WHERE id = 1;
11 UPDATE accounts SET balance = balance + 10000 WHERE id = 2;
12 -- If anything goes wrong before COMMIT:
13ROLLBACK; -- both updates are undone, money doesn't disappear
14
15-- SAVEPOINT - partial rollback within a transaction
16BEGIN;
17 INSERT INTO logs (msg) VALUES ('Step 1');
18 SAVEPOINT sp1;
19 INSERT INTO logs (msg) VALUES ('Step 2');
20 ROLLBACK TO SAVEPOINT sp1; -- undo step 2, keep step 1
21COMMIT;
2210. What is the difference between a subquery and a JOIN?
Both can achieve the same result but differ in readability and performance. JOINs are typically more efficient and preferred when you need columns from both tables. Subqueries are cleaner for filtering against aggregated values or when you only need data from the outer table.
1-- Subquery approach
2SELECT name FROM employees
3WHERE department_id = (SELECT id FROM departments WHERE name = 'Engineering');
4
5-- JOIN approach (usually faster, lets you select from both tables)
6SELECT e.name FROM employees e
7JOIN departments d ON e.department_id = d.id
8WHERE d.name = 'Engineering';
9
10-- Subquery in SELECT (scalar subquery - runs once per row)
11SELECT name, salary,
12 (SELECT AVG(salary) FROM employees) AS company_avg
13FROM employees;
14
15-- Subquery as derived table
16SELECT dept, avg_sal FROM (
17 SELECT department AS dept, AVG(salary) AS avg_sal
18 FROM employees GROUP BY department
19) AS dept_stats
20WHERE avg_sal > 70000;
2111. What is a correlated subquery?
A correlated subquery references columns from the outer query. Unlike a regular subquery that runs once, it runs once for every row processed by the outer query. Powerful but can be slow on large tables - window functions are often a faster alternative.
1-- Find employees who earn more than the average salary IN THEIR department
2-- The inner query references e.department_id from the outer query
3SELECT e.name, e.salary, e.department_id
4FROM employees e
5WHERE e.salary > (
6 SELECT AVG(salary) FROM employees
7 WHERE department_id = e.department_id -- correlated reference
8);
9
10-- Find each customer's most recent order
11SELECT c.name, o.id, o.order_date
12FROM customers c
13JOIN orders o ON c.id = o.customer_id
14WHERE o.order_date = (
15 SELECT MAX(order_date) FROM orders WHERE customer_id = c.id
16);
1712. What is the difference between UNION and UNION ALL?
UNION combines result sets and removes duplicate rows (slower). UNION ALL keeps all rows including duplicates (faster). Use UNION ALL unless you specifically need deduplication. Both require the same number of columns with compatible data types.
1-- UNION - deduplicates (same email in both tables appears once)
2SELECT email FROM customers
3UNION
4SELECT email FROM newsletter_subscribers;
5
6-- UNION ALL - keeps all rows including duplicates (faster)
7SELECT email FROM customers
8UNION ALL
9SELECT email FROM newsletter_subscribers;
10
11-- Practical: combine sales data from multiple years
12SELECT '2024' AS yr, SUM(amount) AS total FROM sales WHERE YEAR(date) = 2024
13UNION ALL
14SELECT '2025', SUM(amount) FROM sales WHERE YEAR(date) = 2025
15ORDER BY yr;
1613. What is the difference between EXISTS and IN?
IN evaluates all values from the subquery first, then checks membership. EXISTS is a correlated subquery that short-circuits on the first match - generally faster for large result sets. A critical difference: NOT IN breaks silently when the subquery returns any NULL, while NOT EXISTS handles NULLs correctly.
1-- IN
2SELECT name FROM customers
3WHERE id IN (SELECT DISTINCT customer_id FROM orders WHERE total > 1000);
4
5-- EXISTS - stops at first match, faster for large subquery results
6SELECT name FROM customers c
7WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id AND o.total > 1000);
8
9-- ✗ NOT IN breaks when subquery contains NULLs - returns zero rows!
10SELECT name FROM customers WHERE id NOT IN (1, 2, NULL);
11
12-- ✓ NOT EXISTS handles NULLs correctly
13SELECT name FROM customers c
14WHERE NOT EXISTS (SELECT 1 FROM blacklist b WHERE b.customer_id = c.id);
1514. What is the CASE statement in SQL?
CASE is SQL's conditional logic - like if/else or switch. It can appear in SELECT, WHERE, ORDER BY, and HAVING. There are two forms: simple CASE (compares one expression) and searched CASE (evaluates boolean conditions).
1-- Searched CASE in SELECT
2SELECT name, salary,
3 CASE
4 WHEN salary < 30000 THEN 'Entry'
5 WHEN salary < 60000 THEN 'Mid'
6 WHEN salary < 100000 THEN 'Senior'
7 ELSE 'Executive'
8 END AS band
9FROM employees;
10
11-- Conditional aggregation - pivot-style counts
12SELECT department,
13 SUM(CASE WHEN status = 'active' THEN 1 ELSE 0 END) AS active,
14 SUM(CASE WHEN status = 'inactive' THEN 1 ELSE 0 END) AS inactive
15FROM employees GROUP BY department;
16
17-- Custom sort order with CASE in ORDER BY
18SELECT name, priority FROM tasks
19ORDER BY CASE priority
20 WHEN 'critical' THEN 1 WHEN 'high' THEN 2
21 WHEN 'medium' THEN 3 ELSE 4
22END;
2315. What is the difference between DELETE, TRUNCATE, and DROP?
Three commands, three very different outcomes. This is one of the most commonly asked SQL interview questions.
1-- DELETE (DML) - removes specific rows
2DELETE FROM employees WHERE status = 'inactive';
3-- ✓ WHERE clause supported | ✓ Can be rolled back | ✓ Fires triggers
4-- ✓ Structure preserved | ✗ Slow on large tables | ✗ Does not reset AUTO_INCREMENT
5
6-- TRUNCATE (DDL) - removes ALL rows instantly
7TRUNCATE TABLE employees;
8-- ✓ Very fast | ✓ Resets AUTO_INCREMENT counter
9-- ✗ No WHERE clause | ✗ Cannot be rolled back in most DBs
10-- ✗ Does not fire triggers | ✗ Cannot truncate tables with FK references
11
12-- DROP (DDL) - removes the entire table
13DROP TABLE employees;
14-- ✓ Removes table + data + indexes + triggers
15-- ✗ Cannot be rolled back | ✗ Table is permanently gone
1616. What is a SELF JOIN and when do you use it?
A SELF JOIN joins a table to itself using two aliases. It is used for hierarchical data - employees and their managers, categories and subcategories, regions and parent regions.
1-- employees: id, name, manager_id (references employees.id)
2-- Each employee and their manager's name
3SELECT e.name AS employee, m.name AS manager
4FROM employees e
5LEFT JOIN employees m ON e.manager_id = m.id;
6-- LEFT JOIN ensures the CEO (no manager) also appears
7
8-- Employees earning more than their manager
9SELECT e.name, e.salary, m.name AS manager, m.salary AS mgr_salary
10FROM employees e
11JOIN employees m ON e.manager_id = m.id
12WHERE e.salary > m.salary;
1317. What is a CROSS JOIN?
A CROSS JOIN produces the Cartesian product - every row from the first table paired with every row from the second. No ON condition. 5 rows × 4 rows = 20 rows. Useful for generating combinations but dangerous when used accidentally.
1-- Generate all size-colour combinations for a product
2SELECT s.size_name, c.color_name
3FROM sizes s CROSS JOIN colors c;
4-- 4 sizes × 6 colours = 24 rows
5
6-- ⚠️ Accidental CROSS JOIN - forgot the ON clause
7SELECT * FROM orders, customers; -- produces orders × customers rows!
8-- Always use explicit JOIN ... ON syntax to avoid this bug
9III. Advanced Level
1. What are window functions in SQL?
Window functions perform calculations across a set of rows related to the current row without collapsing them like GROUP BY does. Every row keeps its identity and gets an additional computed column. They use the OVER() clause. PARTITION BY divides rows into groups; ORDER BY inside OVER defines sequence for rankings or running totals.
1-- All employee rows - each gets its department's statistics alongside
2SELECT
3 name,
4 department,
5 salary,
6 AVG(salary) OVER (PARTITION BY department) AS dept_avg,
7 SUM(salary) OVER (PARTITION BY department) AS dept_total,
8 COUNT(*) OVER (PARTITION BY department) AS dept_headcount
9FROM employees;
10-- Unlike GROUP BY, every individual row is preserved
112. What is the difference between RANK(), DENSE_RANK(), and ROW_NUMBER()?
All three assign a number to each row based on an ORDER BY, but they handle ties differently. Use ROW_NUMBER when you want exactly one row per rank, RANK for standard competition ranking (skips numbers after a tie), and DENSE_RANK when you need no gaps in the sequence.
1-- Salaries: 9000, 8000, 8000, 7000
2SELECT name, salary,
3 ROW_NUMBER() OVER (ORDER BY salary DESC) AS row_num,
4 -- Result: 1, 2, 3, 4 (unique, ties broken arbitrarily)
5 RANK() OVER (ORDER BY salary DESC) AS rank_val,
6 -- Result: 1, 2, 2, 4 (tie gets same rank, next rank skips to 4)
7 DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rank_val
8 -- Result: 1, 2, 2, 3 (tie gets same rank, no gap)
9FROM employees ORDER BY salary DESC;
10
11-- Get top earner per department (keep ties with DENSE_RANK)
12SELECT name, department, salary FROM (
13 SELECT name, department, salary,
14 DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dr
15 FROM employees
16) r WHERE dr = 1;
173. What is a Common Table Expression (CTE) and how is it used?
A CTE is a named temporary result set defined using the WITH clause. It exists only for the duration of the query. CTEs make complex queries more readable by breaking them into named logical steps. They can also reference themselves (recursive CTEs).
1-- Single CTE - cleaner than a nested subquery
2WITH dept_avg AS (
3 SELECT department_id, AVG(salary) AS avg_sal
4 FROM employees GROUP BY department_id
5)
6SELECT e.name, e.salary, da.avg_sal
7FROM employees e
8JOIN dept_avg da ON e.department_id = da.department_id
9WHERE e.salary > da.avg_sal;
10
11-- Multiple CTEs - each can reference the previous
12WITH
13active AS (
14 SELECT * FROM employees WHERE status = 'active'
15),
16stats AS (
17 SELECT department_id, COUNT(*) AS cnt, AVG(salary) AS avg_sal
18 FROM active GROUP BY department_id
19)
20SELECT d.name, s.cnt, ROUND(s.avg_sal, 2) AS avg_salary
21FROM stats s JOIN departments d ON s.department_id = d.id
22WHERE s.cnt > 10;
234. What is a recursive CTE?
A recursive CTE references itself to traverse hierarchical or graph-like data - org charts, category trees, bill of materials. It has two parts joined by UNION ALL: an anchor member (the starting point) and a recursive member (which references the CTE itself).
1-- employees: id, name, manager_id
2-- Build the complete org chart tree
3WITH RECURSIVE org AS (
4 -- Anchor: CEO has no manager
5 SELECT id, name, manager_id, 0 AS level
6 FROM employees WHERE manager_id IS NULL
7
8 UNION ALL
9
10 -- Recursive: direct reports of each person in the previous level
11 SELECT e.id, e.name, e.manager_id, o.level + 1
12 FROM employees e
13 JOIN org o ON e.manager_id = o.id
14)
15SELECT REPEAT(' ', level) AS indent, name, level
16FROM org ORDER BY level, name;
175. What are LEAD() and LAG() window functions?
LAG() accesses data from a previous row in the ordered result without a self-join. LEAD() accesses data from a following row. Both are extremely useful for period-over-period comparisons and time-series analysis.
1SELECT
2 month,
3 revenue,
4 LAG(revenue) OVER (ORDER BY month) AS prev_month,
5 LEAD(revenue) OVER (ORDER BY month) AS next_month,
6 revenue - LAG(revenue) OVER (ORDER BY month) AS mom_change,
7 ROUND(
8 (revenue - LAG(revenue) OVER (ORDER BY month)) * 100.0
9 / NULLIF(LAG(revenue) OVER (ORDER BY month), 0), 2
10 ) AS growth_pct
11FROM monthly_sales
12ORDER BY month;
13
14-- LAG per group: salary history per employee
15SELECT employee_id, effective_date, salary,
16 LAG(salary) OVER (PARTITION BY employee_id ORDER BY effective_date) AS prev_salary
17FROM salary_history;
186. How do you calculate a running total in SQL?
A running total (cumulative sum) adds up values row by row as you move through an ordered result. Use SUM() with OVER() and ORDER BY. You can also compute moving averages by specifying a frame (ROWS BETWEEN N PRECEDING AND CURRENT ROW).
1-- Running total of daily revenue
2SELECT
3 sale_date,
4 daily_revenue,
5 SUM(daily_revenue) OVER (ORDER BY sale_date) AS running_total
6FROM daily_sales;
7
8-- Running total per category
9SELECT category, month, revenue,
10 SUM(revenue) OVER (PARTITION BY category ORDER BY month) AS cat_running_total
11FROM category_sales;
12
13-- 7-day moving average
14SELECT sale_date, daily_revenue,
15 AVG(daily_revenue) OVER (
16 ORDER BY sale_date
17 ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
18 ) AS moving_7day_avg
19FROM daily_sales;
207. How do you find the Nth highest salary from a table?
A classic SQL interview question asked at almost every company. Knowing multiple methods shows depth. DENSE_RANK() is the cleanest and most interview-friendly approach.
1-- Method 1: DENSE_RANK() - handles ties correctly
2-- Find the 3rd highest salary
3SELECT name, salary FROM (
4 SELECT name, salary,
5 DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
6 FROM employees
7) r WHERE rnk = 3;
8
9-- Method 2: LIMIT + OFFSET (MySQL, N=3 → OFFSET=2)
10SELECT DISTINCT salary FROM employees
11ORDER BY salary DESC LIMIT 1 OFFSET 2;
12
13-- Method 3: Correlated subquery (no window functions needed)
14SELECT DISTINCT salary FROM employees e1
15WHERE 2 = (
16 SELECT COUNT(DISTINCT salary) FROM employees e2
17 WHERE e2.salary > e1.salary
18); -- 2 salaries are higher → this is the 3rd highest
198. How do you find and delete duplicate rows in SQL?
The strategy is to identify duplicate rows, keep the one with the lowest (or highest) ID, and delete the rest. ROW_NUMBER() in a CTE is the cleanest approach.
1-- Step 1: Find which values are duplicated
2SELECT email, COUNT(*) AS cnt
3FROM users GROUP BY email HAVING COUNT(*) > 1;
4
5-- Step 2: Delete duplicates - keep the row with the lowest id
6WITH dupes AS (
7 SELECT id,
8 ROW_NUMBER() OVER (PARTITION BY email ORDER BY id ASC) AS rn
9 FROM users
10)
11DELETE FROM users WHERE id IN (SELECT id FROM dupes WHERE rn > 1);
12-- rn = 1 is the first (kept) occurrence; rn > 1 are the duplicates
13
14-- Step 3: Prevent future duplicates
15ALTER TABLE users ADD UNIQUE INDEX idx_unique_email (email);
169. Write a query to find employees who earn more than their manager.
This is a classic self-join problem. The employees table has a manager_id column that references id in the same table. Join the table to itself to compare each employee's salary with their manager's.
1-- employees: id, name, salary, manager_id (references employees.id)
2SELECT
3 e.name AS employee,
4 e.salary AS emp_salary,
5 m.name AS manager,
6 m.salary AS mgr_salary
7FROM employees e
8JOIN employees m ON e.manager_id = m.id
9WHERE e.salary > m.salary;
1010. Write a query to find departments with no employees.
Use a LEFT JOIN from departments to employees, then filter for rows where the employees side is NULL - meaning no matching employee was found for that department.
1-- Method 1: LEFT JOIN + IS NULL (most common)
2SELECT d.name AS department
3FROM departments d
4LEFT JOIN employees e ON d.id = e.department_id
5WHERE e.id IS NULL;
6
7-- Method 2: NOT EXISTS
8SELECT d.name FROM departments d
9WHERE NOT EXISTS (
10 SELECT 1 FROM employees e WHERE e.department_id = d.id
11);
12
13-- Method 3: NOT IN
14SELECT name FROM departments
15WHERE id NOT IN (SELECT DISTINCT department_id FROM employees WHERE department_id IS NOT NULL);
1611. Write a query to get the second highest salary without using LIMIT.
This is asked specifically to test whether you know subqueries and aggregate functions. The trick is to find the MAX salary where a higher salary already exists.
1-- Method 1: Subquery with MAX (no LIMIT)
2SELECT MAX(salary) AS second_highest
3FROM employees
4WHERE salary < (SELECT MAX(salary) FROM employees);
5
6-- Method 2: DENSE_RANK (handles ties properly)
7SELECT salary FROM (
8 SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
9 FROM employees
10) r WHERE rnk = 2 LIMIT 1;
11
12-- What if no second salary exists? Return NULL gracefully
13SELECT IFNULL(
14 (SELECT MAX(salary) FROM employees WHERE salary < (SELECT MAX(salary) FROM employees)),
15 NULL
16) AS second_highest;
1712. Write a query to find the top 3 products by revenue in each category.
Top-N per group is a very common analytics pattern. The cleanest solution uses DENSE_RANK() partitioned by category to rank products within each group, then filters for rank <= 3.
1SELECT category, product_name, revenue
2FROM (
3 SELECT
4 c.name AS category,
5 p.name AS product_name,
6 SUM(oi.quantity * oi.unit_price) AS revenue,
7 DENSE_RANK() OVER (
8 PARTITION BY c.name
9 ORDER BY SUM(oi.quantity * oi.unit_price) DESC
10 ) AS rnk
11 FROM order_items oi
12 JOIN products p ON oi.product_id = p.id
13 JOIN categories c ON p.category_id = c.id
14 GROUP BY c.name, p.name
15) ranked
16WHERE rnk <= 3
17ORDER BY category, rnk;
1813. Write a query to find users who logged in on 3 or more consecutive days.
This is the classic gaps-and-islands problem. The key insight is that consecutive dates have the same value when you subtract their ROW_NUMBER from the date - this creates a group identifier for each consecutive streak.
1WITH daily_logins AS (
2 SELECT DISTINCT user_id, DATE(login_time) AS login_date FROM logins
3),
4numbered AS (
5 SELECT user_id, login_date,
6 ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date) AS rn
7 FROM daily_logins
8),
9grouped AS (
10 SELECT user_id, login_date,
11 DATE_SUB(login_date, INTERVAL rn DAY) AS grp -- same value for consecutive dates
12 FROM numbered
13)
14SELECT
15 user_id,
16 MIN(login_date) AS streak_start,
17 MAX(login_date) AS streak_end,
18 COUNT(*) AS consecutive_days
19FROM grouped
20GROUP BY user_id, grp
21HAVING COUNT(*) >= 3
22ORDER BY user_id, streak_start;
2314. Write a query to calculate month-over-month revenue growth.
Combine a CTE to aggregate monthly revenue with LAG() to bring in the previous month's figure, then compute the percentage change. NULLIF prevents a division-by-zero crash when the previous month had zero revenue.
1WITH monthly AS (
2 SELECT
3 DATE_FORMAT(order_date, '%Y-%m') AS month,
4 SUM(total) AS revenue
5 FROM orders
6 GROUP BY DATE_FORMAT(order_date, '%Y-%m')
7)
8SELECT
9 month,
10 revenue,
11 LAG(revenue) OVER (ORDER BY month) AS prev_revenue,
12 ROUND(
13 (revenue - LAG(revenue) OVER (ORDER BY month)) * 100.0
14 / NULLIF(LAG(revenue) OVER (ORDER BY month), 0),
15 2) AS growth_pct
16FROM monthly
17ORDER BY month;
1815. Write a query to pivot monthly sales data into columns.
MySQL does not have a native PIVOT keyword. The standard approach is conditional aggregation with CASE WHEN inside a SUM - you convert rows into columns manually. This pattern also works in any SQL database.
1-- Source: product | sale_date | amount
2-- Target: one row per product with Jan, Feb, Mar columns
3SELECT
4 product_name,
5 SUM(CASE WHEN MONTH(sale_date) = 1 THEN amount ELSE 0 END) AS jan,
6 SUM(CASE WHEN MONTH(sale_date) = 2 THEN amount ELSE 0 END) AS feb,
7 SUM(CASE WHEN MONTH(sale_date) = 3 THEN amount ELSE 0 END) AS mar,
8 SUM(amount) AS total
9FROM product_sales
10WHERE YEAR(sale_date) = 2025
11GROUP BY product_name;
12
13-- SQL Server / PostgreSQL native PIVOT (when available)
14SELECT product, [January], [February], [March]
15FROM sales
16PIVOT (
17 SUM(amount) FOR month IN ([January], [February], [March])
18) AS pvt;
1916. How do you optimize a slow SQL query?
Always start with EXPLAIN to understand what the engine is actually doing before guessing at fixes. Most slow queries fall into a small set of patterns.
1-- Step 1: Read the execution plan
2EXPLAIN SELECT e.name, d.name
3FROM employees e JOIN departments d ON e.department_id = d.id
4WHERE e.salary > 50000;
5-- Look for: type=ALL (full scan), key=NULL (no index used)
6
7-- Step 2: Add indexes on WHERE / JOIN / ORDER BY columns
8CREATE INDEX idx_salary ON employees(salary);
9CREATE INDEX idx_dept_id ON employees(department_id);
10
11-- Step 3: Avoid functions on indexed columns
12-- ✗ Can't use index
13SELECT * FROM orders WHERE YEAR(created_at) = 2025;
14-- ✓ Range scan uses index
15SELECT * FROM orders WHERE created_at BETWEEN '2025-01-01' AND '2025-12-31';
16
17-- Step 4: Select only needed columns, not SELECT *
18SELECT id, name, price FROM products WHERE category = 'Electronics';
19
20-- Step 5: Prefer keyset pagination over large OFFSETs
21-- ✗ Slow - scans and discards 100k rows
22SELECT * FROM products ORDER BY id LIMIT 10 OFFSET 100000;
23-- ✓ Fast - jumps directly to id > 100000
24SELECT * FROM products WHERE id > 100000 ORDER BY id LIMIT 10;
2517. What is SQL injection and how do you prevent it?
SQL injection is a critical security vulnerability where an attacker inserts malicious SQL into user input that the database then executes. It is consistently ranked #1 in the OWASP Top 10 and can lead to full database compromise.
1-- ✗ VULNERABLE - string concatenation allows injection
2-- User enters: ' OR '1'='1
3-- Query becomes: SELECT * FROM users WHERE name = '' OR '1'='1'
4-- Returns ALL users - authentication bypassed!
5const query = `SELECT * FROM users WHERE name = '${userInput}'`;
6
7-- Another attack - user enters: '; DROP TABLE users; --
8-- Query becomes: SELECT * FROM users WHERE id = '1'; DROP TABLE users; --'
9
10-- ✓ Prevention 1: Parameterised queries (most important)
11-- Parameter is always treated as DATA, never as SQL code
12PREPARE stmt FROM 'SELECT * FROM users WHERE email = ?';
13SET @email = 'user@example.com';
14EXECUTE stmt USING @email;
15
16-- ✓ Prevention 2: Principle of least privilege
17-- App's DB user should only have the permissions it needs
18GRANT SELECT, INSERT, UPDATE ON myapp.* TO 'app_user'@'localhost';
19-- app_user cannot DROP tables or access other databases
20
21-- ✓ Prevention 3: Use an ORM (they use parameterised queries by default)
22-- Django ORM, Hibernate, Sequelize - safe out of the box
2318. What is a deadlock in SQL and how do you prevent it?
A deadlock occurs when two transactions each wait for a lock held by the other, creating a circular dependency. The database engine detects this and kills one transaction (the deadlock victim) to break the cycle.
1-- Classic deadlock scenario
2-- T1 locks row 1, waits for row 2
3-- T2 locks row 2, waits for row 1 → deadlock!
4
5-- ✓ Prevention 1: Always acquire locks in the SAME order
6-- If both transactions lock row 1 then row 2 (never reversed), deadlock is impossible
7
8-- ✓ Prevention 2: Acquire all needed locks upfront with SELECT FOR UPDATE
9BEGIN;
10SELECT * FROM accounts WHERE id IN (1, 2) ORDER BY id FOR UPDATE;
11-- Both locks acquired at once in consistent order - deadlock impossible
12UPDATE accounts SET balance = balance - 500 WHERE id = 1;
13UPDATE accounts SET balance = balance + 500 WHERE id = 2;
14COMMIT;
15
16-- ✓ Prevention 3: Keep transactions short
17-- Don't do API calls, file I/O, or user prompts INSIDE a transaction
18
19-- Check for deadlocks (MySQL)
20SHOW ENGINE INNODB STATUS; -- see LATEST DETECTED DEADLOCK section
2119. What are transaction isolation levels in SQL?
Isolation levels control how much a transaction is protected from other concurrent transactions. Higher isolation means more consistency but more locking and lower throughput.
1-- 1. READ UNCOMMITTED - can read uncommitted (dirty) data
2-- Allows: dirty reads, non-repeatable reads, phantom reads
3-- Rarely used in practice
4
5-- 2. READ COMMITTED - only sees committed data (default in PostgreSQL, Oracle)
6-- Prevents: dirty reads
7-- Allows: non-repeatable reads, phantom reads
8
9-- 3. REPEATABLE READ - same row returns same value within a transaction
10-- Prevents: dirty reads, non-repeatable reads
11-- Allows: phantom reads | Default in MySQL/InnoDB
12
13-- 4. SERIALIZABLE - complete isolation, transactions behave as if sequential
14-- Prevents: all anomalies | Most locking, lowest throughput
15
16SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
17BEGIN;
18 SELECT balance FROM accounts WHERE id = 1; -- sees only committed data
19 UPDATE accounts SET balance = balance - 5000 WHERE id = 1;
20COMMIT;
21
22-- Dirty Read: T1 reads T2's uncommitted change (T2 may roll back)
23-- Non-Repeatable Read: T1 reads a row twice - T2 changed it between reads
24-- Phantom Read: T1 runs a range query twice - T2 inserted a new matching row
2520. What is table partitioning in SQL?
Partitioning divides a large table into smaller physical pieces (partitions) while still appearing as one logical table. Queries that filter on the partition key can scan only the relevant partition (partition pruning), dramatically improving performance on very large tables. Old partitions can also be dropped instantly - much faster than DELETE on millions of rows.
1-- RANGE partitioning by year (most common for time-series data)
2CREATE TABLE orders (
3 id INT NOT NULL,
4 customer_id INT NOT NULL,
5 order_date DATE NOT NULL
6)
7PARTITION BY RANGE (YEAR(order_date)) (
8 PARTITION p_2023 VALUES LESS THAN (2024),
9 PARTITION p_2024 VALUES LESS THAN (2025),
10 PARTITION p_2025 VALUES LESS THAN (2026),
11 PARTITION p_future VALUES LESS THAN MAXVALUE
12);
13
14-- This query only scans p_2025 - partition pruning in action
15SELECT * FROM orders WHERE order_date BETWEEN '2025-01-01' AND '2025-12-31';
16
17-- LIST partitioning by region
18CREATE TABLE employees (
19 id INT, name VARCHAR(200), region VARCHAR(50)
20)
21PARTITION BY LIST COLUMNS(region) (
22 PARTITION p_north VALUES IN ('Delhi', 'Punjab'),
23 PARTITION p_south VALUES IN ('Chennai', 'Bangalore')
24);
25
26-- Drop an old partition instantly (no row-by-row DELETE needed)
27ALTER TABLE orders DROP PARTITION p_2023;
2858. Write a query to find the IDs of products that are both recyclable and low fat.
Retrieve the IDs of all products that satisfy both conditions: the product is recyclable and it is marked as low fat.
1SELECT product_id
2FROM Products
3WHERE low_fats = 'Y'
4 AND recyclable = 'Y';59. Write a query to list all customers who were not referred by customer with ID 2.
Return the names of customers whose referee is not customer ID 2. Customers without any referee should also be included.
1SELECT name
2FROM Customer
3WHERE referee_id <> 2
4 OR referee_id IS NULL;60. Write a query to retrieve countries with either a population of at least 25 million or an area of at least 3 million square kilometers.
Find countries that qualify as 'big' based on either their population or land area, and display their name, population, and area.
1SELECT name,
2 population,
3 area
4FROM World
5WHERE area >= 3000000
6 OR population >= 25000000;61. Write a query to find authors who viewed at least one of their own articles.
Return the IDs of authors who have viewed an article that they themselves wrote. The result should not contain duplicate author IDs.
1SELECT DISTINCT author_id AS id
2FROM Views
3WHERE author_id = viewer_id
4ORDER BY id;62. Write a query to return the IDs of tweets whose content exceeds 15 characters.
Find every tweet whose text length is greater than 15 characters and return only its tweet ID.
1SELECT tweet_id
2FROM Tweets
3WHERE LENGTH(content) > 15;63. Write a query to replace each employee ID with its corresponding unique identifier whenever available.
Display the unique identifier along with each employee's name. If an employee does not have a unique identifier assigned, return NULL for that value.
1SELECT
2 eu.unique_id,
3 e.name
4FROM Employees e
5LEFT JOIN EmployeeUNI eu
6 ON e.id = eu.id;64. Write a query to display the product name, sale year, and price for every recorded sale.
Join the Product and Sales tables to retrieve the product name along with the year in which it was sold and its selling price.
1SELECT
2 p.product_name,
3 s.year,
4 s.price
5FROM Sales s
6JOIN Product p
7 ON s.product_id = p.product_id;65. Write a query to find customers who visited but did not complete any transactions.
Identify customers who made one or more visits but never completed a transaction during those visits. Return each customer along with the number of such visits.
1SELECT
2 customer_id,
3 COUNT(*) AS count_no_trans
4FROM Visits v
5LEFT JOIN Transactions t
6 ON v.visit_id = t.visit_id
7WHERE t.transaction_id IS NULL
8GROUP BY customer_id;66. Write a query to identify dates where the temperature was higher than the previous day.
Compare each day's temperature with the temperature recorded on the previous day and return the IDs of days where the temperature increased.
1SELECT w1.id
2FROM Weather w1
3JOIN Weather w2
4 ON DATEDIFF(w1.recordDate, w2.recordDate) = 1
5WHERE w1.temperature > w2.temperature;67. Write a query to calculate the average processing time for each machine.
Each process has a start and end activity. Compute the average time taken by every machine to complete its processes, rounded to three decimal places.
1SELECT
2 a1.machine_id,
3 ROUND(AVG(a2.timestamp - a1.timestamp), 3) AS processing_time
4FROM Activity a1
5JOIN Activity a2
6 ON a1.machine_id = a2.machine_id
7 AND a1.process_id = a2.process_id
8WHERE a1.activity_type = 'start'
9 AND a2.activity_type = 'end'
10GROUP BY a1.machine_id;Related Articles
AI Interview Questions
Ace your AI interviews with comprehensive questions covering fundamentals, machine learning, deep learning, LLMs, and advanced topics for freshers and experienced professionals.
AI/MLMachine Learning Interview Questions
Master your Machine Learning interviews with comprehensive questions and code examples covering fundamentals, algorithms, model evaluation, and advanced production techniques for freshers and experienced professionals.