#Técnicas de Consulta

#Consultas simples

#seleciona o nome e o endereço de todos os clientes
SELECT nome, endereco FROM cliente;

#seleciona todos os campos de todos os clientes da tabela de cliente
select * from cliente;

#o comando distinct exclui registros repetidos
SELECT DISTINCT id_cidade FROM cliente;

#seleciona todos os cliente cujo o endereço é igual ao parâmetro
SELECT * FROM cliente WHERE endereco='Rua Brasil, 52';

#seleciona todos os pedidos de 2017 e total mennor que 50000
SELECT * FROM pedido WHERE data>='2017-01-01' AND data<='2017-12-31' AND total<=50000;

#seleciona os pedidos com total maior que 500 e que foram feitos em 2014 OU 2017
#sempre que temos operadores OR e AND na mesma consulta, precisa usar parenteses
SELECT * FROM pedido WHERE (total>500) AND 
(((data>='2014-01-01') AND (data<='2014-12-31')) OR ((data>='2017-01-01') AND (data<='2017-12-31')));

#o comando order by ordena o resultado. De formar ascendente ou descendente (ASC ou DESC)
#ASC é o padrão, nem prcisa especificar
#No exemplo ordena por nome de forma ascendente
SELECT * FROM cliente ORDER BY nome ASC;

#no exemplo o resultado é ordenado por nome de forma descendente
#no caso de nomes iguais, o endereço é o segundo critério de ordenação
SELECT * FROM cliente ORDER BY nome DESC, endereco ASC;

#o comando IN serve para comparar um campo com vários valores
#seleciona os funcionarios cujo o salario é igual a 500 ou 1000 ou 2000
SELECT * FROM funcionario WHERE salario_fixo IN (500,1000,2000) ORDER BY nome;

#seleciona os pedidos cujo o campo data está entre 2014 até 2017
SELECT * FROM pedido WHERE data BETWEEN '2014-01-01' AND '2017-12-31' ORDER BY data;

#seleciona os clientes cujo o endereço é igual a NULL
SELECT * FROM cliente WHERE endereco IS NULL;

#seleciona os clientes que tenham Silva em alguma parte do nome
SELECT * FROM cliente WHERE nome LIKE '%Silva%';

#seleciona os clientes cuja a segunda letra é 'i' e a última letra é 'a' 
SELECT * FROM cliente WHERE nome LIKE '_i%a';

#limit serve para limitar a quantidade de registros no resultado
#no exemplo, são retornados apenas 3 registros a partir do registro 1
#a contagem começa no zero, então exibirá o segunda, terceira e quarto registros 
SELECT * FROM cliente WHERE id_cliente<6 ORDER BY nome LIMIT 1,3;

#Junção de Tabelas - JOIN

#O join é usado quando precisamos buscar dados em diferents tabelas que estão relacionadas

#é feito uma junção entre cliente e cidade, para retornar o nome da cidade na consulta de clientes
SELECT cliente.nome, endereco, telefone, cidade.nome FROM cliente, cidade
WHERE cliente.id_cidade=cidade.id_cidade;

#mesmo exemplo anterior, mas usando a sintaxe do JOIN
SELECT cliente.nome, endereco, telefone, cidade.nome FROM cliente JOIN cidade 
ON cliente.id_cidade=cidade.id_cidade; 

#seleciona os clientes cujo nome começa com 'M'
#oberve que os filtros e a condição de junção ficam juntas
SELECT cliente.nome, endereco, telefone, cidade.nome FROM cliente, cidade WHERE
(cliente.id_cidade=cidade.id_cidade AND cliente.nome LIKE 'M%') ORDER BY cliente.nome;
 
#mesmo exemplo anterior, mas usando sintaxe do JOIN
#neste caso, a condição de junça e de filtro ficam separadas
SELECT cliente.nome, endereco, telefone, cidade.nome FROM cliente JOIN cidade
ON (cliente.id_cidade=cidade.id_cidade) WHERE (cliente.nome LIKE 'M%') ORDER BY cliente.nome; 

#a consulta abaixo faz join em 3 tabelas (cliente, cidade e estado)
SELECT cliente.nome, endereco, telefone, cidade.nome, uf FROM
cliente JOIN cidade ON cliente.id_cidade=cidade.id_cidade JOIN
estado ON cidade.id_estado=estado.id_estado 
WHERE cliente.nome LIKE 'M%' ORDER BY cliente.nome; 
 
#é possível colocar um apelida na tabela para não precisar repetir o nome dela diversas vezes
#basta colocar o apelido (alias) após a declaração da tabela
SELECT a.nome, endereco, telefone, b.nome, uf FROM cliente a JOIN cidade b ON a.id_cidade=b.id_cidade
JOIN estado c  ON b.id_estado=c.id_estado WHERE a.telefone LIKE '44%' ORDER BY a.nome;
 
#tabelas com auto relacionamento, como no caso da tabela 'funcionario', deve-se usar auto junção
#para fazer a auto junção, declara a tabela funcionario duas vezes com apelidos diferentes
SELECT func.nome, func.cargo, ger.nome FROM funcionario func, funcionario ger 
WHERE func.id_gerente=ger.id_funcionario ORDER BY func.nome;

#TIPOS E JUNÇÃO

#a junção interna
#a junção interna retorna os registros que possuem correspondentes nas duas tabelas do join
#no caso, se algum cliente não tiver um correspondente em cidade, não será retornado
SELECT id_cidade, cidade.nome, uf FROM cidade INNER JOIN estado ON
 cidade.id_estado=estado.id_estado ORDER BY id_cidade;
 
#a junção externa esquerda retorna todos os registros da tabela na esquerda, mesmo sem correspondente
#no caso, cidade está na esquerda, e mandará na consulta
#serão retornadas todas as cidades, mesmo se não houver um estado correspondente
SELECT id_cidade, cidade.nome, uf FROM cidade LEFT OUTER JOIN estado ON 
cidade.id_estado=estado.id_estado ORDER BY id_cidade;

#na junção externa a direita, quem manda na consulta é a tabela da direita
#no caso, retorna todos os estados, mesmo que não haja cidade correspondente
SELECT id_cidade, cidade.nome, uf FROM cidade RIGHT OUTER JOIN estado ON
 cidade.id_estado=estado.id_estado ORDER BY id_cidade;
 
#quando a chave primária e estrangeira possuem o mesmo nome podemos utilizar o USING
#no exemplo, ao invés de 'on cidade.id_estado=estado.id_estado' usamos apenas 'using(id_estado)'
SELECT id_cidade, cidade.nome, uf FROM cidade INNER JOIN estado USING(id_estado) ORDER BY id_cidade;


#OPERAÇÕES ENTRE COLUNAS

#no exemplo, é feito uma operação de soma entre os campos salario_fixo e bonus
SELECT nome, salario_fixo, bonus, salario_fixo+bonus FROM funcionario ORDER BY salario_fixo+bonus;

#no exemplo utilizamos o 'AS' para colocar um rótulo na coluna da operação matemática
SELECT nome, salario_fixo, bonus, salario_fixo+bonus AS 'Salário Total' FROM funcionario
WHERE nome LIKE 'P%' ORDER BY salario_fixo+bonus;

#calcula o salario líquido que é o desconto de 11% do bruto
SELECT funcionario.nome, departamento.nome, salario_fixo, bonus, ((salario_fixo+bonus)*0.89) AS 'Liquido'
 FROM funcionario JOIN departamento USING(id_departamento) WHERE ((salario_fixo+bonus)*0.89)>2000;

#Quando é feito uma operação entre duas colunas, se uma delas for NULL, o resultado é sempre null
#A função 'coalesce' serve para simular um valor quando o campo possui valor NULL
#no exemplo, o salario+bonus será null se um dos dois for null
SELECT nome, salario_fixo, bonus, salario_fixo+bonus AS Salario_Total FROM funcionario;

#caso salario ou bonus tenham algum valor NULL, será substituído por zero na consulta
SELECT nome, salario_fixo, bonus, COALESCE(salario_fixo,0)+COALESCE(bonus,0) FROM funcionario;


#FUNÇÕES DE ARREDONDAMENTO

#ceiling (arredonda para cima)
#floor (arredonda para cima)
#format ou round (arredonda para o mais próximo)

#arredonda com duas casas decimais
SELECT nome, salario_fixo, FORMAT((salario_fixo/30),2) AS 'Salário Diário' FROM funcionario;


 