Subqueries & Set Operations — Series 2

Preview — 3 of 10 questions

What does this query return for each customer?

javascript

SELECT
  c.name,
  (SELECT COUNT(*) FROM orders o WHERE o.customer_id = c.id) AS order_count
FROM customers c;

Aname, and the total count of all orders in the entire orders table, repeated for every customer

BAn error, because subqueries cannot appear in the SELECT clause

Cname, and a count of orders for that specific customer, computed per row via a correlated scalar subquery

Dname and order_count, but only for customers who have at least one order

What does this query return?

javascript

SELECT name FROM products
WHERE price > ANY (SELECT price FROM products WHERE category = 'Budget');

AProducts priced higher than at least one Budget product — equivalent to being priced above the minimum Budget price

BProducts priced higher than every single Budget product

CAn error — ANY requires a scalar subquery, not multiple rows

DProducts in the Budget category priced above average

Using ALL instead of ANY in the same query, what changes?

javascript

SELECT name FROM products
WHERE price > ALL (SELECT price FROM products WHERE category = 'Budget');

AIt returns the same result as ANY, since ALL and ANY are synonyms in PostgreSQL

BIt returns products priced lower than all Budget products

CIt requires an explicit GROUP BY to work

DIt now returns products priced higher than every Budget product — equivalent to being priced above the maximum Budget price

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