ImpBanco Aula5novo

Prévia do material em texto

IMPLEMENTAÇÃO DE BANCO DE DADOS
Profª. Adriana Sicsú
Aula 5: SQL – Parte 3
IMPLEMENTAÇÃO DE BANCO DE DADOS
Aula 5: SQL – Parte 3
Conteúdo programático
Aprender a ordenar o resultado de uma consulta
Aprender funções de grupo
Recuperar dados agregados de uma tabela
Exercícios
IMPLEMENTAÇÃO DE BANCO DE DADOS
Aula 5: SQL – Parte 3
Onde: 
<lista de atributos> é uma lista de nomes de atributos cujos valores devem ser recuperados pela consulta.
<lista tabelas> é uma lista dos nomes das tabelas exigidos para processar a recuperação.
<condição> é expressão condicional que identifica as tuplas a serem recuperadas pela consulta.
Estrutura: Select – From - Where
SELECT <lista de atributos>
FROM <lista tabelas>
WHERE <condição>
IMPLEMENTAÇÃO DE BANCO DE DADOS
Aula 5: SQL – Parte 3
Exemplos: 
Estrutura: Select – From - Where
IMPLEMENTAÇÃO DE BANCO DE DADOS
Aula 5: SQL – Parte 3
Exemplos: 
Listar o primeiro nome e o cargo dos empregados.
 Select prim_nome, cargo from empregado
2) Listar o primeiro nome, salário e data de Admissão dos empregados.
 Select prim_nome, salario, dt_admissao 
 from empregado
3) Listar todos dos dados dos empregados.
 Select * from empregado
Estrutura: Select – From - Where
IMPLEMENTAÇÃO DE BANCO DE DADOS
Aula 5: SQL – Parte 3
Exemplos: 
3) Listar os gerentes contratados depois de 2012. 
 Select * from empregado
 Where dt_amissao >= ‘01/01/2012’ AND 
 UPPER(cargo) = ‘GERENTE’ 
4) Listar os empregados que trabalham no departamento 01 ou 05.
 Select * from empregado
 Where id_depto = 01 OR id_depto = 05 
IMPLEMENTAÇÃO DE BANCO DE DADOS
Aula 5: SQL – Parte 3
 Permite obter como resultado o valor de uma coluna que não pertence a uma determinada lista de elementos. 
Exemplo: Listar os empregados que NÃO são Gerente, nem Técnico e nem Auxiliar.
 Select * from empregado
 Where UPPER(cargo) NOT IN(‘GERENTE’, ‘TÉCNICO’, ‘AUXILIAR’)
Operador - NOT IN
IMPLEMENTAÇÃO DE BANCO DE DADOS
Aula 5: SQL – Parte 3
 Permite consultar os valores que não se encontram em uma determinada faixa. Exemplo: Listar os empregados que NÃO ganham entre R$ 1000,00 e R$ 3000,00.
 Select * from empregado
 Where salario NOT BETWEEN 1000.00 AND 3000.00
Operador – NOT BETWEEN
IMPLEMENTAÇÃO DE BANCO DE DADOS
Aula 5: SQL – Parte 3
 É empregado nas situações em que usamos como base para realizar pesquisas (ou filtros) as colunas que NÃO estão no formato caractere.
Exemplo: Listar todos os empregados que NÃO começam com a letra M.
 Select * from empregado
 Where UPPER(prim_nome) NOT LIKE ‘M%’ 
Operador – NOT LIKE
IMPLEMENTAÇÃO DE BANCO DE DADOS
Aula 5: SQL – Parte 3
 Para permitir a ordenação o SQL nos fornece a clausula ORDER BY.
Onde: 
	ASC ordena as linhas de forma ascendente; 
	DESC ordena as linhas de forma descendente.
Ordenando Resultados
SELECT <lista de atributos>
FROM nome da tabela
WHERE condição {AND | OR} condição
ORDER BY nome da coluna [ ASC | DESC ]
 [, nome da coluna [ ASC | DESC] ... ]
IMPLEMENTAÇÃO DE BANCO DE DADOS
Aula 5: SQL – Parte 3
Exemplos: 
Listar os empregados em ordem crescente de salário.
 Select * from empregado
 Order by salario
2) Listar os empregados em ordem decrescente de data de admissão.
 Select * from empregado
 Order by dt_admissao DESC
Ordenando Resultados
ASC - Ordenação default
IMPLEMENTAÇÃO DE BANCO DE DADOS
Aula 5: SQL – Parte 3
Exemplos: 
Listar os empregados em ordem decrescente de cargo e dentro do cargo em ordem crescente de salário.
 Select * from empregado
 Order by Cargo DESC, Salario ASC
ou
 Select * from empregado
 Order by 4 DESC, 5 ASC
Ordenando Múltipla
Operando pelo posição da Coluna.
IMPLEMENTAÇÃO DE BANCO DE DADOS
Aula 5: SQL – Parte 3
Eliminando Duplicados - DISTINCT
IMPLEMENTAÇÃO DE BANCO DE DADOS
Aula 5: SQL – Parte 3
Listar os diferentes cargos dos empregados
 Select Distinct cargo
 from empregado
Eliminando Duplicados
IMPLEMENTAÇÃO DE BANCO DE DADOS
Aula 5: SQL – Parte 3
	Funções de grupo operam sobre conjuntos de linhas. Elas retornam resultados baseados sobre um grupo de linhas, antes que um resultado por linha tenha retornado como uma função de linha única. 
	Como padrão todas as linhas de um tabela são trilhadas como um grupo. 
	A clausula GROUP BY da declaração do SELECT é usada para agrupar as linhas em menores grupos.
Funções de Grupo
IMPLEMENTAÇÃO DE BANCO DE DADOS
Aula 5: SQL – Parte 3
Funções de Grupo
IMPLEMENTAÇÃO DE BANCO DE DADOS
Aula 5: SQL – Parte 3
Funções de Grupo
IMPLEMENTAÇÃO DE BANCO DE DADOS
Aula 5: SQL – Parte 3
Listar o salario médio e a soma dos salários dos empregados.
 Select AVG(salario), SUM(salario)
 from empregado
2) Listar o menor e maior salário dos empregados.
 Select MIN(salario), MAX(salario)
 from empregado
Funções de Grupo
IMPLEMENTAÇÃO DE BANCO DE DADOS
Aula 5: SQL – Parte 3
3) Listar o maior e menor valor de sobrenome, salário e data de admissão.
 Select MAX(ult_nome), MIN(ult_nome) , MAX(salario),
 MIN(salario), MAX(dt_Admissao), MIN(dt_admissao)
 from empregado
Funções de Grupo
IMPLEMENTAÇÃO DE BANCO DE DADOS
Aula 5: SQL – Parte 3
4) Contar a quantidade de empregados e a quantidade de empregrados que possuem Gerente.
 Select COUNT(*), COUNT(ID) , COUNT(Id_gerente)
 from empregado
Funções de Grupo
IMPLEMENTAÇÃO DE BANCO DE DADOS
Aula 5: SQL – Parte 3
1) Listar a quantidade de Cargos e de ID_Departamentos difetentes.
 Select COUNT(Cargo), COUNT(DISTINCT cargo) , 
 COUNT(DISTINCT Id_depto)
 from empregado
Funções de Grupo - DISTINCT
IMPLEMENTAÇÃO DE BANCO DE DADOS
Aula 5: SQL – Parte 3
	Outro recurso do comando SELECT é a possibilidade de agruparmos nossos dados, utilizando a cláusula GROUP BY.
Onde:
 expressão - especifica as colunas cujos valores determinam a base para o grupo de linhas;
 GROUP BY - produz uma linha sumarizada para cada grupo de linhas selecionado. 
	
Criando Grupos
SELECT <lista de atributos>
FROM nome da tabela
WHERE condição {AND | OR} condição
GROUP BY expressão
IMPLEMENTAÇÃO DE BANCO DE DADOS
Aula 5: SQL – Parte 3
1) Listar a quantidade de empregados, salário médio e a soma dos salários dos empregados de cada departamento.
 Select COUNT(*), AVG(Salario) , SUM(Salario)
 from empregado
 GROUP by Id_depto
Criando Grupos
IMPLEMENTAÇÃO DE BANCO DE DADOS
Aula 5: SQL – Parte 3
Criando Grupos
IMPLEMENTAÇÃO DE BANCO DE DADOS
Aula 5: SQL – Parte 3
1) Listar a quantidade de empregados, salário médio e a soma dos salários dos empregados de cada departamento e dentro do departamento para cada cargo.
 Select Id_depto, Cargo, COUNT(*), AVG(Salario) , 
 SUM(Salario)
 from empregado
 GROUP by Id_depto, cargo
Criando Grupos
IMPLEMENTAÇÃO DE BANCO DE DADOS
Aula 5: SQL – Parte 3
1) Listar a quantidade de empregados, salário médio e a soma dos salários dos empregados de cada departamento.
 Select COUNT(*), AVG(Salario) , SUM(Salario)
 from empregado
 GROUP by Id_depto
Criando Grupos
IMPLEMENTAÇÃO DE BANCO DE DADOS
Aula 5: SQL – Parte 3
PRATICANDO...
IMPLEMENTAÇÃO DE BANCO DE DADOS
Aula 5: SQL – Parte 3
IMPLEMENTAÇÃO DE BANCO DE DADOS
Aula 5: SQL – Parte 3
IMPLEMENTAÇÃO DE BANCO DE DADOS
Aula 5: SQL – Parte 3
IMPLEMENTAÇÃO DE BANCO DE DADOS
Aula 5: SQL – Parte 3
IMPLEMENTAÇÃO DE BANCO DE DADOSAula 5: SQL – Parte 3
1) Listar o CPF e Ultimo nome dos empregados que trabalham no departamento 04 e ganham entre R%2500,00 e R$ 4000,00.
 Select cpf, ult_nome, from empregado
 Where id_depto = 4 AND 
 salario BETWEEN 2500.00 AND 4000.00
2) Listar o primeiro nome e o cargo dos empregados contratados entre 2007 e 2009.
 Select prim_nome, cargo from empregado
 Where dt_admissao BETWEEN ‘01/01/2007’ AND ’31/12/2009’
Exercício
IMPLEMENTAÇÃO DE BANCO DE DADOS
Aula 5: SQL – Parte 3
3) Listar os empregados que possuem CPF com o dígito verificador 15.
 Select * from empregado
 Where CPF LIKE ‘%15’
4) Listar o primeiro nome e salario com um aumento de 4% para os funcionários que foram contratados antes de 2000 nos cargos de gerente, vendedor ou assistente.
 Select prim_nome, salario *1.04 “Novo Salario” 
 from empregado
 Where dt_admissao < 01/01/2000 AND
 UPPER(cargo) IN (‘GERENTE’, ‘VENDEDOR’, ‘ASSISTENTE’)
IMPLEMENTAÇÃO DE BANCO DE DADOS
Aula 5: SQL – Parte 3
Próxima Aula
Aprender funções de grupo
Recuperar dados agregados de uma tabela
IMPLEMENTAÇÃO DE BANCO DE DADOS
Aula 5: SQL – Parte 3
Referência Bibliográfica		
Livro – Sistemas de Banco de Dados (Elmasri – Navathe, 6ª Edição)
Material de Aula – Professor Sidney Ventury (Estácio de Sá)
Relação: representada por uma tabela de duas dimensões (linhas e colunas);
Tupla: corresponde a uma linha da relação;
Atributo: corresponde às colunas da relação;
Chave primária: conjunto de atributos que identificam univocamente cada tupla da
relação;
Chave extrangeira: atributo de uma relação que é chave primária de outra relação.
*