SQL, files and random numbers

GCSE Computer Science, Programming, 20 minutes

Read SELECT queries with WHERE and ORDER BY, store data in files, and use random numbers.

What you will learn

Work out what a SELECT query returns
Choose the right query for a request
Explain reading and writing files
Give the range of a random number

SQL queries

A database table stores records (rows) made of fields (columns).

SQL asks questions of it.

SELECT Name, Score
FROM Pupils
WHERE Club = 'Chess' AND Score > 50
ORDER BY Score DESC

SELECT names the fields to show (* means all of them).
FROM names the table.
WHERE keeps only the records that meet the condition. AND needs both conditions; OR needs either.
ORDER BY sorts: ASC (the default) is smallest or A first, DESC is largest or Z first.

Conditions use the same comparisons as code: =, <> (not equal), <, >, <= and >=. Text values go in quotes, such as 'Chess'. ORDER BY Name puts names into alphabetical order.

A worked example with this table, Pupils:

Name	Club	Score
Omar	Chess	64
Bea	Drama	81
Kit	Chess	45
Ada	Chess	90

The query above keeps the records where Club = 'Chess' AND Score > 50: Omar (64) and Ada (90). Kit is in Chess but 45 is not more than 50; Bea is not in Chess.
ORDER BY Score DESC puts the highest score first: Ada, then Omar.
SELECT Name, Score shows just those two fields: Ada 90, then Omar 64. The query returns 2 records.

Practise SQL for real: SELECT, WHERE and ORDER BY on Brainlag Code.

The rest of this lesson and its 7 practice questions are locked.

In this lesson

SQL queries
Files and random numbers
Try it: Run a query in your head

7 practice questions, marked as you go; about 20 minutes in all.

Key terms

Table, Records, Fields, SELECT, FROM, WHERE, ORDER BY, File

In this unit: Programming

Data types, operators, selection, loops, arrays, strings, subroutines, robust programs, SQL and files, read and traced step by step.

All of Programming

Back to the unit