>_ IzzyLab

Hands-on task

This task is step 18 of the course “SQL with SQLite”

SQLite: orders above average (subquery)

medium~7 min

The database /root/shop.db has a table orders(id, amount). Find the orders whose amount is greater than the average amount across all orders, and write their id (one per line) to /root/out.txt.

What you'll practise:
  • sqlite3
  • subquery
  • avg
  • scalar select
  • where

Topic breakdown

A scalar subquery is a nested SELECT that returns exactly one value, which can be plugged directly into the outer query's condition. It is the classic technique of "comparing each row against a summary metric over the whole table": an aggregate (AVG, MAX, etc.) is computed in the nested query, and the outer WHERE compares each row against it. A plain WHERE without a subquery cannot express this — a whole-table aggregate is not available there.

What's checked

  1. the file lists exactly the orders above the average

What it looks like