A3.3.2 · Database programming
Querying with SQL
By the end you can
Construct SELECT queries across two tables using WHERE, JOIN, ORDER BY, LIKE and more.
A asks the database a question. Almost all of them are written with SELECT. Data is only useful in context, and a query is how you find it: filtering to what you want, matching patterns, putting it in order, and combining tables.
SELECT, FROM, WHERE, ORDER BY#
The pattern is SELECT columns FROM table WHERE condition ORDER BY column. Use * for all the columns.
- WHERE keeps only the rows that meet a condition, like an if statement:
Price > 20,City = 'Leeds'. Join conditions withAND(both true),OR(either) andNOT. - BETWEEN a AND b is inclusive of both ends. IN ('a', 'b') matches any value in a list. IS NULL finds empty values (you cannot use
= NULL). - LIKE matches a pattern:
%stands for any run of characters,_for exactly one.LIKE '%Data%'finds any title containing Data. - DISTINCT removes duplicates. ORDER BY sorts:
ASC(the default) orDESC, on one or several columns.
JOIN: using more than one table#
Normalised data lives in several tables, so queries have to bring them together. A pairs up rows of two tables where a column matches, usually a foreign key against the primary key it points at:
SELECT Book.Title, Author.Name FROM Book INNER JOIN Author ON Book.AuthorID = Author.AuthorID;
- INNER JOIN (or just JOIN): only the rows with a match in both tables. A book with no author, or an author with no books, disappears.
- LEFT JOIN: every row of the left table, with matching right-hand data where it exists, and NULL where it does not. Use it to find “books with no author” (a WHERE on the NULL).
- RIGHT JOIN: the same for the right table: every author, even with no books.
- When two tables have a column of the same name, write
Table.Column. You can join a third table by adding another JOIN.
Author
| AuthorIDPK | Name | Country |
|---|---|---|
| 1 | Maya Okafor | Nigeria |
| 2 | Liam Chen | China |
| 3 | Sofia Rossi | Italy |
| 4 | Arun Patel | India |
| 5 | Noor Haddad | Egypt |
5 rows. AuthorID INT, Name TEXT(40), Country TEXT(20).
Your turn#
List the title and price of every Fiction book, cheapest first.
Hint
WHERE for the genre, ORDER BY for the price.
Show each city only once, however many customers live there.
Show only books costing less than 10.
Show every title that contains the word “Letters”.
Show the most expensive books first.
Show each book with the name of its author, which is in another table.
0 of 5 matched
Check yourself
Queries and joins
Quick questions, made fresh every time and marked here. Do a few until they feel easy.
Exam practice
Paper 1-style questions
5 questions · 15 marks
Write your answer as you would in the exam, then show the mark points and tick each one your answer made. These are our own questions in the style of the exam, not past-paper questions.
1.A table Item(ItemID, ItemDesc, ItemCost, ArtistID) holds goods for sale. Construct a query that lists the description and cost of every item costing more than 300, most expensive first.
[3]Construct: Draw or write it out (a table, a diagram, a program).
2.Tables Item(ItemID, ItemDesc, ItemCost, ArtistID) and Artist(ArtistID, ArtistName, ArtistEmail) are linked by ArtistID. Construct a query that lists each item’s description with its artist’s name, for items costing less than 100.
[4]Construct: Draw or write it out (a table, a diagram, a program).
3.Explain what the condition WHERE ArtistName LIKE '%work%' does, and the meaning of the % symbol.
[3]Explain: Give reasons: say what happens AND why, or how one thing causes another.
4.Distinguish between an INNER JOIN and a LEFT JOIN.
[3]Distinguish: Make the differences between two things clear.
5.State what the operators DISTINCT and ORDER BY do in a SELECT statement.
[2]State: Give a short answer, no explanation needed.
In a nutshell
- A SELECT statement reads like a sentence: SELECT the columns FROM a table WHERE a condition holds, ORDER BY a column.
- WHERE filters rows with comparisons (=, <>, <, >, <=, >=), BETWEEN, IN, LIKE (with % and _), IS NULL, and AND, OR, NOT. DISTINCT removes duplicates.
- A JOIN combines tables on a matching column, usually a foreign key matching a primary key: ON Book.AuthorID = Author.AuthorID.
- INNER JOIN keeps matches only. LEFT JOIN keeps every row of the left table; RIGHT JOIN every row of the right; unmatched columns are NULL.
- Write Table.Column when two tables have a column with the same name.