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 and the number of races in each track.
assistant
SELECT T2.name , count(*) FROM race AS T1 JOIN track AS T2 ON T1.track_id = T2.track_id GROUP BY T1.track_id
human
Show the name and date for each race and its track name.
assistant
SELECT T1.name , T1.date , T2.name FROM race AS T1 JOIN track AS T2 ON T1.track_id = T2.track_id
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 the name and location of track with 1 race.
assistant
SELECT T2.name , T2.location FROM race AS T1 JOIN track AS T2 ON T1.track_id = T2.track_id GROUP BY T1.track_id HAVING count(*) = 1
human
Show all track names that have had no races.
assistant
SELECT name FROM track WHERE track_id NOT IN (SELECT track_id FROM race)
human
Give the names of tracks that do not have a race in the class 'GT'.
assistant
SELECT name FROM track EXCEPT SELECT T2.name FROM race AS T1 JOIN track AS T2 ON T1.track_id = T2.track_id WHERE T1.class = "GT" ;