SQL Basics
1. What is SQL? basic
SQL (Structured Query Language) is the standard language for reading and managing data in relational databases. With it you can select, insert, update and delete data, and create or change tables.
2. What is the difference between DDL, DML, DCL and TCL? basic
DDL (CREATE, ALTER, DROP) defines structure. DML (SELECT, INSERT, UPDATE, DELETE) works with the data. DCL (GRANT, REVOKE) controls permissions. TCL (COMMIT, ROLLBACK, SAVEPOINT) controls transactions.
3. What is the difference between DELETE, TRUNCATE and DROP? basic
DELETE removes selected rows and can be rolled back. TRUNCATE removes all rows quickly and keeps the table structure. DROP removes the whole table, including its structure.
4. What is a primary key? basic
A primary key uniquely identifies each row in a table. It cannot be NULL and cannot repeat. A table can have only one primary key, though it may be made of several columns.
5. What is a foreign key? basic
A foreign key is a column that refers to the primary key of another table. It enforces a link between the two tables, so you cannot add an order for a customer that does not exist.
6. What is the difference between a primary key and a unique key? basic
Both enforce uniqueness. A primary key cannot be NULL and there is one per table. A unique key can allow a NULL (depending on the database) and a table can have several.
7. What is the difference between WHERE and HAVING? basic
WHERE filters rows before grouping. HAVING filters groups after aggregation. Use WHERE for row conditions and HAVING for conditions on COUNT, SUM and similar.
8. What is the order of execution of a SQL query? basic
Logically: FROM and JOIN, WHERE, GROUP BY, HAVING, SELECT, DISTINCT, ORDER BY, then LIMIT. This is why you cannot use a SELECT alias in WHERE but can in ORDER BY.
9. What is the difference between CHAR and VARCHAR? basic
CHAR(n) is fixed length and pads with spaces. VARCHAR(n) is variable length and stores only what is needed. Use VARCHAR for most text.
10. What is NULL and how is it different from zero or an empty string? basic
NULL means unknown or missing. Zero is a number and an empty string is a text value of length zero. NULL is not equal to anything, even another NULL, so you must use IS NULL.
11. How do you filter NULL values? basic
Use WHERE column IS NULL or IS NOT NULL. Using = NULL returns nothing, because comparisons with NULL are unknown.
12. What does COALESCE do? basic
COALESCE(a, b, c) returns the first value that is not NULL. It is often used to replace NULL with a default, such as COALESCE(discount, 0).
13. What is the difference between DISTINCT and GROUP BY? basic
DISTINCT removes duplicate rows from the result. GROUP BY groups rows so you can calculate aggregates per group. For just unique values both can work, but only GROUP BY allows aggregates.
14. What are aggregate functions? basic
They calculate one value from many rows: COUNT, SUM, AVG, MIN and MAX. They ignore NULLs, except COUNT(*) which counts all rows.
15. What is the difference between COUNT(*), COUNT(column) and COUNT(DISTINCT column)? basic
COUNT(*) counts all rows. COUNT(column) counts rows where the column is not NULL. COUNT(DISTINCT column) counts unique non-NULL values.
16. What is the use of the LIKE operator? basic
LIKE matches text patterns. % matches any number of characters and _ matches one character. For example, name LIKE 'A%' finds names starting with A.
17. What is the difference between IN and EXISTS? basic
IN compares a value to a list or subquery result. EXISTS checks whether a subquery returns any row. EXISTS often performs better with large subqueries and handles NULLs more safely than NOT IN.
18. What does BETWEEN do? basic
BETWEEN a AND b checks whether a value is in a range, including both ends. For dates with time parts, be careful, because the end date at midnight excludes later times that day.
19. What is an alias in SQL? basic
An alias is a temporary name for a column or table, set with AS. It makes results easier to read and queries shorter, especially with joins.
20. How do you sort and limit results? basic
Use ORDER BY column DESC to sort, and LIMIT n (MySQL, PostgreSQL), TOP n (SQL Server) or FETCH FIRST n ROWS ONLY (Oracle and standard SQL) to limit rows.
Joins
21. What are the types of SQL joins? basic
INNER JOIN returns matching rows only. LEFT JOIN returns all rows from the left table plus matches. RIGHT JOIN does the reverse. FULL JOIN returns all rows from both. CROSS JOIN returns every combination.
22. What is the difference between INNER JOIN and LEFT JOIN? basic
INNER JOIN keeps only rows that match in both tables. LEFT JOIN keeps all rows from the left table and fills columns from the right table with NULL where there is no match.
23. How do you find rows in one table that have no match in another? intermediate
Use a LEFT JOIN and filter where the right key is NULL: FROM a LEFT JOIN b ON a.id = b.id WHERE b.id IS NULL. You can also use NOT EXISTS.
24. What is a self join and when do you use it? intermediate
A self join joins a table to itself using different aliases. It is used for hierarchies such as employee and manager, or for comparing rows within the same table.
25. What is a CROSS JOIN? basic
A CROSS JOIN returns the Cartesian product: every row of one table paired with every row of the other. It is used to generate combinations, such as all products for all months.
26. What is the difference between a join and a UNION? intermediate
A join adds columns by combining rows side by side based on a condition. A UNION stacks the results of queries on top of each other and needs the same number and type of columns.
27. What is the difference between UNION and UNION ALL? basic
UNION removes duplicate rows and is slower. UNION ALL keeps all rows including duplicates and is faster. Use UNION ALL when you know there are no duplicates or you want them.
28. Why does a join sometimes produce more rows than expected? intermediate
If the join key is not unique in one of the tables, each match multiplies rows. Check the key's uniqueness and the row count before and after the join, and aggregate or deduplicate before joining.
29. What is the difference between ON and WHERE in a LEFT JOIN? intermediate
A condition in ON is applied while joining, so unmatched left rows are kept. A condition on the right table in WHERE filters after the join and can turn the LEFT JOIN into an inner join.
30. What is a natural join and why is it risky? intermediate
A natural join automatically joins on columns with the same name. It is risky because adding a column with a matching name later can silently change the result. Explicit ON conditions are safer.
31. How do you join on a range, such as a date between start and end? advanced
Use a non-equi join: JOIN b ON a.date BETWEEN b.start_date AND b.end_date. Make sure ranges do not overlap, or rows will duplicate.
32. How do you join more than two tables? intermediate
Chain joins one after another, each with its own ON condition. Start with the main table and add others step by step, and check row counts to avoid accidental duplication.
Grouping and Aggregation
33. How do you find the total sales by region? basic
SELECT region, SUM(amount) AS total FROM sales GROUP BY region; Every non-aggregated column in SELECT must appear in GROUP BY.
34. How do you find duplicate records in a table? basic
SELECT email, COUNT(*) FROM users GROUP BY email HAVING COUNT(*) > 1; This lists values that appear more than once.
35. How do you delete duplicate rows but keep one? intermediate
Use ROW_NUMBER() OVER (PARTITION BY email ORDER BY id) in a CTE and delete rows where the number is greater than 1. Some databases need the delete to be done by joining on the ID.
36. How do you find customers with more than 3 orders? basic
SELECT customer_id, COUNT(*) FROM orders GROUP BY customer_id HAVING COUNT(*) > 3;
37. How do you calculate a percentage of total per group? intermediate
Divide the group sum by the overall sum using a window function: SUM(amount) * 100.0 / SUM(SUM(amount)) OVER () with GROUP BY, or use a subquery for the total.
38. What is GROUP BY ROLLUP? intermediate
ROLLUP adds subtotal and grand total rows to a grouped result. For example, GROUP BY ROLLUP(year, month) gives monthly rows, yearly subtotals and an overall total.
39. How do you do conditional aggregation? intermediate
Put a CASE inside the aggregate: SUM(CASE WHEN status = 'Returned' THEN amount ELSE 0 END). It lets you build several measures in one query, like a pivot.
40. How do you pivot rows into columns in SQL? intermediate
Use conditional aggregation with CASE WHEN for each column, or the PIVOT operator in SQL Server and Oracle. Conditional aggregation works in every database.
41. How do you find the second highest salary? basic
Use DENSE_RANK() OVER (ORDER BY salary DESC) and pick rank 2, or SELECT MAX(salary) FROM emp WHERE salary < (SELECT MAX(salary) FROM emp). The window approach also handles the Nth highest easily.
42. How do you find the highest salary in each department? intermediate
Use a window function: rank salaries within PARTITION BY dept ORDER BY salary DESC and keep rank 1. Or join to a subquery that gives MAX(salary) per department.
43. Can you use an alias in the HAVING clause? basic
Not in all databases. SQL Server and PostgreSQL do not allow it, while MySQL does. The safe way is to repeat the expression or use a subquery.
44. What is the difference between COUNT(1) and COUNT(*)? intermediate
They give the same result and perform the same in modern databases. Both count all rows.
Window Functions
45. What are window functions? intermediate
Window functions calculate values across a set of related rows without collapsing them into one row, as GROUP BY does. They use OVER (PARTITION BY ... ORDER BY ...).
46. What is the difference between ROW_NUMBER, RANK and DENSE_RANK? intermediate
ROW_NUMBER gives unique numbers 1, 2, 3 even for ties. RANK gives ties the same rank and skips the next numbers (1, 1, 3). DENSE_RANK gives ties the same rank without gaps (1, 1, 2).
47. What does PARTITION BY do? intermediate
PARTITION BY splits rows into groups for the window function, and the calculation restarts for each group. It is like GROUP BY but keeps all rows.
48. How do you calculate a running total? intermediate
SUM(amount) OVER (ORDER BY order_date). Add PARTITION BY to restart per customer or product. For exact control, use ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.
49. What are LAG and LEAD? intermediate
LAG(col, n) gets a value from n rows before the current row, and LEAD(col, n) from n rows after. They are used to compare with the previous or next period, such as month-over-month change.
50. How do you calculate month-over-month growth? advanced
Aggregate sales by month, then use LAG(total) OVER (ORDER BY month) to get the previous month, and calculate (total - prev) * 100.0 / prev. Handle the first month where prev is NULL.
51. How do you get the top 3 products in each category? intermediate
Rank products within each category using DENSE_RANK() OVER (PARTITION BY category ORDER BY sales DESC) in a CTE, then filter where the rank is 3 or less.
52. What is the difference between ROWS and RANGE in a window frame? advanced
ROWS counts physical rows. RANGE groups rows with the same ORDER BY value together, so ties are treated as one unit. This can change running totals when there are duplicate values.
53. What do FIRST_VALUE and LAST_VALUE do? intermediate
They return the first or last value in the window frame. Note that LAST_VALUE with the default frame returns the current row, so you must set the frame to UNBOUNDED FOLLOWING to get the true last value.
54. How do you calculate a 7-day moving average? advanced
AVG(sales) OVER (ORDER BY day ROWS BETWEEN 6 PRECEDING AND CURRENT ROW). Make sure there is one row per day, or fill missing dates first.
55. What is NTILE? intermediate
NTILE(n) divides ordered rows into n roughly equal groups and numbers them. For example, NTILE(4) creates quartiles, and NTILE(10) creates deciles.
56. How do you find consecutive days of activity (islands problem)? advanced
Number the dates with ROW_NUMBER() and subtract it from the date. Dates in a consecutive streak give the same result, so grouping by that value finds each streak.
57. How do you remove duplicates using a window function? advanced
Add ROW_NUMBER() OVER (PARTITION BY key_columns ORDER BY some_column) in a CTE and keep only rows where the number is 1.
58. How do you find the first purchase date for each customer? advanced
Use MIN(order_date) with GROUP BY customer_id, or FIRST_VALUE or ROW_NUMBER if you need other columns from that first order.
59. Can you use a window function in a WHERE clause? intermediate
No. Window functions are evaluated after WHERE. Put the window function in a subquery or CTE, then filter on its result in the outer query.
Subqueries and CTEs
60. What is a subquery? basic
A subquery is a query inside another query. It can return a single value, a list or a table, and is used in SELECT, FROM, WHERE or HAVING.
61. What is a correlated subquery? intermediate
A correlated subquery refers to columns of the outer query, so it runs once for each outer row. It can be slow on large tables, and is often replaceable with a join or window function.
62. What is a CTE and why use it? intermediate
A CTE (Common Table Expression) is a named temporary result defined with WITH. It makes long queries easier to read, lets you reuse the result and supports recursion.
63. What is the difference between a CTE and a subquery? intermediate
Both give a temporary result. A CTE is named, can be referenced several times and reads top to bottom, so it is cleaner. A subquery is inline. Performance is usually the same.
64. What is a recursive CTE? advanced
A recursive CTE refers to itself to process hierarchical data, such as an organisation chart or category tree. It has an anchor query and a recursive part joined with UNION ALL.
65. What is the difference between a CTE and a temp table? intermediate
A CTE exists only for one statement and is not stored. A temp table is stored for the session, can be indexed and reused across several statements. Use temp tables for heavy reuse.
66. What is the difference between a view and a table? intermediate
A view is a saved query that shows data from tables, and holds no data itself. A table stores the data. Views simplify queries and control access.
67. What is a materialized view? advanced
A materialized view stores the result of a query physically, so reads are fast. It must be refreshed to stay current. It is used for expensive summaries.
68. How do you find employees who earn more than the average salary? intermediate
SELECT * FROM emp WHERE salary > (SELECT AVG(salary) FROM emp);
69. Why can NOT IN give wrong results with NULLs? intermediate
If the subquery returns even one NULL, NOT IN returns no rows, because the comparison becomes unknown. Use NOT EXISTS or filter NULLs out of the subquery.
70. How do you find customers who bought in January but not in February? intermediate
Use NOT EXISTS or a LEFT JOIN anti-join between January customers and February customers, keeping rows where the February match is NULL.
Keys, Constraints and Design
71. What is normalisation and why is it used? basic
Normalisation organises data into related tables to remove repetition and prevent update errors. It follows rules called normal forms.
72. Explain 1NF, 2NF and 3NF. intermediate
1NF: each field holds one value and rows are unique. 2NF: 1NF plus no partial dependency on part of a composite key. 3NF: 2NF plus no dependency of non-key columns on other non-key columns.
73. What is denormalisation and when is it used? intermediate
Denormalisation adds redundant data on purpose to make reads faster by reducing joins. It is common in reporting and data warehouses, where read speed matters more than update simplicity.
74. What are constraints in SQL? basic
Constraints are rules on columns: NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, CHECK and DEFAULT. They keep data valid.
75. What is a star schema? intermediate
A star schema has a central fact table of measures (such as sales) linked to dimension tables (such as date, product, customer). It is simple and fast for reporting.
76. What is the difference between a fact table and a dimension table? intermediate
A fact table stores measurable events with numeric values and keys. A dimension table stores descriptive attributes used to filter and group the facts.
77. What is a snowflake schema? advanced
A snowflake schema is a star schema where dimensions are further normalised into sub-tables. It saves space but needs more joins.
78. What is a slowly changing dimension? advanced
It is a dimension whose attributes change over time, like a customer's city. Type 1 overwrites the old value, Type 2 adds a new row with dates to keep history, and Type 3 adds a column for the previous value.
79. What is a surrogate key? intermediate
A surrogate key is an artificial key, such as an auto-increment number, with no business meaning. It stays stable even when business values change.
80. What is a composite key? intermediate
A composite key is a primary key made of two or more columns that together identify a row, such as order ID plus line number.
81. What is an ER diagram? basic
An entity-relationship diagram shows tables (entities), their columns and how they relate, such as one-to-many. It is used to design and explain databases.
82. What are the types of relationships between tables? basic
One-to-one, one-to-many and many-to-many. Many-to-many relationships need a bridge (junction) table in between.
Transactions and Data Handling
94. What are ACID properties? intermediate
Atomicity (all or nothing), Consistency (data stays valid), Isolation (transactions do not interfere) and Durability (committed data survives failures).
95. What are COMMIT and ROLLBACK? basic
COMMIT saves the changes made in a transaction permanently. ROLLBACK undoes them. They are used to keep data safe when something fails.
96. What are transaction isolation levels? advanced
They control what one transaction can see of another's changes: Read Uncommitted, Read Committed, Repeatable Read and Serializable. Higher levels prevent more problems like dirty reads but reduce concurrency.
97. What is a deadlock? advanced
A deadlock happens when two transactions each wait for a lock held by the other, so neither can continue. The database detects it and cancels one. Keeping transactions short and accessing tables in the same order reduces it.
98. What is the difference between UPDATE and MERGE? intermediate
UPDATE changes existing rows. MERGE (upsert) can insert, update or delete in one statement depending on whether a match exists in the target table.
99. How do you copy data from one table to another? basic
INSERT INTO target SELECT ... FROM source; or CREATE TABLE new AS SELECT ... (syntax varies by database).
100. What is the difference between CAST and CONVERT? basic
Both change a data type. CAST is standard SQL and works everywhere. CONVERT is specific to SQL Server and MySQL and offers extra formatting options.
101. How do you extract the year and month from a date? intermediate
Use EXTRACT(YEAR FROM date) or YEAR(date) and MONTH(date), depending on the database. For month grouping, DATE_TRUNC('month', date) in PostgreSQL is handy.
102. How do you calculate the difference between two dates? intermediate
DATEDIFF(day, start, end) in SQL Server, end - start in PostgreSQL, and DATEDIFF(end, start) in MySQL. The syntax differs, so know your database.
103. What are CASE statements used for? intermediate
CASE WHEN condition THEN result ... ELSE default END adds if-then logic to queries. It is used to create categories, handle conditions in aggregates and build pivots.
104. What is the difference between SQL and NoSQL? basic
SQL databases store data in tables with fixed schemas and use joins. NoSQL databases store data in other forms, such as documents or key-value pairs, with flexible schemas, and often scale across servers more easily.
Practical SQL Interview Questions
105. Write a query to find the top 5 customers by total spend. intermediate
SELECT customer_id, SUM(amount) AS total FROM orders GROUP BY customer_id ORDER BY total DESC LIMIT 5;
106. Write a query to find customers who have never placed an order. intermediate
SELECT c.* FROM customers c LEFT JOIN orders o ON c.id = o.customer_id WHERE o.id IS NULL;
107. Write a query to find employees who earn more than their manager. intermediate
Self join: SELECT e.name FROM emp e JOIN emp m ON e.manager_id = m.id WHERE e.salary > m.salary;
108. Write a query to find the month with the highest sales in each year. advanced
Aggregate sales by year and month in a CTE, rank months in each year by total with RANK() OVER (PARTITION BY year ORDER BY total DESC), and keep rank 1.
109. Write a query to calculate the retention of customers month to month. advanced
Take each customer's first order month as the cohort, then count distinct customers from that cohort who order in each following month, and divide by the cohort size.
110. Write a query to find the median of a column. advanced
Use PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY col) where supported. Otherwise number the rows with ROW_NUMBER and take the middle one or the average of the two middle ones.
111. Write a query to find the cumulative percentage of sales by product. intermediate
Calculate each product's sales, then SUM(sales) OVER (ORDER BY sales DESC) * 100.0 / SUM(sales) OVER (). This is the basis of an ABC or Pareto analysis.
112. Write a query to count orders per day, including days with no orders. intermediate
Create a calendar table or generate dates, LEFT JOIN orders to it, and use COUNT(order_id), which gives zero where there are no orders.
113. Write a query to find users who logged in on three consecutive days. advanced
Use LAG or LEAD to check that the previous two days exist, or use the date minus ROW_NUMBER trick to find streaks of 3 or more.
114. Write a query to swap two column values or to change gender codes using one update. intermediate
Use a CASE in the UPDATE: UPDATE t SET gender = CASE WHEN gender = 'M' THEN 'F' WHEN gender = 'F' THEN 'M' END; All right-hand sides read the old values.
115. Write a query to get the Nth highest salary. intermediate
SELECT salary FROM (SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS r FROM emp) t WHERE r = N LIMIT 1;
116. Write a query to get year-to-date sales for each month. intermediate
Aggregate by month, then SUM(total) OVER (PARTITION BY year ORDER BY month) to get a running total that restarts each year.
