Skip to content
SQL
All coursesPlacement Report
‹ Back to previous sectionCourse homeAll page URLs

SQL Interview Questions & Answers

All 200 unique questions in one place — 59 easy, 99 medium, 42 hard — grouped by topic.

Practise by topic

SQL Basics · 25Joins · 25Aggregation and Grouping · 24Subqueries and CTEs · 25Window Functions · 26Database Design · 24Indexes and Performance · 25Transactions and Advanced SQL · 26

Practise the way your interview works

Company-wise SQL questionsExperience-wise SQL questionsRole-wise SQL questionsCity-wise SQL questionsMost asked SQL questionsScenario-based questionsPractical / hands-on questionsHR & behavioural questionsSQL mock interviewOnline assessment questionsTechnical round questionsManagerial round questionsSQL topic sheets
Solved overallStart practicing
3 QUESTIONS FREESign up to unlock the full question bank and track your progress.Unlock every question and answer, then continue practising company-wise and package-wise.
3 questions free Unlock the rest in seconds
01

What is SQL and what is it used for?

SQL BasicsEasy
Save to ▾
Show answer
SQL, or Structured Query Language, is the standard language for working with relational databases. It is used to create tables, insert, update and delete data, query data with filters, joins and aggregations, and control access. Almost every application, analytics team and reporting tool relies on SQL, which is why it is tested in interviews for developers, analysts, testers and data engineers.
02

What are DDL, DML, DCL and TCL commands?

SQL BasicsEasy
Save to ▾
Show answer
DDL (Data Definition Language) defines structure: CREATE, ALTER, DROP, TRUNCATE. DML (Data Manipulation Language) changes data: INSERT, UPDATE, DELETE, and SELECT is often grouped here or as DQL. DCL (Data Control Language) manages access: GRANT and REVOKE. TCL (Transaction Control Language) manages transactions: COMMIT, ROLLBACK and SAVEPOINT. Knowing the categories helps explain behaviour such as auto-commit of DDL in some databases.
03

What is the difference between DELETE, TRUNCATE and DROP?

SQL BasicsEasy
Save to ▾
Show answer
DELETE removes selected rows using a WHERE clause, is logged row by row, fires triggers and can be rolled back inside a transaction. TRUNCATE removes all rows quickly by deallocating pages, usually resets identity values and cannot use WHERE. DROP removes the whole table including structure, indexes and permissions. Use DELETE for selective removal, TRUNCATE to empty staging tables and DROP to remove objects.
04

What is the difference between WHERE and HAVING?

SQL BasicsEasy
Save to ▾
Show answer
WHERE filters individual rows before grouping and cannot use aggregate functions. HAVING filters groups after GROUP BY and can use aggregates such as COUNT or SUM. For example, WHERE city = 'Pune' keeps Pune rows, and HAVING COUNT(*) > 5 keeps only groups with more than five rows. Filtering early with WHERE is also more efficient.
05

What is the logical order of execution of a SELECT query?

SQL BasicsEasy
Save to ▾
Show answer
The logical order is FROM and JOIN, then WHERE, GROUP BY, HAVING, SELECT, DISTINCT, ORDER BY, and finally LIMIT or OFFSET (or TOP/FETCH). This explains why a column alias defined in SELECT cannot be used in WHERE but can be used in ORDER BY in most databases. The optimiser may execute differently physically, but results follow this logical order.
06

What is NULL in SQL and how do you compare it?

SQL BasicsEasy
Save to ▾
Show answer
NULL means a missing or unknown value, not zero or an empty string. Any comparison with NULL using = or <> returns unknown, so rows are not returned. Use IS NULL or IS NOT NULL to test it, and COALESCE or IFNULL to replace it. Aggregates like SUM and AVG ignore NULLs, while COUNT(*) counts all rows and COUNT(column) skips NULLs.
07

What are the common SQL data types?

SQL BasicsEasy
Save to ▾
Show answer
Common types are INT, BIGINT and SMALLINT for whole numbers, DECIMAL or NUMERIC for exact values like money, FLOAT for approximate values, CHAR for fixed-length and VARCHAR for variable-length text, DATE, TIME, DATETIME or TIMESTAMP for dates, and BOOLEAN where supported. Choosing correct types saves space, improves performance and prevents errors such as storing amounts in FLOAT and getting rounding issues.
08

What is the difference between CHAR and VARCHAR?

SQL BasicsEasy
Save to ▾
Show answer
CHAR(n) stores fixed-length strings and pads shorter values with spaces, so every value uses n characters. VARCHAR(n) stores variable-length strings up to n and uses only the needed space plus a small length overhead. CHAR suits fixed codes such as country code or PIN format, while VARCHAR suits names, emails and addresses whose length varies.
09

Write a query to get employees from Delhi with salary above 50000, highest first.

SQL BasicsEasy
Save to ▾
Show answer
SELECT emp_id, name, salary FROM employees WHERE city = 'Delhi' AND salary > 50000 ORDER BY salary DESC; WHERE filters rows, and ORDER BY DESC sorts from highest to lowest. Add LIMIT 10 in MySQL or PostgreSQL, or TOP 10 in SQL Server, to return only the first ten.
10

Write SQL to create a students table with a primary key and constraints.

SQL BasicsEasy
Save to ▾
Show answer
CREATE TABLE students ( student_id INT PRIMARY KEY, name VARCHAR(100) NOT NULL, email VARCHAR(150) UNIQUE, age INT CHECK (age >= 17), city VARCHAR(50) DEFAULT 'Delhi', joined_on DATE ); PRIMARY KEY ensures unique non-null IDs, UNIQUE blocks duplicate emails, and CHECK validates age.
11

How do you insert, update and delete a row?

SQL BasicsEasy
Save to ▾
Show answer
INSERT INTO students (student_id, name, city) VALUES (101, 'Anita', 'Noida'); UPDATE students SET city = 'Gurugram' WHERE student_id = 101; DELETE FROM students WHERE student_id = 101; Always use WHERE with UPDATE and DELETE; without it every row changes.
12

Why should we hire you for a SQL or data role as a fresher?

SQL BasicsEasy
Save to ▾
Show answer
Situation: in college I built a library management project with a MySQL backend. Task: make queries fast and correct. Action: I designed normalised tables, wrote queries with joins and group by, and practised over 150 interview problems online. Result: I can write clean, tested SQL today. I learn quickly, ask good questions and want to grow into a strong data professional with your team.
13

What does DISTINCT do and how is it different from GROUP BY?

SQL BasicsMedium
Save to ▾
Show answer
DISTINCT removes duplicate rows from the result based on all selected columns. GROUP BY groups rows so you can apply aggregates per group. SELECT DISTINCT city FROM customers and SELECT city FROM customers GROUP BY city give the same output, but GROUP BY is needed when you also want counts or sums. Overusing DISTINCT to hide duplicate join rows is a common mistake.
14

What is the difference between UNION and UNION ALL?

SQL BasicsMedium
Save to ▾
Show answer
UNION combines the results of two queries and removes duplicate rows, which requires sorting or hashing. UNION ALL combines results and keeps all rows, so it is faster. Both need the same number of columns with compatible types in the same order. Use UNION ALL when duplicates are impossible or acceptable, such as stacking monthly tables, and UNION only when deduplication is required.
15

What is the CASE expression and where can it be used?

SQL BasicsMedium
Save to ▾
Show answer
CASE adds conditional logic in SQL, returning a value based on conditions: CASE WHEN marks >= 60 THEN 'First' WHEN marks >= 45 THEN 'Second' ELSE 'Fail' END. It can be used in SELECT, WHERE, ORDER BY, GROUP BY and inside aggregates, for example SUM(CASE WHEN status = 'Paid' THEN amount ELSE 0 END) to pivot data. Conditions are checked in order and the first true one wins.
16

How does LIKE work and what are wildcards?

SQL BasicsMedium
Save to ▾
Show answer
LIKE matches string patterns. The percent sign matches any sequence of characters and the underscore matches exactly one character. name LIKE 'Ra%' finds names starting with Ra, and code LIKE 'A_1' matches A01 or AB1. Leading wildcards such as LIKE '%kumar' prevent index use and are slow on large tables. Case sensitivity depends on the database collation.
17

Find all customers whose email is missing or empty.

SQL BasicsMedium
Save to ▾
Show answer
SELECT customer_id, name FROM customers WHERE email IS NULL OR TRIM(email) = ''; NULL needs IS NULL, because email = NULL never matches. Empty strings and spaces are separate values, so TRIM handles both. Consider fixing data at entry with NOT NULL and CHECK constraints.
18

Write a query to pivot order counts by status into columns.

SQL BasicsMedium
Save to ▾
Show answer
SELECT customer_id, SUM(CASE WHEN status = 'Delivered' THEN 1 ELSE 0 END) AS delivered, SUM(CASE WHEN status = 'Cancelled' THEN 1 ELSE 0 END) AS cancelled, SUM(CASE WHEN status = 'Returned' THEN 1 ELSE 0 END) AS returned FROM orders GROUP BY customer_id; Conditional aggregation works in every database, unlike vendor-specific PIVOT syntax.
19

How do you get the top 3 rows in different databases?

SQL BasicsMedium
Save to ▾
Show answer
MySQL and PostgreSQL: SELECT * FROM products ORDER BY price DESC LIMIT 3; SQL Server: SELECT TOP 3 * FROM products ORDER BY price DESC; Standard SQL: SELECT * FROM products ORDER BY price DESC FETCH FIRST 3 ROWS ONLY; Always use ORDER BY, otherwise the top rows are not guaranteed.
20

Your query WHERE manager_id <> 5 is not returning employees who have no manager. Why?

SQL BasicsMedium
Save to ▾
Show answer
Employees with no manager have manager_id as NULL, and NULL <> 5 evaluates to unknown, not true, so those rows are excluded. Rewrite it as WHERE manager_id <> 5 OR manager_id IS NULL, or use COALESCE(manager_id, 0) <> 5. This three-valued logic with true, false and unknown causes many subtle bugs in reports and NOT IN subqueries.
21

Someone ran an UPDATE without WHERE on production. What would you do?

SQL BasicsMedium
Save to ▾
Show answer
Stop further changes and inform the lead and DBA immediately. If the session is still in an open transaction, roll back. Otherwise, restore affected rows from the latest backup or point-in-time recovery to a separate database and update the production table from it, or use audit or history tables if available. Afterwards, add safeguards such as safe-update mode, reviews and restricted write access.
22

A search on name LIKE '%sharma%' is very slow on a 20 million row table. What can you do?

SQL BasicsMedium
Save to ▾
Show answer
A leading wildcard cannot use a normal B-tree index, so the database scans the table. Options are full-text indexing, trigram indexes in PostgreSQL, a reversed or computed column if the pattern is a suffix, or a search engine for heavy text search. Also limit the result, filter on other indexed columns first, and check whether users really need contains search.
23

Tell me about a time you automated a manual data task with SQL.

SQL BasicsMedium
Save to ▾
Show answer
Situation: the operations team spent four hours every Monday merging Excel files for a weekly report. Task: reduce the effort. Action: I loaded the files into staging tables, wrote SQL with joins and aggregations to produce the report, and scheduled it as a job. Result: the report was ready automatically at 8 AM, saving about 16 hours a month and removing copy-paste errors.
24

A report sums amounts stored in a FLOAT column and finance says totals are off by a few paise. Why?

SQL BasicsHard
Save to ▾
Show answer
FLOAT stores approximate binary values, so decimals such as 0.1 cannot be represented exactly and rounding errors accumulate over millions of rows. Money should be stored as DECIMAL or NUMERIC with fixed scale, for example DECIMAL(12,2). Fix it by migrating the column type carefully, validating totals before and after, and rounding only at presentation time.
25

How do SQL dialects differ and how do you write portable SQL?

SQL BasicsHard
Save to ▾
Show answer
Databases differ in row limiting (LIMIT, TOP, FETCH), string concatenation (|| versus + versus CONCAT), date functions, identity columns (AUTO_INCREMENT, IDENTITY, SERIAL), upsert syntax and case sensitivity. For portable SQL, use ANSI features such as CASE, COALESCE, standard joins, window functions and FETCH FIRST where supported, keep vendor-specific logic isolated, and test against each target database.
26

What is a join and what types of joins exist?

JoinsEasy
Save to ▾
Show answer
A join combines rows from two or more tables based on a related column. INNER JOIN returns matching rows only. LEFT JOIN returns all rows from the left table with matches or NULLs. RIGHT JOIN does the opposite. FULL OUTER JOIN returns all rows from both. CROSS JOIN returns every combination. A SELF JOIN joins a table to itself, such as employees and managers.
27

What is the difference between INNER JOIN and LEFT JOIN?

JoinsEasy
Save to ▾
Show answer
INNER JOIN keeps only rows where the join condition matches in both tables. LEFT JOIN keeps every row from the left table and fills columns from the right with NULL when there is no match. For customers and orders, INNER JOIN shows only customers who ordered, while LEFT JOIN shows all customers, including those with no orders, which is useful for finding inactive customers.
28

What is a self join? Give an example.

JoinsEasy
Save to ▾
Show answer
A self join joins a table to itself using aliases, used for hierarchical or comparative data in one table. Example: SELECT e.name AS employee, m.name AS manager FROM employees e LEFT JOIN employees m ON e.manager_id = m.emp_id. Each employee row is matched with the row of its manager. LEFT JOIN keeps the top manager whose manager_id is NULL.
29

What is a CROSS JOIN and when is it useful?

JoinsEasy
Save to ▾
Show answer
A CROSS JOIN returns the Cartesian product, every row of the first table combined with every row of the second, so 10 rows by 5 rows gives 50 rows. It is useful for generating combinations, such as all stores by all dates to build a calendar grid and then left join actual sales to show zero-sales days. Accidental cross joins from missing conditions produce huge results.
30

Write a query to list orders with customer names.

JoinsEasy
Save to ▾
Show answer
SELECT o.order_id, o.order_date, c.name, o.amount FROM orders o INNER JOIN customers c ON o.customer_id = c.customer_id ORDER BY o.order_date DESC; Aliases o and c keep the query short and clear. INNER JOIN drops orders whose customer is missing, so use LEFT JOIN if such orders must still appear.
31

Find customers who have never placed an order.

JoinsEasy
Save to ▾
Show answer
SELECT c.customer_id, c.name FROM customers c LEFT JOIN orders o ON c.customer_id = o.customer_id WHERE o.order_id IS NULL; Alternative: SELECT customer_id, name FROM customers c WHERE NOT EXISTS ( SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id );
32

How do you keep improving your SQL skills?

JoinsEasy
Save to ▾
Show answer
Situation: after college I knew only basic SELECT queries. Task: become interview-ready for data roles. Action: I solved two practice problems daily, read execution plans for my own queries, revised window functions weekly and built a small project analysing public sales data in PostgreSQL. Result: within three months I could solve medium-level problems confidently, and I continue practising regularly.
33

What happens when you join on a column that has NULL values?

JoinsEasy
Save to ▾
Show answer
NULL never equals NULL in a join condition, so rows with NULL in the join column do not match each other in an inner join. In a LEFT JOIN, left rows with NULL keys are kept but get NULLs for right columns. If you really need NULLs to match, use a condition such as a.key IS NOT DISTINCT FROM b.key, or COALESCE both sides carefully.
34

What is the difference between a join and a union?

JoinsMedium
Save to ▾
Show answer
A join combines columns from different tables side by side based on a matching condition, making rows wider. A union stacks results of queries with the same column structure vertically, making the result longer. Use a join to bring customer names next to orders, and a union to combine 2024 and 2025 order tables into one list.
35

What is the difference between putting a condition in ON versus WHERE in a LEFT JOIN?

JoinsMedium
Save to ▾
Show answer
In a LEFT JOIN, a condition in ON filters the right table before matching, so all left rows are still kept. A condition on the right table in WHERE runs after the join and removes rows where the right side is NULL, effectively turning it into an inner join. To get all customers with only their 2025 orders, put the date condition in ON.
36

What are equi joins, non-equi joins and natural joins?

JoinsMedium
Save to ▾
Show answer
An equi join uses equality in the condition, such as a.id = b.id, the most common case. A non-equi join uses other operators like BETWEEN or greater than, for example matching salary to a grade range table. A natural join automatically joins on columns with the same names, which is risky because adding a column can silently change results, so explicit ON is preferred.
37

What are anti joins and semi joins?

JoinsMedium
Save to ▾
Show answer
A semi join returns rows from the first table that have at least one match in the second, without duplicating them, usually written with EXISTS or IN. An anti join returns rows with no match, written with NOT EXISTS or LEFT JOIN with WHERE right.key IS NULL. For example, customers who never ordered is an anti join. They avoid duplicates that a normal join could create.
38

Write a query to show each employee with their manager's name.

JoinsMedium
Save to ▾
Show answer
SELECT e.emp_id, e.name AS employee, m.name AS manager FROM employees e LEFT JOIN employees m ON e.manager_id = m.emp_id; The same table is used twice with different aliases. LEFT JOIN ensures the CEO, who has no manager, still appears with a NULL manager name.
39

Join three tables to get total sales by product category.

JoinsMedium
Save to ▾
Show answer
SELECT cat.category_name, SUM(oi.quantity * oi.unit_price) AS revenue FROM order_items oi JOIN products p ON oi.product_id = p.product_id JOIN categories cat ON p.category_id = cat.category_id GROUP BY cat.category_name ORDER BY revenue DESC; Join from the most detailed table outward and aggregate at the end.
40

Match each employee's salary to a grade using a range table.

JoinsMedium
Save to ▾
Show answer
SELECT e.name, e.salary, g.grade FROM employees e JOIN salary_grades g ON e.salary BETWEEN g.min_salary AND g.max_salary; This is a non-equi join. Ensure grade ranges do not overlap, otherwise an employee matches more than one grade and appears twice.
41

After joining orders with payments, revenue doubled. What went wrong?

JoinsMedium
Save to ▾
Show answer
Some orders have multiple payment rows, such as partial payments or retries, so each order row repeats and SUM(order amount) counts it multiple times. This is join fan-out. Fix it by aggregating payments per order in a subquery or CTE before joining, or summing order amounts from the orders table alone. Always check row counts before and after a join.
42

A LEFT JOIN is returning fewer rows than the left table. Why could that happen?

JoinsMedium
Save to ▾
Show answer
A LEFT JOIN never removes left rows by itself, so a WHERE condition on the right table is likely filtering out rows where it is NULL. For example WHERE o.status = 'Paid' drops customers without orders. Move the condition into the ON clause or add OR o.status IS NULL. Another cause is a later INNER JOIN in the chain that removes rows.
43

You need customers who bought both product A and product B. How would you write it?

JoinsMedium
Save to ▾
Show answer
Group by customer and count distinct matching products: SELECT customer_id FROM order_items WHERE product_id IN ('A', 'B') GROUP BY customer_id HAVING COUNT(DISTINCT product_id) = 2. Alternatively use INTERSECT of two queries, or two EXISTS conditions. The HAVING approach is concise, scans the table once and is easy to extend to three or more products.
44

Describe a time you found and fixed a wrong number in a report.

JoinsMedium
Save to ▾
Show answer
Situation: a monthly dashboard showed 30 percent higher revenue than finance figures. Task: find the cause before the leadership review. Action: I traced the SQL, found a join to the shipments table that duplicated orders with split shipments, rewrote it with a pre-aggregated CTE and reconciled totals with finance. Result: numbers matched exactly and I added a row-count test to the report pipeline.
45

What is the difference between FULL OUTER JOIN and UNION of LEFT and RIGHT joins?

JoinsMedium
Save to ▾
Show answer
A FULL OUTER JOIN returns matched rows plus unmatched rows from both sides in one operation. MySQL does not support it, so it is emulated with a LEFT JOIN UNION a RIGHT JOIN, or LEFT JOIN UNION ALL a RIGHT JOIN filtered to unmatched rows. Using plain UNION removes duplicates but can also remove genuinely repeated rows, so the UNION ALL pattern is safer.
46

Compare this month's and last month's sales per product using a full outer join.

JoinsMedium
Save to ▾
Show answer
SELECT COALESCE(c.product_id, p.product_id) AS product_id, COALESCE(c.sales, 0) AS this_month, COALESCE(p.sales, 0) AS last_month FROM cur_month c FULL OUTER JOIN prev_month p ON c.product_id = p.product_id; Products sold in only one of the months still appear, with zero for the other month.
47

What physical join algorithms do databases use?

JoinsHard
Save to ▾
Show answer
Optimisers choose between nested loop joins, efficient when one side is small and the other has an index on the join key, hash joins, which build a hash table on the smaller input and suit large unsorted equality joins, and merge joins, which work on inputs sorted by the join key. Statistics, indexes and memory influence the choice, visible in the execution plan.
48

Show all dates in a month with sales, including days with zero sales.

JoinsHard
Save to ▾
Show answer
WITH days AS ( SELECT generate_series(DATE '2025-01-01', DATE '2025-01-31', INTERVAL '1 day')::date AS d ) SELECT d.d, COALESCE(SUM(s.amount), 0) AS sales FROM days d LEFT JOIN sales s ON s.sale_date = d.d GROUP BY d.d ORDER BY d.d; This PostgreSQL example uses a date series; other databases use a calendar table.
49

A join between two large tables takes minutes. How would you speed it up?

JoinsHard
Save to ▾
Show answer
Check the execution plan for scans, join type and row estimates. Ensure join keys have indexes and matching data types, because implicit conversion blocks index use. Filter both sides early, select only needed columns, update statistics, and avoid functions on join columns. For very large data, consider partitioning, pre-aggregated tables, or batch processing outside peak hours.
50

Two systems store customer IDs differently, one as INT and one as VARCHAR with leading zeros. How do you join them?

JoinsHard
Save to ▾
Show answer
Convert consistently, for example CAST(b.cust_code AS INT) = a.customer_id, but understand this prevents index use on b.cust_code and fails on non-numeric values. Better options are a computed or persisted column with the converted value and an index, or a mapping table built during ETL. Also validate unmatched rows to catch formatting issues such as spaces.
51

What are aggregate functions in SQL?

Aggregation and GroupingEasy
Save to ▾
Show answer
Aggregate functions compute a single value from many rows: COUNT, SUM, AVG, MIN and MAX are standard, and databases add others such as STRING_AGG, GROUP_CONCAT, STDDEV and MEDIAN or PERCENTILE_CONT. Used with GROUP BY they return one value per group; without it they return one value for the whole table. Except COUNT(*), they ignore NULL values.
52

What is the difference between COUNT(*), COUNT(column) and COUNT(DISTINCT column)?

Aggregation and GroupingEasy
Save to ▾
Show answer
COUNT(*) counts all rows including those with NULLs. COUNT(column) counts rows where that column is not NULL. COUNT(DISTINCT column) counts unique non-null values. In a table of 100 orders where 10 have no coupon and 30 distinct coupons were used, COUNT(*) is 100, COUNT(coupon) is 90 and COUNT(DISTINCT coupon) is 30.
53

What is GROUP BY and what is the rule for selected columns?

Aggregation and GroupingEasy
Save to ▾
Show answer
GROUP BY groups rows that share values in specified columns so aggregates can be computed for each group. Every column in SELECT must either be in GROUP BY or inside an aggregate function. SELECT city, COUNT(*) FROM customers GROUP BY city is valid, but adding name without aggregating it is an error in standard SQL, though older MySQL modes allowed it.
54

How does AVG treat NULL values?

Aggregation and GroupingEasy
Save to ▾
Show answer
AVG ignores NULLs, dividing the sum of non-null values by the count of non-null values. If three employees have bonuses 1000, 2000 and NULL, AVG(bonus) is 1500, not 1000. If NULL should mean zero, use AVG(COALESCE(bonus, 0)). Interviewers ask this to check whether you understand how missing data changes business metrics.
55

Find the number of employees and average salary in each department.

Aggregation and GroupingEasy
Save to ▾
Show answer
SELECT department_id, COUNT(*) AS emp_count, ROUND(AVG(salary), 2) AS avg_salary FROM employees GROUP BY department_id ORDER BY emp_count DESC; Join with the departments table if department names are needed, and use LEFT JOIN from departments to include empty departments.
56

Find cities with more than 100 customers.

Aggregation and GroupingEasy
Save to ▾
Show answer
SELECT city, COUNT(*) AS customers FROM customers GROUP BY city HAVING COUNT(*) > 100 ORDER BY customers DESC; HAVING is needed because the condition uses an aggregate. A WHERE clause could still filter rows first, for example only active customers.
57

What is the difference between MIN/MAX on numbers and on strings or dates?

Aggregation and GroupingEasy
Save to ▾
Show answer
MIN and MAX work on any comparable type. On numbers they return smallest and largest values, on dates the earliest and latest, and on strings the first and last in alphabetical order based on collation. MAX(order_date) gives the latest order, and MIN(name) gives the alphabetically first name, which is useful but not a meaningful business metric by itself.
58

Can you use a column alias in GROUP BY or HAVING?

Aggregation and GroupingMedium
Save to ▾
Show answer
In standard SQL, GROUP BY and HAVING are evaluated before SELECT, so aliases are not available there. MySQL and PostgreSQL allow aliases in GROUP BY, and MySQL allows them in HAVING, but SQL Server does not. For portable code, repeat the expression or wrap the query in a subquery or CTE and filter on the alias in the outer query.
59

What are ROLLUP, CUBE and GROUPING SETS?

Aggregation and GroupingMedium
Save to ▾
Show answer
They produce multiple grouping levels in one query. ROLLUP(region, city) gives city totals, region subtotals and a grand total. CUBE(region, product) gives all combinations of subtotals. GROUPING SETS lets you list exact combinations, such as (region), (product) and (). The GROUPING function identifies subtotal rows, which is useful for labelling them in reports.
60

What is the difference between SUM with CASE and filtering with WHERE?

Aggregation and GroupingMedium
Save to ▾
Show answer
WHERE removes rows from the entire query, so all aggregates see only filtered data. SUM(CASE WHEN condition THEN amount END) applies the condition to one aggregate only, letting you calculate several conditional totals in one pass, such as paid and refunded amounts per customer. Some databases support the cleaner FILTER clause: SUM(amount) FILTER (WHERE status = 'Paid').
61

How do you concatenate values within a group?

Aggregation and GroupingMedium
Save to ▾
Show answer
Use string aggregation: STRING_AGG(name, ', ') in PostgreSQL and SQL Server, GROUP_CONCAT(name SEPARATOR ', ') in MySQL, and LISTAGG in some other databases. Most support ordering inside, for example STRING_AGG(name, ', ' ORDER BY name). It is used to list products in an order or skills of an employee in one row, but watch length limits.
62

Find duplicate email addresses in a users table.

Aggregation and GroupingMedium
Save to ▾
Show answer
SELECT email, COUNT(*) AS cnt FROM users GROUP BY email HAVING COUNT(*) > 1; To see full duplicate rows: SELECT * FROM users WHERE email IN ( SELECT email FROM users GROUP BY email HAVING COUNT(*) > 1 ) ORDER BY email; Normalise case with LOWER(email) if needed.
63

Write a query for monthly revenue for the current year.

Aggregation and GroupingMedium
Save to ▾
Show answer
SELECT EXTRACT(MONTH FROM order_date) AS month, SUM(amount) AS revenue, COUNT(DISTINCT customer_id) AS buyers FROM orders WHERE order_date >= DATE '2025-01-01' AND order_date < DATE '2026-01-01' GROUP BY EXTRACT(MONTH FROM order_date) ORDER BY month; A date range in WHERE keeps an index on order_date usable.
64

Produce region subtotals and a grand total in one query.

Aggregation and GroupingMedium
Save to ▾
Show answer
SELECT COALESCE(region, 'All Regions') AS region, COALESCE(city, 'Subtotal') AS city, SUM(sales) AS sales FROM store_sales GROUP BY ROLLUP(region, city) ORDER BY region, city; In MySQL use GROUP BY region, city WITH ROLLUP. Use GROUPING() to label totals when real NULLs exist.
65

Your GROUP BY query returns an error that a column is not in the group by clause. How do you fix it?

Aggregation and GroupingMedium
Save to ▾
Show answer
Each selected column must be grouped or aggregated. Decide what you need: add the column to GROUP BY if it defines the group, aggregate it with MIN, MAX or STRING_AGG if any or all values are acceptable, or remove it. If you need details of the top row in each group, use a window function such as ROW_NUMBER instead of GROUP BY.
66

Average order value looks too low in a report. What could be wrong?

Aggregation and GroupingMedium
Save to ▾
Show answer
Check the grain: if the query averages order items instead of orders, each line counts separately and the average drops. Joins can also duplicate rows. Verify whether cancelled or zero-value orders are included and how NULL amounts are treated. Compute SUM(amount) / COUNT(DISTINCT order_id) at order level and compare with business definitions before changing the report.
67

Management wants the percentage of each category in total sales. How do you write it?

Aggregation and GroupingMedium
Save to ▾
Show answer
Use a window aggregate over the grouped result: SELECT category, SUM(sales) AS sales, ROUND(100.0 * SUM(sales) / SUM(SUM(sales)) OVER (), 2) AS pct FROM sales GROUP BY category. The inner SUM is per category and the outer SUM over an empty window gives the grand total. Multiplying by 100.0 avoids integer division in some databases.
68

Tell me about a time you had to deliver a report under a tight deadline.

Aggregation and GroupingMedium
Save to ▾
Show answer
Situation: the sales head needed region-wise quarterly numbers for a client meeting the same afternoon. Task: deliver accurate figures within three hours. Action: I confirmed metric definitions, reused tested queries, wrote one grouped query with ROLLUP for subtotals and cross-checked totals with the finance summary. Result: the report was delivered in two hours, and it later became a scheduled weekly report.
69

A COUNT(DISTINCT) query on a huge events table is very slow. What options do you have?

Aggregation and GroupingMedium
Save to ▾
Show answer
Exact distinct counts need sorting or hashing all values, which is expensive. Options are filtering the date range tightly, pre-aggregating daily distinct users into a summary table, indexing the columns involved, or using approximate functions such as APPROX_COUNT_DISTINCT or HyperLogLog where small error is acceptable. Explain the accuracy trade-off to stakeholders before switching to approximations.
70

How do databases execute GROUP BY internally?

Aggregation and GroupingHard
Save to ▾
Show answer
The optimiser uses either hash aggregation, building a hash table keyed by group values, which is fast for unsorted data with enough memory, or stream (sort) aggregation, which needs input sorted by the group keys, often from an index. Large groups that do not fit memory spill to disk. Indexes on grouping columns and pre-filtering reduce the cost.
71

Calculate the median salary per department.

Aggregation and GroupingHard
Save to ▾
Show answer
PostgreSQL and standard SQL: SELECT department_id, PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY salary) AS median_salary FROM employees GROUP BY department_id; SQL Server uses PERCENTILE_CONT as a window function with OVER (PARTITION BY department_id). Median resists outliers better than average for salary analysis.
72

A daily aggregation over 2 billion rows is too slow for a dashboard. What would you propose?

Aggregation and GroupingHard
Save to ▾
Show answer
Avoid recalculating raw data on every request. Build a summary table or materialised view at daily grain refreshed incrementally, partition the fact table by date, and use columnar storage if available. Pre-compute common metrics, index the summary on dimensions used in filters, and let the dashboard query the summary. Validate summary totals against raw data periodically.
73

How do you handle a situation where business users dispute your numbers?

Aggregation and GroupingHard
Save to ▾
Show answer
Situation: a regional manager said my sales report undercounted his region. Task: resolve it with facts. Action: I shared the query logic and definitions, compared a sample of his records with the database, and found his team counted orders on booking date while the report used invoice date. Result: we agreed on invoice date with finance, documented it, and added the definition to the report.
74

Finance asks for the count of customers whose total spend crossed 1 lakh in each city. How do you write it?

Aggregation and GroupingHard
Save to ▾
Show answer
Aggregate in two levels. First compute total spend per customer and city in a CTE: SELECT city, customer_id, SUM(amount) AS spend FROM orders GROUP BY city, customer_id. Then in the outer query: SELECT city, COUNT(*) FROM cte WHERE spend > 100000 GROUP BY city. Trying to do it in one GROUP BY by city fails because the threshold applies to each customer, not each city.
75

What is a subquery and where can it be used?

Subqueries and CTEsEasy
Save to ▾
Show answer
A subquery is a query nested inside another statement. It can appear in WHERE, such as salary > (SELECT AVG(salary) FROM employees), in FROM as a derived table, in SELECT as a scalar value, and in HAVING. It can return a single value, a list for IN, or a table. Subqueries break complex logic into smaller steps.
76

What is the difference between a correlated and a non-correlated subquery?

Subqueries and CTEsEasy
Save to ▾
Show answer
A non-correlated subquery runs independently and returns a result once, such as the overall average salary. A correlated subquery references columns from the outer query and logically runs for each outer row, such as comparing each employee's salary with the average of their own department. Correlated subqueries are powerful but may be slow without good indexes, although optimisers often rewrite them as joins.
77

What is a CTE and why use it?

Subqueries and CTEsEasy
Save to ▾
Show answer
A Common Table Expression is a named temporary result defined with WITH before the main query. It improves readability by breaking a complex query into named steps, can be referenced multiple times in the same statement, and supports recursion. Example: WITH high_value AS (SELECT * FROM orders WHERE amount > 10000) SELECT customer_id, COUNT(*) FROM high_value GROUP BY customer_id.
78

Find employees earning more than the company average.

Subqueries and CTEsEasy
Save to ▾
Show answer
SELECT emp_id, name, salary FROM employees WHERE salary > ( SELECT AVG(salary) FROM employees ) ORDER BY salary DESC; The inner query is a non-correlated scalar subquery that runs once and returns one value used for comparison.
79

Find the second highest salary.

Subqueries and CTEsEasy
Save to ▾
Show answer
SELECT MAX(salary) AS second_highest FROM employees WHERE salary < (SELECT MAX(salary) FROM employees); With a window function: SELECT DISTINCT salary FROM ( SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk FROM employees ) t WHERE rnk = 2; Both handle duplicate top salaries correctly.
80

How do you approach a SQL problem you have never seen before in an interview?

Subqueries and CTEsEasy
Save to ▾
Show answer
Situation: in a campus interview I got a problem about consecutive login days that I had not practised. Task: solve it while explaining my thinking. Action: I clarified the table and expected output, wrote a small example, broke it into steps with CTEs, and talked through each step before writing the final query. Result: I solved it with a small hint and the interviewer appreciated the structured approach.
81

What is the difference between IN and EXISTS?

Subqueries and CTEsMedium
Save to ▾
Show answer
IN checks whether a value matches any value in a list or subquery result. EXISTS checks whether a correlated subquery returns at least one row and stops at the first match. Modern optimisers often treat them similarly, but EXISTS is clearer for correlated checks and handles NULLs safely. For large subquery results, EXISTS with an index on the join column is usually efficient.
82

Why is NOT IN dangerous when the subquery contains NULL?

Subqueries and CTEsMedium
Save to ▾
Show answer
If the subquery returns any NULL, value NOT IN (list) becomes unknown for every row, because comparing with NULL is unknown, so the query returns no rows. For example, NOT IN (SELECT manager_id FROM employees) fails when a manager_id is NULL. Use NOT EXISTS or filter out NULLs in the subquery to get correct results.
83

What is the difference between a CTE, a subquery, a temp table and a view?

Subqueries and CTEsMedium
Save to ▾
Show answer
A subquery and a CTE exist only for one statement; a CTE is more readable and can be recursive. A temporary table stores data physically for the session, can be indexed and reused across statements, useful for large intermediate results. A view is a saved query stored in the database that anyone with access can reuse. Choose by reuse, size and performance needs.
84

Are CTEs materialised or inlined?

Subqueries and CTEsMedium
Save to ▾
Show answer
It depends on the database. SQL Server and MySQL usually inline CTEs like subqueries. PostgreSQL before version 12 always materialised CTEs, which could hurt performance; since version 12 non-recursive CTEs referenced once are inlined, and you can force behaviour with MATERIALIZED or NOT MATERIALIZED. Referencing a heavy CTE many times may cause repeated work, so check the plan.
85

Find employees who earn more than their department average.

Subqueries and CTEsMedium
Save to ▾
Show answer
SELECT e.emp_id, e.name, e.department_id, e.salary FROM employees e WHERE e.salary > ( SELECT AVG(e2.salary) FROM employees e2 WHERE e2.department_id = e.department_id ); This is a correlated subquery. A window version uses AVG(salary) OVER (PARTITION BY department_id) in a CTE and filters in the outer query.
86

Rewrite a nested query using CTEs for readability.

Subqueries and CTEsMedium
Save to ▾
Show answer
WITH order_totals AS ( SELECT customer_id, SUM(amount) AS total FROM orders GROUP BY customer_id ), ranked AS ( SELECT customer_id, total, NTILE(10) OVER (ORDER BY total DESC) AS decile FROM order_totals ) SELECT * FROM ranked WHERE decile = 1; Each CTE is a named step, making top 10 percent customer logic easy to read.
87

Find departments where every employee earns above 40000.

Subqueries and CTEsMedium
Save to ▾
Show answer
SELECT d.department_id, d.name FROM departments d WHERE NOT EXISTS ( SELECT 1 FROM employees e WHERE e.department_id = d.department_id AND e.salary <= 40000 ) AND EXISTS ( SELECT 1 FROM employees e WHERE e.department_id = d.department_id ); Double negation expresses for all; the EXISTS excludes empty departments.
88

Your NOT IN query returns zero rows even though you expect results. What do you check?

Subqueries and CTEsMedium
Save to ▾
Show answer
Check whether the subquery returns any NULL values, because NOT IN with a NULL makes every comparison unknown. Run the subquery alone and look for NULLs. Fix it with NOT EXISTS, which handles NULLs correctly, or add WHERE column IS NOT NULL inside the subquery. Also confirm data types match so values compare as expected.
89

A query with a correlated subquery in SELECT runs for every row and is slow. How do you improve it?

Subqueries and CTEsMedium
Save to ▾
Show answer
Rewrite the correlated scalar subquery as a join to a pre-aggregated derived table or CTE, for example aggregate order counts per customer once and LEFT JOIN it. Alternatively use a window function. Ensure the correlation column is indexed if you keep the subquery. Compare execution plans and timing before and after to confirm the improvement.
90

You need to delete duplicate rows but keep one copy of each. How do you do it?

Subqueries and CTEsMedium
Save to ▾
Show answer
Use a CTE with ROW_NUMBER: WITH d AS (SELECT id, ROW_NUMBER() OVER (PARTITION BY email ORDER BY id) AS rn FROM users) DELETE FROM users WHERE id IN (SELECT id FROM d WHERE rn > 1). SQL Server allows DELETE FROM d WHERE rn > 1 directly. Back up the table first, run a SELECT to verify, then add a unique constraint to prevent new duplicates.
91

An interviewer asks for the Nth highest salary in a reusable way. How do you answer?

Subqueries and CTEsMedium
Save to ▾
Show answer
Use DENSE_RANK so ties share a rank: SELECT salary FROM (SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS r FROM employees) t WHERE r = :n. Alternatively use ORDER BY salary DESC with OFFSET n-1 ROWS FETCH NEXT 1 ROW ONLY on distinct salaries. Mention how ties are handled and that per-department versions add PARTITION BY department_id.
92

Describe a time you reviewed someone else's SQL and suggested improvements.

Subqueries and CTEsMedium
Save to ▾
Show answer
Situation: a teammate's report query took eight minutes and had nested subqueries. Task: help without discouraging him. Action: I reviewed it with him, rewrote parts as CTEs, replaced a correlated subquery with a pre-aggregated join, and explained the execution plan differences. Result: the query ran in 40 seconds, and he later applied the same approach to two other reports.
93

What is a recursive CTE and how does it work?

Subqueries and CTEsHard
Save to ▾
Show answer
A recursive CTE has an anchor member that returns the starting rows and a recursive member that references the CTE itself, joined with UNION ALL. The database repeats the recursive part using the previous iteration's rows until no new rows appear. It is used for hierarchies like org charts, bill of materials and generating sequences. Add a depth limit to prevent infinite loops on cyclic data.
94

What is a lateral join or CROSS APPLY?

Subqueries and CTEsHard
Save to ▾
Show answer
A lateral join, written as JOIN LATERAL in PostgreSQL and MySQL 8 or CROSS APPLY and OUTER APPLY in SQL Server, lets a subquery in FROM reference columns of tables listed before it. It acts like a correlated subquery that can return multiple rows and columns, ideal for top N per group, for example the three latest orders for each customer.
95

Write a recursive CTE to show an employee hierarchy with levels.

Subqueries and CTEsHard
Save to ▾
Show answer
WITH RECURSIVE org AS ( SELECT emp_id, name, manager_id, 1 AS lvl FROM employees WHERE manager_id IS NULL UNION ALL SELECT e.emp_id, e.name, e.manager_id, o.lvl + 1 FROM employees e JOIN org o ON e.manager_id = o.emp_id ) SELECT * FROM org ORDER BY lvl, name; SQL Server omits the RECURSIVE keyword.
96

Get the latest 3 orders for every customer using a lateral join.

Subqueries and CTEsHard
Save to ▾
Show answer
SELECT c.customer_id, o.order_id, o.order_date FROM customers c CROSS JOIN LATERAL ( SELECT order_id, order_date FROM orders WHERE orders.customer_id = c.customer_id ORDER BY order_date DESC LIMIT 3 ) o; With an index on (customer_id, order_date), each lookup is fast. SQL Server uses CROSS APPLY with TOP 3.
97

A long report query has eight nested subqueries and nobody can maintain it. How would you refactor it?

Subqueries and CTEsHard
Save to ▾
Show answer
Break it into CTEs with meaningful names, each producing one business step such as active customers, order totals and returns. Remove repeated logic, standardise filters, and add comments on business rules. Test each step's row count against the original output. If parts are reused across reports, move them into views. Finally compare results and execution plans to ensure nothing changed.
98

A recursive CTE on a category tree runs forever. What is the likely cause and fix?

Subqueries and CTEsHard
Save to ▾
Show answer
The data probably has a cycle, such as category A as parent of B and B as parent of A, so recursion never ends. Add a depth column and stop at a maximum level, track a path string or array and exclude nodes already visited, and use database limits such as MAXRECURSION in SQL Server. Then fix the cyclic data at the source with constraints or validation.
99

Tell me about a time you set SQL coding standards for a team.

Subqueries and CTEsHard
Save to ▾
Show answer
Situation: our analytics team of eight wrote queries in very different styles, making reviews slow. Task: create common standards. Action: I drafted guidelines on naming, CTE usage, formatting, comments and anti-patterns, added a linter to the repository, and held a short workshop. Result: review time dropped noticeably, new joiners onboarded faster, and production errors from ambiguous queries reduced.
100

What are window functions in SQL?

Window FunctionsEasy
Save to ▾
Show answer
Window functions perform calculations across a set of rows related to the current row without collapsing them into one row like GROUP BY. They use the OVER clause with optional PARTITION BY, ORDER BY and a frame. Examples are ROW_NUMBER, RANK, running SUM, LAG and LEAD. You can show each order alongside the customer's total, which GROUP BY alone cannot do.
101

What is the difference between ROW_NUMBER, RANK and DENSE_RANK?

Window FunctionsEasy
Save to ▾
Show answer
For salaries 900, 800, 800, 700 ordered descending: ROW_NUMBER gives 1, 2, 3, 4 with unique numbers even for ties; RANK gives 1, 2, 2, 4, skipping after ties; DENSE_RANK gives 1, 2, 2, 3 without gaps. Use ROW_NUMBER for deduplication or picking one row, and DENSE_RANK for Nth highest value questions where ties should share a position.
102

What do PARTITION BY and ORDER BY do inside OVER?

Window FunctionsEasy
Save to ▾
Show answer
PARTITION BY divides rows into groups and the window function restarts for each group, like per department. ORDER BY defines the order of rows within each partition, needed for ranking, running totals and LAG or LEAD. Without PARTITION BY the whole result is one partition. Without ORDER BY, aggregate windows compute over the entire partition.
103

What is the difference between GROUP BY and window functions?

Window FunctionsEasy
Save to ▾
Show answer
GROUP BY collapses rows into one row per group, so detail columns are lost. Window functions keep every row and add the computed value alongside, such as each employee with their department average. You can combine both: group first, then apply a window over the grouped result, for example percent of total using SUM(SUM(sales)) OVER ().
104

Rank employees by salary within each department.

Window FunctionsEasy
Save to ▾
Show answer
SELECT emp_id, name, department_id, salary, DENSE_RANK() OVER ( PARTITION BY department_id ORDER BY salary DESC ) AS dept_rank FROM employees; PARTITION BY restarts ranking for each department, and DENSE_RANK keeps equal salaries at the same rank.
105

Calculate a running total of daily sales.

Window FunctionsEasy
Save to ▾
Show answer
SELECT sale_date, amount, SUM(amount) OVER ( ORDER BY sale_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) AS running_total FROM daily_sales ORDER BY sale_date; Add PARTITION BY store_id to restart the running total for each store.
106

Describe your SQL project from college or internship.

Window FunctionsEasy
Save to ▾
Show answer
Situation: during my internship the team tracked student attendance in spreadsheets. Task: build a database and reports. Action: I designed tables for students, courses and attendance, loaded data, and wrote queries with joins, group by and window functions to show monthly attendance and low-attendance alerts. Result: faculty got a weekly report automatically, and I learned how real data quality issues affect queries.
107

What are LAG and LEAD functions?

Window FunctionsMedium
Save to ▾
Show answer
LAG returns a value from a previous row in the partition and LEAD from a following row, based on the ORDER BY. LAG(revenue, 1, 0) OVER (ORDER BY month) gives last month's revenue with 0 as default for the first row. They are used for month-over-month change, time between events, and detecting status changes without self joins.
108

What is a window frame and what is the default frame?

Window FunctionsMedium
Save to ▾
Show answer
A frame defines which rows around the current row are included, such as ROWS BETWEEN 2 PRECEDING AND CURRENT ROW for a 3-row moving average. When ORDER BY is present and no frame is specified, the default is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, which produces a running total but includes all ties of the current value. Without ORDER BY, the frame is the whole partition.
109

What is the difference between ROWS and RANGE frames?

Window FunctionsMedium
Save to ▾
Show answer
ROWS counts physical rows, so 2 PRECEDING means exactly two rows before. RANGE works on values of the ORDER BY column, so rows with the same value are treated together, and value ranges like INTERVAL '7 days' PRECEDING are possible in some databases. With duplicate dates, a RANGE running total jumps for all ties at once, while ROWS increases row by row.
110

What are FIRST_VALUE, LAST_VALUE and NTH_VALUE?

Window FunctionsMedium
Save to ▾
Show answer
FIRST_VALUE returns the first value in the window frame, LAST_VALUE the last, and NTH_VALUE the nth. A common trap is LAST_VALUE with the default frame, which ends at the current row, so it returns the current row's value. Specify ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING to get the true last value of the partition.
111

What do NTILE, PERCENT_RANK and CUME_DIST do?

Window FunctionsMedium
Save to ▾
Show answer
NTILE(n) splits ordered rows into n roughly equal buckets, useful for quartiles or deciles. PERCENT_RANK returns (rank - 1) / (rows - 1), a relative position between 0 and 1. CUME_DIST returns the fraction of rows with values less than or equal to the current row. They are used for customer segmentation, percentile reports and scoring.
112

Get the top 2 highest paid employees in each department.

Window FunctionsMedium
Save to ▾
Show answer
WITH r AS ( SELECT emp_id, name, department_id, salary, ROW_NUMBER() OVER ( PARTITION BY department_id ORDER BY salary DESC ) AS rn FROM employees ) SELECT * FROM r WHERE rn <= 2; Use DENSE_RANK instead of ROW_NUMBER if tied salaries should all be included.
113

Calculate month-over-month revenue growth percentage.

Window FunctionsMedium
Save to ▾
Show answer
WITH m AS ( SELECT DATE_TRUNC('month', order_date) AS month, SUM(amount) AS revenue FROM orders GROUP BY 1 ) SELECT month, revenue, ROUND(100.0 * (revenue - LAG(revenue) OVER (ORDER BY month)) / NULLIF(LAG(revenue) OVER (ORDER BY month), 0), 2) AS mom_pct FROM m ORDER BY month; NULLIF avoids division by zero.
114

Calculate a 7-day moving average of daily active users.

Window FunctionsMedium
Save to ▾
Show answer
SELECT activity_date, dau, ROUND(AVG(dau) OVER ( ORDER BY activity_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW ), 1) AS dau_7d_avg FROM daily_users ORDER BY activity_date; This assumes one row per day; fill missing dates with a calendar table first, or ROWS will span more than seven days.
115

Your running total jumps by large amounts on some dates instead of row by row. Why?

Window FunctionsMedium
Save to ▾
Show answer
With ORDER BY and no explicit frame, the default frame is RANGE UNBOUNDED PRECEDING, which treats all rows with the same order value as peers and includes them together. When several rows share the same date, the running total adds them all at once. Specify ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, or add a unique tiebreaker to ORDER BY.
116

You need each customer's latest address from a history table. How do you query it?

Window FunctionsMedium
Save to ▾
Show answer
Use ROW_NUMBER partitioned by customer and ordered by updated date descending, then keep rn = 1: WITH r AS (SELECT *, ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY updated_at DESC) AS rn FROM address_history) SELECT * FROM r WHERE rn = 1. Add a tiebreaker such as id DESC in case two updates share the same timestamp.
117

An interviewer asks to show each employee's salary next to their department's average. How do you do it?

Window FunctionsMedium
Save to ▾
Show answer
Use a window aggregate: SELECT name, department_id, salary, AVG(salary) OVER (PARTITION BY department_id) AS dept_avg, salary - AVG(salary) OVER (PARTITION BY department_id) AS diff FROM employees. Unlike GROUP BY, every employee row is kept. Mention that a join with a grouped subquery produces the same result but is longer.
118

Tell me about a time you used advanced SQL to solve a business problem.

Window FunctionsMedium
Save to ▾
Show answer
Situation: the product team wanted to know where users dropped off in the onboarding flow. Task: build a funnel from raw event logs. Action: I used window functions to order events per user, LEAD to find the next step and time taken, and grouped the results into a funnel with conversion rates. Result: we found a 40 percent drop at KYC upload, and the redesign improved completion by 15 percent.
119

Can you use window functions with aggregate functions in the same query?

Window FunctionsMedium
Save to ▾
Show answer
Yes. Window functions are evaluated after GROUP BY, so they can operate on aggregated values. For example, SELECT region, SUM(sales) AS s, RANK() OVER (ORDER BY SUM(sales) DESC) AS rnk FROM orders GROUP BY region ranks regions by total sales. The aggregate is computed per group first, and then the window function runs over the grouped rows.
120

Assign each customer to a spending quartile.

Window FunctionsMedium
Save to ▾
Show answer
WITH spend AS ( SELECT customer_id, SUM(amount) AS total FROM orders GROUP BY customer_id ) SELECT customer_id, total, NTILE(4) OVER (ORDER BY total DESC) AS quartile FROM spend; Quartile 1 contains the highest spenders. Use this for targeted offers or loyalty tiers.
121

Why can't you use a window function in WHERE, and how do you filter on it?

Window FunctionsHard
Save to ▾
Show answer
Window functions are evaluated after WHERE, GROUP BY and HAVING in the logical order, as part of SELECT, so their results do not exist when WHERE runs. To filter, compute the window function in a subquery or CTE and filter in the outer query. Some databases such as Snowflake and BigQuery support a QUALIFY clause for filtering window results directly.
122

What are the performance considerations of window functions?

Window FunctionsHard
Save to ▾
Show answer
Each distinct PARTITION BY and ORDER BY combination usually needs a sort, which is expensive on large data and may spill to disk. Reuse the same window definition where possible, using a WINDOW clause, and create indexes that match the partition and order columns so sorts can be avoided. Filter rows before applying windows and avoid unnecessary large frames.
123

Find users who logged in on 3 or more consecutive days.

Window FunctionsHard
Save to ▾
Show answer
WITH d AS ( SELECT DISTINCT user_id, login_date FROM logins ), g AS ( SELECT user_id, login_date, login_date - CAST(ROW_NUMBER() OVER ( PARTITION BY user_id ORDER BY login_date) AS INT) AS grp FROM d ) SELECT user_id, MIN(login_date) AS start_day, COUNT(*) AS days FROM g GROUP BY user_id, grp HAVING COUNT(*) >= 3; Consecutive dates minus row number give the same group value (PostgreSQL date arithmetic).
124

Calculate the time each user spent between consecutive events.

Window FunctionsHard
Save to ▾
Show answer
SELECT user_id, event_time, event_name, event_time - LAG(event_time) OVER ( PARTITION BY user_id ORDER BY event_time ) AS gap_since_prev FROM events ORDER BY user_id, event_time; Gaps above 30 minutes can start a new session using a SUM of a flag over the same window, a common sessionisation pattern.
125

A retention report needs the percentage of users active in each month after signup. How would you build it in SQL?

Window FunctionsHard
Save to ▾
Show answer
Compute each user's signup month with MIN over their activity, then derive month number as the difference between activity month and signup month. Group by cohort month and month number, counting distinct users. Divide by the cohort size using FIRST_VALUE or a window SUM over month zero within each cohort. Validate with a few known users and handle users with no activity.
126

What is a primary key and a foreign key?

Database DesignEasy
Save to ▾
Show answer
A primary key uniquely identifies each row in a table and cannot be NULL or duplicate, such as student_id. A foreign key is a column in one table that references the primary key of another, such as orders.customer_id referencing customers.customer_id. Foreign keys enforce referential integrity, preventing orders for customers that do not exist.
127

What is the difference between a primary key and a unique key?

Database DesignEasy
Save to ▾
Show answer
Both enforce uniqueness. A table can have only one primary key, and its columns cannot contain NULL. A table can have many unique constraints, and in most databases a unique column can hold NULL, often more than one, though SQL Server allows only one NULL by default. Primary keys often create a clustered index in SQL Server; unique keys create non-clustered indexes.
128

What is normalisation and why is it needed?

Database DesignEasy
Save to ▾
Show answer
Normalisation organises data into tables to reduce redundancy and avoid insert, update and delete anomalies. For example, storing customer address in every order row means updating it in many places. By separating customers and orders and linking with keys, each fact is stored once. Common forms are 1NF, 2NF, 3NF and BCNF, and most transactional systems target 3NF.
129

Explain 1NF, 2NF and 3NF with simple examples.

Database DesignEasy
Save to ▾
Show answer
1NF: each column holds atomic values and there are no repeating groups, so no comma-separated phone numbers. 2NF: in 1NF and every non-key column depends on the whole composite key, so product name should not sit in an order_items table keyed by order and product. 3NF: in 2NF with no transitive dependency, so city should not determine state inside the employees table.
130

Design tables for students, courses and enrolments.

Database DesignEasy
Save to ▾
Show answer
CREATE TABLE students ( student_id INT PRIMARY KEY, name VARCHAR(100) NOT NULL ); CREATE TABLE courses ( course_id INT PRIMARY KEY, title VARCHAR(100) NOT NULL ); CREATE TABLE enrolments ( student_id INT REFERENCES students(student_id), course_id INT REFERENCES courses(course_id), enrolled_on DATE, PRIMARY KEY (student_id, course_id) ); The junction table resolves the many-to-many relationship.
131

What is a composite key?

Database DesignEasy
Save to ▾
Show answer
A composite key is a primary key made of two or more columns that together uniquely identify a row. For example, in an enrolments table, student_id and course_id together form the key because a student can join many courses and a course has many students, but each pair appears once. Column order in a composite key matters for index usage.
132

How do you document a database you designed so others can use it?

Database DesignEasy
Save to ▾
Show answer
Situation: I built a reporting schema that three new analysts needed to use. Task: help them work independently. Action: I created an ER diagram, wrote a data dictionary with table purpose, grain, keys and column meanings, added comments in the database, and shared sample queries for common questions. Result: new analysts started writing correct queries in their first week with very few doubts.
133

What are constraints in SQL?

Database DesignMedium
Save to ▾
Show answer
Constraints enforce rules on data: PRIMARY KEY for unique row identity, FOREIGN KEY for valid references, UNIQUE for no duplicates, NOT NULL for required values, CHECK for conditions like age >= 18, and DEFAULT for automatic values. They protect data quality at the database level, so bad data is rejected even if an application has a bug.
134

What is denormalisation and when is it used?

Database DesignMedium
Save to ▾
Show answer
Denormalisation intentionally adds redundancy, such as storing customer name in an orders reporting table, to reduce joins and speed up reads. It is common in data warehouses, reporting tables and high-read applications. The cost is extra storage and the need to keep copies consistent through ETL, triggers or application logic. Use it when read performance matters more than write simplicity.
135

What is the difference between OLTP and OLAP databases?

Database DesignMedium
Save to ▾
Show answer
OLTP systems handle many short transactions such as orders and payments, with normalised schemas, row-based storage and focus on fast inserts and updates. OLAP systems support analysis over large history, using denormalised star or snowflake schemas, columnar storage and heavy aggregations. A bank's core system is OLTP, while its data warehouse used for monthly reports is OLAP.
136

What is a star schema and a snowflake schema?

Database DesignMedium
Save to ▾
Show answer
A star schema has a central fact table with measures such as sales amount and foreign keys to denormalised dimension tables like date, product, store and customer. A snowflake schema normalises dimensions further, such as product to category to department. Star schemas are simpler and faster for BI queries, while snowflake schemas save space and reduce redundancy.
137

What are surrogate keys and natural keys?

Database DesignMedium
Save to ▾
Show answer
A natural key is a real-world identifier like PAN number, email or product code. A surrogate key is a system-generated value, usually an integer or UUID, with no business meaning. Surrogate keys are stable, compact and good for joins, while natural keys can change or be long. A common design uses a surrogate primary key plus a unique constraint on the natural key.
138

What are referential actions such as ON DELETE CASCADE?

Database DesignMedium
Save to ▾
Show answer
Referential actions define what happens to child rows when a parent row is deleted or updated. CASCADE deletes or updates children automatically, SET NULL sets the foreign key to NULL, SET DEFAULT uses the default value, and RESTRICT or NO ACTION blocks the change. CASCADE is convenient for dependent data like order items but risky on important data, because one delete can remove many rows.
139

Add a foreign key and a check constraint to an existing table.

Database DesignMedium
Save to ▾
Show answer
ALTER TABLE orders ADD CONSTRAINT fk_orders_customer FOREIGN KEY (customer_id) REFERENCES customers(customer_id); ALTER TABLE orders ADD CONSTRAINT chk_amount_positive CHECK (amount > 0); First clean existing rows that violate the rule, otherwise the ALTER fails. Naming constraints makes errors easier to read.
140

A table stores phone numbers as 'ph1, ph2, ph3' in one column. What is wrong and how do you fix it?

Database DesignMedium
Save to ▾
Show answer
The column violates first normal form because it stores multiple values in one field, making search, validation and updates difficult. Create a separate customer_phones table with customer_id, phone and type columns, one row per number, with a foreign key to customers. Migrate data by splitting the strings, then drop the old column after applications are updated.
141

How would you design a database for an online food delivery app at a high level?

Database DesignMedium
Save to ▾
Show answer
Core tables are users, addresses, restaurants, menu_items, orders, order_items, payments, delivery_partners and deliveries. Orders reference users, restaurants and addresses, order_items reference orders and menu_items with price at order time, and payments and deliveries reference orders. Add status history tables for tracking and indexes on user_id, restaurant_id and created_at for common queries.
142

A reporting team wants to query the production OLTP database directly. What are the risks and alternatives?

Database DesignMedium
Save to ▾
Show answer
Heavy analytical queries can lock rows, use CPU and slow customer transactions, and normalised schemas make reporting complex. Alternatives are a read replica for reporting, a data warehouse loaded by ETL or CDC with a star schema, or materialised summary tables. Grant read-only access with limits if direct access is unavoidable, and schedule heavy jobs off-peak.
143

Tell me about a database design decision you made and its outcome.

Database DesignMedium
Save to ▾
Show answer
Situation: our app stored user preferences in a wide table with 60 nullable columns. Task: make it flexible for new settings. Action: I proposed a key-value preferences table with a JSON column for rarely used settings, kept frequently queried fields as columns, and wrote a migration. Result: new settings no longer needed schema changes and query speed for common fields stayed the same.
144

What are slowly changing dimensions in data warehousing?

Database DesignHard
Save to ▾
Show answer
Slowly changing dimensions track changes in dimension attributes over time. Type 1 overwrites old values with no history. Type 2 adds a new row with effective dates and a current flag, preserving full history, such as a customer moving city. Type 3 keeps a previous value column for limited history. Type 2 is most common when reports must reflect attributes as they were at transaction time.
145

What is BCNF and how is it different from 3NF?

Database DesignHard
Save to ▾
Show answer
Boyce-Codd Normal Form requires that for every functional dependency, the determinant is a candidate key. 3NF allows a dependency where the dependent attribute is part of a candidate key, while BCNF does not. For example, if a teacher teaches only one subject but a student-subject pair determines the teacher, the table is in 3NF but not BCNF and should be split.
146

Product prices change often, but old orders must keep the price at the time of purchase. How do you design it?

Database DesignHard
Save to ▾
Show answer
Store the unit price, discount and tax in order_items when the order is created instead of reading the current product price, so history is not affected by updates. Maintain a product_price_history table with effective_from and effective_to dates for analysis. Reports on past orders use order_items values, while catalogue pages use the current price.
147

Your team debates using UUIDs or auto-increment integers as primary keys. What do you advise?

Database DesignHard
Save to ▾
Show answer
Auto-increment integers are compact, fast to index and naturally ordered, but expose counts and are hard to generate across distributed systems. UUIDs are globally unique and good for distributed inserts and merging data, but larger and random, causing index fragmentation. Time-ordered UUIDs such as UUIDv7 reduce fragmentation. Choose based on scale, distribution and security needs.
148

How do you handle disagreement with developers about schema changes?

Database DesignHard
Save to ▾
Show answer
Situation: a developer wanted to drop foreign keys to speed up inserts. Task: protect data integrity without blocking him. Action: I measured insert performance with and without constraints, showed the overhead was under five percent, and suggested batch inserts and proper indexes instead. Result: we kept the constraints, insert speed improved through batching, and orphan records stayed at zero.
149

An e-commerce app needs products with very different attributes, such as phones and shirts. How do you model them?

Database DesignHard
Save to ▾
Show answer
Options are a common products table plus category-specific tables (table per type), an entity-attribute-value table, or a JSON or JSONB column for flexible attributes. A practical design keeps shared fields like name, price and category as columns, stores variable attributes in JSONB with indexes on frequently filtered keys, and validates required attributes in the application or with check constraints.
150

What is an index in a database?

Indexes and PerformanceEasy
Save to ▾
Show answer
An index is a separate data structure, usually a B-tree, that stores column values in sorted order with pointers to rows, so the database can find matching rows without scanning the whole table, like a book index. Indexes speed up WHERE, JOIN, ORDER BY and GROUP BY, but use storage and slow down inserts, updates and deletes because the index must be maintained.
151

What is the difference between clustered and non-clustered indexes?

Indexes and PerformanceEasy
Save to ▾
Show answer
A clustered index defines the physical order of rows in the table, so there can be only one, and in SQL Server the primary key creates it by default. A non-clustered index is a separate structure with key values and pointers to rows, and a table can have many. Range queries on the clustered key are very fast, while non-clustered indexes suit selective lookups.
152

What is a composite index and why does column order matter?

Indexes and PerformanceEasy
Save to ▾
Show answer
A composite index covers multiple columns, such as (customer_id, order_date). It is sorted by the first column, then the second within it. It helps queries filtering on customer_id alone or on both columns, but not queries filtering only on order_date, because of the leftmost prefix rule. Put the most commonly filtered equality column first, followed by range columns.
153

What is an execution plan?

Indexes and PerformanceEasy
Save to ▾
Show answer
An execution plan shows how the database will run a query: which tables are scanned or seeked, which indexes are used, the join algorithms, sort and aggregate steps, and estimated row counts and costs. Use EXPLAIN in MySQL and PostgreSQL, EXPLAIN ANALYZE for actual timing, and the estimated or actual plan in SQL Server. Reading plans is the first step in tuning slow queries.
154

Create an index to speed up searching orders by customer and date.

Indexes and PerformanceEasy
Save to ▾
Show answer
CREATE INDEX idx_orders_cust_date ON orders (customer_id, order_date); This helps queries like: SELECT * FROM orders WHERE customer_id = 101 AND order_date >= '2025-01-01'; Check with EXPLAIN that the index is used, and avoid creating duplicate indexes on the same columns.
155

What is the difference between a unique index and a normal index?

Indexes and PerformanceEasy
Save to ▾
Show answer
A unique index enforces that no two rows have the same value in the indexed columns, and it is created automatically for primary keys and unique constraints. A normal index only speeds up lookups and allows duplicates. Both speed up searches, but a unique index also protects data integrity, for example preventing two users from registering with the same email.
156

What is the difference between an index scan and an index seek?

Indexes and PerformanceMedium
Save to ▾
Show answer
An index seek navigates the B-tree directly to the matching keys, reading only the needed rows, which is efficient for selective filters. An index scan reads the whole index or a large range of it. A table scan reads every row. Scans are not always bad; for queries returning a large share of rows, a scan can be cheaper than many seeks.
157

What is a covering index?

Indexes and PerformanceMedium
Save to ▾
Show answer
A covering index contains all columns a query needs, either as key columns or included columns, so the database answers the query from the index alone without visiting the table. For SELECT order_date, amount FROM orders WHERE customer_id = 5, an index on (customer_id) INCLUDE (order_date, amount) avoids lookups. It speeds reads but increases index size and write cost.
158

What is a SARGable query and why does it matter?

Indexes and PerformanceMedium
Save to ▾
Show answer
A SARGable (Search ARGument able) predicate lets the optimiser use an index seek. Wrapping an indexed column in a function, such as WHERE YEAR(order_date) = 2025 or WHERE LOWER(email) = 'x', usually prevents index use. Rewrite as WHERE order_date >= '2025-01-01' AND order_date < '2026-01-01'. Leading wildcards and implicit type conversions also make predicates non-SARGable.
159

When should you not create an index?

Indexes and PerformanceMedium
Save to ▾
Show answer
Avoid indexes on small tables where scans are cheap, on columns with very low selectivity such as a boolean flag used alone, on tables with heavy writes and few reads, and duplicates of existing indexes. Every index slows inserts and updates and uses memory and storage. Review unused indexes periodically with database statistics and drop them after checking dependencies.
160

What are database statistics and why do they matter?

Indexes and PerformanceMedium
Save to ▾
Show answer
Statistics describe data distribution in columns, such as row counts, distinct values and histograms. The optimiser uses them to estimate how many rows each step returns and pick join types and indexes. Stale statistics after large data loads lead to bad plans, such as nested loops over millions of rows. Update them with ANALYZE in PostgreSQL or UPDATE STATISTICS in SQL Server.
161

Rewrite a non-SARGable date filter to use an index.

Indexes and PerformanceMedium
Save to ▾
Show answer
Slow: SELECT * FROM orders WHERE YEAR(order_date) = 2025 AND MONTH(order_date) = 3; Fast: SELECT * FROM orders WHERE order_date >= '2025-03-01' AND order_date < '2025-04-01'; The second form compares the raw column, so an index on order_date can be used with a range seek.
162

How do you read an EXPLAIN ANALYZE output in PostgreSQL?

Indexes and PerformanceMedium
Save to ▾
Show answer
EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 101; Read the tree from the innermost node outward. Check node types such as Seq Scan, Index Scan or Hash Join, compare estimated rows with actual rows, and look at actual time per node. Large estimate errors suggest stale statistics, and Seq Scan on a selective filter suggests a missing index.
163

A query that ran in 1 second now takes 40 seconds after a data load. What do you check?

Indexes and PerformanceMedium
Save to ▾
Show answer
Compare the current execution plan with the old one. A big data load often makes statistics stale, causing poor row estimates and a different join or scan choice, so update statistics first. Check for index fragmentation, missing indexes after the load, blocking from other sessions, and whether the data volume crossed a threshold. Test again after each change.
164

You added an index but the query still does a full table scan. Why might that be?

Indexes and PerformanceMedium
Save to ▾
Show answer
Possible reasons: the predicate is not SARGable because of a function or type conversion, the query returns a large share of rows so a scan is cheaper, the composite index column order does not match the filter, statistics are stale, or OR conditions prevent index use. Check the plan, rewrite the predicate, update statistics, or adjust the index to match the query.
165

A query uses SELECT * on a table with 80 columns. Why is that a problem?

Indexes and PerformanceMedium
Save to ▾
Show answer
SELECT * fetches every column, increasing disk reads, memory, network transfer and application processing. It prevents covering indexes from being used and can break applications when columns are added or reordered. Select only required columns, which lets the optimiser use narrower indexes and makes the intent clear. SELECT * is acceptable only for quick exploration, not production code.
166

An interviewer asks how you would find why a query is slow. What steps do you describe?

Indexes and PerformanceMedium
Save to ▾
Show answer
First reproduce the issue and measure time. Then check the execution plan for scans, expensive sorts, row estimate errors and join types. Look at indexes on filter and join columns, whether predicates are SARGable, and statistics freshness. Check for blocking or locks from other sessions. Change one thing at a time, measure again, and document the improvement.
167

Tell me about a time you significantly improved query performance.

Indexes and PerformanceMedium
Save to ▾
Show answer
Situation: a daily settlement report took 45 minutes and often missed its deadline. Task: bring it under ten minutes. Action: I studied the execution plan, replaced a non-SARGable date filter, added a composite covering index, pre-aggregated a large join in a temp table and updated statistics. Result: runtime dropped to six minutes, and the finance team received reports on time every day.
168

What is table partitioning and how does it help performance?

Indexes and PerformanceHard
Save to ▾
Show answer
Partitioning splits a large table into smaller physical pieces by a key, such as range partitions by month, list partitions by region, or hash partitions. Queries filtering on the partition key read only relevant partitions, called partition pruning. It also makes maintenance easier, such as dropping old months instantly. It does not help queries that do not filter on the partition key.
169

What is parameter sniffing and how do you handle it?

Indexes and PerformanceHard
Save to ▾
Show answer
In databases that cache plans, such as SQL Server, the plan for a parameterised query is compiled using the first parameter values. If those values are unusual, the cached plan may be poor for typical values, causing sudden slowness. Fixes include OPTION (RECOMPILE) for that query, OPTIMIZE FOR hints, plan guides, splitting procedures for different data shapes, or updating statistics.
170

Implement keyset pagination instead of OFFSET for large tables.

Indexes and PerformanceHard
Save to ▾
Show answer
Slow for deep pages: SELECT * FROM orders ORDER BY id LIMIT 50 OFFSET 500000; Keyset pagination: SELECT * FROM orders WHERE id > :last_seen_id ORDER BY id LIMIT 50; OFFSET reads and discards all skipped rows, while keyset seeks directly using the index on id, keeping each page fast.
171

Inserts into a heavily used table have become slow. What could be causing it?

Indexes and PerformanceHard
Save to ▾
Show answer
Too many indexes increase write cost, so audit and remove unused ones. Random UUID primary keys cause page splits and fragmentation. Triggers, foreign key checks without indexes on child columns, lock contention from long transactions, and log disk bottlenecks also slow inserts. Use batch inserts, sequential keys, proper indexing of foreign keys and monitor waits to find the actual bottleneck.
172

The dashboard queries hit the production database and slow down the app at month end. What do you propose?

Indexes and PerformanceHard
Save to ▾
Show answer
Separate workloads: route reporting to a read replica or data warehouse, pre-aggregate month-end metrics into summary tables refreshed after close, and cache results for dashboards. Tune the heaviest queries with indexes and better predicates. Schedule batch reports outside business hours and set resource limits or query timeouts for reporting users so customer transactions are protected.
173

A query with many OR conditions across different columns is slow. How can you optimise it?

Indexes and PerformanceHard
Save to ▾
Show answer
OR across different columns often prevents a single index seek. Rewrite it as UNION ALL of separate queries, each using its own index, removing duplicates carefully if needed, or use IN for multiple values of the same column. Some optimisers can combine indexes with bitmap or index merge plans. Compare plans and timings to choose the best approach.
174

How do you balance performance tuning with delivery deadlines?

Indexes and PerformanceHard
Save to ▾
Show answer
Situation: a release was due in two days, but a key query was slow under load testing. Task: fix the risk without delaying the release. Action: I identified the top two bottlenecks from the plan, applied an index and a query rewrite that were low risk, and logged deeper redesign work as a follow-up ticket with data. Result: the release went live on time and the redesign was completed in the next sprint.
175

What is a transaction in SQL?

Transactions and Advanced SQLEasy
Save to ▾
Show answer
A transaction is a group of SQL statements executed as a single unit of work that either fully succeeds or fully fails. It starts with BEGIN or START TRANSACTION and ends with COMMIT to save changes or ROLLBACK to undo them. For a fund transfer, debiting one account and crediting another must happen together, so both statements run in one transaction.
176

What are ACID properties?

Transactions and Advanced SQLEasy
Save to ▾
Show answer
Atomicity: all statements in a transaction succeed or none do. Consistency: a transaction moves the database from one valid state to another, respecting constraints. Isolation: concurrent transactions do not interfere and behave as if run in some order, depending on isolation level. Durability: once committed, changes survive crashes because they are written to logs. Banking and payment systems depend on ACID.
177

What is a view and why use it?

Transactions and Advanced SQLEasy
Save to ▾
Show answer
A view is a saved SELECT query that behaves like a virtual table. It simplifies complex joins for users, hides sensitive columns for security, and provides a stable interface when underlying tables change. A normal view does not store data, so it runs the query each time. Simple views can be updatable, while views with aggregates or joins usually are not.
178

What is a stored procedure?

Transactions and Advanced SQLEasy
Save to ▾
Show answer
A stored procedure is a named set of SQL statements saved in the database and executed with CALL or EXEC, optionally with input and output parameters. It encapsulates business logic, reduces network round trips, can be reused by many applications and allows permission to execute without direct table access. Overusing procedures can make logic harder to version and test compared with application code.
179

Write a transaction to transfer money between two accounts.

Transactions and Advanced SQLEasy
Save to ▾
Show answer
BEGIN; UPDATE accounts SET balance = balance - 5000 WHERE account_id = 1 AND balance >= 5000; UPDATE accounts SET balance = balance + 5000 WHERE account_id = 2; COMMIT; If the first update affects zero rows or any error occurs, the application issues ROLLBACK so neither change is saved.
180

How do you make sure your SQL changes do not break anything?

Transactions and Advanced SQLEasy
Save to ▾
Show answer
Situation: in my internship I had to change a report query used by three teams. Task: modify it safely. Action: I tested it on a copy of data, compared row counts and totals with the old version, asked a senior for review, and documented the change. Result: the update went live without issues, and the comparison approach became part of my routine for every change.
181

What is the difference between a stored procedure and a function?

Transactions and Advanced SQLMedium
Save to ▾
Show answer
A function must return a value or table and can be used inside SELECT or WHERE, but in most databases cannot change data or manage transactions. A stored procedure is executed independently, can return multiple result sets or output parameters, can modify data and control transactions. Use functions for reusable calculations and procedures for workflows such as month-end processing.
182

What are transaction isolation levels?

Transactions and Advanced SQLMedium
Save to ▾
Show answer
Isolation levels balance consistency and concurrency. Read Uncommitted allows dirty reads. Read Committed, the default in PostgreSQL and SQL Server, prevents dirty reads but allows non-repeatable reads. Repeatable Read, the default in MySQL InnoDB, prevents non-repeatable reads, and InnoDB also prevents most phantoms with gap locks. Serializable prevents all anomalies but reduces concurrency. Choose based on business correctness needs.
183

What are dirty reads, non-repeatable reads and phantom reads?

Transactions and Advanced SQLMedium
Save to ▾
Show answer
A dirty read sees uncommitted changes from another transaction that may roll back. A non-repeatable read gets different values when reading the same row twice because another transaction updated and committed it. A phantom read gets a different set of rows for the same query because another transaction inserted or deleted matching rows. Higher isolation levels prevent more of these.
184

What is a deadlock and how do databases handle it?

Transactions and Advanced SQLMedium
Save to ▾
Show answer
A deadlock occurs when two transactions each hold a lock the other needs, so both wait forever. For example, T1 locks account A and wants B, while T2 locks B and wants A. The database detects the cycle and kills one transaction as the victim, rolling it back with an error. Applications should retry the victim and access resources in a consistent order.
185

What is a trigger and when should it be used?

Transactions and Advanced SQLMedium
Save to ▾
Show answer
A trigger is code that runs automatically before or after INSERT, UPDATE or DELETE on a table. It is used for auditing changes, enforcing complex rules and maintaining derived data. Triggers are hidden from application developers, can slow writes and make debugging hard, especially when they cascade. Use them sparingly, document them well, and prefer constraints or application logic when possible.
186

What is a materialised view?

Transactions and Advanced SQLMedium
Save to ▾
Show answer
A materialised view stores the result of a query physically, unlike a normal view. Reads are fast because data is precomputed, which suits heavy aggregations for dashboards. The trade-off is staleness, so it must be refreshed on schedule, on commit or incrementally depending on the database. PostgreSQL uses REFRESH MATERIALIZED VIEW, and SQL Server offers indexed views with similar benefits.
187

Write an upsert that inserts a row or updates it if it exists.

Transactions and Advanced SQLMedium
Save to ▾
Show answer
PostgreSQL: INSERT INTO inventory (sku, qty) VALUES ('A100', 10) ON CONFLICT (sku) DO UPDATE SET qty = inventory.qty + EXCLUDED.qty; MySQL: INSERT INTO inventory (sku, qty) VALUES ('A100', 10) ON DUPLICATE KEY UPDATE qty = qty + VALUES(qty); SQL Server and others use MERGE. A unique key on sku is required.
188

Create a simple stored procedure that returns orders for a customer.

Transactions and Advanced SQLMedium
Save to ▾
Show answer
MySQL: DELIMITER // CREATE PROCEDURE get_orders(IN p_customer INT) BEGIN SELECT order_id, order_date, amount FROM orders WHERE customer_id = p_customer ORDER BY order_date DESC; END // DELIMITER ; CALL get_orders(101); Parameters prevent SQL injection compared with building query strings in code.
189

How do you prevent SQL injection in applications?

Transactions and Advanced SQLMedium
Save to ▾
Show answer
Never build SQL by concatenating user input. Use parameterised queries or prepared statements, where input is sent separately from the SQL text, or ORMs that do this automatically. Validate and whitelist inputs such as sort column names, give application users least privilege, and avoid dynamic SQL in procedures unless parameters are bound. Input like ' OR 1=1 -- then cannot change the query logic.
190

Two users book the last seat at the same time and both succeed. How do you fix it?

Transactions and Advanced SQLMedium
Save to ▾
Show answer
This is a race condition. Wrap the check and booking in one transaction and lock the seat row with SELECT ... FOR UPDATE so the second transaction waits and then sees it is taken. Alternatively use an atomic update such as UPDATE seats SET booked_by = :user WHERE seat_id = :id AND booked_by IS NULL and check affected rows, plus a unique constraint as a final safety net.
191

You need to update 50 million rows in production. How do you do it safely?

Transactions and Advanced SQLMedium
Save to ▾
Show answer
Avoid one huge transaction that locks the table and fills the log. Test on a copy first, take a backup, and update in batches of a few thousand rows using a key range, committing each batch and pausing briefly to reduce load. Run during low traffic, monitor replication lag and locks, keep a rollback script, and verify counts after completion.
192

A business user needs a dashboard on a complex 6-table join that runs slowly. Would you use a view or a materialised view?

Transactions and Advanced SQLMedium
Save to ▾
Show answer
A normal view only simplifies the query; it still runs the full join every time, so performance does not improve. If the data can be a few minutes or hours old, a materialised view or summary table refreshed on schedule gives fast reads. If real-time data is required, tune the underlying query with indexes or build an incrementally maintained table.
193

Tell me about a production incident involving a database and how you handled it.

Transactions and Advanced SQLMedium
Save to ▾
Show answer
Situation: during a sale, order placement started failing with lock timeouts. Task: restore service quickly. Action: I identified a long-running reporting query holding locks on the orders table, coordinated with the team to kill it, and moved that report to the read replica. Result: orders recovered within ten minutes, and we added query timeouts and a rule that reports never run on the primary.
194

How do you explain a technical database issue to a non-technical manager?

Transactions and Advanced SQLMedium
Save to ▾
Show answer
Situation: my manager asked why a report was late every Monday. Task: explain and get approval for a fix. Action: I compared the database to a busy shop counter where reports and customers queue together, showed the timing data, and proposed a separate reporting copy. Result: he understood the trade-off in five minutes, approved the read replica, and Monday reports became on time.
195

What is MVCC and how does it improve concurrency?

Transactions and Advanced SQLHard
Save to ▾
Show answer
Multi-Version Concurrency Control keeps multiple versions of rows, so readers see a consistent snapshot without blocking writers, and writers do not block readers. PostgreSQL stores old row versions in the table and cleans them with VACUUM, while MySQL InnoDB uses undo logs. MVCC enables high concurrency in read-heavy systems, but long-running transactions keep old versions alive and cause bloat.
196

What is the difference between optimistic and pessimistic locking?

Transactions and Advanced SQLHard
Save to ▾
Show answer
Pessimistic locking locks rows when read, using SELECT ... FOR UPDATE, so other transactions wait; it suits high-conflict cases such as seat booking. Optimistic locking does not lock but checks at update time that the row has not changed, usually with a version column: UPDATE ... SET version = version + 1 WHERE id = 1 AND version = 5. If zero rows update, the application retries. It suits low-conflict workloads.
197

Create an audit trigger that logs salary changes.

Transactions and Advanced SQLHard
Save to ▾
Show answer
PostgreSQL: CREATE FUNCTION log_salary() RETURNS trigger AS $$ BEGIN IF NEW.salary <> OLD.salary THEN INSERT INTO salary_audit(emp_id, old_sal, new_sal, changed_at) VALUES (OLD.emp_id, OLD.salary, NEW.salary, NOW()); END IF; RETURN NEW; END; $$ LANGUAGE plpgsql; CREATE TRIGGER trg_salary AFTER UPDATE ON employees FOR EACH ROW EXECUTE FUNCTION log_salary();
198

Your application logs frequent deadlock errors at peak hours. How do you investigate and reduce them?

Transactions and Advanced SQLHard
Save to ▾
Show answer
Capture deadlock graphs or logs to see which statements and resources are involved. Common fixes are accessing tables and rows in the same order in all code paths, keeping transactions short, adding indexes so updates lock fewer rows, avoiding user interaction inside transactions, and using lower isolation where safe. Add retry logic for deadlock victims since some deadlocks cannot be fully eliminated.
199

A long-running report transaction is causing table bloat and blocking. What do you do?

Transactions and Advanced SQLHard
Save to ▾
Show answer
Long transactions hold snapshots and locks, preventing cleanup of old row versions and blocking DDL or writers. Move the report to a read replica, break it into smaller batches, use read committed snapshot where supported, and avoid leaving transactions open in client tools. Set statement or idle-in-transaction timeouts, and monitor sessions with long transaction durations.
200

How would you design data archiving for a table growing by 10 million rows a month?

Transactions and Advanced SQLHard
Save to ▾
Show answer
Partition the table by month so old partitions can be detached or moved to an archive table or cheaper storage without heavy deletes. Define a retention policy with business and compliance teams, for example 24 months online. Keep archived data queryable through a separate schema or warehouse, update indexes and statistics, and automate the process with monitoring and alerts.

Frequently asked questions

What jobs can I get after learning SQL?

SQL opens roles such as Data Analyst, MIS Analyst, SQL Developer, BI Developer, Database Administrator and Data Engineer, and it is a required skill for backend developers and many testers. In India, IT services companies, banks, fintech, e-commerce and analytics firms test SQL in most data-related interviews. Combining SQL with Excel, a BI tool like Power BI or Tableau, or Python greatly increases your job options.

Who can learn SQL and is coding experience needed?

Anyone comfortable with computers and basic logic can learn SQL. It is popular with B.Tech, BCA, MCA, B.Com, BBA, B.Sc and MBA students, and with working professionals in operations, finance, support and testing who want to move into data roles. No prior programming is needed, because SQL is declarative and reads close to English, though logical thinking and practice are essential.

How long does it take to learn SQL for interviews?

Most learners can write basic queries within two weeks of daily practice. Becoming interview-ready, including joins, aggregation, subqueries, CTEs and window functions, typically takes six to eight weeks with one to two hours a day. Solving 150 to 200 practice problems, building a small project and reviewing execution plans makes a strong difference in technical rounds.

Which database should I learn first: MySQL, PostgreSQL or SQL Server?

Core SQL is the same across all of them, so start with any one. MySQL is widely used in web applications and is simple to install, PostgreSQL is feature-rich and popular in startups and data teams, and SQL Server is common in enterprises and banks. Learn one deeply, then note dialect differences such as LIMIT versus TOP, date functions and upsert syntax.

Is an SQL certification useful?

Certifications from database vendors or cloud platforms can strengthen your resume, especially for DBA and data engineering roles in IT services companies. However, most interviewers test SQL live through coding problems, so practical skill matters most. A portfolio of solved problems, a project with real queries on a public dataset, and clear explanations of your logic usually create a stronger impression than a certificate alone.

Is SQL still relevant with AI tools that write queries?

Yes. AI assistants can draft queries, but professionals must check whether joins duplicate rows, whether NULLs and filters are handled correctly, and whether queries perform well on large data. Companies still test SQL heavily in interviews because wrong numbers in reports have real business impact. Strong SQL fundamentals also help you use AI tools faster and more safely.