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
How many drivers have participated in 30 or fewer races?
assistant
SELECT COUNT( * ) FROM ( SELECT T1.driverid FROM drivers AS T1 JOIN results AS T2 ON T1.driverid = T2.driverid JOIN races AS T3 ON T2.raceid = T3.raceid GROUP BY T1.driverid HAVING COUNT( * ) < = 30 );
human
What are the ID's of those drivers?
assistant
SELECT T1.driverid FROM drivers AS T1 JOIN results AS T2 ON T1.driverid = T2.driverid JOIN races AS T3 ON T2.raceid = T3.raceid GROUP BY T1.driverid HAVING COUNT( * ) < = 30 ;