insert sample data

2 files changed, 53 insertions, 2 deletions

diff --git a/assignments/final/README.md b/assignments/final/README.md
index 064d7f5..dbbbc75 100644
--- a/assignments/final/README.md
+++ b/assignments/final/README.md
@@ -181,6 +181,9 @@ Order the list by treatment ID, and by reverse chronological order for each trea
 
 Create secondary key indexes to optimize the performance of this query. State any assumptions.
 
+```sql
+```
+
 ## Project Module 3
 
 Consider the following relations:
diff --git a/assignments/final/run.sql b/assignments/final/run.sql
index 8c32b8d..dd20db1 100644
--- a/assignments/final/run.sql
+++ b/assignments/final/run.sql
@@ -20,7 +20,7 @@ DROP TABLE IF EXISTS people;
 
 CREATE TABLE people(
   id bigint primary key,
-  name varchar(255),
+  name varchar(255) NOT NULL,
   address varchar(255),
   birth_date timestamp,
   phone_number varchar(255)
@@ -115,7 +115,7 @@ CREATE TABLE treatments(
   patient_id bigint references patients(id) NOT NULL,
   number integer,
   name varchar(255),
-  occurred_at timestamp,
+  occurred_at timestamp NOT NULL,
   results text
 );
 
@@ -126,3 +126,51 @@ CREATE TABLE visits(
   comments text,
   scheduled_at timestamp NOT NULL
 );
+
+-- dr mo
+INSERT INTO people(id, name) VALUES (1, 'dr. mo');
+INSERT INTO employees(id, person_id, hired_at) VALUES (1, 1, '2004-06-15');
+INSERT INTO physicians(id, employee_id) VALUES (1, 1);
+
+-- dr allison
+INSERT INTO people(id, name) VALUES (2, 'dr. allison');
+INSERT INTO employees(id, person_id, hired_at) VALUES (2, 2, '2010-01-01');
+INSERT INTO physicians(id, employee_id) VALUES (2, 2);
+
+-- dr didi
+INSERT INTO people(id, name) VALUES (3, 'dr. didi');
+INSERT INTO employees(id, person_id, hired_at) VALUES (3, 3, '2020-01-01');
+INSERT INTO physicians(id, employee_id) VALUES (3, 3);
+
+-- dr nini
+INSERT INTO people(id, name) VALUES (4, 'dr. nini');
+INSERT INTO employees(id, person_id, hired_at) VALUES (4, 4, '2020-01-01');
+INSERT INTO physicians(id, employee_id) VALUES (4, 4);
+
+-- moe sislack
+INSERT INTO people(id, name) VALUES (10, 'moe sislack');
+INSERT INTO patients(id, person_id, referring_physician_id) VALUES (10, 10, 1);
+
+-- mose allison
+INSERT INTO people(id, name) VALUES (11, 'mose allison');
+INSERT INTO patients(id, person_id, referring_physician_id) VALUES (11, 11, 2);
+
+-- didi kong
+INSERT INTO people(id, name) VALUES (12, 'didi kong');
+INSERT INTO patients(id, person_id, referring_physician_id) VALUES (12, 12, 1);
+
+
+INSERT INTO treatments(id, physician_id, patient_id, name, occurred_at) VALUES (1, 1, 10, 'heart surgery', '2020-03-01');
+INSERT INTO treatments(id, physician_id, patient_id, name, occurred_at) VALUES (2, 2, 11, 'knee surgery', '2020-03-02');
+INSERT INTO treatments(id, physician_id, patient_id, name, occurred_at) VALUES (3, 1, 12, 'brain surgery', '2020-03-03');
+INSERT INTO treatments(id, physician_id, patient_id, name, occurred_at) VALUES (4, 1, 10, 'foot surgery', '2020-03-04');
+INSERT INTO treatments(id, physician_id, patient_id, name, occurred_at) VALUES (5, 1, 10, 'knee surgery', '2020-03-05');
+
+SELECT pe.name as physician, t.name as treatment
+FROM treatments t
+INNER JOIN physicians p on p.id = t.physician_id
+INNER JOIN employees e on e.id = p.employee_id
+INNER JOIN people pe on pe.id = e.person_id
+WHERE t.occurred_at BETWEEN CURRENT_DATE - INTERVAL '14 days' AND CURRENT_DATE
+;
+-- ORDER BY t.id ASC, t.occurred_at DESC;