.mode column
.header ON

-- SQLite does not enforce foreign keys by default. turn it on like this.
PRAGMA foreign_keys = ON;

CREATE TABLE payroll (
  user_id INT PRIMARY KEY,
  name VARCHAR(256),
  job VARCHAR(256),
  salary INT
);

INSERT INTO payroll
  VALUES 
    (123, 'Jack', 'TA', 50000),
    (345, 'Allison', 'TA', 60000),
    (567, 'Magda', 'Prof', 90000),
    (789, 'Dan', 'Prof', 100000);

CREATE TABLE regist (
  user_id INT REFERENCES payroll,
  car VARCHAR(256)
);

INSERT INTO regist
  VALUES 
    (123, 'Charger'),
    (567, 'Civic'),
    (567, 'Pinto');

-- find all the cars driven by each payroll member
SELECT p.name, r.car
  FROM payroll AS p, regist AS r
 WHERE p.user_id = r.user_id;

-- find all the cars driven by each TA
SELECT p.name, r.car
  FROM payroll AS p, regist AS r
 WHERE p.user_id = r.user_id
   AND p.job = 'TA';

-- another way to write the "all cars" query
SELECT p.name, r.car
  FROM payroll AS p 
  JOIN regist AS r
    ON p.user_id = r.user_id;

-- another way to write the TA query
SELECT p.name, r.car
  FROM payroll AS p 
  JOIN regist AS r
    ON p.user_id = r.user_id
 WHERE p.job = ‘TA’;

-- all combinations of payroll members and cars
SELECT p.name, r.car
  FROM payroll AS p, regist AS r;


SELECT p.name, R1.car as car1, R2.car as car2 
  FROM payroll AS p, regist AS r1, regist AS r2
 WHERE p.user_id = R1.user_id AND
       p.user_id = R2.user_id AND
       R1.car = ‘Civic’ AND
       R2.car = ‘Pinto’;