Step 1 - The basics (SELECT, FROM)
Christmas is coming, and soon we'll all have to manage greeting cards. Don is the headmaster of a high school and he wants to send greeting cards to pupils' parents. He builds a list of pupils.
In SQL, tables have a name and several columns:
|Name||Grade||Parent||Address||Number of behaviour warnings|
|Noah||11||Michael Wilson||72 Oxford Street|
|Kim||8||Ed Thumpson||67 York Street||2|
|Viktor||3||Brian Carter||16 Darlinghurst Street|
|James||9||Helen Wright||59 Paul Street||1|
|Liam||5||Karen Collins||82 Newtown Park|
|Sandra||6||Karen Collins||82 Newtown Park|
In the query, you'll list the fields first and the table name, using this format:
- SELECT and FROM are keywords. They are in every SQL query.
- SELECT defines the list of fields you want to see. In our situation, we don't use the name of the child, so we only select the last two columns. There's the special wildcard of "*" (the star) to select all fields of the table.
- FROM defines the list of tables.
The result of this SQL query will be:
Aggregations (DISTINCT, SUM)
Notice the mother "Karen" has two children, therefore her name appears twice in the list. SQL literally does what you tell it to do. That's where we introduce the DISTINCT keyword:
Using DISTINCT, all records which are exactly the same will be de-duplicated.
Another set of operators are aggregation operators. How many warnings have been given to pupils about their behaviour?
The result will have a single row, because all records are collapsed into one by the "SUM" operator.
There's more information about the SELECT clause in the child page: The SELECT keyword. It is also possible to put more than one table in the FROM clause: See the FROM keyword.
Step 2 - Criteria (the WHERE clause)
Don now wants to send a dedicated mail to pupils reaching the age for the high school diploma. We need a criteria:
This will return only pupils who are in grade 10 or less. It is possible to compound criteria:
Note the use of AND, OR, and IS NULL. In SQL, NULL means the value is unknown, therefore you can't use = NULL (see this StackOverflow question).
The last keyword is "IN". It selects all elements in a sublist, and you can make a subquery. Look:
The subquery finds all the marks 'A', then finds all pupils who had those marks.
So you've learnt the following keywords and operators:
- OR, AND
- IS NULL
Step 3 - Sorting results (ORDER BY)
You need to specify the list of fields to sort by in the footer:
Will returns all pupils, ordered by ascending grade. The keyword ASC is the default and can be omitted. It is also possible to sort according to two columns:
Step 4 - Aggregations (GROUP BY)
Sometimes you want to group two rows or more. For example, Don the headmaster may want to list the number of children per parent:
The COUNT operator counts the number of records. To count the number of records per parent, this query:
- Selects the Parents field
- Uses the GROUP BY clause
Here's the result:
When you use GROUP BY, you must have the same list of columns in the SELECT clause.
You're a SQL master now. You know about:
- WHERE, OR, AND, IN, IS NULL
- GROUP BY ... ASC/DESC
- ORDER BY
Take this quizz to check you've understood it all!