1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
|
-- 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)
);
-- driver list by podium count
DROP VIEW IF EXISTS podium_count;
CREATE VIEW podium_count AS
SELECT drivers.driverId, CONCAT(drivers.forename, ' ', drivers.surname) name,
COUNT(DISTINCT(results.raceId)) podium_count
FROM results JOIN drivers ON results.driverId = drivers.driverId
WHERE position IN (1,2,3)
GROUP BY results.driverId
ORDER BY COUNT(DISTINCT(results.raceId)) DESC;
|
