SQL Foundations Lesson 1 of 7

SQL SELECT: your first query

25 min, 4 exercisesOCR 2.2.3AQA 3.7.2

Learn

Databases, tables and rows

Every app you use is secretly a pile of tables. Your messages, your playlist, your high scores: rows in a database, waiting to be asked for.

A database is a collection of tables. A table looks like a spreadsheet:

  • each column is one kind of fact (title, genre, year)
  • each row is one thing (one game)

The difference from a spreadsheet is that you don't scroll and click. You ask, using a language called SQL (say it "S-Q-L" or "sequel", people fight about this, nobody wins).

Learn

Selecting everything

The most basic question is "show me everything in this table":

SELECT * FROM games;

Read it out loud: "select everything from games". SELECT says what columns you want, * means "all of them", and FROM games says which table. The semicolon ends the statement, like a full stop.

A few things worth knowing early:

  • SQL keywords are not case sensitive. select * from games works too. Writing keywords in CAPITALS is just a habit that makes queries easier to read.
  • Table names must match what exists. Ask for FROM gamez and you get an error saying there is no such table. That is the database being literal, not you being bad at this.
  • The result of a query is itself a little table. That idea comes back again and again.

The sample tables for each lesson are shown above the editor. The === setup code that built them is ordinary SQL too, and by the end of this track you will be able to read all of it.

GCSE exam boards
  • OCR 2.2.3 Strings, files, arrays, sub programs, SQL
  • AQA 3.7.2 SQL

GCSE Python path The GCSE lesson

Try

Run it: every game

The query in the editor runs against this lesson's tables (open Tables above the editor to see them). Run it, change the WHERE or the columns, run it again.

Tables games, players, charts
games 7 rows, 5 columns
idtitlegenreyearrating
1Minecraftsandbox20119.0
2Among Usparty20187.5
3Hollow Knightplatformer20179.5
4Stardew Valleyfarming20169.0
5Fall Guysparty20207.0
6Celesteplatformer20189.0
7Goat Simulatorchaos20146.5
players 5 rows, 3 columns
idgamertagcountry
1NoScopeNanUK
2xX_Toast_XxCanada
3LagIsMyFaultAustralia
4QuietKeyboardIreland
5BrbSnacksUSA
charts 5 rows, 4 columns
positiontitleartistweeks
1Glow UpKai Neon6
2Paper Planes AgainThe Lanterns3
3Bus Stop RainMira Vale11
4Level SelectNova Kid1
5Static HeartsLow Orbit8
Show the SQL that built them
CREATE TABLE games (id INTEGER PRIMARY KEY, title TEXT, genre TEXT, year INTEGER, rating REAL);
INSERT INTO games VALUES
 (1, 'Minecraft', 'sandbox', 2011, 9.0),
 (2, 'Among Us', 'party', 2018, 7.5),
 (3, 'Hollow Knight', 'platformer', 2017, 9.5),
 (4, 'Stardew Valley', 'farming', 2016, 9.0),
 (5, 'Fall Guys', 'party', 2020, 7.0),
 (6, 'Celeste', 'platformer', 2018, 9.0),
 (7, 'Goat Simulator', 'chaos', 2014, 6.5);
CREATE TABLE players (id INTEGER PRIMARY KEY, gamertag TEXT, country TEXT);
INSERT INTO players VALUES
 (1, 'NoScopeNan', 'UK'),
 (2, 'xX_Toast_Xx', 'Canada'),
 (3, 'LagIsMyFault', 'Australia'),
 (4, 'QuietKeyboard', 'Ireland'),
 (5, 'BrbSnacks', 'USA');
CREATE TABLE charts (position INTEGER, title TEXT, artist TEXT, weeks INTEGER);
INSERT INTO charts VALUES
 (1, 'Glow Up', 'Kai Neon', 6),
 (2, 'Paper Planes Again', 'The Lanterns', 3),
 (3, 'Bus Stop Rain', 'Mira Vale', 11),
 (4, 'Level Select', 'Nova Kid', 1),
 (5, 'Static Hearts', 'Low Orbit', 8);
example.sql to run
-- Everything from the games table
SELECT * FROM games;

Practice

Show the players table

There is a second table called players. Show every row and every column from it.

exercise_1.sql to run
-- Ask for everything from the players table
SELECT * FROM

Practice

Write a lowercase query

Show every row and column of the games table, but write the whole query in lowercase letters to prove to yourself that SQL does not care.

exercise_2.sql to run
-- Your query here, in lowercase

Break/fixFix the bug

No such table

This query should show every player, but it stops with an error instead. Run it, read the error, and fix it.

exercise_3.sql to run
SELECT * FROM player;

Your turn

Show this week's chart

A music app keeps this week's top five in a third table. Find its name in the tables above the editor and show every row and column of it.

On your own. Hints open after your first check.

exercise_4.sql to run
-- Your query here
SQLite