summaryrefslogtreecommitdiff
path: root/views
diff options
context:
space:
mode:
authoremkael <emkael@tlen.pl>2015-02-19 09:19:08 +0100
committeremkael <emkael@tlen.pl>2015-02-19 09:19:08 +0100
commit55f10a6e7cead3513600569fb418d571ae88ba57 (patch)
treedfd424ccde404b22f338098b39ae49e7fb4a2929 /views
parentda8c51b97eeb4a011646d470c3797a608782d512 (diff)
* new lines converted
Diffstat (limited to 'views')
-rw-r--r--views/podiums.sql82
1 files changed, 41 insertions, 41 deletions
diff --git a/views/podiums.sql b/views/podiums.sql
index f9576f4..ec0bf63 100644
--- a/views/podiums.sql
+++ b/views/podiums.sql
@@ -1,41 +1,41 @@
--- podium for each race, in order of finishing position
-DROP VIEW IF EXISTS podium_sequences;
-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
-DROP VIEW IF EXISTS podium_sets;
-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
-DROP VIEW IF EXISTS non_champion_nationality_podiums;
-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)
- );
+-- podium for each race, in order of finishing position
+DROP VIEW IF EXISTS podium_sequences;
+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
+DROP VIEW IF EXISTS podium_sets;
+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
+DROP VIEW IF EXISTS non_champion_nationality_podiums;
+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)
+ );