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

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

ALTER TABLE Persons ADD PRIMARY KEY(ssn);

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

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


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


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');

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

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

SELECT p.name from persons p
INNER JOIN crew c on p.ssn = c.ssn
INNER JOIN ships s on c.ship_id = s.ship_id
WHERE s.name LIKE 'Pequod' 
ORDER BY p.name;


SELECT COUNT(*) AS crew_on_titanic FROM persons p
INNER JOIN crew c ON p.ssn = c.ssn
INNER JOIN ships s ON c.ship_id = s.ship_id
WHERE s.name LIKE 'Titanic' ;