SS 3 First Term

Lesson objectives

By the end of the week, candidates should be able to:

1. Define DBMS, table and keys.
2. Write CREATE TABLE with two constraints.
3. Insert three rows with quoting.
4. Query with WHERE and ORDER BY.
5. Interpret a count query.

Key vocabulary

• DBMS: software managing a database.
• Table: rows and columns for one entity.
• Primary key: a unique row identifier.
• Foreign key: a link to another table's key.
• Query: an SQL request for data.

Common misconceptions

• "A primary key may contain NULL." Every key value must be unique and present.
• "SQL keywords need capitals." Capitals are a convention; either case runs.

Teacher activities

1. Starter: read a register.
2. Teach the schema and keys.
3. Model CREATE with constraints.
4. Insert three fictional rows.
5. Run both queries.
6. Break one semicolon.

Student activities

1. Label rows, columns and keys.
2. Copy the schema diagram.
3. Type the CREATE statement.
4. Insert three rows.
5. Run both queries.
6. Print results.

CREATE TABLE students (
  student_id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  class TEXT
);
INSERT INTO students VALUES (1, 'Amaka', 'SS3A');
INSERT INTO students VALUES (2, 'Tunde', 'SS3B');
INSERT INTO students VALUES (3, 'Grace', 'SS3A');
SELECT name, class FROM students
WHERE class = 'SS3A'
ORDER BY name;

name	class
Amaka	SS3A
Grace	SS3A

SELECT COUNT(*) FROM students;

Result: 3.

Practical / learning evidence

ss3_school.sql holds a CREATE statement, three rows, one ordered query and one count. Success needs correct constraints and row order.

Resources and preparation

SQLite with DB Browser, projector and a schema sheet. Offline route: draw the tables and write result rows by hand.

Assessment for learning

Check the key, NOT NULL and ORDER BY. Watch for a missing semicolon.

Inclusion and homework

Provide CREATE with blanks for constraints; extend others to a JOIN. Homework: query SS3B names in alphabetical order.