Python · Advanced level

Advanced level · Module 3: Python and SQL Databases · Lesson 1 of 4

Relational Data and SQL Essentials

Design tables with primary and foreign keys and write core SQL statements.

In this lesson you will

  • Design two related tables
  • Write SELECT queries with WHERE, ORDER BY and JOIN

Key concepts

tableprimary keyforeign keyJOIN

The lesson

A relational database stores data in tables of rows and columns, with relationships between them: each sale refers to a product by its ID instead of repeating the product's name and price.

SQL is the language for working with these tables. CREATE TABLE defines structure; INSERT adds rows; SELECT ... WHERE ... ORDER BY retrieves them. A primary key identifies each row uniquely.

SQLite is a complete database in a single file that comes with Python — ideal for small applications and offline use.

Step by step

  1. Design products and sales tables on paper.
  2. Create them in SQLite with primary keys.
  3. Insert sample products and sales.
  4. Write a query for daily totals and one for the top five products.

Practice activity

Design products and sales tables for a shop and write queries for daily totals and the top five products.

Expected result: Correct schema and queries returning daily totals and top products.

Check your understanding

  1. What does a foreign key do?

    Show answer

    It links a row to a row in another table, such as a sale to its product.

Taught live by an instructor — online or in person. Free course lessons are for self-study and do not lead to a certificate — see registration options.