#Em alguns casos, não é possível buscar dados em uma única consulta
#Por exemplo, selecionar o funcionários com salário acima da média
#Inicialmente deve-se encontrar a média salarial e depois selecionar os funcionários
#Retorna o salário médio (no caso 5500)
select avg(salario_fixo) from funcionario;

#Retorna os funcionários com salário maior que a média
select nome, salario_fixo from funcionario where salario_fixo>5500;

#Para resolver este problema, podemos utilizar a técnica de subquery (subconsulta)
select nome, salario_fixo from funcionario where salario_fixo>(select avg(salario_fixo) from funcionario);

#subquery deve sempre estar entre parenteses e o retorno deve ser compatível com a query de fora (container)

#O exempplo abaixo quer selecionar o id_pedido de todos os pedidos que possui um item com preço maior que 500
#essa consulta gera um erro, pois a subquery retorna mais de uma linha e a container está usando "="
#não pode usar igual para igualar uma subquery que retorna mais de uma linha
select distinct id_pedido from itens_pedido where id_produto =(select id_produto from produto where preco_venda>500);

#Existem aguns operadores para trabalhar com subquery que retornam múltiplas linhas
#São:  IN, NOT IN, ALL, ANY, EXISTS
#No exemplo abaixo trocamos o "=" por "IN" para igualdade
select distinct id_pedido from itens_pedido where id_produto IN (select id_produto from produto where preco_venda>500);

#Se o operador IN serve para igualdade, o NOT IN serve para pegar o diferente
#No exemplo deseja-se selecionar todos os funcionários que não fizeram nenhum pedido no ano de 2017
select id_funcionario, nome from funcionario where id_funcionario NOT IN(select id_funcionario from pedido where year(data)=2017);

#Agora vamos selecionar o funcionario que mais vendeu em 2017
select id_funcionario, nome, cargo, sum(total) from funcionario join pedido using(id_funcionario) 
where year(data)=2017 group by(id_funcionario) having sum(total) =
 (SELECT sum(total) from pedido where year(data)=2017 group by(id_funcionario) order by sum(total) desc limit 0,1);
 
#O operador ALL serve para se referir a todos os elementos de um conjunto.
#Ele é usado junto com outros operadores, como <>, =, etc.
#No exemplo abaixo selecionamos todos os produtos que não foram vendidos em 2017
select descricao from categoria where id_categoria <>ALL
(select id_categoria from produto where id_produto IN 
(select id_produto from itens_pedido join pedido using(id_pedido) where year(data)=2017));

#O operador ANY se refere a algum elemento de um conjunto
#O exemplo abaixo irá retornar todos os funcionários do departamento de “vendas”
#que possuem salário maior do que algum funcionário dos departamentos “Diretoria”, “TI” e “Financeiro”
select funcionario.nome from funcionario join departamento using(id_departamento) where departamento.nome='Vendas'
 and salario_fixo >ANY(select DISTINCT salario_fixo from funcionario join departamento using(id_departamento)
 where departamento.nome in ('Diretoria','TI','Financeiro'));
 
#subquery pode ser usada na inserção
#No caso abaixo usamos a subquery para identificar o id_cidade de Curitiba
INSERT INTO cliente (id_cliente, nome, endereco, id_cidade, telefone)
VALUES (NULL, 'Chuck Nurris', 'Rua Matador, ', (select id_cidade from cidade where nome='Curitiba'), '44999432110');

#também podemos usar subquery no delete
#no exemplo deletamos todos os clientes que não tem pedido
DELETE FROM cliente WHERE id_cliente NOT IN (SELECT id_cliente FROM pedido);

#selecionanado o vendedor que fez o maior pedido de 2017
SELECT id_funcionario, nome, total FROM pedido JOIN funcionario USING(id_funcionario) WHERE total=
(SELECT MAX(total) FROM pedido WHERE YEAR(data)='2017');

#selecionando e dando um aumento de 30% para os funcionarios que venderam mais de 1000 em 2017
SELECT nome, salario_fixo, (salario_fixo*1.3) AS 'Salario Atual' FROM funcionario
WHERE id_funcionario IN(SELECT id_funcionario FROM pedido WHERE YEAR(data)='2017' 
GROUP BY id_funcionario HAVING SUM(total)>=1000)






