-
Notifications
You must be signed in to change notification settings - Fork 0
/
Challenge: Playlist Maker
95 lines (79 loc) · 2.9 KB
/
Challenge: Playlist Maker
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
/* Create a database of songs and artists */
CREATE TABLE artists (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT,
country TEXT,
genre TEXT);
INSERT INTO artists (name, country, genre)
VALUES ("Taylor Swift", "US", "Pop");
INSERT INTO artists (name, country, genre)
VALUES ("Led Zeppelin", "US", "Hard rock");
INSERT INTO artists (name, country, genre)
VALUES ("Lata", "India", "Bollywood");
INSERT INTO artists (name, country, genre)
VALUES ("Queen", "UK", "Rock");
INSERT INTO artists (name, country, genre)
VALUES ("Celine Dion", "Canada", "Pop");
INSERT INTO artists (name, country, genre)
VALUES ("Meatloaf", "US", "Hard rock");
INSERT INTO artists (name, country, genre)
VALUES ("Garth Brooks", "US", "Country");
INSERT INTO artists (name, country, genre)
VALUES ("Shania Twain", "Canada", "Country");
INSERT INTO artists (name, country, genre)
VALUES ("Rihanna", "US", "Pop");
INSERT INTO artists (name, country, genre)
VALUES ("Guns N' Roses", "US", "Hard rock");
INSERT INTO artists (name, country, genre)
VALUES ("Gloria Estefan", "US", "Pop");
INSERT INTO artists (name, country, genre)
VALUES ("Bob Marley", "Jamaica", "Reggae");
INSERT INTO artists (name, country, genre)
VALUES ("Lata", "India", "Bollywood");
INSERT INTO artists (name, country, genre)
VALUES ("Sunidhi", "India", "Bollywood");
CREATE TABLE songs (
id INTEGER PRIMARY KEY AUTOINCREMENT,
artist TEXT,
title TEXT);
INSERT INTO songs (artist, title)
VALUES ("Taylor Swift", "Shake it off");
INSERT INTO songs (artist, title)
VALUES ("Rihanna", "Stay");
INSERT INTO songs (artist, title)
VALUES ("Celine Dion", "My heart will go on");
INSERT INTO songs (artist, title)
VALUES ("Celine Dion", "A new day has come");
INSERT INTO songs (artist, title)
VALUES ("Shania Twain", "Party for two");
INSERT INTO songs (artist, title)
VALUES ("Gloria Estefan", "Conga");
INSERT INTO songs (artist, title)
VALUES ("Led Zeppelin", "Stairway to heaven");
INSERT INTO songs (artist, title)
VALUES ("ABBA", "Mamma mia");
INSERT INTO songs (artist, title)
VALUES ("Queen", "Bicycle Race");
INSERT INTO songs (artist, title)
VALUES ("Queen", "Bohemian Rhapsody");
INSERT INTO songs (artist, title)
VALUES ("Guns N' Roses", "Don't cry");
/* In this first step, select the title of all the songs by the artist named 'Queen'. */
SELECT title
FROM songs
WHERE artist = "Queen" ;
/*
Step 2
Now you'll make a 'Pop' playlist. In preparation, select the name of all of the artists from the 'Pop' genre
*/
SELECT name
FROM artists
WHERE genre LIKE 'Pop' ;
/*
To finish creating the 'Pop' playlist, add another query that will select the title of all the songs from the 'Pop' artists.
It should use IN on a nested subquery that's based on your previous query.
*/
SELECT title
FROM songs
WHERE artist
LIKE (SELECT name FROM artists WHERE genre like 'Pop') ;