Autor: Mateus Amorim Marques; MARQUES, M. A.
Instituição: Universidade Cruzeiro do Sul
Curso: Ciência de Dados
--CRIAÇÃO DE BASE DE DADOS
--CREATE DATABASE F1;
--CRIAÇÃO DE TABELAS
--2018
create table TB_EQUIPE
(
ID_EQUIPE INT PRIMARY KEY NOT NULL,
NM_EQUIPE VARCHAR(100) NOT NULL,
ID_PAIS INT NOT NULL
);
CREATE TABLE TB_PILOTO
(
ID_PILOTO INT PRIMARY KEY NOT NULL,
NM_PILOTO VARCHAR(100) NOT NULL,
DT_NASCIMENTO DATE NOT NULL,
ID_PAIS INT NOT NULL,
ID_EQUIPE INT NOT NULL
);
CREATE TABLE TB_PAIS
(
ID_PAIS INT PRIMARY KEY NOT NULL,
NM_PAIS VARCHAR(100) NOT NULL,
NR_POPULACAO INT NOT NULL
);
CREATE TABLE TB_CIRCUITO
(
ID_CIRCUITO INT PRIMARY KEY NOT NULL,
NM_CIRCUITO VARCHAR(100) NOT NULL,
NR_EXTENSAO VARCHAR(100) NOT NULL,
ID_PAIS INT NOT NULL
);
CREATE TABLE TB_PROVA
(
ID_PROVA INT PRIMARY KEY NOT NULL,
DT_PROVA DATETIME NOT NULL,
NM_SITUACAO VARCHAR(100),
ID_CIRCUITO INT NOT NULL
);
CREATE TABLE TB_RESULTADO
(
ID_PROVA INT NOT NULL,
ID_PILOTO INT NOT NULL,
NR_TEMPO_PROVA VARCHAR(100) NOT NULL,
NR_COLOC_FINAL INT NOT NULL,
NR_POSICAO_GRID INT NOT NULL,
NR_MELHOR_VOLTA VARCHAR(100) NOT NULL
);
--CRIAÇÃO DE CHAVES ESTRANGEIRAS
ALTER TABLE TB_RESULTADO
ADD CONSTRAINT FK_PROVA_RESULT
FOREIGN KEY(ID_PROVA) REFERENCES
TB_PROVA(ID_PROVA);
ALTER TABLE TB_RESULTADO
ADD CONSTRAINT FK_PILOTO_RESULT
FOREIGN KEY(ID_PILOTO) REFERENCES
TB_PILOTO(ID_PILOTO);
ALTER TABLE TB_PROVA
ADD CONSTRAINT FK_CIRCUITO_PROVA
FOREIGN KEY(ID_CIRCUITO)
REFERENCES TB_CIRCUITO(ID_CIRCUITO);
ALTER TABLE TB_CIRCUITO
ADD CONSTRAINT FK_PAIS_CIRCUITO
FOREIGN KEY(ID_PAIS) REFERENCES
TB_PAIS(ID_PAIS);
ALTER TABLE TB_PILOTO
ADD CONSTRAINT FK_PAIS_PILOTO
FOREIGN KEY(ID_PAIS) REFERENCES
TB_PAIS(ID_PAIS);
ALTER TABLE TB_PILOTO
ADD CONSTRAINT FK_EQUIPE_PILOTO
FOREIGN KEY(ID_EQUIPE) REFERENCES
TB_EQUIPE(ID_EQUIPE);
ALTER TABLE TB_EQUIPE
ADD CONSTRAINT FK_PAIS_EQUIPE
FOREIGN KEY(ID_PAIS) REFERENCES
TB_PAIS(ID_PAIS);
--INCERINDO DADOS NA TB_PAIS
--British -> Reino Unido
--Abu Dhabi -> Emirados Árabes Unidos
INSERT INTO dbo.TB_PAIS
(ID_PAIS,NM_PAIS,NR_POPULACAO)
VALUES
(001,'Reino Unido',66460000),
(002,'Alemanha',82910000),
(003,'Finlândia',5516000),
(004,'Países Baixos',17230000),
(005,'Austrália',24980000),
(006,'México',126200000),
(007,'Dinamarca',5795000),
(008,'Espanha',46800000),
(009,'França',67100000),
(010,'Mônaco',38682),
(011,'Bélgica',11430000),
(012,'Suécia',10180000),
(013,'Canadá',37070000),
(014,'Nova Zelândia',4901000),
(015,'Federação Russa',144500000),
(016,'Bahrain',1569000),
(017,'China',1393000000),
(018,'Azerbaijão',9940000),
(019,'Hungria',9776000),
(020,'Itália',60420000),
(021,'Singapura',5639000),
(022,'Japão',126500000),
(023,'EUA',326800000),
(024,'Brasil',209500000),
(025,'Emirados Árabes Unidos',9631000),
(026,'Suíça',8514000),
(027,'Índia',1353000000);
INSERT INTO dbo.TB_PAIS
(ID_PAIS,NM_PAIS,NR_POPULACAO)
VALUES
(028,'Áustria',8841000);
INSERT INTO dbo.TB_EQUIPE
(ID_EQUIPE,NM_EQUIPE,ID_PAIS)
VALUES
(0001,'Ferrari',020),
(0002,'Red Bull',028),
(0003,'Mercedes',002),
(0004,'Renault',009),
(0005,'Haas',023),
(0006,'McLaren',001),
(0007,'Force India',027),
(0008,'Ponto de corrida',001),
(0009,'Limpar',026),
(0010,'Toro Rosso',020),
(0011,'Williams',001),
(0012,'Aston Martin',001),
(0013,'Alfa Romeo',026),
(0014,'Alpino',009),
(0015,'Alfa Tauri',020);
INSERT INTO dbo.TB_PILOTO
(ID_PILOTO,NM_PILOTO,DT_NASCIMENTO,
ID_PAIS,ID_EQUIPE)
VALUES
(00001,'Sebastian Vettel','03/07/1987',002,0012),
(00002,'Daniel Ricciardo','01/07/1989',028,0006),
(00003,'Lewis Hamilton','07/01/1985',001,0003),
(00004,'Max Verstappen','30/09/1997',004,0002),
(00005,'Kimi Raikkonen','17/10/1979',003,0013),
(00006,'Valtteri Bottas','28/08/1989',003,0013),
(00007,'Nico Hülkenberg','19/08/1987',002,0012),
(00008,'Sergio Pérez','26/01/1990',006,0002),
(00009,'Kevin Magnussen','05/10/1992',007,0005),
(00010,'Carlos Sainz','01/09/1994',008,0001),
(00011,'Fernando Alonso','29/07/1981',008,0014),
(00012,'Esteban Ocon','17/09/1996',009,0014),
(00013,'Charles Leclerc','16/10/1997',010,0001),
(00014,'Romain Grosjean','17/04/1986',009,0005),
(00015,'Pierre Gasly','07/02/1996',009,0015),
(00016,'Stoffel Vandoorne','26/03/1992',011,0003),
(00017,'Marcus Ericsson','02/09/1990',012,0009),
(00018,'Lance Stroll','29/10/1998',013,0008),
(00019,'Brendon Hartley','10/11/1989',014,0010),
(00020,'Sergey Sirotkin','25/08/1995',015,0011);
INSERT INTO dbo.TB_CIRCUITO
(ID_CIRCUITO,NM_CIRCUITO,NR_EXTENSAO,
ID_PAIS)
VALUES
(000001,'2018 Australiano F1 GP','5.303 km',005),
(000002,'2018 Bahrain F1 GP','5.412 km',016),
(000003,'2018 chinês F1 GP','5.451 km',017),
(000004,'GP de F2018 do Azerbijão 1','6.003 km',018),
(000005,'2018 Espanhol F1 GP','4.655 km',008),
(000006,'GP de Mônaco F2018 1','3.337 km',010),
(000007,'2018 canadense F1 GP','4.361 km',013),
(000008,'2018 francês F1 GP','5.842 km',009),
(000009,'2018 austríaco F1 GP','4.318 km',028),
(000010,'2018 British F1 GP','5.891 km',001),
(000011,'2018 alemão F1 GP','4.574 km',002),
(000012,'GP de F2018 da Hungria de 1','4.381 km',019),
(000013,'2018 belga F1 GP','7.004 km',011),
(000014,'2018 italiano F1 GP','5.793 km',020),
(000015,'2018 Singapura F1 GP','5.063 km',021),
(000016,'2018 Russo F1 GP','5.848 km',015),
(000017,'2018 Japonês F1 GP','5.807 km',022),
(000018,'GP de F2018 dos EUA 1','5.513 km',023),
(000019,'GP mexicano de F2018 1','4.304 km',006),
(000020,'2018 GP Brasileiro de F1','4.309 km',024),
(000021,'GP de Abu Dhabi F2018 1','5.281 km',025);
INSERT INTO dbo.TB_PROVA
(ID_PROVA,DT_PROVA,NM_SITUACAO,ID_CIRCUITO)
VALUES
(0000001,'25/03/2018','Circuito Albert Park',000001),
(0000002,'08/04/2018','Circuito Internacional do Bahrein',
000002),
(0000003,'15/04/2018','Circuito Internacional de Xangai',
000003),
(0000004,'29/04/2018','Circuito de Baku',000004),
(0000005,'13/05/2018','Circuito da Catalunha',000005),
(0000006,'27/05/2018','Circuito de Mônaco',000006),
(0000007,'10/06/2018','Circuito Gilles Villeneuve',000007),
(0000008,'24/06/2018','Circuito Paul Ricard',000008),
(0000009,'01/07/2018','Red Bull Anel',000009),
(0000010,'08/07/2018','Circuito Silverstone',000010),
(0000011,'22/07/2018','Hockenheimring',000011),
(0000012,'29/07/2018','Hungaroring',000012),
(0000013,'26/08/2018','Circuito Spa Francorchamps',
000013),
(0000014,'02/09/2018','Autodromo Nazionale Monza',
000014),
(0000015,'16/09/2018','Circuito de Cingapura',000015),
(0000016,'30/09/2018','Circuito internacional de rua de Sochi',
000016),
(0000017,'07/10/2018','Circuito de Suzuka',000017),
(0000018,'21/10/2018','Circuito das Américas',000018),
(0000019,'28/10/2018','Autodromo Hermanos Rodriguez',
000019),
(0000020,'11/11/2018','Autódromo de Interlagos',000020),
(0000021,'25/11/2018','Yas Marina Circuit',000021);
INSERT INTO dbo.TB_RESULTADO
(ID_PROVA,ID_PILOTO,NR_TEMPO_PROVA,
NR_COLOC_FINAL,NR_POSICAO_GRID,
NR_MELHOR_VOLTA)
VALUES
(0000001,00001,'01: 29: 33.283',1,3,'01: 29: 33.283'),
(0000001,00003,'01: 29: 38.319',2,1,'01: 29: 38.319'),
(0000001,00005,'01: 29: 39.592',3,2,'01: 29: 39.592'),
(0000002,00001,'01: 32: 01.940',1,1,'01: 32: 01.940'),
(0000002,00006,'01: 32: 02.639',2,3,'01: 32: 02.639'),
(0000002,00003,'01: 32: 08.452',3,9,'01: 32: 08.452'),
(0000003,00002,'01: 35: 36.380',1,6,'01: 35: 36.380'),
(0000003,00006,'01: 35: 45.274',2,3,'01: 35: 45.274'),
(0000003,00005,'01: 35: 46.017',3,2,'01: 35: 46.017'),
(0000004,00003,'01: 43: 44.391',1,2,'01: 43: 44.391'),
(0000004,00005,'01: 43: 46.751',2,6,'01: 43: 46.751'),
(0000004,00008,'01: 43: 48.315',3,8,'01: 43: 48.315'),
(0000005,00003,'01: 35: 29.972',1,1,'01: 35: 29.972'),
(0000005,00006,'01: 35: 50.565',2,2,'01: 35: 50.565'),
(0000005,00004,'01: 35: 56.845',3,5,'01: 35: 56.845'),
(0000006,00002,'01: 42: 54.807',1,1,'01: 42: 54.807'),
(0000006,00001,'01: 43: 02.143',2,2,'01: 43: 02.143'),
(0000006,00003,'01: 43: 11.820',3,3,'01: 43: 11.820'),
(0000007,00001,'01: 28: 31.377',1,1,'01: 28: 31.377'),
(0000007,00006,'01: 28: 38.753',2,2,'01: 28: 38.753'),
(0000007,00004,'01: 28: 39.737',3,3,'01: 28: 39.737'),
(0000008,00003,'01: 30: 11.385',1,1,'01: 30: 11.385'),
(0000008,00004,'01: 30: 18.475',2,4,'01: 30: 18.475'),
(0000008,00005,'01: 30: 37.273',3,6,'01: 30: 37.273'),
(0000009,00004,'01: 21: 56.024',1,4,'01: 21: 56.024'),
(0000009,00005,'+ Voltas 0',2,3,'+ Voltas 0'),
(0000009,00001,'+ Voltas 0',3,6,'+ Voltas 0'),
(0000010,00001,'01: 27: 29.784',1,2,'01: 27: 29.784'),
(0000010,00003,'01: 27: 32.048',2,1,'01: 27: 32.048'),
(0000010,00005,'01: 27: 33.436',3,3,'01: 27: 33.436'),
(0000011,00003,'01: 32: 29.845',1,14,'01: 32: 29.845'),
(0000011,00006,'01: 32: 34.380',2,2,'01: 32: 34.380'),
(0000011,00005,'01: 32: 36.577',3,3,'01: 32: 36.577'),
(0000012,00003,'01: 37: 16.427',1,1,'01: 37: 16.427'),
(0000012,00001,'01: 37: 33.550',2,4,'01: 37: 33.550'),
(0000012,00005,'01: 37: 36.528',3,3,'01: 37: 36.528'),
(0000013,00001,'01: 23: 34.476',1,2,'01: 23: 34.476'),
(0000013,00003,'01: 23: 45.537',2,1,'01: 23: 45.537'),
(0000013,00004,'01: 24: 05.848',3,7,'01: 24: 05.848'),
(0000014,00003,'01: 16: 54.484',1,3,'01: 16: 54.484'),
(0000014,00005,'01: 17: 03.189',2,1,'01: 17: 03.189'),
(0000014,00006,'01: 17: 08.550',3,4,'01: 17: 08.550'),
(0000015,00003,'01: 51: 11.611',1,1,'01: 51: 11.611'),
(0000015,00004,'01: 51: 20.572',2,2,'01: 51: 20.572'),
(0000015,00001,'01: 51: 51.556',3,3,'01: 51: 51.556'),
(0000016,00003,'01: 27: 25.181',1,2,'01: 27: 25.181'),
(0000016,00006,'01: 27: 25.181',2,1,'01: 27: 25.181'),
(0000016,00001,'01: 27: 25.181',3,3,'01: 27: 25.181'),
(0000017,00003,'01: 27: 17.062',1,1,'01: 27: 17.062'),
(0000017,00006,'01: 27: 29.981',2,2,'01: 27: 29.981'),
(0000017,00004,'01: 27: 31.357',3,3,'01: 27: 31.357'),
(0000018,00005,'01: 34: 18.643',1,2,'01: 34: 18.643'),
(0000018,00004,'01: 34: 19.924',2,18,'01: 34: 19.924'),
(0000018,00003,'01: 34: 20.985',3,1,'01: 34: 20.985'),
(0000019,00004,'01: 38: 28.851',1,2,'01: 38: 28.851'),
(0000019,00001,'01: 38: 46.167',2,4,'01: 38: 46.167'),
(0000019,00005,'01: 39: 18.765',3,6,'01: 39: 18.765'),
(0000020,00003,'01: 27: 09.066',1,1,'01: 27: 09.066'),
(0000020,00004,'01: 27: 10.535',2,5,'01: 27: 10.535'),
(0000020,00005,'01: 27: 13.830',3,4,'01: 27: 13.830'),
(0000021,00003,'01: 39: 40.382',1,1,'01: 39: 40.382'),
(0000021,00001,'01: 39: 42.963',2,3,'01: 39: 42.963'),
(0000021,00004,'01: 39: 53.088',3,6,'01: 39: 53.088');
--1ªQUESTÃO
SELECT E.NM_EQUIPE AS EQUIPE,
P.NM_PILOTO AS PILOTO
FROM TB_EQUIPE AS E
INNER JOIN TB_PILOTO AS P
ON E.ID_EQUIPE = P.ID_EQUIPE
ORDER BY EQUIPE;
--2ªQUESTÃO
SELECT YEAR(DT_NASCIMENTO) AS "ANO NASCIMENTO",
COUNT(NM_PILOTO) AS "PILOTOS"
FROM TB_PILOTO
GROUP BY YEAR(DT_NASCIMENTO)
ORDER BY "ANO NASCIMENTO";
--3ªQUESTÃO
SELECT NM_PILOTO, PAIS_PILOTO.NM_PAIS AS PAIS_PILOTO,
NM_EQUIPE, PAIS_EQUIPE.NM_PAIS AS PAIS_EQUIPE,
NR_COLOC_FINAL, NM_CIRCUITO
FROM TB_PILOTO
LEFT JOIN TB_PAIS PAIS_PILOTO
ON TB_PILOTO.ID_PAIS = PAIS_PILOTO.ID_PAIS
JOIN TB_EQUIPE
ON TB_PILOTO.ID_EQUIPE = TB_EQUIPE.ID_EQUIPE
LEFT JOIN TB_PAIS PAIS_EQUIPE
ON TB_EQUIPE.ID_PAIS = PAIS_EQUIPE.ID_PAIS
JOIN TB_RESULTADO
ON TB_PILOTO.ID_PILOTO = TB_RESULTADO.ID_PILOTO
JOIN TB_CIRCUITO
ON PAIS_EQUIPE.ID_PAIS = TB_CIRCUITO.ID_PAIS
JOIN TB_PROVA
ON TB_CIRCUITO.ID_CIRCUITO = TB_PROVA.ID_CIRCUITO
ORDER BY NM_CIRCUITO, NR_COLOC_FINAL;
Comentários
Postar um comentário