human
Schema:
party (Party_ID, Party_Theme, Location, First_year, Last_year, Number_of_hosts)
host (Host_ID, Name, Nationality, Age)
party_host (Party_ID, Host_ID, Is_Main_in_Charge)
Please answer with SQL.
assistant
Sure, I understand.
human
Show the host names for parties with number of hosts greater than 20.
assistant
SELECT T2.Name FROM party_host AS T1 JOIN HOST AS T2 ON T1.Host_ID = T2.Host_ID JOIN party AS T3 ON T1.Party_ID = T3.Party_ID WHERE T3.Number_of_hosts > 20
human
Show the most common nationality of hosts.
assistant
SELECT Nationality FROM HOST GROUP BY Nationality ORDER BY COUNT(*) DESC LIMIT 1
human
Show the locations of parties and the names of the party hosts in ascending order of the age of the host.
assistant
SELECT T3.Location , T2.Name FROM party_host AS T1 JOIN HOST AS T2 ON T1.Host_ID = T2.Host_ID JOIN party AS T3 ON T1.Party_ID = T3.Party_ID ORDER BY T2.Age
human
Show the name and the nationality of the oldest host.
assistant
SELECT Name , Nationality FROM HOST ORDER BY Age DESC LIMIT 1
human
Show the locations of parties with hosts older than 50.
assistant
SELECT T3.Location FROM party_host AS T1 JOIN HOST AS T2 ON T1.Host_ID = T2.Host_ID JOIN party AS T3 ON T1.Party_ID = T3.Party_ID WHERE T2.Age > 50
human
Show the nations that have both hosts older than 45 and hosts younger than 35.
assistant
SELECT Nationality FROM HOST WHERE Age > 45 INTERSECT SELECT Nationality FROM HOST WHERE Age < 35 ;