ECNU数据库作业——Lab3

Lab 3

EX1

What are the names and release years for all the songs of the youngest singer?

SELECT singer.`Name` AS Singer_name, singer.Song_Name, singer.Song_release_year
FROM singer
WHERE singer.Age = (
	SELECT MIN(singer.Age)
	FROM singer
);

EX2

List all song names by singers above the average age.

SELECT singer.Song_Name, singer.`Name`, singer.Age
FROM singer
WHERE singer.Age > (
	SELECT AVG(singer.Age)
	FROM singer
);

EX3

Show the stadium name and the number of concerts in each stadium.

# 使用COALESCE()函数将没有匹配的空值设置为0，便于统计count
SELECT statium.Stadium_ID, stadium.`Name`, COALESCE(COUNT(c.Stadium_ID), 0) AS `number of concerts`
FROM stadium
# 使用左外连接
LEFT JOIN concert ON stadium.Stadium_ID = concert.Stadium_ID
GROUP BY stadium.Stadium_ID, stadium.`Name`
ORDER BY `number of concerts` DESC

EX4

What is the name and capacity of the stadium with the most concerts after 2013 ?

SELECT stadium.`Name`, stadium.Capacity 
FROM stadium 
JOIN concert ON stadium.Stadium_ID = concert.Stadium_ID 
GROUP BY stadium.Stadium_ID 
HAVING COUNT(concert.Stadium_ID) = ( 
    # 筛选举办演唱会次数最多的场馆
	SELECT MAX(count_concerts) 
	FROM (
        # 统计场馆举办的演唱会次数
		SELECT COUNT(concert.Stadium_ID) AS count_concerts 
		FROM concert 
		WHERE concert.`Year` > 2013 
		GROUP BY concert.Stadium_ID 
	) AS subquery_maxCount
)

EX5

Show names for all stadiums except for stadiums having a concert in year 2014.

SELECT stadium.`Name`
FROM stadium
# 左连接，并添加2014年的限定条件：可以实现将非2014年举办演唱会/没有举办过演唱会的场馆ID全部设置为NULL
LEFT JOIN concert ON concert.Stadium_ID = stadium.Stadium_ID AND concert.`Year` = 2014
WHERE concert.Stadium_ID IS NULL

EX6

Find the female singers who have participated in the ‘Auditions’ concert but have not participated in the ‘Super bootcamp’ concert. Additionally, their song names should start with a vowel (A, E, I, O, U). Return the name of the singer, the name of the song, the year of the concert, and the name and location of the stadium where the concert was held. Also, sort the results in descending order by the year of the concert.

SELECT singer.`Name` AS singer_name, singer.Song_Name, concert.`Year`, stadium.`Name` AS stadium_name, stadium.Location
FROM singer
JOIN singer_in_concert ON singer_in_concert.Singer_ID = singer.Singer_ID
JOIN concert ON concert.concert_ID = singer_in_concert.concert_ID
JOIN stadium ON stadium.Stadium_ID = concert.Stadium_ID
WHERE (
    # 女性歌手
	singer.Is_male = 0 
    # 参加过'Auditions'演唱会
	AND concert.concert_Name = 'Auditions' 
    # 没参加过'Super bootcamp'演唱会，使用 NOT IN 筛选
	AND singer.Singer_ID NOT IN (
		SELECT singer.Singer_ID
		FROM singer
		JOIN singer_in_concert ON singer_in_concert.Singer_ID = singer.Singer_ID
		JOIN concert ON concert.concert_ID = singer_in_concert.concert_ID
		WHERE concert.concert_Name = 'Super bootcamp'
	)
    # 歌曲名称以元音开头
	AND singer.Song_Name REGEXP '^[AEIOU]'
)
ORDER BY concert.`Year` DESC