Introdução ao SQL

Prévia do material em texto

Banco de Dados
Structured Query Language (SQL)
3
 Livros Texto : Introdução a Sistemas de Banco de Dados - Date
 Sistemas de Banco de Dados – Korth & Silberschatz 		Sistemas de Banco de Dados Fundamentos e 			Aplicações – Elmasri & Navathe
		
Structured Query Language - Slide ‹nº›
1
SQL 
 Manipulação de Dados
	
	SELECT/ORDER BY 
	Classifica o resultado da consulta ( em ordem ascendente, caso nada seja especificado)
Itens especificados no ORDER BY não precisam aparecer na lista da seleção
Com ORDER BY nulos são listados primeiros.
select [distinct] lista from tabela
[where condicao]
[order by {coluna | expressao} [asc|desc]]
	Select matricula, nome from empregado order by nome 
	Select nome, salario from empregado order by salario desc 	 
Structured Query Language - Slide ‹nº›
2
SQL 
 Manipulação de Dados
FUNÇÃO DE AGREGAÇÃO e AGRUPAMENTO
Funções comuns aplicadas a coleções de valores numérico, incluem SUM(soma), AVG(média), MAX (máximo) e MIN (mínimo);
Função COUNT é utilizada para contagem de tuplas
Função SUM e AVG só funcionam com valores numéricos;
 
Utilizadas no SELECT e na cláusula HAVING;
Structured Query Language - Slide ‹nº›
3
SQL 
 Manipulação de Dados
FUNÇÃO DE AGREGAÇÃO
Não podem ser utilizadas num comando WHERE;
Podem ser aplicados a todas as linhas em uma tabela ou num grupo de linhas de uma tabela.
select funcao_agregada ([distinct] expressao)
from tabela [where condicoes]	 
Structured Query Language - Slide ‹nº›
4
SQL 
 Manipulação de Dados
	
	FUNÇÃO DE AGREGAÇÃO - COUNT 
	Soma o número de linhas resultante de uma condição estabelecida. 
Determinar o número de linhas da tabela
	select count(*) from empregado
Número de valores não nulos de um atributo
	select count(atributo) from empregado
 
Structured Query Language - Slide ‹nº›
5
SQL 
 Manipulação de Dados
	
	FUNÇÃO DE AGREGAÇÃO – MAX/ MIN 
Determinar o maior valor
select max(salario) from empregado
Determina o menor valor
	
	 select min(salario) from empregado
 
Structured Query Language - Slide ‹nº›
6
SQL 
 Manipulação de Dados
	
	FUNÇÃO DE AGREGAÇÃO – SUM/AVG 
Determinar o somatório dos valores da coluna especificada
select sum(salario) from empregado where coddep = 4
Determina a média dos valores da coluna especificada
	
	 select avg(salario) from empregado where funcao = ‘analista’
 
Structured Query Language - Slide ‹nº›
7
SQL 
 Manipulação de Dados
	
	FUNÇÃO DE AGREGAÇÃO – DISTINCT
Opcional com sum, avg e count;
Não permitida com min, max e count (*);
Permitida com count (atributo);
Usado apenas com atributos, não com expressões aritméticas
	
	Média dos salarios distintos
	 select avg(distinct salario) from empregado 
	Conta as funções distintas
 	Select count (distinct funcao) from empregado
 
Structured Query Language - Slide ‹nº›
8
SQL 
 Manipulação de Dados
	
	FUNÇÃO DE AGREGAÇÃO
Funções de agregação, com exceção do COUNT(*), ignoram valores nulos;
SUM e AVG só funcionam com valores numéricos;
 Retorna apenas uma linha (sem GROUP BY );
Structured Query Language - Slide ‹nº›
9
SQL 
 Manipulação de Dados
	FUNÇÃO DE AGRUPAMENTO - SELECT/GROUP BY
Divide os dados em grupos;
Normalmente utilizada com uma função de agregação na lista de seleção;
Os valores nulos na coluna do group by são tratados como um grupo;
		select [distinct] lista from tabela group by expressao
	Select codproj, avg(horas) from Trabalhaem group by codproj
	 
Structured Query Language - Slide ‹nº›
10
SQL 
 Manipulação de Dados
	
	SELECT/GROUP BY/HAVING
	
	HAVING estabelece condições para a sentença GROUP BY;
 
select [distinct] lista from tabela
group by expressao
having condicoes
	 Select codproj, avg(horas) from Trabalhaem group by codproj having avg(horas) > 30
	 
Structured Query Language - Slide ‹nº›
11
SQL 
 Manipulação de Dados
	Exercícios
	
	1. Qual é a média de salários dos empregados:
 	a) por departamento?
 	b) por funcao?
	2. Qual é o salario mais baixo e mais alto de cada departamento?
	3. Quantos horas cada empregado trabalhou em projetos ?
	4. Para os empregado que trabalharam menos de 10 horas por projeto, calcule a média de horas trabalhadas em projetos destes funcionários.
	
	5. Quantos empregados existem por funcao ?
	
Structured Query Language - Slide ‹nº›
12
SQL 
 Manipulação de Dados
	Exercícios
	
	6. Qual o número de empregados por departamento?
	7. Quantas horas foram gastas por projeto?
	8. Quantos horas cada empregado trabalhou em projetos?
	9. Quantos salarios distintos existem na tabela de empregados? 
	
	10. Por codigo do projeto recupere quantos funcionários trabalharam nele e qual a valor minimo e máximo do número de horas trabalhadas em cada projeto.	
Structured Query Language - Slide ‹nº›
13
SQL 
 Manipulação de Dados
	Exercícios
	
	11. Sem utilizar AVG calcule a média de sálario dos empregados por departamento.
	12. Listar o codigo do departamento e nome de todos os empregados classificados por codigo do departamento e nome.
	13. Qual o número de dependentes filhos de sexo feminino?
	14. Selecione as matriculas dos empregados que trabalham em mais de 2 projetos.
	15. Selecione os projetos onde trabalham mais de 3 empregados. 
Structured Query Language - Slide ‹nº›
14
SQL 
 Manipulação de Dados
	Junções
	
	Pode-se utilizar tabelas do mesmo banco ou de banco diferentes;
	
	Atributos comparados devem ter valores similares;
	Valores nulos não participam da operação de junção;
	Os atributos na condição de junção não precisam estar na lista de seleção
	Select e. nome, d.nome from empregado e, departamento d
			where codepart = cod 
	
Structured Query Language - Slide ‹nº›
15
SQL 
 Manipulação de Dados
	Junções
	
	Recupera dados de duas ou mais tabelas;
	Combina tabelas através da correspondência de valores de linhas em cada tabela;
	Qualificar os atributos que têm o mesmo nome, usando o nome do atributo precedido pelo nome da tabela; Ex: tabela.atributo
	 	
Structured Query Language - Slide ‹nº›
16
SQL 
 Manipulação de Dados
	Junções
	
	Idênticas
 Os atributos de junção são do mesmo tipo e fazem parte de um relacionamento entre as tabelas. (integridade referencial, chaves primária e estrangeira).
Não idênticas
 Os atributos de junção podem não ser do mesmo tipo e nem fazer parte de um relacionamento entre as tabelas.
 Muito utilizada para associar o resultado de uma função de agregação com outras tabelas.
	
Structured Query Language - Slide ‹nº›
17
SQL 
 Manipulação de Dados
	Exercícios
	1. Mostre nome do departamento e todos os seus empregados classificado por nome do departamento e nome empregado;
	2.Recupere os nome departamento, nome empregado e nome do projeto ordenados por departamento e nome; 
	
Structured Query Language - Slide ‹nº›
18
SQL 
 Manipulação de Dados
	Junções – Expressão JOIN 
	SQL2 - Possibilidade de colocar na cláusula FROM a especificação da junção 
	[CROSS, NATURAL, INNER, LEFT OUTER, RIGHT OUTER, FULL OUTER] JOIN
 Cross Join – Produto cartesiano
	Natural Join – Junção Natural - Junta tabelas sem precisar declarar a condição seleção. Para isso as relações devem possuir atributos com mesmo nome. Será executada uma igualdade para cada par de atributos com o mesmo nome. 
Structured Query Language - Slide ‹nº›
19
SQL 
 Manipulação de Dados
	Junções – Expressão JOIN 
	Inner Join – Junção Interna - Padrão para join. Junta tabelas a partir de uma condição especificada. Uma tupla será incluída no resultado somente se existir uma tupla que combine na outra relação.
	
 	Outer Join – Junção Externa - Junta tabelas a partir de uma condição especificada. Uma tupla será incluída no resultado se existir uma tupla que combine na outrarelação, caso não combine, a tupla será incluída para a relação sinalizada (esquerda ou direita) e os atributos da outra relação serão preenchidos com nulo.
	LEFT OUTER JOIN – Junção externa à direita
	RIGHT OUTER JOIN – Junção externa à esquerda
	FULL OUTER JOIN – Junção externa completa
Structured Query Language - Slide ‹nº›
20
SQL 
 Manipulação de Dados
	Junções – Expressão JOIN 
	
 	Produto cartesiano
	
	SELECT * FROM departamento, empregado;
	pode ser : 
	SELECT * FROM departamento CROSS JOIN empregado;
	
Structured Query Language - Slide ‹nº›
21
SQL 
 Manipulação de Dados
	Junções interna – Expressão INNER JOIN 
	
	Selecione o nome e função dos empregados que trabalham no departamento de Pesquisa 
	SELECT empregado. nome, empregado.funcao FROM (empregado INNER JOIN departamento ON empregado.codepart = departamento.cod )
 WHERE departamento.nome = ‘Pesquisa’;
	semelhante a :
	SELECT empregado. nome, empregado.funcao FROM empregado, departamento WHERE empregado.codepart = departamento.cod 
 AND departamento.nome = ‘Pesquisa’;	
Structured Query Language - Slide ‹nº›
22
SQL 
 Manipulação de Dados
	Junções interna – Expressão INNER JOIN 
	
	Para os projetos concluídos, liste o número do projeto, departamento controlador e o nome do gerente do departamento. 
	SELECT projeto.cod, departamento.nome, empregado. nome 
 FROM ((projeto JOIN departamento ON projeto.coddepar = departamento.cod )
 JOIN empregado on departamento.matgerente=empregado.mat)
 WHERE projeto.situacao=‘Concluido’;
			semelhante a :
	SELECT projeto.cod, departamento.nome, empregado. nome 
 FROM projeto, departamento, empregado
 WHERE projeto.coddepar = departamento.cod AND
 departamento.matgerente=empregado.mat AND
		 projeto.situacao=‘Concluido’;
	
Structured Query Language - Slide ‹nº›
23
SQL 
 Manipulação de Dados
	
	
	Na junção interna (inner join) uma tupla é incluída no resultado somente se as tuplas das duas relações combinarem. 
	
	SELECT empregado.nome, dependente. nome 
 FROM (empregado JOIN dependente ON empregado.mat = dependente.mat)
	Neste caso os empregados sem dependentes não aparecerão
	
		
Structured Query Language - Slide ‹nº›
24
SQL 
 Manipulação de Dados
	Junções externas – Expressão OUTER JOIN 
	No junção externa (outer join) as tuplas que não atendem a condição aparecerão com os atributos da relação indicada (ou right ou left ou full) e com os atributos da outra relação nulos.
	
	
	SELECT empregado.nome, dependente. nome 
 FROM (empregado LEFT OUTER JOIN dependente ON empregado.mat = dependente.mat)
	Neste caso todos os empregados aparecerão, nos empregados sem dependentes o nome do dependente ficará nulo.
	
		
Structured Query Language - Slide ‹nº›
25
SQL 
 Manipulação de Dados
	Exercícios
	1. Listar para cada empregado, o nome de todos os seus dependentes e sexo dos dependentes, ordenados por sexo do dependente;
	
	2.Mostre para cada nome de departamento o total de salário de seus empregados, ordenado por total de salário;
	3.Recupere o matrícula, nome e salário total (salário + comissão) de todos os empregados que ganham mais que seu gerente; 
	
Structured Query Language - Slide ‹nº›
26
SQL 
 Manipulação de Dados
	Exercícios
	4. Selecione os nomes e funções dos empregados que trabalham no projeto Sistema de Pagamento e são do departamento Informática; 
	5. Mostre para cada nome de departamento, o nome de seu gerente e salário, ordenado por salário em ordem descendente;
	6. Mostre o nome, horas trabalhadas e situação dos projetos que Ana Santos trabalhou. 
	7. Calcule o salário mais comissão de todos os funcionarios do departamento Informática que têm funcao Analista;
	
Structured Query Language - Slide ‹nº›
27
SQL 
 Manipulação de Dados
	Exemplos
	8. Selecionar todos os projetos em andamento com seus empregados em ordem de projetos e empregados.
	9. Selecionar os empregados e quantidade de dependentes para empregados que têm mais de 2 dependentes.
	10. Selecionar todos os empregados com seus dependentes.
	11. Selecione o nome de todos os empregados e dos projetos em que estão envolvidos.
	
Structured Query Language - Slide ‹nº›
28