Learn
9.3 | PRIMARY KEYS
01 | A TABLE IS USEFUL WHEN YOU CAN ASK QUESTIONS
A library stores hundreds of book records. A student wants fiction, while the librarian needs the number of available copies. Reading every row manually would be slow. A database query describes the answer required so the DBMS can retrieve or calculate it.
SQL means Structured Query Language. It is a language used to give instructions to a relational database. In this lesson, every query reads one table. A query result is the data returned by that request, not a new version of the stored table.
02 | MEET THE BOOKS TABLE
Our table is named Books. Each record describes one catalogue title, with Copies storing the number of available copies. BookID is its primary key. Category is text, and Copies is an INTEGER.
Keep this table beside the examples. SQL uses its exact table and field names. A displayed result may show fewer columns or records than this original table.
| BookID | Title | Category | Copies |
|---|---|---|---|
| B001 | The Coding Club | Fiction | 3 |
| B002 | Ocean Science | Non-fiction | 2 |
| B003 | A Journey to Mars | Fiction | 4 |
| B004 | Robots at Work | Non-fiction | 1 |
| B005 | The Hidden Island | Fiction | 0 |
03 | SELECT FIELDS FROM A TABLE
SELECT names the fields to return. FROM names the table containing them. Read this as “return Title and Copies from Books”. The comma separates field names; it is not placed before FROM.
The query returns all five records because it has no filtering condition. Only Title and Copies are displayed. BookID and Category remain stored even though they are absent from this result.
SELECT Title, Copies FROM Books;
| Title | Copies |
|---|---|
| The Coding Club | 3 |
| Ocean Science | 2 |
| A Journey to Mars | 4 |
| Robots at Work | 1 |
| The Hidden Island | 0 |
04 | THE STAR AND THE STATEMENT
SELECT * requests all fields. The star does not mean “multiply” in this position. Use named fields when the question asks for particular columns.
SQL keywords are conventionally uppercase to make a query easy to read. Line breaks help organise the clauses. A semicolon terminates the statement. Names and text matching can depend on the DBMS, so copy the provided identifiers and values carefully.
Without ORDER BY, SQL does not guarantee the order of returned rows. The unsorted worked tables here follow source order for ease of checking.
SELECT * FROM Books;
05 | WHERE CHOOSES RECORDS
WHERE gives a condition that each record must satisfy. To find fiction, compare its Category value with the text value Fiction. Text literals are enclosed in single quotation marks. Field names are not quoted as text literals.
B001, B003 and B005 match. B002 and B004 do not. The result still shows only the fields listed after SELECT.
SELECT Title, Copies FROM Books WHERE Category = 'Fiction';
| Title | Copies |
|---|---|
| The Coding Club | 3 |
| A Journey to Mars | 4 |
| The Hidden Island | 0 |
06 | NUMERIC CONDITIONS AND BOUNDARIES
For numeric comparisons, write the number without quotation marks. Copies > 2 means strictly greater than two, so two does not qualify. Copies >= 2 includes two. The boundary changes the result.
Use = for equal to, <> for not equal to, < and > for strict comparisons, and <= or >= when equality is included. SQL uses = for a comparison here, rather than the pseudocode assignment arrow.
SELECT Title, Copies FROM Books WHERE Copies > 2;
| Title | Copies |
|---|---|
| The Coding Club | 3 |
| A Journey to Mars | 4 |
07 | AND REQUIRES BOTH CONDITIONS
Suppose the student needs fiction with at least one available copy. Category alone includes The Hidden Island, which has zero. Copies alone includes non-fiction. AND keeps a record only when both conditions are true for that same record.
The Coding Club and A Journey to Mars qualify. The Hidden Island fails the availability condition, even though it is fiction.
SELECT Title FROM Books WHERE Category = 'Fiction' AND Copies > 0;
| Title |
|---|
| The Coding Club |
| A Journey to Mars |
08 | OR ACCEPTS EITHER CONDITION
OR keeps a record when at least one condition is true, including when both are true. It does not mean “exactly one”. In this example every fiction title qualifies, and any non-fiction title with more than one copy also qualifies.
Ocean Science is included because Copies is 2. Robots at Work fails both conditions. A row matching both conditions appears once, not twice.
SELECT Title FROM Books WHERE Category = 'Fiction' OR Copies > 1;
| Title |
|---|
| The Coding Club |
| Ocean Science |
| A Journey to Mars |
| The Hidden Island |
09 | ORDER BY ARRANGES THE RESULT
ORDER BY names the field used to sort the returned rows. Ascending order puts smaller numbers first, text in the database’s ascending text order, and earlier dates first. Descending reverses that direction.
The standard SQL spellings are ASC and DESC. These implement the syllabus’s ascending and descending ordering. Put ORDER BY after WHERE when both clauses are needed.
This query filters fiction first, then sorts by Copies from largest to smallest. Sorting does not alter stored values. If values tie, their relative order is not guaranteed unless another sort field is specified.
SELECT Title, Copies FROM Books WHERE Category = 'Fiction' ORDER BY Copies DESC;
| Title | Copies |
|---|---|
| A Journey to Mars | 4 |
| The Coding Club | 3 |
| The Hidden Island | 0 |
10 | SUM ADDS VALUES, COUNT COUNTS RECORDS
An aggregate combines values from several rows into one result. SUM(Copies) adds the numeric Copies values: 3 + 2 + 4 + 1 + 0 = 10. It does not count the number of book titles.
COUNT(*) counts records. Five records means five catalogue titles, not ten copies. COUNT(Copies) counts non-NULL Copies values, rather than adding them. All five are present here, including zero, so it also returns 5.
A zero value is present data, not NULL. With missing values, COUNT(field) can differ from COUNT(*). SUM ignores NULL values; keep that distinction separate from the elementary arithmetic examples.
SELECT SUM(Copies) FROM Books; SELECT COUNT(*) FROM Books;
| Query expression | Single result |
|---|---|
| SUM(Copies) | 10 |
| COUNT(*) | 5 |
| COUNT(Copies) | 5 |
11 | FILTER BEFORE CALCULATING
To total fiction copies, WHERE restricts the records used by SUM. The relevant values are 3, 4 and 0, so the answer is 7. Counting those same records gives 3, including the title with no available copies.
Without grouping, these aggregate queries each return a single summary value rather than one output row per matching book. The exact displayed heading depends on the DBMS.
SELECT SUM(Copies) FROM Books WHERE Category = 'Fiction'; SELECT COUNT(*) FROM Books WHERE Category = 'Fiction';
| Expression on fiction records | Result |
|---|---|
| SUM(Copies) | 7 |
| COUNT(*) | 3 |
12 | BUILD A QUERY AND SEE ITS OUTPUT
Choose a result, a filter and a sorting direction. Read the generated SQL before checking the output. The fixed Books table above supplies the data. For summary queries, sorting is omitted because the result is one value.
Before running, predict which records survive the filter. Then predict the columns, order or summary value. This is a teaching simulation of these fixed query choices.
13 | READ A QUERY IN A RELIABLE ORDER
Find the source table in FROM. Check each row against WHERE, including the exact comparison boundary and whether conditions use AND or OR. Then identify the requested fields or calculation in SELECT. Finally apply ORDER BY if supplied.
This is a method for predicting the answer, not a claim about the DBMS’s physical execution steps. Write the requested field headings and values, rather than copying complete source records when only Title is requested.
When completing a query, translate the requirement carefully: “at least” uses >=; “more than” uses >; “both” usually needs AND; “either” uses OR. Confirm whether the task asks for records, their count, or a total.
14 | PRACTISE COMPLETING THE REQUEST
The librarian asks for non-fiction titles with at least two copies, ordered from fewest to most copies. Begin with the required output fields, use the exact table name, combine the restrictions, and choose ascending order.
SELECT Title, Copies FROM Books WHERE Category = 'Non-fiction' AND Copies >= 2 ORDER BY Copies ASC;
| Title | Copies |
|---|---|
| Ocean Science | 2 |
15 | KEEP THE COMMON MISTAKES SEPARATE
SELECT chooses columns; WHERE chooses records. FROM names the table. ORDER BY arranges the result, rather than deciding which rows qualify. SUM adds values; COUNT counts records or non-NULL field values.
A query returning no matching rows is not necessarily incorrect: its conditions may simply match none of the stored records. COUNT(*) then returns 0. SUM with no matching values generally returns NULL, not an automatic zero.
These SELECT queries retrieve information and do not edit the original table. Check names, quotation marks, condition boundaries and aggregate choice before checking the final output.