--**** Lab Preparation Tasks. Execute lines 2 - 74 now ****
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SONG]') AND type in (N'U'))
DROP TABLE [dbo].[SONG]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ALBUM]') AND type in (N'U'))
DROP TABLE [dbo].[ALBUM]
GO

CREATE TABLE ALBUM
(
	albumID				INT				IDENTITY(1,1),
	albumName			VARCHAR(50)		NOT NULL,
	albumReleaseDate	DATE,
	albumLength			SMALLINT,
	albumIsDoubleLP		BIT,

	CONSTRAINT pk_album_albumID	PRIMARY KEY(albumID)
)

INSERT INTO ALBUM	(albumName, albumReleaseDate, albumLength, albumIsDoubleLP)
VALUES				('Evolve', '2017-06-23', 2352, 0),
					('The Whole Enchilada', '2017-06-23', 2899, 1),
					('A Head Full Of Dreams', '2015-12-04', 2745, 0),
					('Everything Now', '2017-07-28', 2831, 0),
					('One More Light', '2017-05-19', 2119, 0)

CREATE TABLE SONG
(
	songID					INT IDENTITY(1,1),
	songTrackNo				TINYINT				NOT NULL,
	songName				VARCHAR(50)			NOT NULL,
	songLength				SMALLINT,
	songFileExtension		VARCHAR(10),
	albumID					INT					NOT NULL,

	CONSTRAINT pk_song_songID PRIMARY KEY(songID),
	CONSTRAINT fk_album_song FOREIGN KEY(albumID) REFERENCES album(albumID)
)
--Evolve songs
INSERT INTO song(songTrackNo,songName,songLength,songFileExtension,albumID)
VALUES			(1,'I Don''t Know Why', 190,'mp4', 1),
				(3, 'Believer', 204, 'mp4', 1),
				(9, 'Thunder', 187, 'mp3', 1)

--The Whole Enchilada Songs
INSERT INTO song(songTrackNo,songName,songLength,songFileExtension,albumID)
VALUES			(1,'You Got Gold', 276, 'mp3',2),
				(7, 'Everywhere I Go', 198, 'mp3',2),
				(9, 'Memphis Money', 224, 'mp4',2),
				(14,'Good Night', 200, 'mp4',2)

--A Head Full Of Dreams Songs
INSERT INTO song(songTrackNo,songName,songLength,songFileExtension,albumID)
VALUES			(2, 'Birds', 229, 'mp4',3),
				(3, 'A Hymn for the Weekend', 258, 'mp3',3),
				(6, 'Fun', 267, 'mp4',3),
				(10, 'Colour Spectrum', NULL, NULL,3),
				(11, 'Up & Up', NULL, NULL,3)

--One More Light Songs
INSERT INTO song(songTrackNo,songName,songLength,songFileExtension,albumID)
VALUES			(1, 'Nobody Can Save Me', 200, 'mp4',5),
				(2, 'Say Goodbye', 180, 'mp4',5),
				(3, 'Talking To Myself', 201, 'mp3',5),
				(8, 'Halfway Right', 245, 'mp4',5)

CREATE TABLE PLAYLIST
(
	playListID			INT IDENTITY(1,1),
	playListName		VARCHAR(100)		NOT NULL,
	playListCreatedOn	DATE,

	CONSTRAINT pk_playList_playListID PRIMARY KEY(playListID)
)

--****END OF LAB PREP TASKS****

--1: CREATE THE SONG_PLAYLIST TABLE HERE

CREATE TABLE SONG_PLAYLIST
(
	songPlayID	INT IDENTITY(1, 1),
	songPlayTrackNo TINYINT NOT NULL,
	songID INT NOT NULL,
	playListID INT NOT NULL,

	CONSTRAINT pk_song_playList PRIMARY KEY(songPlayID),
	CONSTRAINT fk_songID FOREIGN KEY(songID) REFERENCES SONG(songID),
	CONSTRAINT fk_playListID FOREIGN KEY(playListID) REFERENCES PLAYLIST(playListID)
)

/*
2A.	Add the following playlist information to the database.
The playlist, Songs to Play to Your Pugs, was created on November 15, 2017, and it has the following songs
listed on it in this order:
i.	You Got Gold
ii.	Birds
iii.I Don’t Know Why
iv.	Everywhere I Go
*/

drop table SONG_PLAYLIST
drop table SONG
drop table PLAYLIST
drop table ALBUM

Select * from SONG

INSERT INTO PLAYLIST(playListName, playListCreatedOn)
VALUES('Songs to Play to Your Pugs', 'November 15, 2017')

INSERT INTO SONG_PLAYLIST(songPlayTrackNo, songID, playListID)
VALUES	(1, (SELECT songID FROM SONG WHERE songName = 'You Got Gold'), 1),
		(2, (SELECT songID FROM SONG WHERE songName = 'Birds'), 1),
		(3, (SELECT songID FROM SONG WHERE songName = 'I Don''t Know Why'), 1),
		(4, (SELECT songID FROM SONG WHERE songName = 'Everywhere I Go'), 1)

/*
2B.	On November 20, 2017, the playlist, Hipster’s Guide to Break-ups was created. It has these songs
on it in the following order:
i.	I Don’t Know Why
ii.	Everywhere I Go
iii.Nobody Can Save Me
iv.	Say Goodbye
v.	Talking To Myself
*/

INSERT INTO PLAYLIST(playListName, playListCreatedOn)
VALUES('Hipster''s Guide to Break-ups', 'November 20, 2017')

INSERT INTO SONG_PLAYLIST(songPlayTrackNo, songID, playListID)
VALUES	(1, (SELECT songID FROM SONG WHERE songName = 'I Don''t Know Why'), 2),
		(2, (SELECT songID FROM SONG WHERE songName = 'Everywhere I Go'), 2),
		(3, (SELECT songID FROM SONG WHERE songName = 'Nobody Can Save Me'), 2),
		(4, (SELECT songID FROM SONG WHERE songName = 'Say Goodbye'), 2),
		(5, (SELECT songID FROM SONG WHERE songName = 'Talking To Myself'), 2)
/*
3C.	Lastly, the playlist, The New State of Chill, has these 3 songs on it:
i.	Everywhere I Go
ii.	A Hymn for the Weekend
iii.Say Goodbye
*/
INSERT INTO PLAYLIST(playListName)
VALUES ('The New State of Chill')

INSERT INTO SONG_PLAYLIST(songPlayTrackNo, songID, playListID)
VALUES
(1, (SELECT songID FROM SONG WHERE songName = 'Everywhere I Go'), 3),
(2, (SELECT songID FROM SONG WHERE songName = 'A Hymn for the Weekend'), 3),
(3, (SELECT songID FROM SONG WHERE songName = 'Say Goodbye'), 3)

/*3A For each playlist, show its playlist name, songs, and song lengths*/

SELECT PLAYLIST.playListName, SONG.songName, SONG.songLength
FROM SONG_PLAYLIST
JOIN SONG ON SONG_PLAYLIST.songID = SONG.songID
JOIN PLAYLIST ON SONG_PLAYLIST.playListID = PLAYLIST.playListID


/*3B How many days has it been since the playlist, Songs to Play to Your Pugs, was created?*/

Select DATEDIFF(DAY, playListCreatedOn, GETDATE()) AS 'Days Since Created'
FROM PLAYLIST
WHERE playListName = 'Songs to Play to Your Pugs'

/*3C How many songs does each playlist have and what is its total playtime?
Put the playListID in the output, too*/

SELECT PLAYLIST.playListID, playListName, COUNT(SONG_PLAYLIST.songID) AS '# of Songs', SUM(songLength) as 'Total Playtime'
FROM SONG_PLAYLIST
JOIN SONG
ON SONG.songID = SONG_PLAYLIST.songID
JOIN PLAYLIST
ON PLAYLIST.playListID = SONG_PLAYLIST.playListID
GROUP BY PLAYLIST.playListName, PLAYLIST.playListID

/*3D For each song, show how many times it appears in a playlist.
Include songs that are not on a playlist in your output*/

SELECT SONG.songName, COUNT(SONG_PLAYLIST.playListID) AS 'Times in Playlist'
FROM SONG_PLAYLIST RIGHT JOIN SONG ON SONG.songID = SONG_PLAYLIST.songID
GROUP BY SONG.songName;

/*3E Which album has its songs listed in playlists 4 or more times?*/

SELECT albumName, COUNT(SONG_PLAYLIST.songID) AS '# of Times Songs Appear in PlayLists'
FROM SONG_PLAYLIST
JOIN SONG ON SONG_PLAYLIST.songID = SONG.songID
JOIN ALBUM ON SONG.albumID = ALBUM.albumID
GROUP BY albumName
HAVING COUNT(SONG_PLAYLIST.songID) >= 4

--3F Bonus question!!! Which song has appeared in every playlist?

SELECT songName
FROM SONG_PLAYLIST
JOIN SONG ON SONG_PLAYLIST.songID = SONG.songID
WHERE SONG_PLAYLIST
GROUP BY songName