· 8 years ago · Apr 17, 2018, 09:22 AM
1DROP DATABASE IF EXISTS music_DB;
2CREATE DATABASE music_DB;
3USE music_DB;
4
5CREATE TABLE musicians (
6ID INT AUTO_INCREMENT PRIMARY KEY,
7name VARCHAR(30) NOT NULL,
8birth_date DATE NULL,
9nationality VARCHAR(30) NULL,
10gender ENUM ('male', 'female') NULL);
11
12INSERT INTO musicians (name, birth_date, nationality, gender)
13VALUES ('Thomas Newman', '1955-10-20', 'U.S.', 'male'),
14 ('Hans Zimmer', '1957-09-12', 'German', 'male'),
15 ('Steven Wilson', '1967-11-03', 'English', 'male'),
16 ('Alice Cooper', '1948-02-04', 'U.S.', 'male'),
17 ('James Kent', NULL, 'French', 'male');
18
19CREATE TABLE types (
20ID INT AUTO_INCREMENT PRIMARY KEY,
21type VARCHAR(30) NOT NULL);
22
23INSERT INTO types (type)
24VALUES ('composer'),
25 ('artist');
26
27CREATE TABLE musician_types (
28musician_ID INT,
29CONSTRAINT FOREIGN KEY(musician_ID) REFERENCES musicians(ID),
30type_ID INT,
31CONSTRAINT FOREIGN KEY(type_ID) REFERENCES types(ID),
32PRIMARY KEY (musician_ID, type_ID));
33
34INSERT INTO musician_types
35VALUES (1, 1),
36 (1, 2),
37 (2, 1),
38 (2, 2),
39 (3, 1),
40 (3, 2),
41 (4, 1),
42 (4, 2),
43 (5, 1),
44 (5, 2);
45
46CREATE TABLE songs (
47ID INT AUTO_INCREMENT PRIMARY KEY,
48name VARCHAR(30) NOT NULL,
49style VARCHAR(30) NOT NULL,
50arrangement VARCHAR(30) NULL,
51duration TIME NULL);
52
53INSERT INTO songs (name, style, arrangement, duration)
54VALUES ('Raining Steel', 'Synthwave', NULL, '00:04:09'),
55 ('Welcome to the Nocturne City', 'Synthwave', NULL, '00:05:46'),
56 ('Any Other Name', 'Instrumental', 'Classical', '00:04:09'),
57 ('The Fire Rises', 'Instrumental', 'Classical', '00:05:33'),
58 ('The Raven That Refused to Sing', 'Rock', NULL, '00:07:57');
59
60CREATE TABLE genres (
61ID INT AUTO_INCREMENT PRIMARY KEY,
62name VARCHAR(50));
63
64
65INSERT INTO genres (name)
66VALUES ('Electronic'),
67 ('Industrial'),
68 ('Pop'),
69 ('Classical'),
70 ('Progressive Rock'),
71 ('Metal'),
72 ('Jazz');
73
74CREATE TABLE composers_songs (
75song_ID INT NOT NULL,
76CONSTRAINT FOREIGN KEY(song_ID) REFERENCES songs(ID),
77composer_ID INT NOT NULL,
78CONSTRAINT FOREIGN KEY(composer_ID) REFERENCES musicians(ID),
79PRIMARY KEY (song_ID, composer_ID));
80
81INSERT INTO composers_songs (song_ID, composer_ID)
82VALUES (1, 5),
83 (2, 5),
84 (3, 1),
85 (4, 2),
86 (5, 3);
87
88CREATE TABLE artists_songs (
89song_ID INT NOT NULL,
90CONSTRAINT FOREIGN KEY(song_ID) REFERENCES songs(ID),
91artist_ID INT NOT NULL,
92CONSTRAINT FOREIGN KEY(artist_ID) REFERENCES musicians(ID),
93PRIMARY KEY (song_ID, artist_ID));
94
95INSERT INTO artists_songs (song_ID, artist_ID)
96VALUES (1, 5),
97 (2, 5),
98 (3, 1),
99 (4, 2),
100 (5, 3);
101
102CREATE TABLE genres_songs (
103song_ID INT NOT NULL,
104CONSTRAINT FOREIGN KEY(song_ID) REFERENCES songs(ID),
105genre_ID INT NOT NULL,
106CONSTRAINT FOREIGN KEY (genre_ID) REFERENCES genres(ID),
107PRIMARY KEY(song_ID, genre_ID));
108
109INSERT INTO genres_songs (song_ID, genre_ID)
110VALUES ( 1, 1),
111 ( 1, 2),
112 ( 2, 1),
113 ( 2, 2),
114 ( 3, 4),
115 ( 4, 4),
116 ( 5, 5);
117
118#2
119
120SELECT songs.name AS Name, songs.duration AS Duration
121FROM songs
122WHERE songs.duration > '00:04:09';
123
124#3
125
126SELECT COUNT(musicians.name) AS Musicians, musicians.nationality AS Nationality
127FROM musicians
128GROUP BY musicians.nationality;
129
130#4.1 --> INNER JOIN Example
131
132SELECT s.name AS Song_Name, g.name AS Genre_Name
133FROM songs AS s
134JOIN genres AS g
135ON g.id IN (
136SELECT genres_songs.genre_ID
137FROM genres_songs
138WHERE genres_songs.song_ID = s.id
139);
140
141#4.2 --> OUTER JOIN Example
142
143SELECT s.name AS Song_Name, g.name AS Genre_Name
144FROM songs AS s
145RIGHT JOIN genres AS g
146ON g.id IN (
147SELECT genres_songs.genre_ID
148FROM genres_songs
149WHERE genres_songs.song_ID = s.id);
150
151#5
152
153SELECT comp.name AS Composer_Name, COUNT(s.ID) AS Song_Count
154FROM songs AS s
155JOIN musicians AS comp
156ON comp.ID IN (
157SELECT composers_songs.composer_ID
158FROM composers_songs
159WHERE composers_songs.song_ID = s.id)
160GROUP BY comp.ID;