SQL interview questions for freshers

01What is the difference between WHERE and HAVING?

WHERE filters rows before grouping. HAVING filters groups after GROUP BY, so it can use aggregates like SUM or COUNT.

02In what order does SQL process a query?

FROM and JOIN, WHERE, GROUP BY, HAVING, SELECT, DISTINCT, ORDER BY, then TOP or LIMIT. That is why a SELECT alias cannot be used in WHERE.

03What is the difference between DELETE, TRUNCATE and DROP?

DELETE removes chosen rows and supports WHERE. TRUNCATE quickly removes all rows but keeps the table. DROP removes the table completely.

04Primary key vs unique key?

A primary key identifies each row, cannot be NULL and there is only one per table. A table can have several unique keys, and they usually allow NULL.

05How does NULL behave in comparisons?

NULL means unknown, so NULL = NULL is not true. Use IS NULL. COUNT(column) skips NULLs, COUNT(*) counts every row.

06UNION vs UNION ALL?

UNION removes duplicates (extra work). UNION ALL keeps every row and is faster.

07Explain INNER, LEFT and FULL OUTER JOIN.

INNER returns matching rows only. LEFT returns all left rows plus matches. FULL returns all rows from both sides, with NULLs where there is no match.

08How do you find customers with no orders?

LEFT JOIN orders and keep rows where the order side is NULL, or use NOT EXISTS.

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

09Why can a LEFT JOIN act like an INNER JOIN?

A WHERE filter on a right-table column removes the NULL rows. Move that condition into the ON clause.

10EXISTS vs IN?

EXISTS stops at the first match and handles NULLs safely. NOT IN returns nothing if the subquery has a NULL, so prefer NOT EXISTS.

11How do you find duplicate rows?

Group by the columns that should be unique and keep groups with COUNT(*) > 1.

SELECT email, COUNT(*)
FROM customers
GROUP BY email
HAVING COUNT(*) > 1;

12Find the second highest salary.

Use DENSE_RANK so ties are handled correctly.

SELECT DISTINCT salary FROM (
  SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS r
  FROM employees) t
WHERE r = 2;

13ROW_NUMBER vs RANK vs DENSE_RANK?

For ties: ROW_NUMBER gives 1,2,3. RANK gives 1,1,3. DENSE_RANK gives 1,1,2.

14How do you get the latest record per customer?

ROW_NUMBER partitioned by customer, ordered by date descending, keep row 1.

15How do you write a running total?

SUM as a window function ordered by date.

SUM(amount) OVER (ORDER BY order_date ROWS UNBOUNDED PRECEDING)

16What is a CTE?

A named subquery written with WITH. It makes long queries readable and can be recursive.

17What is a correlated subquery?

A subquery that uses a value from the outer query, so it runs per row. A join or window function is often faster.

18What is an index?

A sorted structure that lets the database find rows without scanning the whole table. It speeds up reads and slightly slows writes.

19What does SARGable mean?

A filter that can use an index. Wrapping the column in a function, like YEAR(order_date) = 2026, usually breaks it. Use a date range instead.

20What is a star schema?

A central fact table with measures, joined to dimension tables like date, customer and product. It is simple and fast for reporting.