-- Steven Carr
-- CS630 Assignment 3, Question 2
-- October 23rd, 2024

-- Answer to part a; I insert values into the tables for testing
CREATE TABLE Books(
        bid INT PRIMARY KEY,
        bname VARCHAR(50),
        author VARCHAR(30),
        pubyear INT,
        pubcompany VARCHAR(50)
);

CREATE TABLE Students(
        sid INT PRIMARY KEY,
        sname VARCHAR(30),
        age REAL,
        state VARCHAR(2)
);

CREATE TABLE Reads(
        sid INT,
        bid INT,
        year INT,
        PRIMARY KEY (sid, bid),
        FOREIGN KEY (sid) REFERENCES Students(sid),
        FOREIGN KEY (bid) REFERENCES Books(bid)
);

-- Answer for part b

INSERT INTO Books(bid, bname, author, pubyear, pubcompany) VALUES(21212, 'The Greatest Book', 'SDC', 1996, 'Penguin');
INSERT INTO Books(bid, bname, author, pubyear, pubcompany) VALUES(54679, 'The Worst Book', 'KIB', 2016, 'Chronicle');
INSERT INTO Books(bid, bname, author, pubyear, pubcompany) VALUES(92571, 'How To Live A Happy Life', 'ZJF', 1977, 'Ventura');
INSERT INTO Books(bid, bname, author, pubyear, pubcompany) VALUES(88433, 'Midgar Studies', 'SDC', 2020, 'Chronicle');
INSERT INTO Books(bid, bname, author, pubyear, pubcompany) VALUES(70587, 'The Anatomy Of A Spider', 'KIB', 2024, 'Ventura');

INSERT INTO Students(sid, sname, age, state) VALUES(3005, 'Daniel Alves', 29, 'MA');
INSERT INTO Students(sid, sname, age, state) VALUES(3087, 'Christian Hine', 23, 'NC');
INSERT INTO Students(sid, sname, age, state) VALUES(3054, 'Joseph Miranda', 19, 'CA');
INSERT INTO Students(sid, sname, age, state) VALUES(3029, 'Robin Zemke', 31, 'MA');

INSERT INTO Reads(sid, bid, year) VALUES(3005, 92571, 2022);
INSERT INTO Reads(sid, bid, year) VALUES(3029, 70587, 2024);
INSERT INTO Reads(sid, bid, year) VALUES(3054, 21212, 2017);
INSERT INTO Reads(sid, bid, year) VALUES(3087, 92571, 2011);
INSERT INTO Reads(sid, bid, year) VALUES(3005, 88433, 2021);
INSERT INTO Reads(sid, bid, year) VALUES(3087, 21212, 2019);

-- Answer for part c
SELECT sname FROM Students
WHERE state = 'MA' AND (age < 25 OR age > 35);

-- Answer for part d
SELECT COUNT(*) AS Books_Authors_B FROM Books
WHERE author LIKE 'B%' OR author LIKE 'b%';

-- Answer for part e
SELECT * FROM Books
WHERE pubyear = (SELECT MIN(pubyear) FROM Books);

-- Answer for part f
SELECT state, AVG(age) AS Average_Age_Students FROM Students
GROUP BY state
HAVING COUNT(sid) >= 50;

-- Answer for part g
SELECT s.sid, s.sname FROM Students s
JOIN Reads r ON s.sid = r.sid
GROUP BY s.sid, s.sname
HAVING COUNT(DISTINCT r.bid) = (SELECT COUNT(*) FROM Books);

-- Answer for part h
SELECT s.sid, s.sname, s.state FROM Students s
JOIN Reads r ON s.sid = r.sid
JOIN Books b ON r.bid = b.bid
WHERE b.pubcompany = 'Penguin'
GROUP BY s.sid, s.sname, s.state
HAVING COUNT(DISTINCT b.bid) = (SELECT COUNT(*) FROM Books WHERE pubcompany = 'Penguin')
ORDER BY s.sname DESC;

-- Answer for part i
SELECT s.sid, s.sname FROM Students s
JOIN Reads r ON s.sid = r.sid
JOIN Books b ON r.bid = b.bid
GROUP BY s.sid, s.sname
HAVING SUM(CASE WHEN b.pubcompany = 'Penguin' THEN 1 ELSE 0 END) > 0
AND SUM(CASE WHEN b.pubcompany = 'Simon' THEN 1 ELSE 0 END) = 0;

-- Answer for part j
SELECT s.sname FROM Students s
JOIN Reads r ON s.sid = r.sid
JOIN Books b ON r.bid = b.bid
WHERE r.year = b.pubyear;

-- Answer for part k
SELECT s.sname, s.age, s.state FROM Students s
JOIN Reads r ON s.sid = r.sid
JOIN Books b ON r.bid = b.bid
WHERE b.pubcompany = 'Penguin' AND r.year = 2020
GROUP BY s.sname, s.age, s.state
HAVING COUNT(DISTINCT b.bid) < (SELECT COUNT(*) FROM Books WHERE pubcompany = 'Penguin' AND pubyear = 2020);

-- Answer for part l
SELECT b.bid, b.bname, b.author FROM Books b
JOIN Reads r ON b.bid = r.bid
WHERE b.pubcompany = 'Penguin'
GROUP BY b.bid, b.bname, b.author
HAVING COUNT(DISTINCT r.sid) = (SELECT COUNT(*) FROM Students);