Querying with SQL
Introduced| English | Meaning |
|---|---|
| SQL/ˌes kjuː ˈel/ | Structured Query Language, used to define, query and manipulate data in relational databases. |
| query/ˈkwɪərɪ/ | A request to retrieve or manipulate information, especially data in a database. |
| record/ˈrekɔːd/ | A stored collection of information about an item, person or event. |
| field/fiːld/ | A named item of data within a database record. |
| condition/kənˈdɪʃn/ | A test controlling a choice or repetition; in a query, WHERE keeps records for which the condition is true. |
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:
Difficulty 1/5 · 100 coins + a small bonus on first completion today
SELECT chooses the fields; FROM the table; WHERE the rows; ORDER BY the sort.
The WHERE clause:
Difficulty 1/5 · 100 coins + a small bonus on first completion today
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:
Difficulty 1/5 · 100 coins + a small bonus on first completion today
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;
Each field in a record holds one data type
Match each SQL keyword to what it does.
Difficulty 2/5 · 140 coins + a small bonus on first completion today
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.
Difficulty 3/5 · 200 coins + a small bonus on first completion today
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