gwecode

courses / sql / level 2

level 2 · medium

intermediate
sql.

Join tables, group and choose, design safe databases and work with dates to build useful reports.

  • 28 lessons
  • 7 units
  • 4 h 21 m in all
  • £0, for good
start level 2

what’s inside.

For people who finished Level 1, or can already write small programs with variables, decisions, loops and functions.

SQL Level 2 picks up where Level 1 ends. In 28 lessons you join tables, group and sieve results, nest queries, choose values with CASE, design linked tables with rules that keep bad data out, and count days between dates. It ends with a level check: one set of reports that works for five different libraries.

every lesson goes the same way.

  1. meet it
  2. guess first
  3. your turn
  4. fix it
  5. nailed it

unit 1

Groups and totals

Get one answer for every group, not one for the whole table.

4 lessons · 35 min

  1. One answer per group8 minCount and total each kind of thing in a single query.
  2. Sorting and sieving groups9 minSieve the rows first, group them, then sort the answers.
  3. Picking groups with HAVING9 minKeep only the groups that pass a test.
  4. Groups inside groups9 minGroup by two columns at once for a finer report.

unit 2

Join tables together

Store each fact once, link tables by id, then bring them back together.

4 lessons · 38 min

  1. Tables that point at each other9 minStore each fact once and link rows by their id.
  2. JOIN glues rows together10 minBring linked rows from two tables into one result.
  3. Short names for tables9 minGive tables aliases to keep joins short and clear.
  4. Join, then group10 minBuild a joined table, then sum it up per customer.

unit 3

More joins

Keep rows with no match, find what is missing and join three tables.

4 lessons · 38 min

  1. LEFT JOIN keeps everyone9 minKeep every row from one table, matched or not.
  2. Finding what is missing9 minList the rows that have no match at all.
  3. Three tables at once10 minChain joins to answer questions that need three tables.
  4. Counting zeros10 minCount matches for every row, even the ones with none.

unit 4

Queries inside queries

Check values against lists, and use one query's answer inside another.

4 lessons · 36 min

  1. Is it in the list?8 minMatch any value from a list without a chain of ORs.
  2. A query inside a query9 minUse one query's answer as a value in another.
  3. IN with a subquery9 minLet a query build the list for IN.
  4. A subquery as a column10 minCompare each group with the whole table in one report.

unit 5

Choose with CASE

Make a query choose a value for every row, and fill in the blanks.

4 lessons · 37 min

  1. CASE picks a value9 minGive every row a label that depends on its values.
  2. First match wins9 minSort rows into bands, testing from the top down.
  3. Counting with CASE10 minCount several kinds of rows side by side in one report.
  4. Filling the blanks9 minSwap NULL for a value that makes sense.

unit 6

Rules that protect your data

Let the database refuse bad data before it ever gets in.

4 lessons · 35 min

  1. No two the same8 minStop two rows sharing a value that must be one of a kind.
  2. A value when none is given8 minFill left-out columns with a sensible starting value.
  3. Checking values on the way in9 minWrite your own rule that every row must pass.
  4. Links that cannot break10 minMake the database refuse a foreign key that points nowhere.

unit 7

Design and dates

Link tables many to many, work with dates, then build a full report.

4 lessons · 42 min

  1. Link tables10 minDesign a many-to-many link with a table in the middle.
  2. Dates are text in a set order10 minStore dates so they sort, compare and group correctly.
  3. Counting days10 minMove a date along and count the days between two dates.
  4. Putting it all together12 minTurn a librarian's brief into a working report.
end of level 2 level check: the librarian's reports Build one small project with everything from this level. Pass it and your face levels up. see the check

next, level 3

advanced
sql.

Write SQL like a professional: windows, recursion, transactions, triggers, indexes and JSON.

see level 3