SQL foundations
On this page 8 topics
Overview
Topics
- DDL
- DML
- SELECT
- WHERE
- ORDER BY
- GROUP BY
- HAVING
- JOINs
- subqueries
Learn first
101 min of reading · 6 h of practice (estimate)
Learning outcomes
- Create and alter tables with DDL.
- Insert, update, and delete rows with DML, always with a correct
WHERE. - Query with
SELECT,WHERE,ORDER BY,GROUP BY,HAVING, and aggregates. - Combine tables with inner and left joins and explain the difference.
- Use a subquery where it is clearer than a join.
Lessons
- 01SQL overview: DDL vs DML11 min read
- 02CREATE and ALTER12 min read
- 03INSERT, UPDATE, DELETE11 min read
- 04SELECT, WHERE, ORDER BY10 min read
- 05Aggregates, GROUP BY, HAVING10 min read
- 06Joins12 min read
- 07Subqueries12 min read
- 08Demo: answering library questions with SQL11 min read
- 09Demo: joins that silently drop rows12 min read
Lab and exercises
Lab
BEG-050-LAB1 · Library queries
Outcomes: 1–5 · Time: ~6 h Answer 25 graded queries against a seeded library database: overdue loans, most-borrowed authors, members with no loans, and more. Acceptance: 20 of 25 correct.
Exercises (assessment: timed SQL test, also part of the BEG graduation gate)
| ID | Outcome | Task |
|---|---|---|
| BEG-050-EX01 | 1 | Create a publishers table (id, name, city) and add a website column afterwards. |
| BEG-050-EX02 | 2 | Insert two publishers, update one city, delete the other, each with a correct WHERE. |
| BEG-050-EX03 | 3 | List books from after 1990, newest first, at most five of them (ties in year broken by title). |
| BEG-050-EX04 | 3 | Count books per author and keep only authors with at least two. |
| BEG-050-EX05 | 4 | List every member and their loans, including members with no loans. |
| BEG-050-EX06 | 4 | Explain in two sentences the difference between inner and left join. |
| BEG-050-EX07 | 5 | List titles of books never loaned, using a subquery. |
| BEG-050-EX08 | 2 | Explain why you run a SELECT before an UPDATE and what a missing WHERE does. |
Show solutions
- EX01
CREATE TABLE publishers (id INTEGER PRIMARY KEY, name TEXT NOT NULL, city TEXT); ALTER TABLE publishers ADD COLUMN website TEXT; - EX02
INSERT ... VALUES (...);UPDATE publishers SET city = 'Oslo' WHERE id = 1;DELETE FROM publishers WHERE id = 2; - EX03
SELECT title FROM books WHERE published_year > 1990 ORDER BY published_year DESC, title LIMIT 5;LIMIT 5means at most five rows: fewer qualify, fewer come back. The second sort key (title) makes the order of books from the same year predictable. - EX04
SELECT author_id, COUNT(*) FROM books GROUP BY author_id HAVING COUNT(*) >= 2; - EX05
SELECT m.name, l.id FROM members m LEFT JOIN loans l ON l.member_id = m.id; - EX06 Inner join keeps only matched rows; left join keeps all left rows and fills missing right columns with
NULL. - EX07
SELECT title FROM books WHERE id NOT IN (SELECT book_id FROM loans);(this works ifloans.book_idhas noNULLs). - EX08 The
SELECTshows which rows will change; withoutWHEREevery row is changed.
Quiz
Outcomes: O1 DDL · O2 DML · O3 queries and aggregates · O4 joins · O5 subqueries.
A self-check for you: nothing is graded, saved or sent anywhere. Choose an answer, then check it. You can try again as often as you like.
Practical part
Timed SQL test plus the 25-query lab (EX01–EX08). Pass: 20 of 25 lab queries correct and at least 8 of 10 quiz questions.
Common mistakes
UPDATE without WHERE; WHERE on aggregates instead of HAVING; inner join silently dropping rows.
Assessment
Timed SQL test (also part of the BEG graduation gate).
Career relevance
SQL mastery (MID-011) and every data-access module.