human
Schema:
member (Member_ID, Name, Nationality, Role)
performance (Performance_ID, Date, Host, Location, Attendance)
member_attendance (Member_ID, Performance_ID, Num_of_Pieces)
Please answer with SQL.
assistant
Sure, I understand.
human
Show the names of members and the dates of performances they attended in descending order of attendance of the performances.
assistant
SELECT T2.Name , T3.Date FROM member_attendance AS T1 JOIN member AS T2 ON T1.Member_ID = T2.Member_ID JOIN performance AS T3 ON T1.Performance_ID = T3.Performance_ID ORDER BY T3.Attendance DESC
human
Show the locations that have at least two performances.
assistant
SELECT LOCATION FROM performance GROUP BY LOCATION HAVING COUNT(*) >= 2
human
Show the locations that have both performances with more than 2000 attendees and performances with less than 1000 attendees.
assistant
SELECT LOCATION FROM performance WHERE Attendance > 2000 INTERSECT SELECT LOCATION FROM performance WHERE Attendance < 1000
human
List the hosts of performances in ascending order of attendance.
assistant
SELECT HOST FROM performance ORDER BY Attendance ASC
human
Show the names of members and the location of the performances they attended.
assistant
SELECT T2.Name , T3.Location FROM member_attendance AS T1 JOIN member AS T2 ON T1.Member_ID = T2.Member_ID JOIN performance AS T3 ON T1.Performance_ID = T3.Performance_ID
human
Show the attendances of the performances at location 'TD Garden' or 'Bell Centre
assistant
SELECT Attendance FROM performance WHERE LOCATION = "TD Garden" OR LOCATION = "Bell Centre" ;