Querying with SQL
| English | Chinese | Pinyin |
|---|---|---|
| SQL | 结构化查询语言 | jié gòu huà chá xún yǔ yán |
| query | 查询 | chá xún |
| record | 记录 | jì lù |
| field | 字段 | zì duàn |
| condition | 条件 | tiáo jiàn |
Asking a database questions
- SQL 结构化查询语言 (Structured Query 查询 Language) picks out the records 记录 you want.
- A few keywords do almost everything at IGCSE.
- Read a query in a fixed order to work out its output.
SELECT, FROM, WHERE
SELECT FirstName, FormClass
FROM Student
WHERE FeesPaid = TRUE;
SELECT= which fields 字段 to show (use*for all).FROM= which table.WHERE= a condition 条件, so only matching records appear.
Read an SQL query in this order: FROM (which table), WHERE (which rows), SELECT (which fields), ORDER BY (sort).
SELECT picks fields, FROM names the table, WHERE sets the condition
Run a SELECT … WHERE query
WHERE filters the rows to those that match; SELECT then keeps only the columns you chose. Step through to watch the table shrink to the query's answer.
In an SQL query, SELECT specifies:
SELECT chooses the fields; FROM the table; WHERE the rows; ORDER BY the sort.
The WHERE clause:
WHERE filters rows by a condition.
ORDER BY, AND, OR
SELECT FirstName, DateOfBirth
FROM Student
WHERE FormClass = '10A' AND FeesPaid = FALSE
ORDER BY DateOfBirth ASC;
ORDER BYsorts:ASC(smallest/A→Z first) orDESC(largest/Z→A first).- Join conditions with
AND(both true) orOR(at least one true).
A range check accepts sensible values and rejects the rest
ORDER BY mark DESC sorts the results:
DESC = descending (largest/Z→A first); ASC = ascending.
SUM, COUNT, and reading order
COUNTcounts matching records;SUMadds up a number field.- To work out a query's output, read it in this order:
- FROM (which table) → WHERE (keep matching rows) → SELECT (show chosen fields) → ORDER BY (sort).
SELECT COUNT(StudentID) FROM Student WHERE FeesPaid = FALSE;
Field types: text, number, date/time and boolean
Match each SQL keyword to what it does.
SELECT picks columns, WHERE filters rows, ORDER BY sorts, COUNT tallies the matching rows.
Put the SQL clauses in the order you read them to find the output.
FROM → WHERE → SELECT → ORDER BY gives you exactly which rows and columns appear.
You've got it
SELECT(fields) ·FROM(table) ·WHERE(condition);*= all fieldsORDER BY … ASC/DESCsorts;AND/ORjoin conditionsCOUNTcounts rows;SUMtotals a number field- read a query: FROM → WHERE → SELECT → ORDER BY