diff options
author | emkael <emkael@tlen.pl> | 2018-03-12 08:44:30 +0100 |
---|---|---|
committer | emkael <emkael@tlen.pl> | 2018-03-12 08:44:30 +0100 |
commit | 0590268827499752afa648ac79b32c0a170709f0 (patch) | |
tree | d5d798feb8c0b6fde7cb5586a2ad6bb802cec6d5 /views/oldest_youngest.sql | |
parent | cd48367fa5157df49ef6ece3be6a69a1fe83b5ff (diff) |
Bugfixes in views
Diffstat (limited to 'views/oldest_youngest.sql')
-rw-r--r-- | views/oldest_youngest.sql | 7 |
1 files changed, 6 insertions, 1 deletions
diff --git a/views/oldest_youngest.sql b/views/oldest_youngest.sql index 320ad6c..d1faab8 100644 --- a/views/oldest_youngest.sql +++ b/views/oldest_youngest.sql @@ -1,16 +1,18 @@ -- first and last season (not full, any entry counts) for each driver +DROP VIEW IF EXISTS first_last_season; CREATE VIEW first_last_season AS SELECT MIN(races.year) AS first_season, MAX(races.year) AS last_season, drivers.driverId AS driverId, CONCAT(drivers.forename, ' ', drivers.surname) AS driver FROM races JOIN results ON results.raceId = races.raceId - JOIN drivers drivers.driverId = results.driverId + JOIN drivers ON drivers.driverId = results.driverId GROUP BY drivers.driverId; -- earliest and latest birth dates in each season among all the drivers -- entered in a season, so in 1991 Schumacher is the youngest, -- despite Hakkinen being younger than him on their respective debut races +DROP VIEW IF EXISTS youngest_oldest_birth_dates_in_season; CREATE VIEW youngest_oldest_birth_dates_in_season AS SELECT races.year AS year, MIN(drivers.dob) AS oldest, MAX(drivers.dob) AS youngest @@ -20,6 +22,7 @@ CREATE VIEW youngest_oldest_birth_dates_in_season AS GROUP BY races.year; -- youngest and oldest drivers in each season +DROP VIEW IF EXISTS youngest_oldest_in_season; CREATE VIEW youngest_oldest_in_season AS SELECT tmp.year AS year, tmp.oldest AS oldest, @@ -31,6 +34,7 @@ CREATE VIEW youngest_oldest_in_season AS JOIN drivers d2 ON d2.dob = tmp.youngest; -- list of drivers who were youngest in their debut season +DROP VIEW IF EXISTS youngest_in_first_season; CREATE VIEW youngest_in_first_season AS SELECT first_last_season.first_season AS first_season, first_last_season.driver AS driver @@ -40,6 +44,7 @@ CREATE VIEW youngest_in_first_season AS AND youngest_oldest_in_season.youngest_name = first_last_season.driver; -- list of drivers who were oldest in their last season +DROP VIEW IF EXISTS oldest_in_last_season; CREATE VIEW oldest_in_last_season AS SELECT first_last_season.last_season AS last_season, first_last_season.driver AS driver |