SQL review: food data detectives

SQL and databases · Lesson

SQL · Lesson 8 / 8 · SQLite lab

SQL review: food data detectives

Guided SQL review on real food data, with hints and no JOINs.

← Back to SQL path

Before you start

Explore real Open Food Facts data in three independent tables, with no JOINs.

Source and license: Open Food Facts — Open Database License (ODbL) 1.0.

Experiment freely: Reset reloads the original food dataset.

Download food database DB Browser for SQLite ↗

Count and calculate

COUNT(*) counts rows, COUNT(column) excludes NULL. AVG, MIN, MAX and SUM ignore NULL. ROUND rounds averages.

SELECT ROUND(AVG(zuccheri),2) AS media FROM cereali;

GROUP BY: one row per group

GROUP BY groups brands, categories or Nutri-Score and calculates each aggregate per group.

SELECT categoria, COUNT(*) AS totale FROM prodotti GROUP BY categoria ORDER BY totale DESC LIMIT 10;

WHERE or HAVING?

WHERE filters products before grouping; HAVING filters groups afterwards. Requiring at least five observations helps avoid misleading tiny groups.

SELECT categoria, ROUND(AVG(zuccheri),2) AS media FROM prodotti WHERE zuccheri IS NOT NULL GROUP BY categoria HAVING COUNT(zuccheri) >= 5 ORDER BY media DESC LIMIT 10;

Lab and practice

0 completed · 10 to go

Write a query for each task. Run it here or in DB Browser; check columns, rows and conditions.

Exercise 1

Count all rows in prodotti.

Expected row count: 1

Exercise 2

Count drinks with a known sugar value.

Expected row count: 1

Exercise 3

Find average sugar in cereals rounded to two decimals.

Expected row count: 1

Exercise 4

Find the minimum and maximum calories among drinks.

Expected row count: 1

Exercise 5

Rank the ten most common product categories.

Expected row count: 10

Exercise 6

Count cereals for each known Nutri-Score grade.

Expected row count: 5

Exercise 7

Show the top ten brands with at least five products.

Expected row count: 10

Exercise 8

Find ten highest-average-sugar categories with at least five known values.

Expected row count: 10

Exercise 9

Which drink categories have at least ten products?

Expected row count: 1

Exercise 10

Find ten cereal brands with the highest average calories and at least three available observations.

Expected row count: 10

Choose «Check exercise» to verify your result. If you get stuck, use «Help me» for gradual hints without seeing the full solution immediately.

Interactive lab

SQLite · conceptstep_alimenti.sqlite

Write one SQL statement at a time, run it and carefully inspect the columns, rows and values returned.

SQL editor

After each query, compare the result with the task. Use «Reset» whenever you want to start again from the original eight books.

Select an exercise and try to solve it. «Check exercise» verifies the result; «Help me» gives increasingly specific hints and, after a few attempts, you can compare your work with an example solution.

Loading lab…

Result

Database structure

To continue on your computer, download conceptstep_alimenti.sqlite and open it in DB Browser for SQLite. You may reopen the modified file here.

Your competency map

What you need to know for SQL review: food data detectives

ConceptStep separates what is required from what is recommended or useful as reinforcement. Only required prerequisites block your progress.

You can start right away. This is a foundation module: it does not require earlier skills from the learning path.

GUIDED LEARNING PATHS

Want to choose the next skill to develop?

Explore learning paths