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
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 distinct locations where tracks are located?
assistant
SELECT DISTINCT LOCATION FROM track
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
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 ;