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
What is the minimum, maximum, and average seating for all tracks.
assistant
SELECT min(seating) , max(seating) , avg(seating) FROM track
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
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
What is the name, location and seating for the most recently opened track?
assistant
SELECT name , LOCATION , seating FROM track ORDER BY year_opened DESC LIMIT 1
human
What are the names and seatings for all tracks opened after 2000, ordered by seating?
assistant
SELECT name , seating FROM track WHERE year_opened > 2000 ORDER BY seating ;