--- registrar.sql
---
--- A demo SQL database
--- 
---   +-----------------+ 
---   |  Student        |
---   |-----------------|
---   |  id             |
---   |  name           |
---   +-----------------+
---            |
---            |
---           /|\
---   +-----------------+
---   |  Registration   |
---   |-----------------|
---   |  id             |
---   |  course_id      |
---   |  student_id     |
---   |  date           |
---   +-----------------+
---           \|/
---            |
---            |
---      +----------+
---      |  Course  |
---      |----------|
---      |  id      |
---      |  name    |
---      |  blurb   |
---      +----------+
---
---
--- setup : create registrar.db ; get to SQL prompt
---
---       $ sqlite3 registrar.db < registrar.sql
---       $ sqlite3 registrar.db
---       sqlite> YOU TYPE STUFF HERE;
---
--- exercise :
---
---       What courses has River taken?
---
---          --- answer 1 : with JOIN 
---                         Notes :  * fully qualified column names 
---                                  * "like" string partial match
---
---          SELECT Course.name
---          FROM Course JOIN Registration JOIN Student
---          ON Student.id=Registration.student_id
---             AND Registration.course_id=Course.id
---          WHERE Student.name LIKE "%River%";
---
---          --- answer 2 : with subqueries
---                         Notes :  * "WHERE id IN ..." for multiple matches
---                                  * "WHERE id = ..." for single match
---
---          SELECT name from Course
---          WHERE id IN (
---              SELECT course_id from Registration
---              WHERE student_id=(
---                  SELECT id from Student
---                  WHERE name LIKE "%River%" ));
---
---        Answer is : Compilers, Data Science, Graphics
---

--- Enable foreign key syntax (sqlite specific)
PRAGMA foreign_keys = ON;

--- Remove all the tables, so we can start fresh.
DROP TABLE IF EXISTS Student;
DROP TABLE IF EXISTS Course;
DROP TABLE IF EXISTS Registration;

--- Define the tables.

CREATE TABLE Student (
  id            INTEGER PRIMARY KEY,
  name          TEXT
);

CREATE TABLE Course (
  id            INTEGER PRIMARY KEY,
  name          TEXT NOT NULL,
  blurb         TEXT NOT NULL
);

CREATE TABLE Registration (
  id            INTEGER PRIMARY KEY,
  student_id    INTEGER REFERENCES Student(id),  
  course_id     INTEGER REFERENCES Course(id),  
  date          TEXT NOT NULL
);

--- Put some data into the tables.

INSERT INTO Student VALUES (1, 'John Smith');
INSERT INTO Student VALUES (2, 'Jane Doe');
INSERT INTO Student VALUES (3, 'Wendy Rogers');
INSERT INTO Student VALUES (4, 'Bob Bradly');
INSERT INTO Student VALUES (5, 'River Song');

INSERT INTO Course VALUES (1, 'Intro CS', 'Learn to program.');
INSERT INTO Course VALUES (2, 'Algorithms', 'Solve stuff.');
INSERT INTO Course VALUES (3, 'Graphics', 'Shaders!');
INSERT INTO Course VALUES (4, 'Data Science', 'Better living with numbers.');
INSERT INTO Course VALUES (5, 'Compilers', 'Parse trees are fun!');

--- Notes:
---   * column names specified (usually good practice; don't need specific order)
---   * "id" column isn't given, so it is assigned automatically.
---   * date in ISO8601 ... a good choice (SQLite doesn't have its own date format.)

INSERT INTO Registration (student_id, course_id, date) VALUES (1, 1, '2020-09-01');  -- John in Intro CS
INSERT INTO Registration (student_id, course_id, date) VALUES (1, 2, '2021-02-01');  -- John in Algorithms
INSERT INTO Registration (student_id, course_id, date) VALUES (2, 3, '2019-09-01');  -- Jane in Graphics
INSERT INTO Registration (student_id, course_id, date) VALUES (5, 5, '2019-09-01');  -- River in Compilers
INSERT INTO Registration (student_id, course_id, date) VALUES (5, 4, '2019-09-01');  -- River in Data Science
INSERT INTO Registration (student_id, course_id, date) VALUES (1, 3, '2020-09-01');  -- everyone in Graphics
INSERT INTO Registration (student_id, course_id, date) VALUES (2, 3, '2020-09-01');  -- everyone in Graphics
INSERT INTO Registration (student_id, course_id, date) VALUES (3, 3, '2020-09-01');  -- everyone in Graphics
INSERT INTO Registration (student_id, course_id, date) VALUES (4, 3, '2020-09-01');  -- everyone in Graphics
INSERT INTO Registration (student_id, course_id, date) VALUES (5, 3, '2020-09-01');  -- everyone in Graphics