Java SQL fundamentals
Writing a correct query on a whiteboard, which service-based rounds ask for directly. Joins and grouping come up far more often than tuning.
5 concepts · 14 interview questions
What this topic covers
Every concept in sql fundamentals, and the questions each one gets asked as. Where a question links, it has a full write-up.
Joins
Inner keeps only matches; outer keeps unmatched rows from one side and fills the other with NULL. Which side you keep is the entire question.
- What is the difference between an INNER JOIN and a LEFT JOIN?
- What does a CROSS JOIN produce, and when is it deliberate?
- How do you find rows in A with no match in B?
GROUP BY and aggregates
Collapses rows into groups so aggregates can be computed, with HAVING filtering the groups after aggregation rather than before.
- What is the difference between WHERE and HAVING?
- Why must every non-aggregated column appear in GROUP BY?
- How does COUNT(*) differ from COUNT(column)?
NULL and three-valued logic
NULL means unknown, not empty, so comparisons return unknown rather than true or false — and that propagates through the whole query.
- Why is NULL = NULL not true?
- How do you compare a column against NULL correctly?
- What does an aggregate do with NULL values?
Subqueries, CTEs and window functions
Ways to compute an intermediate result: a subquery inline, a CTE named up front, or a window function that aggregates without collapsing rows.
- Write a query to find the second-highest salary.
- What is the difference between a correlated and an uncorrelated subquery?
- When does a window function beat a GROUP BY?
DELETE, TRUNCATE and DROP
Three ways to remove data with different transactional behaviour, different speed, and different recoverability.
- What is the difference between DELETE, TRUNCATE and DROP?
- How do you update rows based on another table?