-- This file is for CS630 Homework2 Question 3
-- Steven Carr
-- October 13th, 2024

-- Answer to part a:
CREATE TABLE songs(
        songid INT PRIMARY KEY,
        title VARCHAR(50),
        release DATE
);

-- Values I am inputting into the table to test (same goes for the next two tables)
INSERT INTO songs(songid, title, release) VALUES(10, 'Keep It 100', TO_DATE('2017-06-16', 'YYYY-MM-DD'));
INSERT INTO songs(songid, title, release) VALUES(11, 'Evil Twin', TO_DATE('2013-11-05', 'YYYY-MM-DD'));
INSERT INTO songs(songid, title, release) VALUES(12, 'Thats What I Like', TO_DATE('2016-11-18', 'YYYY-MM-DD'));

CREATE TABLE singers(
        singerid INT PRIMARY KEY,
        name VARCHAR(50),
        city VARCHAR(20),
        state VARCHAR(2)
);

INSERT INTO singers(singerid, name, city, state) VALUES(110, 'Bruno Mars', 'Honolulu', 'HI');
INSERT INTO singers(singerid, name, city, state) VALUES(111, 'Joyner Lucas', 'Worcester', 'MA');
INSERT INTO singers(singerid, name, city, state) VALUES(112, 'Eminem', 'Detroit', 'MI');

CREATE TABLE singsin(
        singerid INT,
        songid INT,
        PRIMARY KEY (singerid, songid),
        FOREIGN KEY (singerid) REFERENCES singers(singerid),
        FOREIGN KEY (songid) REFERENCES songs(songid)
);

INSERT INTO singsin(singerid, songid) VALUES(110, 12);
INSERT INTO singsin(singerid, songid) VALUES(111, 10);
INSERT INTO singsin(singerid, songid) VALUES(112, 11);

-- Just testing the tables
SELECT * FROM songs;
SELECT * FROM singers;
SELECT * FROM singsin;


-- Answer to part b:
SELECT name FROM singers
WHERE state='MA'
ORDER BY name DESC;

-- Answer to part c:
SELECT singers.name, songs.title, songs.release FROM singers
JOIN singsin ON singers.singerid = singsin.singerid
JOIN songs ON singsin.songid = songs.songid;

-- Answer to part d:
SELECT COUNT(*) FROM singers
WHERE city = 'Boston' AND state = 'MA';

-- Answer to part e:
SELECT * FROM singers
WHERE name LIKE 'A%'
ORDER BY state ASC;

-- Answer to part f:
SELECT singers.name, singers.city, singers.state FROM singers
JOIN singsin ON singers.singerid = singsin.singerid
JOIN songs ON singsin.songid = songs.songid
WHERE LOWER(songs.title) LIKE '%joy%';

-- Answer to part g:
SELECT singers.name, singers.city, singers.state FROM singers
JOIN singsin ON singers.singerid = singsin.singerid
JOIN songs ON singsin.songid = songs.songid
WHERE songs.release < TO_DATE('2021-09-01', 'YYYY-MM-DD');

-- Answer to part h:
SELECT singers.name, singers.state FROM singers
JOIN singsin ON singers.singerid = singsin.singerid
JOIN songs ON singsin.songid = songs.songid
WHERE EXTRACT(YEAR FROM songs.release) = 2020;

-- Answer to part i:
SELECT singers.singerid, singers.name, singers.city FROM singers
JOIN singsin ON singers.singerid = singsin.singerid
JOIN songs ON singsin.songid = songs.songid
WHERE singers.state = 'MA'
AND songs.release BETWEEN TO_DATE('2020-01-01', 'YYYY-MM-DD') AND TO_DATE('2022-07-31', 'YYYY-MM-DD')
ORDER BY singers.name DESC;

-- Answer to part j:
SELECT COUNT(DISTINCT singers.singerid) AS unique_singers FROM singers
JOIN singsin ON singers.singerid = singsin.singerid
JOIN songs ON singsin.songid = songs.songid
WHERE songs.release > TO_DATE('2021-12-10', 'YYYY-MM-DD');