TdpcpBAX

· 4 years ago · Jun 16, 2021, 07:18 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
111DELIMITER //
112CREATE TRIGGER 
113	verificaIdade_pilotos
114AFTER INSERT ON
115	pilotos
116FOR EACH ROW
117BEGIN
118	IF(NEW.idade < 18) THEN
119		INSERT INTO
120			log
121		SET
122			dataLog = curdate(),
123            obs = "Idade fora do padrão",
124            tabela = "pilotos",
125            atributo = "idade";
126		END IF;
127END;
128//
129DELIMITER ;
130
131# Testando
132
133SELECT * FROM log;
134
135
136INSERT INTO 
137	pilotos(codigo, nome, pais, idade, equipe, motor, pontos)
138VALUES
139	(877, "Paulo T", "Brasil", 17, "Lotus", "Lotus", 0);
140    
141    
142SELECT * FROM pilotos;
143
144
145# --------------------------------
146
147# Exemplo 2
148
149# Trigger disparado após uma atualização na tabela Pilotos, 
150# caso a equipe e o motor de um piloto sejam da mesma empresa,
151# são somados três pontos ao seu total.
152
153DELIMITER //
154CREATE TRIGGER
155	verificaPontos_pilotos
156BEFORE UPDATE ON
157	pilotos
158FOR EACH ROW
159BEGIN
160	IF (NEW.equipe = NEW.motor)
161    THEN
162		SET NEW.pontos = NEW.pontos + 3;
163	END IF;
164END;
165//
166DELIMITER ;
167
168
169# Testando
170
171SELECT * FROM pilotos WHERE codigo = 877;
172
173
174UPDATE
175	pilotos
176SET
177	equipe = "Ferrari", motor = "Ferrari"
178WHERE
179	codigo = 877;
180    
181
182SELECT * FROM pilotos WHERE codigo = 877;