Managing Data with SQL: Exercises

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!

Challenge 3: Zigzag meal plan

Challenge 4: No leftovers!

Challenge 4B: Leftovers = PROFIT