IGCSE2.3Paper 2 — Algorithms, Programming and Logic

Databases and SQL

Storing records in a table, and the language used to ask questions of them.

YOU SHOULD BE ABLE TO

Define a single-table database with records and fields.
Explain the purpose of a primary key.
Choose appropriate data types for fields.
Write SQL scripts using SELECT, FROM, WHERE, ORDER BY, SUM and COUNT.

WRITE THE QUERY1/7

THE DATABASEopen the playground

ClassID
ClassName
Teacher

StudentID
FirstName
LastName
ClassID
DateOfBirth
House

GradeID
StudentID
Subject
Mark
TermNo

TASKList the FirstName and LastName of every student in Ariun house.

//The vocabulary

A TABLE holds the data. A RECORD is one row — all the information about one thing. A FIELD is one column — one piece of information. The PRIMARY KEY is the field that is unique for every record, so no two records can be confused.

//The SQL you must be able to write

The clause order is fixed: SELECT, then FROM, then WHERE, then ORDER BY. Finish with a semicolon.

SELECT Name, Mark
FROM   Results
WHERE  Mark > 50
ORDER  BY Mark DESC;

SELECT COUNT(*) FROM Results WHERE Passed = TRUE;
SELECT SUM(Mark) FROM Results;
SELECT * FROM Results WHERE Name = 'Bat';

//Choosing field data types

Text/varchar for names, integer for whole counts, real for measurements, Boolean for yes/no, date/time for dates. A phone number is TEXT, not a number — it can start with 0 and you never do arithmetic on it.

IN THE EXAM

Text values inside a WHERE go in single quotes: WHERE Name = 'Bat'.
* means every field. Only use it when the question asks for all fields.

CHECK YOURSELF

1.What makes a good primary key?

2.Which data type should store a phone number?

3.Which SQL returns names of students with a mark above 50, highest first?

File Handling Boolean Logic

CHECK YOURSELF

1.What makes a good primary key?

2.Which data type should store a phone number?

3.Which SQL returns names of students with a mark above 50, highest first?