diff options
| -rw-r--r-- | views/champions.sql | 2 | ||||
| -rw-r--r-- | views/full_season_drivers.sql | 4 | ||||
| -rw-r--r-- | views/podiums.sql | 3 | ||||
| -rw-r--r-- | views/team_switches.sql | 4 | ||||
| -rw-r--r-- | views/teammates.sql | 2 |
5 files changed, 15 insertions, 0 deletions
diff --git a/views/champions.sql b/views/champions.sql index d8e4d18..f4fee6e 100644 --- a/views/champions.sql +++ b/views/champions.sql @@ -1,5 +1,6 @@ -- all drivers listed in the standings at position = 1 -- for last race of each season +DROP VIEW IF EXISTS world_drivers_champions; CREATE VIEW world_drivers_champions AS SELECT drivers.*, races.year FROM driverStandings @@ -10,6 +11,7 @@ CREATE VIEW world_drivers_champions AS -- all constructors listed in the standings at position = 1 -- for last race of each season +DROP VIEW IF EXISTS world_constructors_champions; CREATE VIEW world_constructors_champions AS SELECT constructors.*, races.year FROM constructorStandings diff --git a/views/full_season_drivers.sql b/views/full_season_drivers.sql index ae36cc8..0ad0bbd 100644 --- a/views/full_season_drivers.sql +++ b/views/full_season_drivers.sql @@ -1,11 +1,13 @@ -- view holds distinct driver-race entries -- (eliminating duplicate race entries from shared drives) +DROP VIEW IF EXISTS driver_races; CREATE VIEW driver_races AS SELECT driverId, raceId FROM results GROUP BY driverId, raceId; -- view aggregates races by season for each driver +DROP VIEW IF EXISTS driver_season_races; CREATE VIEW driver_season_races AS SELECT driver_races.driverId, races.year, GROUP_CONCAT(driver_races.raceId @@ -16,12 +18,14 @@ CREATE VIEW driver_season_races AS GROUP BY driver_races.driverId, races.year; -- view aggregates races by season +DROP VIEW IF EXISTS season_races; CREATE VIEW season_races AS SELECT year, GROUP_CONCAT(raceId ORDER BY raceId ASC) AS races FROM races GROUP BY year; -- selecting only driver-season pairs with all races in a season +DROP VIEW IF EXISTS full_season_drivers; CREATE VIEW full_season_drivers AS SELECT driver_season_races.driverId, driver_season_races.year FROM season_races diff --git a/views/podiums.sql b/views/podiums.sql index 6e677da..f9576f4 100644 --- a/views/podiums.sql +++ b/views/podiums.sql @@ -1,4 +1,5 @@ -- 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)
@@ -11,6 +12,7 @@ CREATE VIEW podium_sequences AS 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)
@@ -25,6 +27,7 @@ CREATE VIEW podium_sets AS -- 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 (
diff --git a/views/team_switches.sql b/views/team_switches.sql index 2514911..6d10410 100644 --- a/views/team_switches.sql +++ b/views/team_switches.sql @@ -1,4 +1,5 @@ -- constructors' standings at the end of each season +DROP VIEW IF EXISTS constructor_championship_results; CREATE VIEW constructor_championship_results AS SELECT * FROM constructorStandings WHERE raceId IN ( @@ -6,6 +7,7 @@ CREATE VIEW constructor_championship_results AS ); -- every driver-constructor pair for each season +DROP VIEW IF EXISTS season_driver_constructor; CREATE VIEW season_driver_constructor AS SELECT results.driverId, results.constructorId, races.year FROM results @@ -13,6 +15,7 @@ CREATE VIEW season_driver_constructor AS GROUP BY results.driverId, results.constructorId, races.year; -- every team each driver would go to drive for in the following year +DROP VIEW IF EXISTS next_season_driver_constructor; CREATE VIEW next_season_driver_constructor AS SELECT sdc.*, sdc2.constructorId AS nextConstructor FROM season_driver_constructor sdc @@ -21,6 +24,7 @@ CREATE VIEW next_season_driver_constructor AS -- every driver team switch with both cosntructors' positions -- for the initial season +DROP VIEW IF EXISTS driver_team_switches; CREATE VIEW driver_team_switches AS SELECT nsdc.driverId, nsdc.year, nsdc.constructorId, nsdc.nextConstructor, cs.position AS prevPosition, cs2.position AS nextPosition diff --git a/views/teammates.sql b/views/teammates.sql index 772d59d..dec4f3a 100644 --- a/views/teammates.sql +++ b/views/teammates.sql @@ -1,5 +1,6 @@ -- all driver pairs listed in the results as driving for the same constructor -- in the same race, for every race +DROP VIEW IF EXISTS teammates; CREATE VIEW teammates AS SELECT r1.raceId, r1.constructorId, r1.driverId driver1, r1.position d1position, @@ -13,6 +14,7 @@ CREATE VIEW teammates AS AND r1.driverId <> r2.driverId; -- tally of results from the previous view +DROP VIEW IF EXISTS teammate_tally; CREATE VIEW teammate_tally AS SELECT CONCAT(d1.forename, " ", d1.surname) driver, COALESCE(SUM(teammates.d1position < teammates.d2position), 0) wins, |