Question

a. Write five select statements to select data from each table individually.

b. Write one select statement with joins to list the playlist name, playlist track number, song name, artist name, album name, sorted by playlist name and playlist track number.

DROP TABLE Artists CASCADE CONSTRAINTS;
DROP TABLE Albums CASCADE CONSTRAINTS;
DROP TABLE Songs CASCADE CONSTRAINTS;
DROP TABLE Playlists CASCADE CONSTRAINTS;
DROP TABLE PlaylistSongs CASCADE CONSTRAINTS;

CREATE TABLE Artists (
ArtistID int NOT NULL,
ArtistName varchar(255),
PRIMARY KEY (ArtistID)
);

CREATE TABLE Albums(
AlbumID int NOT NULL,
AlbumName varchar(255),
YearReleased int,
ArtistID int NOT NULL,
PRIMARY KEY (AlbumID)
);

CREATE TABLE Songs(
SongID int NOT NULL,
SongName varchar(255),
TrackNumber int,
AlbumID int NOT NULL,
PRIMARY KEY (SongID)
);

CREATE TABLE Playlists(
PlaylistID int NOT NULL,
PlaylistName varchar(255),
PRIMARY KEY(PlaylistID)
);

CREATE TABLE PlaylistSongs
(
PlaylistID int NOT NULL,
SongID int NOT NULL,
PlaylistTRackNumber int,
PRIMARY KEY(PlaylistID,SongID)
);

INSERT INTO Artists (ArtistID, ArtistName)
VALUES ('1', 'Primus');
INSERT INTO Artists (ArtistID, ArtistName)
VALUES ('2', 'Captain Murphy');
INSERT INTO Artists (ArtistID, ArtistName)
VALUES ('3', 'King Krule');

INSERT INTO Albums (AlbumID, AlbumName, YearReleased, ArtistID)
VALUES ('1', 'Sailing the Seas of Cheese', '1991', '1');
INSERT INTO Albums (AlbumID, AlbumName, YearReleased, ArtistID)
VALUES ('2', 'Frizzle Fry', '1990', '1');
INSERT INTO Albums (AlbumID, AlbumName, YearReleased, ArtistID)
VALUES ('3', 'Duality', '2012', '2');
INSERT INTO Albums (AlbumID, AlbumName, YearReleased, ArtistID)
VALUES ('4', '6 Feet Beneath the Moon', '2013', '3');

INSERT INTO Songs (SongID, SongName, TrackNumber, AlbumID)
VALUES ('1', 'John The Fisherman', '6', '2');
INSERT INTO Songs (SongID, SongName, TrackNumber, AlbumID)
VALUES ('2', 'Jerry was a Race Car Driver', '5', '1');
INSERT INTO Songs (SongID, SongName, TrackNumber, AlbumID)
VALUES ('3', 'Tommy the Cat', '9', '1');
INSERT INTO Songs (SongID, SongName, TrackNumber, AlbumID)
VALUES ('4', 'The Killing Joke', '9', '3');
INSERT INTO Songs (SongID, SongName, TrackNumber, AlbumID)
VALUES ('5', 'Out Getting Ribs', '13', '4');

INSERT INTO Playlists (PlaylistID, PlaylistName)
VALUES ('1', 'Putting in Work');
INSERT INTO Playlists (PlaylistID, PlaylistName)
VALUES ('2', 'Relaxing');

INSERT INTO PlaylistSongs (PlaylistID, SongID, PlaylistTRackNumber)
VALUES ('1', '1', '1');
INSERT INTO PlaylistSongs (PlaylistID, SongID, PlaylistTRackNumber)
VALUES ('1', '2', '2');
INSERT INTO PlaylistSongs (PlaylistID, SongID, PlaylistTRackNumber)
VALUES ('2', '4', '1');
INSERT INTO PlaylistSongs (PlaylistID, SongID, PlaylistTRackNumber)
VALUES ('2', '5', '2');

Answer 1

a. Write five select statements to select data from each table individually.

a.1 Select all details of the albums which is released after 2000.

select * from Albums where YearReleased > 2000;

Number of Records: 2

AlbumID	AlbumName	YearReleased	ArtistID
3	Duality	2012	2
4	6 Feet Beneath the Moon	2013	3

a.2 Give the artists detail whose name is not Primus.

select ArtistId, ArtistName from Artists where ArtistName != 'Primus';

Number of Records: 2

ArtistID	ArtistName
2	Captain Murphy
3	King Krule

a.3 Display the song name and song tack number where song name is equal to Tommy the Cat or album id is greater than 3.

select SongName, TrackNumber from Songs
where SongName = 'Tommy the Cat' or AlbumID > 3;

Result:

Number of Records: 2

SongName	TrackNumber
Tommy the Cat	9
Out Getting Ribs	13

a.4 Give all the details of the playlist whose id is 1.

select * from Playlists where PlaylistID = 1;

Number of Records: 1

PlaylistID	PlaylistName
1	Putting in Work

a.5 Provide the total number of songs in the playlist songs.

select count(*) from PlaylistSongs;

Number of Records: 1

count(*)
4

b. Write one select statement with joins to list the playlist name, playlist track number, song name, artist name, album name, sorted by playlist name and playlist track number.

select PlaylistName, PlaylistTRackNumber, SongName, ArtistName, AlbumName from Playlists
inner join PlaylistSongs on PlaylistSongs.PlaylistID = Playlists.PlaylistID
inner join Songs on Songs.SongID = PlaylistSongs.SongID
inner join Artists on Artists.ArtistID = Albums.ArtistID
inner join Albums on Albums.AlbumID = Songs.AlbumID;

Number of Records: 4

PlaylistName	PlaylistTRackNumber	SongName	ArtistName	AlbumName
Putting in Work	1	John The Fisherman	Primus	Frizzle Fry
Putting in Work	2	Jerry was a Race Car Driver	Primus	Sailing the Seas of Cheese
Relaxing	1	The Killing Joke	Captain Murphy	Duality
Relaxing	2	Out Getting Ribs	King Krule	6 Feet Beneath the Moon