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 most common location of performances.
assistant
SELECT LOCATION FROM performance GROUP BY LOCATION ORDER BY COUNT(*) DESC LIMIT 1
human
Show the dates of performances with attending members whose roles are 'Violin'.
assistant
SELECT 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 WHERE T2.Role = 'Violin'
human
What are the dates and locations of performances?
assistant
SELECT Date , LOCATION FROM performance
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 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 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 ;