Junções - Gabarito

# 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);

# ---------- Consultas ----------

# CONSULTA 1
# Listar os dados dos pilotos que estão classificados no ranking.

# Usando vírgula e WHERE
SELECT
    *
FROM
    pilotos, ranking
WHERE
    pilotos.codigo = ranking.codigo_piloto
ORDER BY
    nome;

# Usando JOIN e ON (sintaxe mais limpa e de acordo com os padrões ANSI)
SELECT
    *
FROM
    pilotos JOIN ranking ON pilotos.codigo = ranking.codigo_piloto
ORDER BY
    nome;

# -----------

# CONSULTA 2

# Selecionar nome, equipe, pontos e pódio dos pilotos de Fórmula 1.

# Usando vírgula e WHERE
SELECT
    pilotos.codigo, pilotos.nome, pilotos.equipe, pilotos.pontos, ranking.GP, ranking.podio
FROM
    pilotos, ranking
WHERE
    pilotos.codigo = ranking.codigo_piloto
ORDER BY
    nome;

# Usando JOIN e ON (sintaxe mais limpa e de acordo com os padrões ANSI)
SELECT
    pilotos.codigo, pilotos.nome, pilotos.equipe, pilotos.pontos, ranking.GP, ranking.podio
FROM
    pilotos JOIN ranking ON pilotos.codigo = ranking.codigo_piloto
ORDER BY
    nome;

# -----------

# CONSULTA 3

# Listar nome dos pilotos que subiram ao pódio pelo menos uma vez.

# Usando vírgula e WHERE
SELECT
    p.codigo,p.nome, r.podio
FROM
    pilotos p, ranking r
WHERE
    p.codigo = r.codigo_piloto AND r.podio > 0
ORDER BY
    podio DESC;

# Usando JOIN e ON (sintaxe mais limpa e de acordo com os padrões ANSI)
SELECT
    p.codigo,p.nome, r.podio
FROM
    pilotos p JOIN ranking r ON p.codigo = r.codigo_piloto AND r.podio > 0
ORDER BY
    podio DESC;


# -----------

# CONSULTA 4

# Listar nome dos pilotos e pole position dos que estão no ranking

# Usando vírgula e WHERE
SELECT
    codigo,nome, numPolePosition
FROM
    pilotos p, ranking r
WHERE
    p.codigo = r.codigo_piloto
ORDER BY
    numPolePosition;

# Usando JOIN e ON (sintaxe mais limpa e de acordo com os padrões ANSI)
SELECT
    codigo,nome, numPolePosition
FROM
    pilotos p JOIN ranking r on p.codigo = r.codigo_piloto
ORDER BY
    numPolePosition;


# Nas consultas que trabalhamos até aqui, utilizamos várias tabelas,
# mas sempre indicando o relacionamento para associá-las e obter as
# informações deseja- das. A linguagem SQL nos oferece recursos para
# fazer a junção das tabelas, por exemplo, o JOIN na cláusula FROM,
# em que podemos indicar o tipo da junção.

# -----------

# CONSULTA 5

# Listar nome dos pilotos e pole position dos que estão no ranking

# A mesma consulta 4 é repetida, mas desta vez não vamos relacionar os
# campos utilizando o sinal de igualdade na cláusula WHERE, e sim o JOIN,
# recurso proposto para o mesmo relacionamento pela linguagem.

SELECT
    codigo, nome, numPolePosition
FROM
    pilotos p JOIN ranking r ON p.codigo = r.codigo_piloto
ORDER BY
    numPolePosition;

# -----------

# CONSULTA 6

# Mesma consulta porém utilizando LEFT JOIN

# Nesta junção são retornadas todas as linhas da tabela esquerda em uma junção.
# Todas as linhas da tabela do lado esquerdo da equação serão retornadas
# independentemente de terem correspondência ou não. Se não houver correspondência,
# o valor NULL será retornado.

SELECT
    codigo, nome, numPolePosition
FROM
    pilotos p LEFT JOIN ranking r ON p.codigo = r.codigo_piloto
ORDER BY
    numPolePosition;

# -----------

# CONSULTA 7

# Mesma consulta porém utilizando RIGHT JOIN

# RIGHT JOIN ́é a forma inversa do LEFT JOIN: em vez de a referência ficar à esquerda, fica à direita.

SELECT
    codigo, nome, numPolePosition
FROM
    pilotos p RIGHT JOIN ranking r ON p.codigo = r.codigo_piloto
ORDER BY
    numPolePosition;


ALTER TABLE
    Pilotos
RENAME COLUMN
    codigo
TO
    codigo_piloto;

SELECT
    codigo_piloto, nome, numPolePosition
FROM
    pilotos p NATURAL JOIN ranking r
ORDER BY
    numPolePosition;