SQL for Cambridge IGCSE: Single-table Databases and Queries

Hello students, welcome to Kwickprep. In your Computer Science exam, you may get a table and an unfinished SQL script. Can you fill the gaps and predict the output? Today we will learn fields, records, keys, data types, validation, and the six SQL words your syllabus needs. Kwickprep is independent, and is not endorsed by any board, so always check your current syllabus.

Here is a single-table database for a school library, with a table called books. The first record is Wings of Fire, priced three hundred fifty rupees. The second is Malgudi Days at one hundred ninety nine. The third is Gitanjali at one hundred fifty. The fourth is Panchatantra at two hundred fifty.

Let us name the parts, because the exam uses these exact words. A table holds data about one type of thing, like books. A field is one column, holding one item of data, like the price. A record is one row, holding all the data about one book. Your syllabus only needs databases made of a single table.

Every table needs a primary key. A primary key is a field whose value is unique for every record. No two records can have the same primary key value. Here Book ID is the key, because two different books could have the same title. Names and prices make poor keys, because they can repeat.

Each field is given a data type, which says what kind of data it can store. Text, also called alphanumeric, stores letters, digits and symbols. Character stores exactly one character. Boolean stores only two values, such as true or false, or yes or no. Integer stores whole numbers. Real stores numbers with a decimal point. Date and time store dates or times.

Exams often ask you to choose the best data type for each field. A phone number should be text, because it may start with zero and we never add phone numbers. The number of pages is an integer. Whether a book is issued is Boolean, because the answer is yes or no. The date borrowed is date and time.

Wrong data can enter the table, so we add validation. Validation is an automatic check that data is sensible and allowed before it is stored. A range check makes sure a value lies between limits, like a price from one to five thousand. A length check tests the number of characters, like exactly three for Book ID. A presence check stops a field being left empty. A format check tests a pattern, like one letter followed by two digits.

There are two more checks to know. A type check makes sure the data is the right type, like a number for price. A check digit is an extra digit worked out from the other digits, as in an ISBN code on a book. But remember, validation only proves data is reasonable, not that it is correct. Checking that data was copied correctly is called verification, such as typing it twice.

Exams may ask you to write a validation check as pseudocode. This loop asks for the price. It keeps repeating until the price is at least one and at most five thousand. So an entry of zero is rejected, and the user must type the price again.

Now SQL, which stands for Structured Query Language. A query is a question we ask the database. Select lists the fields to show. From names the table. Where gives the condition a record must meet. Only books priced more than two hundred appear, so we get Wings of Fire and Panchatantra.

The condition in where uses comparison operators. A single equals sign means equal to. Less than followed by greater than means not equal to. Greater than and less than compare sizes. Greater than or equal to, and less than or equal to, also include the value itself. And, or, join two conditions together.

Order by sorts the results. A S C means ascending, smallest first, and it is the default. D E S C means descending, largest first. Here we want books under three hundred rupees, most expensive first. So Panchatantra comes first and Gitanjali comes last.

Sum adds up the values of a numeric field across the records. Here there is no where, so all four books are added. Three hundred fifty, plus one ninety nine, plus one fifty, plus two fifty, gives nine hundred forty nine.

Count tells how many records match, and the star means count whole records. Pause and predict. How many books cost more than one hundred eighty rupees? The answer is three, because only Gitanjali is cheaper.

Many questions give a script with gaps to fill. Start by reading the question and noting which fields and which records it wants. After select, write the fields to show, in the order asked. After from, copy the table name exactly as printed. Then write the condition after where, and the sort after order by.

Here is a typical question. The task is to show the title and Book ID of books costing one hundred ninety nine or more, in alphabetical order of title. Try filling the three gaps yourself now. Pause the video if you need a moment.

Here is the answer. The fields are title, then Book ID, in the order the question asked. The table is books. The sort is order by title, ascending. Notice that Malgudi Days costs exactly one hundred ninety nine, and greater than or equal to includes it. Alphabetical order puts M before P before W.

Here are common mistakes that lose marks. Showing the fields in a different order from the question. Forgetting quotes around text values, so always write Gitanjali inside quotes. Missing the commas between field names. And using sum when the question asks how many, which needs count.

Let us revise what we learned today. A field is a column, a record is a row, and the primary key is unique for every record. Choose each data type carefully, such as text for phone numbers. Validation checks include range, length, type, presence, format and check digit. And the SQL words to master are select, from, where, order by, sum and count. Practise with past paper tables and write the output before checking.