Local lab20 min+60 XP

Databases and SQL

Files are fine for small data. Real apps keep data in a database and ask it questions in SQL.

0% done

Run this room's code on your own computer in VS Code. You set this up in Module 07.

TASK 1

Learn it

Key idea

SQL commands create, add, read and change rows in database tables.

Key words
CREATE TABLEmakes a new tableINSERTadds a rowSELECTreads rowsUPDATE / DELETEchanges or removes rowsscores tablenamescoreNova128Kai95Mira140
The four main SQL commands and the scores table they work on.

A database stores data in tables of rows and columns, and lets many programs read and change it safely. SQL (Structured Query Language) is the language for asking a database questions.

Python includes SQLite, a small database that lives in a single file, with the sqlite3 module. Nothing to install.

The four main SQL commands are CREATE TABLE (make a table), INSERT (add rows), SELECT (read rows) and UPDATE/DELETE (change or remove rows). Always commit() to save changes.

Real life linkYour school's register, a shop's orders and your bank account all live in databases. SQL is the language used to ask them questions.
Example code
import sqlite3

con = sqlite3.connect("game.db")
cur = con.cursor()
cur.execute("DROP TABLE IF EXISTS scores")
cur.execute("CREATE TABLE scores (name TEXT, score INTEGER)")
cur.execute("INSERT INTO scores VALUES ('Nova', 128)")
cur.execute("INSERT INTO scores VALUES ('Kai', 95)")
con.commit()
for row in cur.execute("SELECT name, score FROM scores"):
    print(row)
con.close()
Output on your computer
('Nova', 128)
('Kai', 95)
TASK 2

Try it yourself

+10 XP

Add a third player, then select only the players with a score over 100, highest first.

Create a free account to start this lab

Sign in to write, run and check your code here. Your progress, XP and code are saved to your account. No card needed.

TASK 3

Capture the flag

+10 XP

Which SQL command reads rows from a table?

Create a free account to start this lab

Sign in to answer the flag question. Your progress, XP and code are saved to your account. No card needed.

HOMEWORK

Inventory database

+20 XP

Create inventory.db with a table items (name TEXT, qty INTEGER). Insert 4 items, update one quantity with UPDATE items SET qty = ? WHERE name = ?, delete one item, then print all remaining rows ordered by name.

Create a free account to start this lab

Sign in to do this homework and have it checked. Your progress, XP and code are saved to your account. No card needed.