SELECT musician_name, birthday FROM Musicians WHERE birthday BETWEEN'1997-01-01'AND'2012-12-31';
b. 列出每个乐队的总演出次数。包括乐队名称。
1 2 3 4 5 6 7 8 9 10
SELECT b.band_name, COUNT(ep.event_id) as performance_count -- as重命名 FROM Bands b -- join:连接 LEFTJOIN EventPerformances ep ON b.band_id = ep.band_id -- 分组,去除重复的数据 GROUPBY b.band_id, b.band_name -- order by对查找的数据排序 ORDERBY performance_count DESC;
SELECT b.band_name, b.genre, b.formation_year, SUM(CASE WHEN ep.headliner =TRUETHEN e.ticket_revenue *0.8*0.6 ELSE (e.ticket_revenue *0.8*0.4) / (SELECTCOUNT(*) FROM EventPerformances ep2 WHERE ep2.event_id = e.event_id AND ep2.headliner =FALSE) END) as total_ticket_revenue, SUM(CASE WHEN ep.headliner =TRUETHEN e.merchandise_sales *0.6 ELSE (e.merchandise_sales *0.4) / (SELECTCOUNT(*) FROM EventPerformances ep2 WHERE ep2.event_id = e.event_id AND ep2.headliner =FALSE) END) as total_merchandise_revenue FROM Bands b LEFTJOIN EventPerformances ep ON b.band_id = ep.band_id LEFTJOIN Events e ON ep.event_id = e.event_id GROUPBY b.band_id, b.band_name, b.genre, b.formation_year ORDERBY total_ticket_revenue DESC;
d. 列出所有在2023年3月期间,除了“The Waves”或“Groove Squad”之外的所有乐队都演出的场馆名称。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17
SELECTDISTINCT v.venue_name FROM Venues v JOIN Events e ON v.venue_id = e.venue_id JOIN EventPerformances ep ON e.event_id = ep.event_id JOIN Bands b ON ep.band_id = b.band_id WHERE e.event_date BETWEEN'2023-03-01'AND'2023-03-31' GROUPBY v.venue_name HAVINGCOUNT(DISTINCT b.band_name) = (SELECTCOUNT(DISTINCT b2.band_name) FROM Bands b2 JOIN EventPerformances ep2 ON b2.band_id = ep2.band_id JOIN Events e2 ON ep2.event_id = e2.event_id WHERE e2.venue_id = v.venue_id AND e2.event_date BETWEEN'2023-03-01'AND'2023-03-31' AND b2.band_name NOTIN ('The Waves', 'Groove Squad'));
SELECT v.venue_name FROM Venues v JOIN Events e ON v.venue_id = e.venue_id JOIN EventPerformances ep ON e.event_id = ep.event_id JOIN Bands b ON ep.band_id = b.band_id WHERE e.event_date BETWEEN'2023-03-01'AND'2023-03-31' GROUPBY v.venue_name HAVINGCOUNT(DISTINCTCASE WHEN b.band_name NOTIN ('The Waves', 'Groove Squad') THEN b.band_name END) = ( SELECTCOUNT(DISTINCT band_name) FROM Bands WHERE band_name NOTIN ('The Waves', 'Groove Squad') );
e. 编写一个查询,为每个场馆列出年末总结,显示场馆名称、举办的活动数量、总门票收入、总商品销售额和每个活动的平均观众规模。
1 2 3 4 5 6 7 8 9 10 11 12
SELECT v.venue_name, COUNT(e.event_id) as total_events, SUM(e.ticket_revenue) as total_ticket_revenue, SUM(e.merchandise_sales) as total_merchandise_sales, ROUND(AVG(e.audience_size), 0) as avg_audience_size FROM Venues v LEFTJOIN Events e ON v.venue_id = e.venue_id GROUPBY v.venue_id, v.venue_name ORDERBY total_events DESC;
# ROUND(..., 0): 将平均观众数四舍五入到整数
f. 列出那些是在他们出生日期之前成立的乐队的音乐家。
1 2 3 4 5 6 7 8 9 10
SELECTDISTINCT m.musician_name, m.birthday, b.band_name, b.formation_year FROM Musicians m JOIN BandMembers bm ON m.musician_id = bm.musician_id JOIN Bands b ON bm.band_id = b.band_id WHEREYEAR(m.birthday) > b.formation_year ORDERBY m.musician_name;
g. 选择一个特定的地点(场馆),列出所有在任何爵士主题活动(与爵士音乐相关的活动)中演出的乐队。如果一个乐队演出了多次,只包括第一次演出的日期。
1 2 3 4 5 6 7 8 9 10 11
SELECT b.band_name, MIN(e.event_date) as first_performance_date FROM Venues v JOIN Events e ON v.venue_id = e.venue_id JOIN EventPerformances ep ON e.event_id = ep.event_id JOIN Bands b ON ep.band_id = b.band_id WHERE v.venue_name ='The Pavilion'-- 这里可以更换成你想查询的场馆名称 AND e.theme LIKE'%Jazz%'-- 匹配包含'Jazz'的主题 GROUPBY b.band_id, b.band_name ORDERBY first_performance_date;
h. 找出观众规模最大的活动(如果有多个活动)。列出场馆、日期和演出的乐队。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19
SELECT v.venue_name, e.event_date, e.audience_size, b.band_name, CASE WHEN ep.headliner =TRUETHEN'Headliner' ELSE'Support' ENDas band_role FROM Events e JOIN Venues v ON e.venue_id = v.venue_id JOIN EventPerformances ep ON e.event_id = ep.event_id JOIN Bands b ON ep.band_id = b.band_id WHERE e.audience_size = ( SELECTMAX(audience_size) FROM Events ) ORDERBY e.event_date;
i. 找出在2023年3月期间版税收入最高的音乐家(如果有多位音乐家)。包括他们的姓名、他们演奏的所有乐器和总版税收入。
# 先创建了一个新表 WITH MusicianRevenue AS ( SELECT m.musician_id, m.musician_name, GROUP_CONCAT(DISTINCT bm.instrument) as instruments, # 合并所有的乐器 SUM( CASE WHEN ep.headliner =TRUETHEN e.ticket_revenue *0.8*0.6* bm.royalty_rate ELSE (e.ticket_revenue *0.8*0.4/ (SELECTCOUNT(*) FROM EventPerformances ep2 WHERE ep2.event_id = e.event_id AND ep2.headliner =FALSE) ) * bm.royalty_rate END ) as total_royalty FROM Musicians m JOIN BandMembers bm ON m.musician_id = bm.musician_id JOIN EventPerformances ep ON bm.band_id = ep.band_id JOIN Events e ON ep.event_id = e.event_id WHERE e.event_date BETWEEN'2023-03-01'AND'2023-03-31' GROUPBY m.musician_id, m.musician_name ) SELECT musician_name, instruments, total_royalty FROM MusicianRevenue WHERE total_royalty = ( SELECTMAX(total_royalty) FROM MusicianRevenue ) ORDERBY musician_name;
简化版本:只能查询主乐队的成员的总税收
1 2 3 4 5 6 7 8 9 10 11
SELECT m.musician_id, m.musician_name, SUM(e.ticket_revenue *0.8*0.6* bm.royalty_rate) as total_royalty FROM Musicians m JOIN BandMembers bm ON m.musician_id = bm.musician_id JOIN EventPerformances ep ON bm.band_id = ep.band_id JOIN Events e ON ep.event_id = e.event_id WHERE e.event_date BETWEEN'2023-03-01'AND'2023-03-31' AND ep.headliner ='True' GROUPBY m.musician_id, m.musician_name
j. 识别任何在乐队中也是独奏艺术家的音乐家。此外,确定这些独奏艺术家在演出时是否演奏了多种乐器,并列出他们与乐队一起演出的日期。
-- 先找出独奏艺术家(假设独奏艺术家就是自己组成的乐队) WITH SoloArtists AS ( SELECTDISTINCT m.musician_id, m.musician_name FROM Musicians m JOIN BandMembers bm ON m.musician_id = bm.musician_id JOIN Bands b ON bm.band_id = b.band_id GROUPBY b.band_id, m.musician_id, m.musician_name HAVINGCOUNT(bm.musician_id) =1 ), -- 找出多人乐队的成员 BandArtists AS ( SELECTDISTINCT m.musician_id, m.musician_name FROM Musicians m JOIN BandMembers bm ON m.musician_id = bm.musician_id JOIN Bands b ON bm.band_id = b.band_id GROUPBY b.band_id, m.musician_id, m.musician_name HAVINGCOUNT(bm.musician_id) >1 ) -- 主查询 SELECT m.musician_name, e.event_date, b.band_name FROM Musicians m JOIN BandMembers bm ON m.musician_id = bm.musician_id JOIN Bands b ON bm.band_id = b.band_id JOIN EventPerformances ep ON b.band_id = ep.band_id JOIN Events e ON ep.event_id = e.event_id WHERE m.musician_id IN ( SELECT s.musician_id FROM SoloArtists s JOIN BandArtists ba ON s.musician_id = ba.musician_id ) GROUPBY m.musician_name, e.event_date, b.band_name ORDERBY m.musician_name, e.event_date;