human
Schema:
race (Race_ID, Name, Class, Date, Track_ID)
track (Track_ID, Name, Location, Seating, Year_Opened)
Please answer with SQL.
assistant
Sure, I understand.
human
Show the name of track with most number of races.
assistant
SELECT T2.name FROM race AS T1 JOIN track AS T2 ON T1.track_id = T2.track_id GROUP BY T1.track_id ORDER BY count(*) DESC LIMIT 1
human
Show year where a track with a seating at least 5000 opened and a track with seating no more than 4000 opened.
assistant
SELECT year_opened FROM track WHERE seating BETWEEN 4000 AND 5000
human
Show names and seatings, ordered by seating for all tracks opened after 2000.
assistant
SELECT name , seating FROM track WHERE year_opened > 2000 ORDER BY seating
human
Find the locations where have both tracks with more than 90000 seats and tracks with less than 70000 seats.
assistant
SELECT LOCATION FROM track WHERE seating > 90000 INTERSECT SELECT LOCATION FROM track WHERE seating < 70000
human
Show the name, location, open year for all tracks with a seating higher than the average.
assistant
SELECT name , LOCATION , year_opened FROM track WHERE seating > (SELECT avg(seating) FROM track)
human
Return the minimum, maximum, and average seating across all tracks.
assistant
SELECT MIN( seating ) , MAX( seating ) , avg ( seating ) FROM track ;