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

-- Answer to part a
CREATE TABLE Books(
        bid INT PRIMARY KEY,
        bname VARCHAR(20),
        author VARCHAR(30),
        pubyear INT,
        pubcompany VARCHAR(30)
);

CREATE TABLE Authors(
        aid INT PRIMARY KEY,
        name VARCHAR(20),
        rating INT,
        state VARCHAR(2)
);

CREATE TABLE Writes(
        aid INT,
        bid INT,
        PRIMARY KEY (aid, bid),
        FOREIGN KEY (aid) REFERENCES Authors(aid),
        FOREIGN KEY (bid) REFERENCES Books(bid)
);

INSERT INTO Books(bid, bname, author, pubyear, pubcompany) VALUES(100, 'BookOne', 'Dawn', 1988, NULL);
INSERT INTO Books(bid, bname, author, pubyear, pubcompany) VALUES(101, 'BookTwo', 'Ky', 2007, 'Penguin');
INSERT INTO Books(bid, bname, author, pubyear, pubcompany) VALUES(102, 'BookThree', 'Z', 2020, NULL);

INSERT INTO Authors(aid, name, rating, state) VALUES(1, 'Dawn', 4, 'FL');
INSERT INTO Authors(aid, name, rating, state) VALUES(2, 'Ky', 8, 'MA');
INSERT INTO Authors(aid, name, rating, state) VALUES(3, 'Z', 10, 'MA');

INSERT INTO Writes(aid, bid) VALUES(1, 100);
INSERT INTO Writes(aid, bid) VALUES(2, 101);
INSERT INTO Writes(aid, bid) VALUES(3, 102);

-- Answer to part b
SELECT * FROM Books
WHERE pubcompany IS NOT NULL;

-- Answer to part c
SELECT * FROM Books
WHERE pubcompany IS NULL;

-- Answer to part d
SELECT state, rating, COUNT(*) AS Author_Count FROM Authors
GROUP BY state, rating;

-- Answer to part e
SELECT a.name, b.bid, b.bname FROM Authors a
INNER JOIN Writes w ON a.aid = w.aid
INNER JOIN Books b ON w.bid = b.bid;

-- Answer to part f
SELECT a.name, w.bid FROM Authors a
LEFT JOIN Writes w ON a.aid = w.aid;

-- Answer to part g
INSERT INTO Books(bid, bname, author, pubyear, pubcompany) VALUES(103, 'NewBook', 'Ken', 1973, 'Penguin');
INSERT INTO Authors(aid, name, rating, state) VALUES(4, 'Ken', 9, 'VA');
INSERT INTO Writes(aid, bid) VALUES(4, 103);

-- Answer to part h
UPDATE Authors SET name = 'Kendall'
WHERE aid = 4;

UPDATE Books SET pubyear = 2024
WHERE bid = 103;

-- Answer to part i
UPDATE Books SET pubyear = 2020
WHERE pubcompany = 'Penguin';

-- Answer to part j
UPDATE Authors SET rating = 10;

-- Answer to part k
DELETE FROM Authors
WHERE aid NOT IN (SELECT aid FROM Writes);

-- Answer to part l
ALTER TABLE Authors ADD (age REAL);

-- Answer to part m
DROP TABLE Writes;
DROP TABLE Books;
DROP TABLE Authors;

-- Answer to part n

CREATE TABLE Authors(
        aid INT PRIMARY KEY,
        name VARCHAR(20),
        rating INT CHECK (rating BETWEEN 1 AND 5),
        state VARCHAR(2),
        age REAL
);