lesson 28 Dates, strings & subqueries · intermediate

Subqueries

Use the result of one query inside another.

the idea

A subquery is a query inside another query. A scalar subquery returns one value, such as an overall average, that the outer WHERE can compare with. A correlated subquery refers to the outer row, so it is evaluated for each row, such as the average of that product’s own category.

Dataset: Retail

The tables the challenges use, with their first 6 rows. Under each column name: the kind of value it holds, and which table it links to.

worked example

Run these steps in order to see the idea before you write your own query. The step marked “the pattern” is what the challenges ask for. Running the example does not count toward progress.

1. 1

   Compute one benchmark value. Run this. The subquery returns a single average price for all merchandise.

   step-1.sql Run

   sql›

   SELECT ROUND(AVG(list_price), 2) AS avg_price
   FROM products
   WHERE is_merchandise = 1 AND list_price IS NOT NULL;

2. 2

   Find rows that match an extreme. Run this. WHERE list_price equals the scalar subquery’s MAX picks the most expensive merchandise item.

   step-2.sql Run

   sql›

   SELECT stock_code, description, list_price
   FROM products
   WHERE list_price = (
     SELECT MAX(list_price)
     FROM products
     WHERE is_merchandise = 1
   );

3. 3

   Put it together. The lesson's pattern in one query.the pattern

   example.sql Run

   sql›

   SELECT stock_code, list_price
   FROM products
   WHERE list_price = (
     SELECT MAX(list_price)
     FROM products
     WHERE is_merchandise = 1
   );

common mistake

Comparing with the overall average answers a different question from comparing with the product’s own category average.