· 8 years ago · Dec 18, 2017, 04:40 PM
1--**** Lab Preparation Tasks. Execute lines 2 - 74 now ****
2IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SONG]') AND type in (N'U'))
3DROP TABLE [dbo].[SONG]
4GO
5
6IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ALBUM]') AND type in (N'U'))
7DROP TABLE [dbo].[ALBUM]
8GO
9
10CREATE TABLE ALBUM
11(
12 albumID INT IDENTITY(1,1),
13 albumName VARCHAR(50) NOT NULL,
14 albumReleaseDate DATE,
15 albumLength SMALLINT,
16 albumIsDoubleLP BIT,
17
18 CONSTRAINT pk_album_albumID PRIMARY KEY(albumID)
19)
20
21INSERT INTO ALBUM (albumName, albumReleaseDate, albumLength, albumIsDoubleLP)
22VALUES ('Evolve', '2017-06-23', 2352, 0),
23 ('The Whole Enchilada', '2017-06-23', 2899, 1),
24 ('A Head Full Of Dreams', '2015-12-04', 2745, 0),
25 ('Everything Now', '2017-07-28', 2831, 0),
26 ('One More Light', '2017-05-19', 2119, 0)
27
28CREATE TABLE SONG
29(
30 songID INT IDENTITY(1,1),
31 songTrackNo TINYINT NOT NULL,
32 songName VARCHAR(50) NOT NULL,
33 songLength SMALLINT,
34 songFileExtension VARCHAR(10),
35 albumID INT NOT NULL,
36
37 CONSTRAINT pk_song_songID PRIMARY KEY(songID),
38 CONSTRAINT fk_album_song FOREIGN KEY(albumID) REFERENCES album(albumID)
39)
40--Evolve songs
41INSERT INTO song(songTrackNo,songName,songLength,songFileExtension,albumID)
42VALUES (1,'I Don''t Know Why', 190,'mp4', 1),
43 (3, 'Believer', 204, 'mp4', 1),
44 (9, 'Thunder', 187, 'mp3', 1)
45
46--The Whole Enchilada Songs
47INSERT INTO song(songTrackNo,songName,songLength,songFileExtension,albumID)
48VALUES (1,'You Got Gold', 276, 'mp3',2),
49 (7, 'Everywhere I Go', 198, 'mp3',2),
50 (9, 'Memphis Money', 224, 'mp4',2),
51 (14,'Good Night', 200, 'mp4',2)
52
53--A Head Full Of Dreams Songs
54INSERT INTO song(songTrackNo,songName,songLength,songFileExtension,albumID)
55VALUES (2, 'Birds', 229, 'mp4',3),
56 (3, 'A Hymn for the Weekend', 258, 'mp3',3),
57 (6, 'Fun', 267, 'mp4',3),
58 (10, 'Colour Spectrum', NULL, NULL,3),
59 (11, 'Up & Up', NULL, NULL,3)
60
61--One More Light Songs
62INSERT INTO song(songTrackNo,songName,songLength,songFileExtension,albumID)
63VALUES (1, 'Nobody Can Save Me', 200, 'mp4',5),
64 (2, 'Say Goodbye', 180, 'mp4',5),
65 (3, 'Talking To Myself', 201, 'mp3',5),
66 (8, 'Halfway Right', 245, 'mp4',5)
67
68CREATE TABLE PLAYLIST
69(
70 playListID INT IDENTITY(1,1),
71 playListName VARCHAR(100) NOT NULL,
72 playListCreatedOn DATE,
73
74 CONSTRAINT pk_playList_playListID PRIMARY KEY(playListID)
75)
76
77--****END OF LAB PREP TASKS****
78
79--1: CREATE THE SONG_PLAYLIST TABLE HERE
80
81CREATE TABLE SONG_PLAYLIST
82(
83 songPlayID INT IDENTITY(1, 1),
84 songPlayTrackNo TINYINT NOT NULL,
85 songID INT NOT NULL,
86 playListID INT NOT NULL,
87
88 CONSTRAINT pk_song_playList PRIMARY KEY(songPlayID),
89 CONSTRAINT fk_songID FOREIGN KEY(songID) REFERENCES SONG(songID),
90 CONSTRAINT fk_playListID FOREIGN KEY(playListID) REFERENCES PLAYLIST(playListID)
91)
92
93/*
942A. Add the following playlist information to the database.
95The playlist, Songs to Play to Your Pugs, was created on November 15, 2017, and it has the following songs
96listed on it in this order:
97i. You Got Gold
98ii. Birds
99iii.I Don’t Know Why
100iv. Everywhere I Go
101*/
102
103drop table SONG_PLAYLIST
104drop table SONG
105drop table PLAYLIST
106drop table ALBUM
107
108Select * from SONG
109
110INSERT INTO PLAYLIST(playListName, playListCreatedOn)
111VALUES('Songs to Play to Your Pugs', 'November 15, 2017')
112
113INSERT INTO SONG_PLAYLIST(songPlayTrackNo, songID, playListID)
114VALUES (1, (SELECT songID FROM SONG WHERE songName = 'You Got Gold'), 1),
115 (2, (SELECT songID FROM SONG WHERE songName = 'Birds'), 1),
116 (3, (SELECT songID FROM SONG WHERE songName = 'I Don''t Know Why'), 1),
117 (4, (SELECT songID FROM SONG WHERE songName = 'Everywhere I Go'), 1)
118
119/*
1202B. On November 20, 2017, the playlist, Hipster’s Guide to Break-ups was created. It has these songs
121on it in the following order:
122i. I Don’t Know Why
123ii. Everywhere I Go
124iii.Nobody Can Save Me
125iv. Say Goodbye
126v. Talking To Myself
127*/
128
129INSERT INTO PLAYLIST(playListName, playListCreatedOn)
130VALUES('Hipster''s Guide to Break-ups', 'November 20, 2017')
131
132INSERT INTO SONG_PLAYLIST(songPlayTrackNo, songID, playListID)
133VALUES (1, (SELECT songID FROM SONG WHERE songName = 'I Don''t Know Why'), 2),
134 (2, (SELECT songID FROM SONG WHERE songName = 'Everywhere I Go'), 2),
135 (3, (SELECT songID FROM SONG WHERE songName = 'Nobody Can Save Me'), 2),
136 (4, (SELECT songID FROM SONG WHERE songName = 'Say Goodbye'), 2),
137 (5, (SELECT songID FROM SONG WHERE songName = 'Talking To Myself'), 2)
138/*
1393C. Lastly, the playlist, The New State of Chill, has these 3 songs on it:
140i. Everywhere I Go
141ii. A Hymn for the Weekend
142iii.Say Goodbye
143*/
144INSERT INTO PLAYLIST(playListName)
145VALUES ('The New State of Chill')
146
147INSERT INTO SONG_PLAYLIST(songPlayTrackNo, songID, playListID)
148VALUES
149(1, (SELECT songID FROM SONG WHERE songName = 'Everywhere I Go'), 3),
150(2, (SELECT songID FROM SONG WHERE songName = 'A Hymn for the Weekend'), 3),
151(3, (SELECT songID FROM SONG WHERE songName = 'Say Goodbye'), 3)
152
153/*3A For each playlist, show its playlist name, songs, and song lengths*/
154
155SELECT PLAYLIST.playListName, SONG.songName, SONG.songLength
156FROM SONG_PLAYLIST
157JOIN SONG ON SONG_PLAYLIST.songID = SONG.songID
158JOIN PLAYLIST ON SONG_PLAYLIST.playListID = PLAYLIST.playListID
159
160
161/*3B How many days has it been since the playlist, Songs to Play to Your Pugs, was created?*/
162
163Select DATEDIFF(DAY, playListCreatedOn, GETDATE()) AS 'Days Since Created'
164FROM PLAYLIST
165WHERE playListName = 'Songs to Play to Your Pugs'
166
167/*3C How many songs does each playlist have and what is its total playtime?
168Put the playListID in the output, too*/
169
170SELECT PLAYLIST.playListID, playListName, COUNT(SONG_PLAYLIST.songID) AS '# of Songs', SUM(songLength) as 'Total Playtime'
171FROM SONG_PLAYLIST
172JOIN SONG
173ON SONG.songID = SONG_PLAYLIST.songID
174JOIN PLAYLIST
175ON PLAYLIST.playListID = SONG_PLAYLIST.playListID
176GROUP BY PLAYLIST.playListName, PLAYLIST.playListID
177
178/*3D For each song, show how many times it appears in a playlist.
179Include songs that are not on a playlist in your output*/
180
181SELECT SONG.songName, COUNT(SONG_PLAYLIST.playListID) AS 'Times in Playlist'
182FROM SONG_PLAYLIST RIGHT JOIN SONG ON SONG.songID = SONG_PLAYLIST.songID
183GROUP BY SONG.songName;
184
185/*3E Which album has its songs listed in playlists 4 or more times?*/
186
187SELECT albumName, COUNT(SONG_PLAYLIST.songID) AS '# of Times Songs Appear in PlayLists'
188FROM SONG_PLAYLIST
189JOIN SONG ON SONG_PLAYLIST.songID = SONG.songID
190JOIN ALBUM ON SONG.albumID = ALBUM.albumID
191GROUP BY albumName
192HAVING COUNT(SONG_PLAYLIST.songID) >= 4
193
194--3F Bonus question!!! Which song has appeared in every playlist?
195
196SELECT songName
197FROM SONG_PLAYLIST
198JOIN SONG ON SONG_PLAYLIST.songID = SONG.songID
199WHERE SONG_PLAYLIST
200GROUP BY songName