Skip to content

Managing Data with SQL: Exercises

Jump to bottom
Jonathan edited this page Jun 20, 2023 · 10 revisions

Exercise 1: SELECT

In SQL Online, type the following query and click "Run":

SELECT *
FROM FOOD_NAME

Exercise 2: WHERE

What if we want to get only specific entries from our data? The WHERE clause can be used after the FROM clause to add conditions to what data is retrieved, like so:

SELECT *
FROM FOOD_NAME
WHERE column operator value;
  • column contains the value we want to test
  • operator is the type of comparison (e.g., =, <, >, <=, >=)
  • value is the value we want to match (e.g., a number)

Only data where the WHERE clause is true will be retrieved.

Exercise 2A

Refine the query from Exercise 1 using a WHERE clause to SELECT only food with a FoodID of 5, i.e. FoodID = 5

Query from Exercise 1:

SELECT *
FROM FOOD_NAME

Exercise 2B

Design a query to SELECT foods with FoodIDs up to and including 7.

Exercise 2C

Design a query to SELECT foods in the poultry food group (FoodGroupID = 5), with a food source ID of 28.

Exercise 2D

Comparison operators can also be used with text, for example, using = to search for an exact text match (including capitalization): Use the condition FoodDescription = 'Corn fritter' to SELECT only the food with the description of 'Corn fritter'

Exercise 3: Matching text with LIKE

Exercise 3A

Create a query to retrieve (SELECT) data that contains the word apple anywhere in its FoodDescription column.

Exercise 3B

Create a query to retrieve data from FoodGroupID =‌ 19 (Sweets) whose FoodDescription starts with dessert.

Exercise 4: ORDER BY

Use this query as a starting point 👇:

SELECT FoodID, FoodGroupID, FoodSourceID, FoodDescription
FROM FOOD_NAME
WHERE FoodDescription LIKE "%apple%";

Exercise 4A

Reuse the query with LIKE (above) to sort retrieved data by FoodID, from highest to lowest.

Exercise 4B

Reuse the query with LIKE (above) to Sort retrieved data in ascending order on FoodDescription.

Exercise 5: GROUP BY

How can we use GROUP BY determine how many foods exist in each food group? What function should we use? What column should we use for grouping?

Modify the query below to use an SQL function and a GROUP BY clause to display the number of foods in each food group (i.e., for each FoodGroupID).

SELECT FoodGroupID 
FROM FOOD_NAME

Hints

  • The COUNT(*) function can be used to count the number of data entries (rows) that have been included in a group.

Exercise 6: JOIN

In the left pane of the SQLite IDE, expand each of the following tables to view their columns:

  1. NUTRIENT_NAME
  2. NUTRIENT_AMOUNT Which column do they have in common that would allow us to JOIN them on matching values?

Write a query to join the NUTRIENT_NAME and NUTRIENT_AMOUNT tables to get the nutrient breakdown (nutrient names and nutrient values) for food ID 20 (brie cheese).

Exercise 7: Table aliases

Rewrite the query from EXERCISE 6 to use aliases nn and na for the NUTRIENT_NAME and NUTRIENT_AMOUNT tables.

Stretch Exercise(s)

More than one JOIN

Let's get a bit more practice joining tables:

  1. Take a look at the FOOD_NAME, REFUSE_AMOUNT and REFUSE_NAME tables
  2. How can these tables be joined?
  3. Select a food you like.
  4. Write a query to print the amounts of each type of refuse (waste) produced by a portion of that food.
  5. Each line should list: the food name; the type of refuse; and the amount of refuse.

Challenge Problems

Challenge 1: Critical groups

Ms. Flamm Boyant, France's foremost food critic is visiting your city!

She has heard of Canada's intriguing "food groups" and her request is simple. She requires:

  • A list of the food groups available (by name).
  • The number of foods in each group.
  • The groups listed from largest (most foods) to smallest.

Find her this data before she writes a bad review on social media!

Hints

  • Where can you find food group names?
  • What function can you use to get the number of foods per group?
  • What 2 tables will you need to join in this query?

Challenge 2: I want my greens!

Brock O. Lee is adopting a new low-carb diet.

He requires foods matching the following criteria:

  • Must be a vegetable.
  • Must include the word green in its description.
  • Must have less than 10 grams of carbs for a 100 gram portion. Your client wants a simple table listing all of these foods with the name of the food and the grams of carbs, from least to most.

Challenge 3: Zigzag meal plan

Amanda A. Appleton has always hated the letter "z". This unusual phobic state has now resulted in a health condition, as she suffers from a zinc deficiency!

A woman her age requires 8 mg of zinc daily.

Her doctor recommends the following treatment:

  • Eat foods providing at least half the daily requirement of zinc per 100g portion
  • Eat foods containing the letter "z" in their descriptions, to desensitize yourself from the phobia Run a quick query to provide her with a list of suitable foods!

Challenge 4: No leftovers!

Your old high school math teacher bursts through the door!

He's joined an environmental organization and is convinced there's a conspiracy at play!

He needs a list of foods whose ID is evenly divided by their RefuseAmount.

You try to explain this doesn't make much sense, but he's adamant.

Note: A number is evenly divided by another when there's no remainder after division. The remainder (modulo) operator % can help you here.

Challenge 4B: Leftovers = PROFIT

Your old teacher's ecstatic!

He offers to pay you $0.50 for each unit of refuse in your list, instead of your flat rate of $200.00.

You're not sure if he's trying to short-change you or if he's truly lost his mind.

Adjust your query to calculate your pay and find out!

Clone this wiki locally