DROP TABLE IF EXISTS crew ;
DROP TABLE IF EXISTS Persons ;
DROP TABLE IF EXISTS Ships ;

-- 2
CREATE TABLE Ships (
    ship_id INT,
    name VARCHAR,
    PRIMARY KEY(ship_id)
) ;

-- 3
INSERT INTO Ships VALUES (1, 'Pequod'), (2, 'Titanic'), (3, 'Le pharaon');

-- 4
CREATE TABLE Persons (
    name VARCHAR,
    ssn CHAR(9),
    street_address VARCHAR,
    zip CHAR(5)
);

-- 5 and 6 (ssn is very "reasonable")
ALTER TABLE Persons ADD PRIMARY KEY(ssn);

-- 7
-- some ssn-checking could make sense
-- also a foreign key constraint on zip codes might be good

-- 8
INSERT INTO Persons
VALUES ('Ishmael', '111111110', '20 Cooper Square Manhattan', '10001'),
       ('Captain Ahab', '111111112', '16 Nantucket Avenue', '02554'),
       ('Queequeg', '111111113', 'Rocovoco', '99991'),
       ('Molly Brown', '111111114', 'Hannibal Missouri', '63401'),
       ('Edmond Dantes', '111111115', 'Marseille', '13003');

-- 9 (and 10 (and 11))
CREATE TABLE Crew (
    ssn CHAR(9) REFERENCES Persons(ssn),
    ship_id INT REFERENCES Ships(ship_id),
    PRIMARY KEY(ssn, ship_id)
);

-- 11 (alternative)
ALTER TABLE Crew
ADD FOREIGN KEY (ssn) REFERENCES Persons(ssn);

ALTER TABLE Crew
ADD FOREIGN KEY (ship_id) REFERENCES Ships(ship_id);

-- 12
INSERT INTO Crew
VALUES ('111111110', 1),
       ('111111112', 1),
       ('111111113', 1),
       ('111111114', 2),
       ('111111115', 3);

-- 13
SELECT * FROM persons
WHERE street_address LIKE '%Manhattan%' ;

-- 14
SELECT p.name FROM persons p
JOIN Crew USING (ssn)
JOIN ships s USING (ship_id)
WHERE s.name LIKE 'Pequod' 
ORDER BY p.name;

-- 15
SELECT COUNT(*) AS crew_on_titanic FROM persons
JOIN crew USING (ssn)
JOIN ships s USING (ship_id)
WHERE s.name LIKE 'Titanic' ;