SQL Café · Lesson 8 of 8 · Final project

Your first day running the café's data.

Lessons: 1 · 2 · 3 · 4 · 5 · 6 · 7

You have learned the main parts of SQL. Today you put them all together. The café manager has gone home and left you a note with ten jobs. Each job uses something from one of the lessons, and every job has a hint if you need one.

A note from the manager

“Hello, and welcome! Please could you check the menu, tell me who our best customer is, add the new Lemon Tart, put up the drink prices a little, and start collecting reviews. The full list is in the playground below. Thank you so much!”

Before you start

All of SQL Café on one page

Here is everything you learned, with the memory line from each lesson. Please read it once before you start the jobs.

Lesson 1
SELECTFROMWHERE
What? Where is it? Which ones?Text in 'quotes', numbers without. After SELECT, a comma means “and”.Open Lesson 1
Lesson 2
ORDER BYASCDESCLIMIT
ASC walks up the stairs, DESC walks down. Sort first, then cut.In ORDER BY, a comma means “then by”. Without ORDER BY, the order is not guaranteed.Open Lesson 2
Lesson 3
COUNTSUMAVGMINMAX
The receipt words: many rows in, one row out.Brackets mean “of”: AVG(price) is the average of price.Open Lesson 3
Lesson 4
GROUP BYHAVING
Make piles, then squash each pile.WHERE picks rows, HAVING picks piles. After SELECT: the pile label or a receipt word.Open Lesson 4
Lesson 5
JOINONLEFT JOINIS NULL
JOIN looks it up. ON says what must match.The dot means 's. No match, no row, unless LEFT JOIN.Open Lesson 5
Lesson 6
INSERTUPDATEDELETE
Add with VALUES. Change with SET. Remove with FROM. And always, always WHERE.Look first with SELECT, then change.Open Lesson 6
Lesson 7
CREATE TABLEINTEGERDECIMALTEXT
Every column: name, kind, rules.PRIMARY KEY, NOT NULL, DEFAULT, UNIQUE, REFERENCES. DROP TABLE throws it all away.Open Lesson 7
The order of a SELECT, in one sentence
SomeFriendlyWaitersGladlyHandOutLemonade
SELECT→FROM … JOIN … ON→WHERE→GROUP BY→HAVING→ORDER BY→LIMIT
The project

Ten jobs for the café

Please work through the jobs in order, because some build on earlier ones (you can't take an order for the Lemon Tart before it is on the menu). Your ticks are saved in this browser. If the tables get muddled, Reset tables puts them back, and you can redo the jobs that change tables.

Ctrl + Enter also runs
Today's jobs
      The tables right now
      One last check

      Seven questions, one from each lesson

      Please answer from memory. Each question comes from a different lesson, so it is a quick way to see what has stuck.

      Your certificate

      Finish all ten jobs to unlock it

      SQL Café · Certificate Your name

      has finished the SQL Café final project: reading, adding up, grouping, joining, changing and building tables with SQL.

      SELECTWHEREORDER BYSUMGROUP BYJOINUPDATECREATE TABLE

      When it unlocks, please take a screenshot to keep it.

      What next?

      Keep going

      SQL Café is a free course by MASSH AI. Want SQL or data training for your team? Please get in touch.