Keys and joins

Rules that keep bad data out, keys that identify and link rows, and joins that bring linked tables back together in a query.

About 6 hours of study

Learning objectives

• Add NOT NULL, UNIQUE and CHECK constraints
• Tell candidate, primary and foreign keys apart
• Use INNER, LEFT and self-joins
• Find rows with no match

Lessons

1. 1

   Keys and constraints

   Rules the DBMS enforces for you: NOT NULL, UNIQUE, CHECK, PRIMARY KEY and FOREIGN KEY, plus what candidate, primary and foreign keys are.

2. 2

   Joining tables

   Combine rows from several tables: CROSS JOIN pairs everything, INNER JOIN keeps matching pairs, LEFT JOIN keeps every row of the left table, and a self join matches a table with itself.

Module practice

• Only adults may joinEasy
• One account per email addressMedium
• When an author leaves, their books go tooHard
• Who ordered whatEasy
• Customers who never orderedMedium
• Earning more than the bossHard

‹ Changing and summarising dataAlgebra, subqueries and views ›