Interview Q&A / SQL & DBMS

1. Write an SQL query to find the second highest salary.

   Find the highest salary that is less than the maximum, with SELECT MAX(salary) FROM employees WHERE salary < (SELECT MAX(salary) FROM employees). For the Nth highest, use DENSE_RANK() so that duplicate salaries share a rank, or ORDER BY DISTINCT salary with LIMIT and OFFSET.

   BeginnerIn-depthCoding3 min read
2. What happens when a subquery used with = returns more than one row?

   The database raises an error such as "subquery returns more than 1 row", because = expects a single value. Use IN, ANY or ALL for multiple values, or make the subquery return one row with an aggregate or LIMIT.

   BeginnerQuick answer1 min read
3. What is a derived table (subquery in FROM)?

   A derived table is a subquery in the FROM clause that acts like a temporary table for that one statement. It must have an alias, and it is useful for filtering or aggregating before joining.

   BeginnerQuick answer1 min read
4. What is a CTE and how is it different from a subquery?

   A CTE (WITH clause) names a query at the top of a statement so it can be read like a step and reused several times. Results are the same as a subquery, but CTEs read better and also support recursion.

   IntermediateQuick answer1 min read
5. What is the difference between a correlated and a non-correlated subquery?

   A non-correlated subquery runs once and does not refer to the outer query. A correlated subquery refers to a column of the outer row, so it is evaluated once for each outer row, at least logically.

   IntermediateQuick answer1 min read
6. What is the difference between IN and EXISTS?

   IN compares a value against a list of values returned by a subquery. EXISTS only checks whether the subquery returns any row and stops at the first match. EXISTS handles NULLs more safely, especially with NOT.

   IntermediateQuick answer1 min read