diff options
| author | emkael <emkael@tlen.pl> | 2015-02-18 12:11:37 +0100 |
|---|---|---|
| committer | emkael <emkael@tlen.pl> | 2015-02-18 12:11:37 +0100 |
| commit | a0f5e709cfc9b61a8919b9ddb3ce915028128287 (patch) | |
| tree | b711d7da5ab1716d667ab53e65514afcdf7fe11b /views/podiums.sql | |
| parent | eba0b7e9529dd8eb6702615e6a8c3b2dc092e5bc (diff) | |
* useful views, separated by theme
Diffstat (limited to 'views/podiums.sql')
| -rw-r--r-- | views/podiums.sql | 38 |
1 files changed, 38 insertions, 0 deletions
diff --git a/views/podiums.sql b/views/podiums.sql new file mode 100644 index 0000000..6e677da --- /dev/null +++ b/views/podiums.sql @@ -0,0 +1,38 @@ +-- podium for each race, in order of finishing position
+CREATE VIEW podium_sequences AS
+ SELECT races.*,
+ GROUP_CONCAT(CONCAT(drivers.forename, " ", drivers.surname)
+ ORDER BY results.position ASC, results.driverId ASC)
+ AS podium
+ FROM results
+ JOIN races ON results.raceId = races.raceId
+ JOIN drivers ON results.driverId = drivers.driverId
+ WHERE results.position IN (1,2,3)
+ GROUP BY results.raceId;
+
+-- podium for each race, in alphabetical order
+CREATE VIEW podium_sets AS
+ SELECT races.*,
+ GROUP_CONCAT(CONCAT(drivers.forename, " ", drivers.surname)
+ ORDER BY drivers.surname ASC, drivers.forename ASC,
+ drivers.driverRef ASC)
+ AS podium
+ FROM results
+ JOIN races ON results.raceId = races.raceId
+ JOIN drivers ON results.driverId = drivers.driverId
+ WHERE results.position IN (1,2,3)
+ GROUP BY results.raceId;
+
+-- races in which the entire podium consisted of driver of nationality
+-- yet to produce a drivers' world champion
+CREATE VIEW non_champion_nationality_podiums AS
+ SELECT raceId, year, name, podium FROM podium_sequences
+ WHERE raceId NOT IN (
+ SELECT results.raceId FROM results
+ JOIN drivers ON drivers.driverId = results.driverId
+ JOIN races ON races.raceId = results.raceId
+ WHERE drivers.nationality IN (
+ SELECT nationality FROM world_drivers_champions
+ WHERE year < races.year
+ ) AND position IN (1,2,3)
+ );
|