Question 1

Subquery with IN

Accepted Answer

The subquery returns department ids where budget > 100000 - only Eng (id=20). The outer query then keeps employees whose dept_id is in that set - only Bob (dept_id=20). Subquery with IN is equivalent to a JOIN but is often more readable when you only need data from one table in the final result.

Question 2

Scalar subquery in WHERE

Accepted Answer

The subquery AVG(salary) = (90000+75000+82000)/3 = 82333.33. The outer query keeps employees whose salary exceeds that average. Only Alice (90000) qualifies. Carol (82000) is below the average, Bob (75000) is well below. A subquery in WHERE that returns a single value is called a scalar subquery.

Question 3

Subquery with NOT IN

Accepted Answer

The subquery returns ('Alice', 'Dave'). NOT IN excludes employees whose name appears in that list. Alice is excluded. Bob and Carol are not in the managers list and are returned. Dave is in managers but not in employees, so he is irrelevant to the outer query.

Question 4

Derived table must be aliased

Accepted Answer

In standard SQL (and all major databases), every derived table - a subquery used in the FROM clause - must be given a name via AS alias. The fix: add an alias, e.g. ) AS totals at the end. Without it, the database cannot reference the result set and raises a syntax error. PostgreSQL, MySQL, and SQL Server all enforce this. The alias is then used to qualify columns if needed: totals.customer.