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 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
What is the average number of attendees for performances?
assistant
SELECT avg(Attendance) 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
Show the names of members and the location of performances they attended in ascending alphabetical order of their names.
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 ORDER BY T2.Name ASC
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
List the hosts of performances in ascending order of attendance.
assistant
SELECT HOST FROM performance ORDER BY Attendance ASC ;