Worksheet

F7.3 SQL: SELECT

Files and databases · GCSE · OCR J277 2.2.3, AQA 8525 3.7.2 · about 15 min

What this lesson is about

SELECT, FROM, WHERE and ORDER BY on the class's robot runs.

• SELECT and FROM
• WHERE
• ORDER BY
• Using the answers in Python

Questions 5 marks in all

1. [1 mark]Which SQL keyword names the table to search?

   1. AFROM
   2. BSELECT
   3. CWHERE
   4. DORDER BY

2. [1 mark]Put the parts of a query in order.

   Number the lines 1 to 4 to put them in the right order.

   1. ORDER BY cm DESC
   2. WHERE task = 'wall'
   3. SELECT run_id, cm
   4. FROM runs

3. [1 mark]What does SELECT * FROM robots return?

   1. AEvery field of every record in robots
   2. BOnly the first record
   3. CThe number of robots
   4. DNothing: * is not allowed

4. [1 mark]Runs have cm values 42, 80, 38, 45.5, 76.5 and 81. Which match WHERE cm > 76.5?

   Tick every answer that is true.

   1. A80
   2. B81
   3. C76.5
   4. D45.5

5. [1 mark]Complete the query so it lists the longest runs first: SELECT run_id, cm FROM runs ORDER BY cm ____

The task: the longest square runs

Write one SQL query that finds the run_id and cm of every square run longer than 77 cm, longest first, and print each result as run 6: 81.0 cm. Then drive forward the distance of the shortest of those runs, divided by 4.

# the two lines every program starts with: the commands, then the robot
from bugbot import *
connect()

import logbook
db = logbook.open_db()

Plan your program here, then type it in and press Run.

Do it on the robot
www.bugbotlab.com/learn/f7-3-sql-select/
The simulator checks it and tells you when it passes. Nothing to install, no account.

Challenges

1. Find every run that took less than 5 seconds, fastest first.
2. Find the robots whose colour is not green.
3. Write the Python loop that does the same job as SELECT run_id FROM runs WHERE cm > 50. How many lines is it?