human
Schema:
circuits (circuitId, circuitRef, name, location, country, lat, lng, alt, url)
races (raceId, year, round, circuitId, name, date, time, url)
drivers (driverId, driverRef, number, code, forename, surname, dob, nationality, url)
status (statusId, status)
seasons (year, url)
constructors (constructorId, constructorRef, name, nationality, url)
constructorStandings (constructorStandingsId, raceId, constructorId, points, position, positionText, wins)
results (resultId, raceId, driverId, constructorId, number, grid, position, positionText, positionOrder, points, laps, time, milliseconds, fastestLap, rank, fastestLapTime, fastestLapSpeed, statusId)
driverStandings (driverStandingsId, raceId, driverId, points, position, positionText, wins)
constructorResults (constructorResultsId, raceId, constructorId, points, status)
qualifying (qualifyId, raceId, driverId, constructorId, number, position, q1, q2, q3)
pitStops (raceId, driverId, stop, lap, time, duration, milliseconds)
lapTimes (raceId, driverId, lap, position, time, milliseconds)
Please answer with SQL.
assistant
Sure, I understand.
human
Give me a list of names and years of races that had any driver whose forename is Lewis?
assistant
SELECT T2.name , T2.year FROM results AS T1 JOIN races AS T2 ON T1.raceid = T2.raceid JOIN drivers AS T3 ON T1.driverid = T3.driverid WHERE T3.forename = 'Lewis'
human
What are the drivers' first, last names and id who had more than 8 pit stops or participated in more than 5 race results?
assistant
SELECT T1.forename , T1.surname , T1.driverid FROM drivers AS T1 JOIN pitstops AS T2 ON T1.driverid = T2.driverid GROUP BY T1.driverid HAVING count(*) > 8 UNION SELECT T1.forename , T1.surname , T1.driverid FROM drivers AS T1 JOIN results AS T2 ON T1.driverid = T2.driverid GROUP BY T1.driverid HAVING count(*) > 5
human
What are the drivers' last names and id who had 11 pit stops and participated in more than 5 race results?
assistant
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
human
How many drivers participated in the race Australian Grand Prix held in 2009?
assistant
SELECT count(*) FROM results AS T1 JOIN races AS T2 ON T1.raceid = T2.raceid WHERE T2.name = 'Australian Grand Prix' AND YEAR = 2009
human
Find the id and forenames of drivers who participated both the races with name Australian Grand Prix and the races with name Chinese Grand Prix?
assistant
SELECT T2.driverid , T3.forename FROM races AS T1 JOIN results AS T2 ON T1.raceid = T2.raceid JOIN drivers AS T3 ON T2.driverid = T3.driverid WHERE T1.name = 'Australian Grand Prix' INTERSECT SELECT T2.driverid , T3.forename FROM races AS T1 JOIN results AS T2 ON T1.raceid = T2.raceid JOIN drivers AS T3 ON T2.driverid = T3.driverid WHERE T1.name = 'Chinese Grand Prix'
human
What are the first and last names of all drivers who participated in the Australian Grand Prix but not the Chinese Grand Prix?
assistant
SELECT T3.forename , T3.surname FROM races AS T1 JOIN results AS T2 ON T1.raceid = T2.raceid JOIN drivers AS T3 ON T2.driverid = T3.driverid WHERE T1.name = "Australian Grand Prix" EXCEPT SELECT T3.forename , T3.surname FROM races AS T1 JOIN results AS T2 ON T1.raceid = T2.raceid JOIN drivers AS T3 ON T2.driverid = T3.driverid WHERE T1.name = "Chinese Grand Prix" ;