Subqueries & Set Operations

Preview — 3 of 10 questions

What makes the following subquery a correlated subquery?

javascript

SELECT e.name, e.salary
FROM employees e
WHERE e.salary > (
  SELECT AVG(e2.salary)
  FROM employees e2
  WHERE e2.department_id = e.department_id
);

AIt uses the AVG aggregate function inside the subquery

BIt uses table aliases

CThe subquery is placed in the WHERE clause

DThe subquery references a column (e.department_id) from the outer query

Which query is generally more efficient when checking existence, especially when the subquery could return NULLs?

AWHERE id IN (SELECT user_id FROM orders WHERE status = 'active')

BBoth are always identical in performance

CWHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id AND o.status = 'active')

DWHERE id = ANY (SELECT user_id FROM orders WHERE status = 'active')

Given two tables table_a and table_b, which set operator returns only rows that appear in table_a but not in table_b?

javascript

-- Customers who never placed an order
SELECT id FROM customers
EXCEPT
SELECT customer_id FROM orders;

AEXCEPT

BINTERSECT

CUNION

DDIFFERENCE

Answer all 10 questions (7 more), see explanations for every answer, and track your score.