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 all track names that have had no races.
assistant
SELECT name FROM track WHERE track_id NOT IN (SELECT track_id FROM race)
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
What are distinct locations where tracks are located?
assistant
SELECT DISTINCT LOCATION FROM track
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 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 the name and location for all tracks.
assistant
SELECT name , LOCATION FROM track ;