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
How many performances are there?
assistant
SELECT count(*) FROM performance
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 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
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
List the names of members who did not attend any performance.
assistant
SELECT Name FROM member WHERE Member_ID NOT IN ( SELECT Member_ID FROM member_attendance ) ;