>_ IzzyLab

Hands-on task

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

SQLite: INNER JOIN of two tables

medium~7 min

The database /root/shop.db has tables customers(id, name) and orders(id, customer_id, amount). Write a query with an INNER JOIN that, for each order, outputs the customer's name and the order amount, and save the result to /root/out.txt.

The result set must include only customers that have orders (a customer with no orders must produce no rows). The default row format is name|amount.

What you'll practise:
  • sqlite3
  • inner join
  • on clause
  • table joins
  • foreign key

Topic breakdown

The relational model stores related data across separate tables, and a join operation brings them back together. INNER JOIN takes rows from two tables and keeps only those pairs where the ON join condition on a common field holds (usually the foreign key of one table equals the primary key of the other). Rows with no match on the other side do not appear in an inner join — that is what distinguishes it from a LEFT JOIN. The query result is redirected to a file with the usual >.

What's checked

  1. the result has name|amount pairs for all orders
  2. customer with no orders is not in the result

What it looks like