Junções - Criação

# Aula - Junções
# Cria a base de dados
CREATE DATABASE ProjetoFormula1;

# Seleciona a base a ser utilizadas nos comando abaixo
USE ProjetoFormula1;

# Cria a tabela Pilotos
CREATE TABLE Pilotos
(
    codigo INT(11),
    nome VARCHAR(40) NOT NULL,
    pais VARCHAR(20),
    idade INT(6),
    equipe VARCHAR(20) NOT NULL,
    motor VARCHAR(20),
    pontos INT(10),
    PRIMARY KEY (codigo)
);


CREATE TABLE Ranking
(
    codigo_piloto INT(11),
    GP INT(4) NOT NULL,
    classificacao1 INT(3) NOT NULL,
    classificacao2 INT(3) NOT NULL,
    classificacao3 INT(3) NOT NULL,
    podio INT(4) NOT NULL,
    numPolePosition INT(4) NOT NULL,
    voltaMaisRapida INT(4) NOT NULL,
    PRIMARY KEY (codigo_piloto),
    FOREIGN KEY (codigo_piloto) REFERENCES Pilotos (codigo)
);


# ---------- Inserindo dados ----------

INSERT INTO Pilotos
    (codigo, nome, pais, idade, equipe, motor, pontos)
VALUES
    (111, "Sebastian Vettel", "Alemanha", 25, "RedBull", "Renault", 256),
    (112, "Fernando Alonso", "Espanha", 28, "Ferrari", "Ferrari", 252),
    (113, "Mark Alan Webber", "Áustria", 26, "RedBull", "Renault", 242),
    (114, "Lewis Hamilton", "Inglaterra", 22, "McLaren", "Mercedes", 240),
    (115, "Jenson Button", "Inglaterra", 21, "McLaren", "Mercedes", 214),
    (116, "Felipe Massa", "Brasil", 27, "Ferrari", "Ferrari", 144),
    (117, "Nico Rosberg", "Alemanha", 24, "Mercedes", "Mercedes", 142),
    (118, "Robert Kubica", "Polônia", 21, "Renault", "Renault", 136),
    (119, "Michael Schumacher", "Alemanha", 23, "Mercedes", "Mercedes", 72),
    (120, "Rubens Barrichello", "Brasil", 29, "Williams", "Cosworth", 47),
    (121, "Adrian Sutil", "Alemanha", 24, "ForceIndia", "Mercedes", 47),
    (122, "Kamui Kobayashi", "Japão", 23, "Sauber", "Ferrari", 32),
    (123, "Vitaly Petrov", "Rússia", 22, "Renault", "Renault", 27),
    (124, "Nico Hulkenberg", "Alemanha", 20, "Williams", "Cosworth", 22),
    (125, "Vitantonio Liuzzi", "Itália", 25, "ForceIndia", "Mercedes", 21),
    (126, "Sebastian Buemi", "Suiça", 16, "ToroRosso", "Ferrari", 8),
    (127, "Pedro de la Rosa", "Espanha", 22, "Sauber", "Ferrari", 6),
    (128, "Nivk Heidfeld", "Alemanha", 22, "Sauber", "Ferrari", 6),
    (129, "Jaime Alguersuari", "Espanha", 27, "ToroRosso", "Ferrari", 5),
    (130, "Sakon Yamamoto", "Japão", 20, "Hispania", "Cosworth", 0),
    (131, "Lucas Tucci di Grassi", "Brasil", 25, "Virgin", "Cosworth", 0),
    (132, "Jarno Trulli", "Itália", 18, "Lotus", "Cosworth", 0),
    (133, "Heikki Kovalainen", "Finlândia", 19, "Lotus", "Cosworth", 0),
    (134, "Timo Glock", "Alemanha", 24, "Virgin", "Cosworth", 0),
    (135, "Christian Klien", "Austrália", 20, "Hispania", "Cosworth", 0),
    (136, "Bruno Senna", "Brasil", 21, "Hispania", "Cosworth", 0),
    (137, "Karun Chandhok", "Índia", 20, "Hispania", "Cosworth", 0);


INSERT INTO Ranking
    (codigo_piloto, GP, classificacao1, classificacao2, classificacao3, podio, numPolePosition, voltaMaisRapida)
VALUES
    (135, 49, 0, 0, 0, 0, 0, 0),
    (116, 154, 11, 11, 11, 33, 15, 14),
    (112, 179, 28, 26, 20, 74, 20, 19),
    (133, 91, 1, 2, 1, 4, 1, 2),
    (132, 252, 1, 4, 6, 11, 3, 1),
    (122, 42, 0, 0, 0, 0, 0, 0),
    (114, 92, 17, 16, 11, 44, 21, 11),
    (113, 178, 7, 10, 13, 30, 9, 13),
    (119, 289, 91, 43, 20, 154, 67, 76),
    (128, 183, 0, 8, 5, 13, 1, 2),
    (124, 21, 0, 0, 0, 0, 1, 0),
    (117, 110, 0, 1, 4, 5, 0, 2),
    (127, 87, 0, 1, 0, 1, 0, 1),
    (118, 76, 1, 5, 6, 12, 1, 1),
    (120, 323, 11, 29, 28, 38, 14, 17),
    (111, 83, 21, 10, 6, 37, 30, 9),
    (126, 55, 0, 0, 0, 0, 0, 0),
    (134, 74, 0, 2, 1, 3, 0, 1),
    (123, 40, 0, 0, 1, 1, 0, 1);