SQL basics with SELECT, WHERE and ORDER BY

Four short tasks on the first SQL keywords. Put the clauses in order, match each keyword to its job, pick the right query for eight questions and fill in the gaps, with answers from a real table.

Exercise25 minBachelor +2CC BY 4.0
4 tasks · 25 min

What you will learn

  • Know the order of the clauses in a SELECT query
  • Choose the query that answers a question
  • Complete a query with the right keywords
  • Filter with LIKE and IN and limit the number of rows

SQL (Structured Query Language) is the language for asking a database questions. A query names the columns you want (SELECT), the table they come from (FROM) and, if you like, which rows to keep (WHERE) and in which order to show them (ORDER BY).

The tasks use a table called customers with the columns name, city and country. Try the queries for real in the SQL sandbox: its office supplies shop has exactly this table, with eight customers (and two more columns, id and company). The exercise has four tasks and takes about 25 minutes.

A worked example

Question: "Which customers live in Germany, in alphabetical order?"

Customers in Germany, A to Z
SELECT name, city
FROM customers
WHERE country = 'Germany'
ORDER BY name;

Read it as a sentence: show name and city, from the customers table, only where the country is Germany, sorted by name. In the shop database it returns three rows: Anna Berger (Hamburg), Emma Schulz (Hamburg) and Farid Haddad (Berlin). Text values such as 'Germany' go in single quotes; numbers do not.

Common mistakes

  • Text without quotes, or with the wrong ones. Write 'Germany' in single quotes. In standard SQL, double quotes are for names of columns and tables.

  • Upper and lower case in values. In SQLite, country = 'germany' finds nothing, because the data says 'Germany'. Keywords like SELECT can be written in any case.

  • Clauses in the wrong order. ORDER BY before WHERE is an error. The order block above is the order SQL expects.

  • A count in WHERE. Conditions on COUNT, SUM or AVG go into HAVING.

  • = NULL. An empty value is found with IS NULL, never with = NULL.

For fast finishers

Run these in the sandbox (Office supplies shop) and note the answers:

  1. Which customers live in a city that starts with B?

  2. How many different cities are in the table?

  3. Which customers do not live in Germany? Sort them by name.

  4. Which customers work for a company with "Trading" in its name?

Extension solutions
  1. Farid Haddad (Berlin): SELECT name, city FROM customers WHERE city LIKE 'B%';

  2. Seven: SELECT COUNT(DISTINCT city) FROM customers; (Hamburg appears twice.)

  3. Ben Okafor, Carla Mendes, David Lee, Greta Nilsson and Hannah Wolf: SELECT name FROM customers WHERE country <> 'Germany' ORDER BY name;

  4. Anna Berger and Emma Schulz, both at Northwind Trading: SELECT name, company FROM customers WHERE company LIKE '%Trading%';

Teacher notes

How to run it. Show the worked example in the sandbox first, then learners do the four tasks alone. Learners who finish open the sandbox and try the extension. With a class link (unfold "Use it with a class" below the exercise) you collect a result code from each learner and see the class average per task in Class results.

Typical mistakes. WHERE placed after ORDER BY; LIKE '%Ham' for "starts with"; AND used where IN or OR is needed; GROUP BY typed as one word in the blanks (the gap expects "GROUP BY" with a space).

Follow-up questions.

  • Why does SQL need both WHERE and HAVING?

  • What happens with ORDER BY country, name? (Sorted by country, and by name within each country.)

  • How would you find customers with no city entered?

Differentiation. Easier: give the order of clauses on a card and skip the HAVING questions. Harder: continue with the first two shop tasks in the sandbox, which check the answer automatically, then with the joins exercise.

Use it with a class
  1. 1. Copy the class link and share it with your class.
  2. 2. Learners finish the exercise and get a result code.
  3. 3. Paste the codes into Class results to see everyone at once.
Change the tasks in the builder

Reuse this material

CC BY 4.0

You may copy, adapt and share it, also commercially, as long as you credit ManyPI and link back.

"SQL basics with SELECT, WHERE and ORDER BY" by ManyPI (https://manypi.com/edu/sql-basics-exercise), licensed under CC BY 4.0 (https://creativecommons.org/licenses/by/4.0/)