Structured query language (SQL)

Computer Science · Databases. The idea in plain words, the rules to remember, the mistake to avoid and a worked example, written and checked by Mega Lecture.

Test this topic free All Computer Science topics

The idea

SQL is the language used to ask a database for data. A request is called a query. The basic query has three parts: SELECT lists the fields to show, FROM names the table, and WHERE gives the condition a record must meet to be included.

Conditions can be joined with AND or OR. The results can be sorted with ORDER BY. SUM and COUNT give one number instead of a list of records.

To find the output of a query, work in this order: test every record against the WHERE condition, keep the ones that pass, sort them if asked, then show only the fields named after SELECT.

Rules to remember

Order of clauses: SELECT fields FROM table WHERE condition ORDER BY field;
SELECT chooses the fields (columns). WHERE chooses the records (rows).
AND: a record must meet both conditions. OR: a record must meet at least one condition.
ORDER BY Price ASC sorts from smallest to largest. ORDER BY Price DESC sorts from largest to smallest.
SUM(field) adds up the values in a numeric field for the matching records.
COUNT(field) gives the number of matching records.
Text values go in quotes, e.g. WHERE Type = 'Pen'. Numbers do not.

Common mistake

Students mix up COUNT and SUM: COUNT says how many records match, SUM adds their values. Another common error is showing a field from the WHERE clause instead of the field named after SELECT.

Worked example

Table BOOK holds these records (BookID, Genre, Price, Copies): B1, Crime, 8.00, 3; B2, Poetry, 5.50, 0; B3, Crime, 6.25, 7; B4, Travel, 9.00, 2. What is output by SELECT SUM(Copies) FROM BOOK WHERE Genre = 'Crime' OR Price > 8.50;

Genre = 'Crime' is true for B1 and B3.
Price > 8.50 is true for B4 only (9.00). B1 at 8.00 does not pass this test, but it is already included.
OR keeps records that pass at least one test: B1, B3 and B4.
SUM adds their Copies: 3 + 7 + 2 = 12.

Answer: 12

Ask about this

Type what you are stuck on. The helper finds the explanation, worked examples and the notes to read, free and instant.

Keep going

← Single-table databases and primary keys Logic gates →Take a Computer Science test