Interview Q&A / SQL & DBMS

1. What is the difference between WHERE and HAVING?

   WHERE filters individual rows before they are grouped, so it cannot use aggregate functions like SUM or COUNT. HAVING filters groups after GROUP BY has combined the rows, so it is where conditions on aggregates go.

   BeginnerIn-depth3 min read
2. How does GROUP BY work with more than one column?

   GROUP BY with several columns makes one group for each unique combination of those column values, and aggregates are computed inside each combination.

   BeginnerQuick answer1 min read
3. How is a window function different from GROUP BY?

   GROUP BY collapses rows into one row per group. A window function computes an aggregate or rank over a set of related rows but keeps every original row in the output.

   BeginnerQuick answer1 min read
4. Why does SELECT with GROUP BY fail when you pick a column that is not grouped?

   After grouping, each group becomes one output row, so a column that is neither in GROUP BY nor inside an aggregate has many possible values and the database cannot pick one. Group by it or aggregate it.

   BeginnerQuick answer1 min read
5. Write a query to list departments that have more than 5 employees.

   Group by department and filter groups with HAVING COUNT(*) > 5. WHERE cannot be used because the count exists only after grouping.

   BeginnerQuick answerCoding1 min read
6. Can you use HAVING without GROUP BY?

   Yes. Without GROUP BY, the whole table is treated as one group, so HAVING can filter that single aggregate row. If the condition is false, the query returns no rows.

   IntermediateQuick answer1 min read
7. What does GROUP BY ROLLUP do?

   ROLLUP adds subtotal and grand-total rows to a grouped result. For GROUP BY ROLLUP(a, b) you get rows for (a, b), then a subtotal per a, then one grand total, with NULL marking the rolled-up columns.

   IntermediateQuick answer1 min read
8. Write a query to find the highest-paid employee in each department.

   Compute MAX(salary) per department in a subquery and join back, or use RANK() OVER (PARTITION BY dept ORDER BY salary DESC) and keep rank 1. Both return all tied employees.

   IntermediateQuick answerCoding1 min read