-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy pathSQL-zoo musicians solutions
120 lines (92 loc) · 2.78 KB
/
SQL-zoo musicians solutions
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
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
Solutions to Musicians - Hard Questions
Not available elsewhere
Scripts can be pasted and run at link below:
http://sqlzoo.net/wiki/Musicians_hard_questions
11.
select a.m_name, c.place_town
from
(select * from musician) a
join
(select born_in from musician where m_name = 'James First') b
join
(select * from place) c
on a.born_in = b.born_in and a.born_in = c.place_no
12.
select a.m_name as musician, count(e.cmpn_no) as compositions, count(c.instrument) as instruments
from
(select * from musician) a
join
(select * from place where place_country = 'England') b
join
(select * from performer) c
join
(select * from composer) d
join
(select * from has_composed) e
on a.born_in = b.place_no and a.m_no = c.perf_is and a.m_no= d.comp_is and d.comp_no = e.cmpn_no
group by a.m_name
13.
select b.band_name as band, a.m_name as conductor, b.band_contact as contact
from
(select * from musician) a
join
(select * from band) b
join
(select * from concert where concert_venue = 'Royal Albert Hall') c
join
(select * from performance) d
on a.m_no = d.conducted_by and d.performed_in = c.concert_no and d.gave = b.band_no
14.
# Try changing third column to 'Salzburg' so it will populate
select a.* from
(select m_name as name,
case when m_no in (select m_no from musician
join place on musician.born_in = place.place_no and place.place_town ='Glasgow')
then 'Yes' else null end as Born_In,
case when m_no in (select m_no from musician
join place on musician.living_in = place.place_no and place.place_town ='Glasgow')
then 'Yes' else null end as Lives_In,
case when m_no in (select a.m_no from
(select m_no from musician) a
join
(select * from plays_in) b
join
(select * from performance) c
join
(select * from place where place.place_town ='Glasgow' ) d
join
(select * from concert) e
on a.m_no = b.player
and b.band_id = c.gave
and c.performed_in = e.concert_no
and d.place_no = e.concert_in) then 'Yes' else null end as Performed_In,
case when m_no in
(select a.m_no
from
(select m_no from musician) a
join
(select * from plays_in) b
join
(select * from band) c
join
(select * from place where place.place_town ='Glasgow' ) d
on a.m_no = b.player and b.band_id = c.band_no and c.band_home = d.place_no)
then 'Yes' else null end as In_Band_In
from musician ) a
where (a.born_in is not null or a.lives_in is not null or a.performed_in is not null or a.in_band_in is not null)
15.
# Bad question - there are no musicians that overlap bands in this dataset
select a.player, a.band_id, b.band_id
from
(select * from plays_in
where band_id in
(select b.band_id from musician a
join plays_in b
on a.m_no = b.player where a.m_name = 'James First')) a
join
(select * from plays_in
where band_id in
(select b.band_id from musician a
join plays_in b
on a.m_no = b.player where a.m_name = 'Davis Heavan')) b
on a.player = b.player