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
count the number of records for each track id | do you mean count the number of race ids for each track id? | How about this--list all track Ids
assistant
SELECT track_id FROM track;
human
add a column for track name
assistant
SELECT track_id , name FROM track;
human
add a column showing the total number of rows in the table race that contain each of the Track_IDs
assistant
SELECT T2.track_id , T2.name , COUNT( * ) FROM race AS T1 JOIN track AS T2 ON T1.track_id = T2.track_id GROUP BY T1.track_id;
human
Filter the list to show only those rows with a number in the count column that is greater than 1
assistant
SELECT T2.track_id , T2.name , COUNT( * ) FROM race AS T1 JOIN track AS T2 ON T1.track_id = T2.track_id GROUP BY T1.track_id having COUNT( * ) > 1 ;