Databases & SQL
A database stores tables
- A database keeps data in tables, like a spreadsheet.
- Each row is a record; each column is a field.
- We ask questions in a language called SQL.
SELECT: choose columns
SELECTpicks which columns to show.FROMnames the table to read.*means "all columns".
SELECT name, grade FROM student;
WHERE: filter rows
WHEREkeeps only the rows whose condition is true.- Put text in single quotes:
'A'.
SELECT … WHERE, visualized
WHERE keeps only matching rows; SELECT then keeps only the chosen columns.
SELECT name FROM student WHERE grade = 'A';
ORDER BY: sort the results
ORDER BYsorts the rows by a column.- Add
DESCfor largest first, orASC(the default) for smallest first.
SELECT name, age FROM student ORDER BY age DESC;
AND and OR: two conditions
ANDkeeps a row only when both conditions are true.ORkeeps a row when at least one is true.
SELECT name, age FROM student WHERE grade = 'A' AND age >= 17;
COUNT and SUM
COUNT(*)counts the rows that match;SUM(field)adds up a number field.- Name the answer with
AS, so its column has a clear heading.
SELECT COUNT(*) AS n FROM student WHERE grade = 'A';
In the exam: SQL
- IGCSE uses
SELECT,FROM,WHERE,ORDER BY,SUMandCOUNT, withAND,ORand the comparisons=,<>,<,>,<=,>=. - The primary key is a field whose value is different in every record, like
idhere. - To work out what a query shows, read it as FROM → WHERE → SELECT → ORDER BY.
SELECT Name, Grade
FROM Student
WHERE Grade = 'A';
Common mistakes
- Text values go in single quotes:
WHERE name = 'Ann'. SELECTchooses the columns;WHEREfilters the rows.- Show only the fields the question asks for: filtering on
gradedoes not meangradebelongs inSELECT. - "How many" is
COUNT; "total" isSUM.
Now you try
- Write one SQL query for each task.
- Press Check answer to run it against the table.
רמת קושי 3/5 · 200 מטבעות + בונוס קטן בהשלמה הראשונה היום
Show the name of every student. (Use SELECT name FROM student;.)
לחץ על הרץ כדי לראות את התוצא כאן.
רמת קושי 3/5 · 200 מטבעות + בונוס קטן בהשלמה הראשונה היום
Show the name וגם grade of every student whose grade הוא A. (Text goes in single quotes: 'A'.)
לחץ על הרץ כדי לראות את התוצא כאן.
רמת קושי 3/5 · 200 מטבעות + בונוס קטן בהשלמה הראשונה היום
Show the name of every student, oldest first (sort by age in descending order).
לחץ על הרץ כדי לראות את התוצא כאן.
רמת קושי 3/5 · 200 מטבעות + בונוס קטן בהשלמה הראשונה היום
Show the name of every student whose grade הוא A וגם whose age הוא under 17. Show only the name field.
לחץ על הרץ כדי לראות את התוצא כאן.
רמת קושי 3/5 · 200 מטבעות + בונוס קטן בהשלמה הראשונה היום
In one query, show how many students there are as n, ו סה"כ of their merits as total.
לחץ על הרץ כדי לראות את התוצא כאן.