-- Steven Carr
-- November 5th, 2024
-- Question 2, HW4, CS630

-- Answer to part a
CREATE TABLE Cars(
        carid INT PRIMARY KEY,
        make VARCHAR(15),
        model VARCHAR(15),
        myear INT CHECK (myear >= 2010),
        dailyfee REAL
);

-- Answer to part b
CREATE TABLE Customers(
        custid INT PRIMARY KEY,
        name VARCHAR(30) NOT NULL,
        city VARCHAR(30) NOT NULL,
        state VARCHAR(2) NOT NULL,
        dob DATE NOT NULL
);

-- Answer to part c
CREATE TABLE Rents(
        carid INT,
        custid INT,
        rday DATE NOT NULL,
        PRIMARY KEY (carid, custid),
        FOREIGN KEY (carid) REFERENCES Cars(carid),
        FOREIGN KEY (custid) REFERENCES Customers(custid)
);

INSERT INTO Cars(carid, make, model, myear, dailyfee) VALUES(1234, 'Toyota', 'RAV4', 2018, 56.78);
INSERT INTO Cars(carid, make, model, myear, dailyfee) VALUES(2543, 'Hyundai', 'Elantra', 2013, 31.92);
INSERT INTO Cars(carid, make, model, myear, dailyfee) VALUES(4512, 'Honda', 'Accord', 2021, 75.64);

INSERT INTO Customers(custid, name, city, state, dob) VALUES(11, 'SDC', 'Providence', 'RI', TO_DATE('06-03-1996', 'MM-DD-YYYY'));
INSERT INTO Customers(custid, name, city, state, dob) VALUES(12, 'JM', 'Boston', 'MA', TO_DATE('04-15-1998', 'MM-DD-YYYY'));
INSERT INTO Customers(custid, name, city, state, dob) VALUES(13, 'ZF', 'Brockton', 'MA', TO_DATE('06-21-1995', 'MM-DD-YYYY'));

INSERT INTO Rents(carid, custid, rday) VALUES(1234, 13, TO_DATE('12-20-2022', 'MM-DD-YYYY'));
INSERT INTO Rents(carid, custid, rday) VALUES(2543, 13, TO_DATE('11-20-2022', 'MM-DD-YYYY'));
INSERT INTO Rents(carid, custid, rday) VALUES(4512, 13, TO_DATE('10-20-2022', 'MM-DD-YYYY'));
INSERT INTO Rents(carid, custid, rday) VALUES(2543, 11, TO_DATE('02-14-2019', 'MM-DD-YYYY'));
INSERT INTO Rents(carid, custid, rday) VALUES(4512, 12, TO_DATE('08-23-2023', 'MM-DD-YYYY'));

-- Answer to part d
SELECT cust.custid, cust.name FROM Customers cust
WHERE NOT EXISTS(
        SELECT c.carid FROM Cars c
        MINUS
        SELECT r.carid FROM Rents r
        WHERE r.custid = cust.custid
);

-- Answer to part e
INSERT INTO Cars(carid, make, model, myear, dailyfee) VALUES(9999, 'Dodge', 'RAM', 2014, 49.13);

-- Answer to part f
SELECT c.carid, c.make, c.model FROM Cars c
WHERE c.carid IN (
        SELECT r.carid FROM Rents r
        WHERE EXTRACT(YEAR FROM rday) = 2022
)
AND c.carid NOT IN (
        SELECT r.carid FROM Rents r
        WHERE EXTRACT(YEAR FROM rday) = 2021
);