· 4 years ago · Jun 16, 2021, 08:52 PM
1# Cria a base de dados
2CREATE DATABASE IF NOT EXISTS ProjetoFormula1;
3
4# Seleciona a base a ser utilizadas nos comando abaixo
5USE ProjetoFormula1;
6
7# Cria a tabela Pilotos
8CREATE TABLE IF NOT EXISTS Pilotos
9(
10 codigo INT(11),
11 nome VARCHAR(40) NOT NULL,
12 pais VARCHAR(20),
13 idade INT(6),
14 equipe VARCHAR(20) NOT NULL,
15 motor VARCHAR(20),
16 pontos INT(10),
17 PRIMARY KEY (codigo)
18);
19
20
21CREATE TABLE IF NOT EXISTS Ranking
22(
23 codigo_piloto INT(11),
24 GP INT(4) NOT NULL,
25 classificacao1 INT(3) NOT NULL,
26 classificacao2 INT(3) NOT NULL,
27 classificacao3 INT(3) NOT NULL,
28 podio INT(4) NOT NULL,
29 numPolePosition INT(4) NOT NULL,
30 voltaMaisRapida INT(4) NOT NULL,
31 PRIMARY KEY (codigo_piloto),
32 FOREIGN KEY (codigo_piloto) REFERENCES Pilotos (codigo)
33);
34
35
36# ---------- Inserindo dados ----------
37
38INSERT INTO Pilotos
39 (codigo, nome, pais, idade, equipe, motor, pontos)
40VALUES
41 (111, "Sebastian Vettel", "Alemanha", 25, "RedBull", "Renault", 256),
42 (112, "Fernando Alonso", "Espanha", 28, "Ferrari", "Ferrari", 252),
43 (113, "Mark Alan Webber", "Áustria", 26, "RedBull", "Renault", 242),
44 (114, "Lewis Hamilton", "Inglaterra", 22, "McLaren", "Mercedes", 240),
45 (115, "Jenson Button", "Inglaterra", 21, "McLaren", "Mercedes", 214),
46 (116, "Felipe Massa", "Brasil", 27, "Ferrari", "Ferrari", 144),
47 (117, "Nico Rosberg", "Alemanha", 24, "Mercedes", "Mercedes", 142),
48 (118, "Robert Kubica", "Polônia", 21, "Renault", "Renault", 136),
49 (119, "Michael Schumacher", "Alemanha", 23, "Mercedes", "Mercedes", 72),
50 (120, "Rubens Barrichello", "Brasil", 29, "Williams", "Cosworth", 47),
51 (121, "Adrian Sutil", "Alemanha", 24, "ForceIndia", "Mercedes", 47),
52 (122, "Kamui Kobayashi", "Japão", 23, "Sauber", "Ferrari", 32),
53 (123, "Vitaly Petrov", "Rússia", 22, "Renault", "Renault", 27),
54 (124, "Nico Hulkenberg", "Alemanha", 20, "Williams", "Cosworth", 22),
55 (125, "Vitantonio Liuzzi", "Itália", 25, "ForceIndia", "Mercedes", 21),
56 (126, "Sebastian Buemi", "Suiça", 16, "ToroRosso", "Ferrari", 8),
57 (127, "Pedro de la Rosa", "Espanha", 22, "Sauber", "Ferrari", 6),
58 (128, "Nivk Heidfeld", "Alemanha", 22, "Sauber", "Ferrari", 6),
59 (129, "Jaime Alguersuari", "Espanha", 27, "ToroRosso", "Ferrari", 5),
60 (130, "Sakon Yamamoto", "Japão", 20, "Hispania", "Cosworth", 0),
61 (131, "Lucas Tucci di Grassi", "Brasil", 25, "Virgin", "Cosworth", 0),
62 (132, "Jarno Trulli", "Itália", 18, "Lotus", "Cosworth", 0),
63 (133, "Heikki Kovalainen", "Finlândia", 19, "Lotus", "Cosworth", 0),
64 (134, "Timo Glock", "Alemanha", 24, "Virgin", "Cosworth", 0),
65 (135, "Christian Klien", "Austrália", 20, "Hispania", "Cosworth", 0),
66 (136, "Bruno Senna", "Brasil", 21, "Hispania", "Cosworth", 0),
67 (137, "Karun Chandhok", "Índia", 20, "Hispania", "Cosworth", 0);
68
69
70INSERT INTO Ranking
71 (codigo_piloto, GP, classificacao1, classificacao2, classificacao3, podio, numPolePosition, voltaMaisRapida)
72VALUES
73 (135, 49, 0, 0, 0, 0, 0, 0),
74 (116, 154, 11, 11, 11, 33, 15, 14),
75 (112, 179, 28, 26, 20, 74, 20, 19),
76 (133, 91, 1, 2, 1, 4, 1, 2),
77 (132, 252, 1, 4, 6, 11, 3, 1),
78 (122, 42, 0, 0, 0, 0, 0, 0),
79 (114, 92, 17, 16, 11, 44, 21, 11),
80 (113, 178, 7, 10, 13, 30, 9, 13),
81 (119, 289, 91, 43, 20, 154, 67, 76),
82 (128, 183, 0, 8, 5, 13, 1, 2),
83 (124, 21, 0, 0, 0, 0, 1, 0),
84 (117, 110, 0, 1, 4, 5, 0, 2),
85 (127, 87, 0, 1, 0, 1, 0, 1),
86 (118, 76, 1, 5, 6, 12, 1, 1),
87 (120, 323, 11, 29, 28, 38, 14, 17),
88 (111, 83, 21, 10, 6, 37, 30, 9),
89 (126, 55, 0, 0, 0, 0, 0, 0),
90 (134, 74, 0, 2, 1, 3, 0, 1),
91 (123, 40, 0, 0, 1, 1, 0, 1);
92
93
94CREATE TABLE IF NOT EXISTS log
95(
96 dataLog DATETIME NOT NULL,
97 obs TEXT NOT NULL,
98 tabela VARCHAR(50) NOT NULL,
99 atributo VARCHAR(50) NOT NULL
100);
101
102
103# --------------------------------
104
105# Exemplo 1
106
107# Caso o piloto adicionado na tabela “pilotos” tenha menos que 18 anos,
108# devemos registrar uma informação na tabela “log” automaticamente.
109
110
111
112# --------------------------------
113
114# Exemplo 2
115
116# Trigger disparado após uma atualização na tabela Pilotos,
117# caso a equipe e o motor de um piloto sejam da mesma empresa,
118# são somados três pontos ao seu total.