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 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
How many performances are there?
assistant
SELECT count(*) FROM performance
human
What is the date of the performance with the highest number of attendees?
assistant
SELECT Date FROM performance ORDER BY Attendance DESC LIMIT 1
human
What is the average number of attendees for performances?
assistant
SELECT avg ( Attendance ) FROM performance ;