Database: race_track

CREATE TABLE "race" (
"Race_ID" int,
"Name" text,
"Class" text,
"Date" text,
"Track_ID" text,
PRIMARY KEY ("Race_ID"),
FOREIGN KEY ("Track_ID") REFERENCES "track"("Track_ID")
);
CREATE TABLE "track" (
"Track_ID" int,
"Name" text,
"Location" text,
"Seating" real,
"Year_Opened" real,
PRIMARY KEY ("Track_ID")
);

Question: How many tracks do we have?
SQL: SELECT count(*) FROM track

Question: Count the number of tracks.
SQL: SELECT count(*) FROM track

Question: Show the name and location for all tracks.
SQL: SELECT name ,  LOCATION FROM track

Question: What are the names and locations of all tracks?
SQL: SELECT name ,  LOCATION FROM track

Question: Show names and seatings, ordered by seating for all tracks opened after 2000.
SQL: SELECT name ,  seating FROM track WHERE year_opened  >  2000 ORDER BY seating

Question: What are the names and seatings for all tracks opened after 2000, ordered by seating?
SQL: SELECT name ,  seating FROM track WHERE year_opened  >  2000 ORDER BY seating

Question: What is the name, location and seating for the most recently opened track?
SQL: SELECT name ,  LOCATION ,  seating FROM track ORDER BY year_opened DESC LIMIT 1

Question: Return the name, location, and seating of the track that was opened in the most recent year.
SQL: SELECT name ,  LOCATION ,  seating FROM track ORDER BY year_opened DESC LIMIT 1

Question: What is the minimum, maximum, and average seating for all tracks.
SQL: SELECT min(seating) ,  max(seating) ,  avg(seating) FROM track

Question: Return the minimum, maximum, and average seating across all tracks.
SQL: SELECT min(seating) ,  max(seating) ,  avg(seating) FROM track

Question: Show the name, location, open year for all tracks with a seating higher than the average.
SQL: SELECT name ,  LOCATION ,  year_opened FROM track WHERE seating  >  (SELECT avg(seating) FROM track)

Question: What are the names, locations, and years of opening for tracks with seating higher than average?
SQL: SELECT name ,  LOCATION ,  year_opened FROM track WHERE seating  >  (SELECT avg(seating) FROM track)

Question: What are distinct locations where tracks are located?
SQL: SELECT DISTINCT LOCATION FROM track

Question: Give the different locations of tracks.
SQL: SELECT DISTINCT LOCATION FROM track

Question: How many races are there?
SQL: SELECT count(*) FROM race

Question: Count the number of races.
SQL: SELECT count(*) FROM race

Question: What are the distinct classes that races can have?
SQL: SELECT DISTINCT CLASS FROM race

Question: Return the different classes of races.
SQL: SELECT DISTINCT CLASS FROM race

Question: Show name, class, and date for all races.
SQL: SELECT name ,  CLASS ,  date FROM race

Question: What are the names, classes, and dates for all races?
SQL: SELECT name ,  CLASS ,  date FROM race

Question: Show the race class and number of races in each class.
SQL: SELECT CLASS ,  count(*) FROM race GROUP BY CLASS

Question: What are the different classes of races, and how many races correspond to each?
SQL: SELECT CLASS ,  count(*) FROM race GROUP BY CLASS

Question: What is the race class with most number of races.
SQL: SELECT CLASS FROM race GROUP BY CLASS ORDER BY count(*) DESC LIMIT 1

Question: Give the class of races that is most common.
SQL: SELECT CLASS FROM race GROUP BY CLASS ORDER BY count(*) DESC LIMIT 1

Question: List the race class with at least two races.
SQL: SELECT CLASS FROM race GROUP BY CLASS HAVING count(*)  >=  2

Question: What are the classes of races that have two or more corresponding races?
SQL: SELECT CLASS FROM race GROUP BY CLASS HAVING count(*)  >=  2

Question: What are the names for tracks without a race in class 'GT'.
SQL: 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'

Question: Give the names of tracks that do not have a race in the class 'GT'.
SQL: 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'

Question: Show all track names that have had no races.
SQL: SELECT name FROM track WHERE track_id NOT IN (SELECT track_id FROM race)

Question: Return the names of tracks that have no had any races.
SQL: SELECT name FROM track WHERE track_id NOT IN (SELECT track_id FROM race)

Question: Show year where a track with a seating at least 5000 opened and a track with seating no more than 4000 opened.
SQL: SELECT year_opened FROM track WHERE seating BETWEEN 4000 AND 5000

Question: What are the years of opening for tracks with seating between 4000 and 5000?
SQL: SELECT year_opened FROM track WHERE seating BETWEEN 4000 AND 5000

Question: Show the name of track and the number of races in each track.
SQL: SELECT T2.name ,  count(*) FROM race AS T1 JOIN track AS T2 ON T1.track_id  =  T2.track_id GROUP BY T1.track_id

Question: What are the names of different tracks, and how many races has each had?
SQL: SELECT T2.name ,  count(*) FROM race AS T1 JOIN track AS T2 ON T1.track_id  =  T2.track_id GROUP BY T1.track_id

Question: Show the name of track with most number of races.
SQL: 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

Question: What is the name of the track that has had the greatest number of races?
SQL: 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

Question: Show the name and date for each race and its track name.
SQL: SELECT T1.name ,  T1.date ,  T2.name FROM race AS T1 JOIN track AS T2 ON T1.track_id  =  T2.track_id

Question: What are the names and dates of races, and the names of the tracks where they are held?
SQL: SELECT T1.name ,  T1.date ,  T2.name FROM race AS T1 JOIN track AS T2 ON T1.track_id  =  T2.track_id

Question: Show the name and location of track with 1 race.
SQL: 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

Question: What are the names and locations of tracks that have had exactly 1 race?
SQL: 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

Question: Find the locations where have both tracks with more than 90000 seats and tracks with less than 70000 seats.
SQL: SELECT LOCATION FROM track WHERE seating  >  90000 INTERSECT SELECT LOCATION FROM track WHERE seating  <  70000

Question: What are the locations that have both tracks with more than 90000 seats, and tracks with fewer than 70000 seats?
SQL: SELECT LOCATION FROM track WHERE seating  >  90000 INTERSECT SELECT LOCATION FROM track WHERE seating  <  70000