Page tree
Skip to end of metadata
Go to start of metadata

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:

Pupils
NameGradeParentAddressNumber of behaviour warnings
Noah11Michael Wilson72 Oxford Street  
Kim8Ed Thumpson67 York Street2
Viktor3Brian Carter16 Darlinghurst Street 
James9Helen Wright59 Paul Street1
Liam5Karen Collins82 Newtown Park 
Sandra6Karen Collins82 Newtown Park 

In the query, you'll list the fields first and the table name, using this format:

Structure of a SQL query
SELECT Parent, Address
FROM Pupils
  • 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:

SELECT DISTINCT Parent, Address
FROM Pupils

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?

SELECT SUM(Warnings)
FROM Pupils

The result will have a single row, because all records are collapsed into one by the "SUM" operator.

SUM
3

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:

SELECT *
FROM Pupils
WHERE Grade < 11

This will return only pupils who are in grade 10 or less. It is possible to compound criteria:

SELECT *
FROM Pupils
WHERE Grade < 11 AND (Warnings = 1 OR Warnings IS NULL)

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:

SELECT *
FROM Pupils
WHERE Name IN (SELECT Name FROM Marks WHERE Mark = 'A')

The subquery finds all the marks 'A', then finds all pupils who had those marks.

So you've learnt the following keywords and operators:

  • WHERE
  • OR, AND
  • IS NULL
  • IN

Step 3 - Sorting results (ORDER BY)

You need to specify the list of fields to sort by in the footer:

SELECT Name, Grade, Parents
FROM Pupils
ORDER BY Grade ASC

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:

SELECT Name, Grade, Parents
FROM Pupils
ORDER BY Grade ASC, Name DESC

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:

SELECT Parents, COUNT(*)
FROM Pupils
GROUP BY Parents

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.

Done!

(tick) You're a SQL master now. You know about:

  • SELECT
  • FROM
  • WHERE, OR, AND, IN, IS NULL
  • GROUP BY ... ASC/DESC
  • ORDER BY

Take this quizz to check you've understood it all!

  • No labels