# 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, país e idade de todos os pilotos de Fó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 nação e a equipe a que pertencem. Mostrar no máximo dez pilotos.
SELECT 
	nome AS Piloto, pais AS Nação, equipe AS Equipe
FROM 
	pilotos 
LIMIT 
	10;

# -----------

# CONSULTA 5

# Selecionar os países dos pilotos.
SELECT DISTINCT 
	pais AS Nação
FROM 
	pilotos;
    
# -----------

# CONSULTA 6

# Selecionar nome, idade, país e equipe dos pilotos de Fórmula 1. Os nomes devem estar dispostos em ordem alfabética.
SELECT 
	nome AS Nome, idade AS Idade, pais AS Pais, equipe AS Equipe 
FROM 
	pilotos 
ORDER BY 
	nome;
    
# -----------

# CONSULTA 7   

# Selecionar nome, idade, país, equipe e pontuação de cada piloto de Fó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 países a que pertencem os pilotos de Fórmula 1 em ordem alfabética.
SELECT DISTINCT 
	pais AS Pais 
FROM 
	pilotos 
ORDER BY 
	pais ASC;
    
# -----------

# CONSULTA 9

# No relatório requisitado sobre os pilotos de Fórmula 1, é necessário conter o nome 
# dos pilotos, a equipe a que pertencem, os pontos e um cálculo desses com a seguinte 
# fó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 Fórmula 1, a equipe a que pertencem, sua pontuação
# correspondente e esta acrescida de 20 e dividida por 5. Desses dados, devem ser 
# mostrados somente os dez pilotos com menor pontuaçã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 pontuação dos pilotos de Fórmula 1 que são da Alemanha. 
# Os dados devem estar dispostos de acordo com a equipe, em ordem alfabé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 Fórmula 1 que possuem uma pontuação diferente de zero. 
# É necessário mostrar nome, equipe, país e pontuação. Esses dados devem estar 
# dispostos em ordem decrescente de pontuaçã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 país dos pilotos de Fó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 relatório a ser emitido, devem constar apenas os pilotos que tenham menos que 50 
# anos. Os dados que precisam aparecer são: nome, equipe, país e pontuação dos pilotos 
# de Fó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 Fórmula 1 que tenham uma pontuação superior a 100. 
# Para completar a informação, devem estar incluídos também nome, equipe, país e 
# pontuaçã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 Fórmula 1 que tenham idade inferior ou igual a 20 anos. 
# Incluir nesta listagem nome, idade, equipe, país e a pontuaçã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 Fórmula 1 que utilizam os motores Ferrari; 
# é importante mencionar nome, país e pontuaçã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 países que começam com a letra A. Mostrar nome, equipe, 
# pontuação e país dos pilotos de Fórmula 1. É importante que os países estejam em 
# ordem alfabé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 Fórmula 1 que pertençam a equipes que 
# terminem com ‘auber’. Essa lista deve ser por equipe e nela devem constar nome, 
# equipe, país e a pontuaçã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

# Faça uma listagem dos pilotos de Fórmula 1 que representam países que não possuem 
# a sílaba “an” no meio de seu nome. Essa listagem deve ser ordenada por país e 
# constar de nome, equipe, país e pontuaçã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 Fórmula 1 maiores de 25 anos e os com 30 anos ou menos. 
# É necessário mostrar também os respectivos nome, idade, equi- pe, país e pontuaçã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 Fórmula 1 que pertençam a equipes cujo nome comece com F 
# ou utilizem motores cujos nomes iniciem com R. É necessário para essas informações 
# constar nome, equipe e país dos pilotos de Fó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 Fórmula 1 que tenham 
# menos que 25 anos, mas também faç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 Fórmula 1 que alcançaram pontos de 136 até 252. 
# Nesta listagem, é importante colocar nome, idade, paí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 Fórmula 1 que tenham uma pontuação maior que 136 e também 
# não estejam devendo nenhum ponto também. Inclua nesta lista nome, idade, paí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, país e equipe dos pilotos de Fórmula 1 que tenham idade 
# igual a 22 ou 24 ou ainda 29. Ordenar esta informação por idade.

SELECT 
	nome, idade, pais, equipe 
FROM 
	pilotos 
WHERE 
	idade IN (22, 24, 29) 
ORDER BY
	idade;
    
# -----------

# CONSULTA 27

# Listar nome, idade, país e equipe dos pilotos de Fórmula 1 que nã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 Fórmula 1 que não pontuaram ainda.

SELECT 
	* 
FROM 
	pilotos
WHERE 
	pontos IS NULL;
    
# -----------

# CONSULTA 29

# Mostrar dados dos pilotos de Fórmula 1 que possuem pontuação menor que 10.

SELECT 
	* 
FROM 
	pilotos 
WHERE 
	pontos IS NOT NULL AND pontos < 10;

# -----------

# CONSULTA 30

# Informar o número de pilotos de Fórmula 1 existente nesse cadastro.

SELECT 
	COUNT(*) 
AS 
	NumeroPilotos 
FROM 
	pilotos;


# -----------

# CONSULTA 31

# Indicar o número de pilotos de Fórmula 1 que tenham 21 anos ou mais.

SELECT 
	COUNT(*) 
AS 
	NumeroPilotos 
FROM 
	pilotos 
WHERE 
	idade >= 21;

# -----------

# CONSULTA 32

# Informar a mé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 média dos pontos dos pilotos de Fórmula 1.

SELECT 
	AVG(pontos) AS MediaPontosPilotos
FROM 
	pilotos;
    
# -----------

# CONSULTA 34

# Informar o piloto mais novo de Fórmula 1.

SELECT 
	MIN(idade) AS IdadeMinima
FROM 
	pilotos;
    
# -----------

# CONSULTA 35

# Indicar pontuação máxima alcançada pelos pilotos de Fórmula 1.

SELECT 
	MAX(pontos) AS PontuacaoMaxima
FROM 
	pilotos;
    
# -----------

# CONSULTA 36

# Mostrar o total dos pontos dos pilotos de Fórmula 1 da Inglaterra.

SELECT 
	SUM(pontos) AS SomaDosPontos 
FROM 
	pilotos 
WHERE 
	pais = "Inglaterra";

# -----------

# CONSULTA 37

# Mostrar a pontuação total por país dos pilotos de Fórmula 1.

SELECT 
	SUM(pontos) AS SomaDosPontos, pais AS Pais 
FROM 
	pilotos 
GROUP BY 
	pais 
ORDER BY 
	SomaDosPontos;

# -----------

# CONSULTA 38

# Listar a média de idade dos pilotos de Fórmula 1 de cada país dessa tabela. 
# Devem ser elencados somente os países que apresentaram a média de idade dos 
# pilotos maior que 20 anos.

# Para resolver essa consulta, vamos dividi-la em duas partes: 

# Parte 1: Listar a média de idade dos pilotos de Fórmula 1 de cada paí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 não devem constar no resultado 
# final, e no código da consulta é necessário incluir a cláusula HAVING com a condição 
# para que seja feita a seleçã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 Fórmula 1 de cada país que consta nesse cadastro. 
# Listar os países em ordem alfabética.

SELECT 
	COUNT(*) AS NumeroDePilotos, pais AS Pais 
FROM 
	pilotos 
GROUP BY 
	pais
ORDER BY
	pais;