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
What is the average number of attendees for performances?
assistant
SELECT avg(Attendance) FROM performance
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 most common location of performances.
assistant
SELECT LOCATION FROM performance GROUP BY LOCATION ORDER BY COUNT(*) DESC LIMIT 1
human
Show different locations and the number of performances at each location.
assistant
SELECT LOCATION , COUNT(*) FROM performance GROUP BY LOCATION
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 ;