CREATE TABLE circuits (
    circuitId NUMBER PRIMARY KEY,
    circuitRef TEXT,
    name TEXT,
    location TEXT,
    country TEXT,
    lat NUMBER,
    lng NUMBER,
    alt NUMBER,
    url TEXT
);
CREATE TABLE races (
    raceId NUMBER PRIMARY KEY,
    year NUMBER,
    round NUMBER,
    circuitId NUMBER,
    name TEXT,
    date TEXT,
    time TEXT,
    url TEXT,
    FOREIGN KEY ("circuitId") REFERENCES circuits("circuitId")
);
CREATE TABLE drivers (
    driverId NUMBER PRIMARY KEY,
    driverRef TEXT,
    number NUMBER,
    code TEXT,
    forename TEXT,
    surname TEXT,
    dob TEXT,
    nationality TEXT,
    url TEXT
);
CREATE TABLE status (
    statusId NUMBER PRIMARY KEY,
    status TEXT
);
CREATE TABLE seasons (
    year NUMBER PRIMARY KEY,
    url TEXT
);
CREATE TABLE constructors (
    constructorId NUMBER PRIMARY KEY,
    constructorRef TEXT,
    name TEXT,
    nationality TEXT,
    url TEXT
);
CREATE TABLE constructorStandings (
    constructorStandingsId NUMBER PRIMARY KEY,
    raceId NUMBER,
    constructorId NUMBER,
    points NUMBER,
    position NUMBER,
    positionText TEXT,
    wins NUMBER,
    FOREIGN KEY ("raceId") REFERENCES races("raceId"),
    FOREIGN KEY ("constructorId") REFERENCES constructors("constructorId")
);
CREATE TABLE results (
    resultId NUMBER PRIMARY KEY,
    raceId NUMBER,
    driverId NUMBER,
    constructorId NUMBER,
    number NUMBER,
    grid NUMBER,
    position NUMBER,
    positionText TEXT,
    positionOrder NUMBER,
    points NUMBER,
    laps NUMBER,
    time TEXT,
    milliseconds NUMBER,
    fastestLap NUMBER,
    rank NUMBER,
    fastestLapTime TEXT,
    fastestLapSpeed TEXT,
    statusId NUMBER,
    FOREIGN KEY ("driverId") REFERENCES drivers("driverId"),
    FOREIGN KEY ("raceId") REFERENCES races("raceId"),
    FOREIGN KEY ("constructorId") REFERENCES constructors("constructorId")
);
CREATE TABLE driverStandings (
    driverStandingsId NUMBER PRIMARY KEY,
    raceId NUMBER,
    driverId NUMBER,
    points NUMBER,
    position NUMBER,
    positionText TEXT,
    wins NUMBER,
    FOREIGN KEY ("driverId") REFERENCES drivers("driverId"),
    FOREIGN KEY ("raceId") REFERENCES races("raceId")
);
CREATE TABLE constructorResults (
    constructorResultsId NUMBER PRIMARY KEY,
    raceId NUMBER,
    constructorId NUMBER,
    points NUMBER,
    status NUMBER,
    FOREIGN KEY ("raceId") REFERENCES races("raceId"),
    FOREIGN KEY ("constructorId") REFERENCES constructors("constructorId")
);
CREATE TABLE qualifying (
    qualifyId NUMBER PRIMARY KEY,
    raceId NUMBER,
    driverId NUMBER,
    constructorId NUMBER,
    number NUMBER,
    position NUMBER,
    q1 TEXT,
    q2 TEXT,
    q3 TEXT,
    FOREIGN KEY ("driverId") REFERENCES drivers("driverId"),
    FOREIGN KEY ("raceId") REFERENCES races("raceId"),
    FOREIGN KEY ("constructorId") REFERENCES constructors("constructorId")
);
CREATE TABLE pitStops (
    raceId NUMBER PRIMARY KEY,
    driverId NUMBER,
    stop NUMBER,
    lap NUMBER,
    time TEXT,
    duration TEXT,
    milliseconds NUMBER,
    FOREIGN KEY ("driverId") REFERENCES drivers("driverId"),
    FOREIGN KEY ("raceId") REFERENCES races("raceId")
);
CREATE TABLE lapTimes (
    raceId NUMBER PRIMARY KEY,
    driverId NUMBER,
    lap NUMBER,
    position NUMBER,
    time TEXT,
    milliseconds NUMBER,
    FOREIGN KEY ("driverId") REFERENCES drivers("driverId"),
    FOREIGN KEY ("raceId") REFERENCES races("raceId")
);

Question: What are the drivers' last names and id who had 11 pit stops and participated in more than 5 race results?
SQL: SELECT T1.surname ,  T1.driverid FROM drivers AS T1 JOIN pitstops AS T2 ON T1.driverid  =  T2.driverid GROUP BY T1.driverid HAVING count(*)  =  11 INTERSECT SELECT T1.surname ,  T1.driverid FROM drivers AS T1 JOIN results AS T2 ON T1.driverid  =  T2.driverid GROUP BY T1.driverid HAVING count(*)  >  5

Question: What are the last names and ids of all drivers who had 11 pit stops and participated in more than 5 races?
SQL: SELECT T1.surname ,  T1.driverid FROM drivers AS T1 JOIN pitstops AS T2 ON T1.driverid  =  T2.driverid GROUP BY T1.driverid HAVING count(*)  =  11 INTERSECT SELECT T1.surname ,  T1.driverid FROM drivers AS T1 JOIN results AS T2 ON T1.driverid  =  T2.driverid GROUP BY T1.driverid HAVING count(*)  >  5

Question: What is the id and last name of the driver who participated in the most races after 2010?
SQL: SELECT T1.driverid ,  T1.surname FROM drivers AS T1 JOIN results AS T2 ON T1.driverid  =  T2.driverid JOIN races AS T3 ON T2.raceid = T3.raceid WHERE T3.year > 2010 GROUP BY T1.driverid ORDER BY count(*) DESC LIMIT 1

Question: What is the id and last name of the driver who participated in the most races after 2010?
SQL: SELECT T1.driverid ,  T1.surname FROM drivers AS T1 JOIN results AS T2 ON T1.driverid  =  T2.driverid JOIN races AS T3 ON T2.raceid = T3.raceid WHERE T3.year > 2010 GROUP BY T1.driverid ORDER BY count(*) DESC LIMIT 1

Question: What are the names of circuits that belong to UK or Malaysia?
SQL: SELECT name FROM circuits WHERE country = "UK" OR country = "Malaysia"

Question: What are the names of all the circuits that are in the UK or Malaysia?
SQL: SELECT name FROM circuits WHERE country = "UK" OR country = "Malaysia"

Question: Find the id and location of circuits that belong to France or Belgium?
SQL: SELECT circuitid ,  LOCATION FROM circuits WHERE country = "France" OR country = "Belgium"

Question: What are the ids and locations of all circuits in France or Belgium?
SQL: SELECT circuitid ,  LOCATION FROM circuits WHERE country = "France" OR country = "Belgium"

Question: Find the names of Japanese constructors that have once earned more than 5 points?
SQL: SELECT T1.name FROM constructors AS T1 JOIN constructorstandings AS T2 ON T1.constructorid = T2.constructorid WHERE T1.nationality = "Japanese" AND T2.points > 5

Question: What are the names of all the Japanese constructors that have earned more than 5 points?
SQL: SELECT T1.name FROM constructors AS T1 JOIN constructorstandings AS T2 ON T1.constructorid = T2.constructorid WHERE T1.nationality = "Japanese" AND T2.points > 5

Question: What is the average fastest lap speed in race named 'Monaco Grand Prix' in 2008 ?
SQL: SELECT avg(T2.fastestlapspeed) FROM races AS T1 JOIN results AS T2 ON T1.raceid = T2.raceid WHERE T1.year = 2008 AND T1.name = "Monaco Grand Prix"

Question: What is the average fastest lap speed for the Monaco Grand Prix in 2008?
SQL: SELECT avg(T2.fastestlapspeed) FROM races AS T1 JOIN results AS T2 ON T1.raceid = T2.raceid WHERE T1.year = 2008 AND T1.name = "Monaco Grand Prix"

Question: What is the maximum fastest lap speed in race named 'Monaco Grand Prix' in 2008 ?
SQL: SELECT max(T2.fastestlapspeed) FROM races AS T1 JOIN results AS T2 ON T1.raceid = T2.raceid WHERE T1.year = 2008 AND T1.name = "Monaco Grand Prix"

Question: What is the maximum fastest lap speed in the Monaco Grand Prix in 2008?
SQL: SELECT max(T2.fastestlapspeed) FROM races AS T1 JOIN results AS T2 ON T1.raceid = T2.raceid WHERE T1.year = 2008 AND T1.name = "Monaco Grand Prix"

Question: What are the maximum fastest lap speed in races held after 2004 grouped by race name and ordered by year?
SQL: SELECT max(T2.fastestlapspeed) ,  T1.name ,  T1.year FROM races AS T1 JOIN results AS T2 ON T1.raceid = T2.raceid WHERE T1.year > 2014 GROUP BY T1.name ORDER BY T1.year

Question: For each race name, What is the maximum fastest lap speed for races after 2004 ordered by year?
SQL: SELECT max(T2.fastestlapspeed) ,  T1.name ,  T1.year FROM races AS T1 JOIN results AS T2 ON T1.raceid = T2.raceid WHERE T1.year > 2014 GROUP BY T1.name ORDER BY T1.year

Question: What are the average fastest lap speed in races held after 2004 grouped by race name and ordered by year?
SQL: SELECT avg(T2.fastestlapspeed) ,  T1.name ,  T1.year FROM races AS T1 JOIN results AS T2 ON T1.raceid = T2.raceid WHERE T1.year > 2014 GROUP BY T1.name ORDER BY T1.year