[RESPOSTA] Aula - consultas simples - Tabela e Inserts

# Aula - Consultas simples

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



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


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

# CONSULTA 1

# Listar todos os dados da tabela Pilotos
 SELECT * FROM Pilotos;

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

# CONSULTA 2

# Listar nome, país e idade de todos os pilotos de Fórmula 1
SELECT
    nome, pais, idade
FROM
    pilotos;

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

# CONSULTA 3

# Mostrar os pilotos e suas respectivas equipes
SELECT
    codigo, nome, equipe
FROM
    pilotos;

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

# CONSULTA 4

#  Selecionar o nome dos pilotos, sua nação e a equipe a que pertencem. Mostrar no máximo dez pilotos.
SELECT
    nome AS Piloto, pais AS Nação, equipe AS Equipe
FROM
    pilotos
LIMIT
    10;

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

# CONSULTA 5

# Selecionar os países dos pilotos.
SELECT DISTINCT
    pais AS Nação
FROM
    pilotos;

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

# CONSULTA 6

# Selecionar nome, idade, país e equipe dos pilotos de Fórmula 1. Os nomes devem estar dispostos em ordem alfabética.
SELECT
    nome AS Nome, idade AS Idade, pais AS Pais, equipe AS Equipe
FROM
    pilotos
ORDER BY
    nome;

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

# CONSULTA 7

# Selecionar nome, idade, país, equipe e pontuação de cada piloto de Fórmula 1. Esses dados devem estar dispostos de acordo com a idade em ordem decrescente.
SELECT
    nome AS Nome, idade AS Idade, pais AS Pais, equipe AS Equipe, pontos AS Pontuacao
FROM
    pilotos
ORDER BY
    idade DESC;

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

# CONSULTA 8

# Mostrar os países a que pertencem os pilotos de Fórmula 1 em ordem alfabética.
SELECT DISTINCT
    pais AS Pais
FROM
    pilotos
ORDER BY
    pais ASC;

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

# CONSULTA 9

# No relatório requisitado sobre os pilotos de Fórmula 1, é necessário conter o nome
# dos pilotos, a equipe a que pertencem, os pontos e um cálculo desses com a seguinte
# fórmula: ((pontos-3)*1.5). Os dados devem estar dispostos em ordem decrescente e
# bastam apenas os dez primeiros classificados.
SELECT
    nome AS Piloto, equipe AS Equipe, pontos AS PontosAtuais, ((pontos-3)*1.5) AS PontosAlterados
FROM
    pilotos
ORDER BY
    pontos DESC
LIMIT 10;

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

# CONSULTA 10

# Selecionar o nome dos pilotos de Fórmula 1, a equipe a que pertencem, sua pontuação
# correspondente e esta acrescida de 20 e dividida por 5. Desses dados, devem ser
# mostrados somente os dez pilotos com menor pontuação.

SELECT
    nome AS Piloto, equipe AS Equipe, pontos AS PontosAtuais, ((pontos+20)/5) AS PontosAlterados
FROM
    pilotos
ORDER BY
    PontosAlterados ASC
LIMIT
    10;

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

# CONSULTA 11

# Selecionar nome, equipe e pontuação dos pilotos de Fórmula 1 que são da Alemanha.
# Os dados devem estar dispostos de acordo com a equipe, em ordem alfabética.

SELECT
    nome AS Nome, equipe AS Equipe, pontos AS Pontuacao, pais AS Pais
FROM
    pilotos
WHERE
    pais = "Alemanha"
ORDER BY
    equipe;

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

# CONSULTA 12

# Mostrar os pilotos de Fórmula 1 que possuem uma pontuação diferente de zero.
# É necessário mostrar nome, equipe, país e pontuação. Esses dados devem estar
# dispostos em ordem decrescente de pontuação.

SELECT
    nome AS Nome, equipe AS Equipe, pais AS Nacao, pontos AS pontuacao
FROM
    pilotos
WHERE
    pontos <> 0
ORDER BY
    pontos DESC;

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

# CONSULTA 13

# Selecionar nome, idade, equipe e país dos pilotos de Fórmula 1 que tenham idade
# igual ou superior a 25 anos.

SELECT
    nome AS Nome, idade AS Idade, equipe AS Equipe, pais AS Nacao, pontos AS pontuacao
FROM
    pilotos
WHERE
    idade >= 25
ORDER BY
    idade;

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

# CONSULTA 14

# No relatório a ser emitido, devem constar apenas os pilotos que tenham menos que 50
# anos. Os dados que precisam aparecer são: nome, equipe, país e pontuação dos pilotos
# de Fórmula 1.

SELECT
    nome AS Nome, equipe AS Equipe, pais AS Nacao, pontos AS pontuacao
FROM
    pilotos
WHERE
    pontos < 50;

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

# CONSULTA 15

# Selecionar todos os pilotos de Fórmula 1 que tenham uma pontuação superior a 100.
# Para completar a informação, devem estar incluídos também nome, equipe, país e
# pontuação dos pilotos.

SELECT
    nome AS Nome, equipe AS Equipe, pais AS Nacao, pontos AS pontuacao
FROM
    pilotos
WHERE
    pontos > 100;

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

# CONSULTA 16

# Listar os pilotos de Fórmula 1 que tenham idade inferior ou igual a 20 anos.
# Incluir nesta listagem nome, idade, equipe, país e a pontuação dos referidos pilotos.

SELECT
    nome AS Nome, idade AS Idade, equipe AS Equipe, pais AS Nacao, pontos AS pontuacao
FROM
    pilotos
WHERE
    idade <= 20
ORDER BY
    idade;

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

# CONSULTA 17

# Selecionar por equipe os pilotos de Fórmula 1 que utilizam os motores Ferrari;
# é importante mencionar nome, país e pontuação dos pilotos.

SELECT
    nome AS Nome, equipe AS Equipe, pais AS Nacao, pontos AS Pontos
FROM
    pilotos
WHERE
    motor LIKE "Ferrari"
ORDER BY
    equipe;

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

# CONSULTA 18

# Selecionar os pilotos dos países que começam com a letra A. Mostrar nome, equipe,
# pontuação e país dos pilotos de Fórmula 1. É importante que os países estejam em
# ordem alfabética.

SELECT
    nome AS Nome, equipe AS Equipe, pais AS Nacao, pontos AS Pontos
FROM
    pilotos
WHERE
    pais LIKE "A%"
ORDER BY
    pais;

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

# CONSULTA 19

# Foi requisitada uma listagem dos pilotos de Fórmula 1 que pertençam a equipes que
# terminem com ‘auber’. Essa lista deve ser por equipe e nela devem constar nome,
# equipe, país e a pontuação dos pilotos.

SELECT
    nome AS Nome, equipe AS Equipe, pais AS Nacao, pontos AS Pontos
FROM
    pilotos
WHERE
    equipe LIKE "_auber"
ORDER BY
    equipe;

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

# CONSULTA 20

# Faça uma listagem dos pilotos de Fórmula 1 que representam países que não possuem
# a sílaba “an” no meio de seu nome. Essa listagem deve ser ordenada por país e
# constar de nome, equipe, país e pontuação dos pilotos.

SELECT
    nome AS Nome, equipe AS Equipe, pais AS Nacao, pontos AS Pontos
FROM
    pilotos
WHERE
    pais NOT LIKE "%an%"
ORDER BY
    pais;

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

# CONSULTA 21

# Selecionar os pilotos de Fórmula 1 maiores de 25 anos e os com 30 anos ou menos.
# É necessário mostrar também os respectivos nome, idade, equi- pe, país e pontuação.

SELECT
    nome AS Nome, idade AS Idade, equipe AS Equipe, pais AS Nacao, pontos AS Pontos
FROM
    pilotos
WHERE
    idade > 25 AND idade <=30;

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

# CONSULTA 22

# Selecionar os pilotos de Fórmula 1 que pertençam a equipes cujo nome comece com F
# ou utilizem motores cujos nomes iniciem com R. É necessário para essas informações
# constar nome, equipe e país dos pilotos de Fórmula 1.

SELECT
    nome AS Nome, equipe AS Equipe, motor AS Motor, pais AS Nacao
FROM
    pilotos
WHERE
    equipe
LIKE "F%" OR motor LIKE "R%";

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

# CONSULTA 23

# Listar nome, idade, equipe e motores utilizados pelos pilotos de Fórmula 1 que tenham
# menos que 25 anos, mas também façam parte de equipes cujo nome inicie com a letra R
# ou que utilizem motores com a letra M em seu nome.

SELECT
    nome AS Nome, idade AS Idade, equipe AS Equipe, motor AS Motor
FROM
    pilotos
WHERE
    (equipe LIKE "R%" OR motor LIKE "%m%") AND idade < 25;

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

# CONSULTA 24

# Listar os pilotos de Fórmula 1 que alcançaram pontos de 136 até 252.
# Nesta listagem, é importante colocar nome, idade, país e pontos dos pilotos.

SELECT
    nome, idade, pais, equipe, pontos
FROM
    pilotos
WHERE
    pontos BETWEEN 136 AND 252;

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

# CONSULTA 25

# Mostrar os pilotos de Fórmula 1 que tenham uma pontuação maior que 136 e também
# não estejam devendo nenhum ponto também. Inclua nesta lista nome, idade, país,
# equipe e pontos desses pilotos.

SELECT
    nome, idade, pais, equipe, pontos
FROM
    pilotos
WHERE
    pontos NOT BETWEEN 0 AND 136;

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

# CONSULTA 26

# Selecionar nome, idade, país e equipe dos pilotos de Fórmula 1 que tenham idade
# igual a 22 ou 24 ou ainda 29. Ordenar esta informação por idade.

SELECT
    nome, idade, pais, equipe
FROM
    pilotos
WHERE
    idade IN (22, 24, 29)
ORDER BY
    idade;

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

# CONSULTA 27

# Listar nome, idade, país e equipe dos pilotos de Fórmula 1 que não sejam da
# Alemanha ou da Inglaterra ou da Espanha.

SELECT
    nome, idade, pais, equipe
FROM
    pilotos
WHERE
    pais NOT IN ("Alemanha", "Inglaterra", "Espanha")
ORDER BY
    pais;

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

# CONSULTA 28

# Selecionar os pilotos de Fórmula 1 que não pontuaram ainda.

SELECT
    *
FROM
    pilotos
WHERE
    pontos IS NULL;

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

# CONSULTA 29

# Mostrar dados dos pilotos de Fórmula 1 que possuem pontuação menor que 10.

SELECT
    *
FROM
    pilotos
WHERE
    pontos IS NOT NULL AND pontos < 10;

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

# CONSULTA 30

# Informar o número de pilotos de Fórmula 1 existente nesse cadastro.

SELECT
    COUNT(*)
AS
    NumeroPilotos
FROM
    pilotos;


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

# CONSULTA 31

# Indicar o número de pilotos de Fórmula 1 que tenham 21 anos ou mais.

SELECT
    COUNT(*)
AS
    NumeroPilotos
FROM
    pilotos
WHERE
    idade >= 21;

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

# CONSULTA 32

# Informar a média de idade dos pilotos da Alemanha ou da Inglaterra ou do Brasil.

SELECT
    AVG(idade) AS MediaIdadePilotos
FROM
    pilotos
WHERE
    pais IN ("Alemanha", "Inglaterra", "Brasil");

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

# CONSULTA 33

# Informar a média dos pontos dos pilotos de Fórmula 1.

SELECT
    AVG(pontos) AS MediaPontosPilotos
FROM
    pilotos;

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

# CONSULTA 34

# Informar o piloto mais novo de Fórmula 1.

SELECT
    MIN(idade) AS IdadeMinima
FROM
    pilotos;

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

# CONSULTA 35

# Indicar pontuação máxima alcançada pelos pilotos de Fórmula 1.

SELECT
    MAX(pontos) AS PontuacaoMaxima
FROM
    pilotos;

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

# CONSULTA 36

# Mostrar o total dos pontos dos pilotos de Fórmula 1 da Inglaterra.

SELECT
    SUM(pontos) AS SomaDosPontos
FROM
    pilotos
WHERE
    pais = "Inglaterra";

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

# CONSULTA 37

# Mostrar a pontuação total por país dos pilotos de Fórmula 1.

SELECT
    SUM(pontos) AS SomaDosPontos, pais AS Pais
FROM
    pilotos
GROUP BY
    pais
ORDER BY
    SomaDosPontos;

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

# CONSULTA 38

# Listar a média de idade dos pilotos de Fórmula 1 de cada país dessa tabela.
# Devem ser elencados somente os países que apresentaram a média de idade dos
# pilotos maior que 20 anos.

# Para resolver essa consulta, vamos dividi-la em duas partes:

# Parte 1: Listar a média de idade dos pilotos de Fórmula 1 de cada país desta tabela.

SELECT
    AVG(idade) AS MediaDaIdade, pais AS Pais
FROM
    pilotos
GROUP BY
    pais
ORDER BY
    pais;


# Parte 2: Os registros destacados na tabela anterior não devem constar no resultado
# final, e no código da consulta é necessário incluir a cláusula HAVING com a condição
# para que seja feita a seleção como mostrado a seguir:

SELECT
    AVG(idade) AS MediaDaIdade, pais AS Pais
FROM
    pilotos
GROUP BY
    pais
HAVING
    AVG(idade) > 20
ORDER BY
    pais;

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

# CONSULTA 39

# Mostrar a quantidade de pilotos de Fórmula 1 de cada país que consta nesse cadastro.
# Listar os países em ordem alfabética.

SELECT
    COUNT(*) AS NumeroDePilotos, pais AS Pais
FROM
    pilotos
GROUP BY
    pais
ORDER BY
    pais;