Introdução ao SQL - Manipulação de Dados

Prévia do material em texto

Banco de Dados
Structured Query Language (SQL)
4
 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
	Subconsultas
	
Uma subconsulta é uma declaração select, usada como uma expressão, como parte de outra declaração select, update, insert ou delete.
A subconsulta (select aninhado) é resolvida e depois os resultados são substituídos na consulta mais externa.
 Se a declaração where da consulta mais externa incluir o nome de uma coluna, esta deverá ser compatível com a coluna nomeada na lista de seleção da subconsulta (ou seja: mesmo domínio ).	
Structured Query Language - Slide ‹nº›
2
SQL 
 Manipulação de Dados
	Subconsultas
	
	Existem 4 tipos básicos de sub consultas: 	
operador-de-comparação (subconsulta)
 [NOT] IN (subconsulta)
operador-de-comparação [ANY/SOME/ALL] (subconsulta)
 [NOT] EXISTS (subconsulta) 
Structured Query Language - Slide ‹nº›
3
SQL 
 Manipulação de Dados
	Subconsultas
	
	Tipo 1 – operador-de-comparação (subconsulta)
	Subconsulta devolve um único valor
	Ex : Lista dos empregados que ganham o menor salário 
	select e.nome from empregado e
 where salario = (select min(salario) from empregado);
Structured Query Language - Slide ‹nº›
4
SQL 
 Manipulação de Dados
	Subconsultas
	
	Tipo 2 – [NOT] IN (subconsulta)
	Compara se um valor pertence ao conjunto de valores gerado pela subconsulta
	Ex : Selecione matricula dos empregados que trabalham na mesma combinação (projeto, horas) em algum projeto no qual o empregado de matricula 111 trabalha
	select mat from trabalhaem
 where (codproj, horas) in 
 (select codproj, horas from trabalhaem where mat = 111);
Structured Query Language - Slide ‹nº›
5
SQL 
 Manipulação de Dados
	Subconsultas
	
	Tipo 2 – [NOT] IN (subconsulta)
	Ex : Selecione o nome do departamento que não tem empregados
	select d.nome from departamento d 
 where cod not in 
 (select coddepart from empregado);
Structured Query Language - Slide ‹nº›
6
SQL 
 Manipulação de Dados
	Subconsultas
	
	Tipo 3 – operador-de-comparação [ANY/SOME/ALL] (subconsulta)
	Usado para encontrar tuplas que satisfazem a comparação com alguma ou todas as tuplas da subconsulta
	Ex: Obter os nomes dos empregados com salário maior que o dos empregados do departamento 5
	select nome from empregado 
 where salario > ALL 
 (select distinct salario from empregado 
 where coddepart = 5);
Structured Query Language - Slide ‹nº›
7
SQL 
 Manipulação de Dados
	Subconsultas
	
	Equivalência entre operadores
	IN 	 = ANY
 IN	 = SOME
 	NOT IN <> ALL
Structured Query Language - Slide ‹nº›
8
SQL 
 Manipulação de Dados
	Subconsultas
	
	Tipo 4 – [NOT] EXISTS (subconsulta)
	Funciona como um teste de existência, a subconsulta retorna verdadeiro se existir pelo menos uma tupla ou falso se não existir tupla.
	Ex: Obter os nomes dos empregados com pelo menos um dependente
	select e.nome from empregado e 
 where exists 
 (select d.nome from dependente d 
 where d.matricula = e.matricula);
Structured Query Language - Slide ‹nº›
9
SQL 
 Manipulação de Dados
	Subconsultas
	
	Tipo 4 – [NOT] EXISTS (subconsulta)
	Ex: Obter os nomes dos empregados sem dependentes
	select e.nome from empregado e 
 where not exists 
 (select d.nome from dependente d 
 where d.matricula = e.matricula);
Structured Query Language - Slide ‹nº›
10
SQL 
 Manipulação de Dados
Subconsultas
	
	Exists – implementa a interseção algébrica
	Not Exists – implementa a diferença algébrica
Peças que são fornecidas por algum fornecedor
select p.nome from pecas p where exits
		(select * from pecasfornecedor pf
	 		 where pf.peca = p.peca)
Peças que não são fornecidas por nenhum fornecedor
Select p.nome from pecas p where not exists
		(select * from pecasfornecedor pf 
			where pf.pecas = p.peca)
Structured Query Language - Slide ‹nº›
11
SQL 
 Manipulação de Dados
Subconsultas
	
	É possível utilizar a subconsulta na cláusula from representando uma tabela
	select e.nome from (select * from empregado) e 	 where e.coddepart=4; 
	Select d.nome from (select * from departamento where localizacao = ‘Predio 1’) d;
Structured Query Language - Slide ‹nº›
12
SQL 
 Manipulação de Dados
Recomendações sobre SELECT :
Evitar o uso do * na lista de seleção;
Sempre qualificar os atributos com nome ou alias das tabelas;
Dar preferência ao uso de JOIN no lugar de subconsultas;
Utilizar apenas as tabelas e os atributos necessários;
Evitar consultas que varrem toda tabela;
Structured Query Language - Slide ‹nº›
13
SQL 
 Manipulação de Dados
	Subconsultas 
Recupere os nomes e salários dos empregados cujos salários sejam maiores que a média do departamento 1.
Recupere os nomes dos empregados que não possuem dependentes
Que empregados moram no mesmo bairro de Luis Inácio?
Quais empregados ganham mais que o salário médio dos empregados?
Que empregados ganham comissão acima das dos empregados do departamento ‘Administração’?
Structured Query Language - Slide ‹nº›
14
SQL 
 Manipulação de Dados
	Subconsultas 
6. Que empregados participaram de projeto já concluídos?
7. Que dependentes têm o mesmo parentesco do dependente Antonio Carlos?
8. Que empregados possuem algum dependente com parentesco semelhante aos dos dependentes de ‘Ana Maria’?
9. Quais departamentos não têm empregados ?
10. Que empregados não estão alocados em projetos ?
11. Que empregados ganham menos que os de função auxiliar ? 
Structured Query Language - Slide ‹nº›
15
SQL 
 Definição de Dados
	
	Criação de tabelas com select
	create table Empregado1 as select * from Empregado;
	create table InfoDepart as select d.nome, count(*) as totalemp , sum(e.salario) as totalsalario 
	from departamento d join empregado e on e.coddepart = d.cod 
	group by d.nome
 order by totalsalario;
Structured Query Language - Slide ‹nº›
16
SQL 
 Manipulação de Dados
	Atualizações com select 
	INSERT INTO tabela (atributo1, atributo2, ..., atributon) VALUES
 (valor1, valor2, ..., valorn) 
					ou 
				comando select
	insert into InfoDepart (nome, totalemp, totalsalario)
 select d.nome, count(*), sum(e.salario) from
 Departamento d join Empregado e 
		 on e.coddepart = d.cod 
		 group by d.nome;
	Obs : número de colunas no select e no insert devem ser iguais e os domínios compatíveis
Structured Query Language - Slide ‹nº›
17
SQL 
 Manipulação de Dados
	
	UPDATE tabela SET <atribuições> WHERE <condição>;
 
 <atribuições> = <atribuições> = <atributo=constantes>
				ou
		<atributo=atributo>
				ou
		<atributo=operações de atributos e constantes>
 ou 
		<atributo=comando select>
 update Departamento set totsalario = (select sum(e.salario) from empregado e join departamento d1 on 	e.coddepart=d.cod
 where d.nome=‘Informática’) where nome = ‘Informática’;
	
Structured Query Language - Slide ‹nº›
18
SQL 
 Manipulação de Dados
	
	UPDATE tabela SET <atribuições> WHERE <condição>;
 
 <condição> = <atributo> <operador> <constante>
				 ou 
			<atributo> <operador> <atributo> 
				 ou 
			 <atributo> <operador> <comando select> 
	update Empregado set salario = salario * 1.1 where coddepart =
 (select cod from Departamentowhere nome = ‘Informática’);
	
Structured Query Language - Slide ‹nº›
19
SQL 
 Manipulação de Dados
	
	DELETE FROM tabela WHERE <condição>;
	<condição> = <atributo> <operador> <constante>
				 ou 
			<atributo> <operador> <atributo> 
				 ou 
			 <atributo> <operador> <comando select> 
	delete from Empregado where matricula in
 (select mat from Trabalhaem 
 where codproj = 01);
	
Structured Query Language - Slide ‹nº›
20
SQL 
 Manipulação de Dados
DML – Comandos de manipulação SQL
Simples: apenas 4 comandos;
Poderosa: cobre toda a gama de operações possíveis sobre um conjunto de tabelas;
Flexível: permite escrever uma consulta de diversas formas e diversos graus de complexidade
Structured Query Language - Slide ‹nº›
21
SQL 
 Manipulação de Dados
	Exercício 
1. Recupere o nome do departamento, a função, o total de empregados e o total de salários por departamento e função cujo total de salários seja maior que a médias dos sálarios ordenados por departamento e função. 
2. Selecionar todos os empregados do departamento Informática que desempenham papel de Coordenação por mais de 40 hs.
Structured Query Language - Slide ‹nº›
22
SQL 
 Visão
	Tabela virtual cujo conteúdo é definido por uma consulta SQL. 
	Características:
	
Não possui existência física, não possui dados próprios
Pode ser derivada de uma ou mais tabelas ou visões
Sua existência reduz-se à sua definição, sendo armazenada como uma consulta Select
Seu conteúdo é calculado no momento
Usuários podem fazer uso das visões referenciando-se ao nome delas na cláusula from dos comandos SQL-DMl
Structured Query Language - Slide ‹nº›
23
SQL 
 Visão
	Sua utilização :
Independência lógica de dados
Segurança
Simplicidade 
Flexibilidade 
Structured Query Language - Slide ‹nº›
24
SQL 
 Visão
	CREATE VIEW <nome da visão> [(atributo1, atributo2,...)]
		AS SELECT ... [WITH CHECK OPTION] 
	WITH CHECK OPTION – garante que as modificações executadas sobre as visões (Insert, Update e Delete) não violem os critério da cláusula WHERE do SELECT. 
	
	create view infodepart (nomedepart,totempregados,totsalarios) as
select d.nome, count(*), SUM(e.salario) from
	Departamento d, Empregado e 
where d.cod = e.coddepart
group by d.nome;
Structured Query Language - Slide ‹nº›
25
SQL 
 Visão
		
	create view trabalhaem1 as
select e.nome, p.nome, horas from
Empregado e, Trabahaem t, Projeto p
where e.matricula=t.mat and
 t.codproj = p.cod;
Structured Query Language - Slide ‹nº›
26
SQL 
 Visão
	Atualizações sobre Visões - Limitações
	WITH CHECK OPTION – garante que as atualizações não violem os condições impostas na Visão 
	INSERT – Pode ser executado em Visões que manipulem apenas uma tabela e todos os atributos not null devem estar presentes na visão.
	UPDATE – Pode ser executado em Visões que manipulem apenas uma tabela, ou múltiplas tabelas quando o comando afetar apenas atributos de um única tabela.
	DELETE - Pode ser executado em Visões que manipulem uma única tabela.
	Para eliminar uma visão : DROP VIEW <nome da visão>
Structured Query Language - Slide ‹nº›
27
SQL 
 Visão
	Exercício
	Crie uma visão contendo matricula, nome e função dos empregados Analista.
	create view Analista as select matricula, nome, funcao from empregado where funcao = ‘Analista’ with check option 	
	update Analista set funcao = ‘Programador’ where matricula = 117  Erro violação da cláusula where da view WITH CHECK OPTION
	
	insert into Analista values (222, 'Carlos Antonio', 'Analista');  Erro
	atributo salario é obrigatório (not null) 
Structured Query Language - Slide ‹nº›
28
SQL 
 Visão
	Exercício
	Crie uma visão (MaioresSalarios) contendo a matricula, função e salário dos empregados que ganham mais de R$ 3000 use a opção with check option
	O que acontecerá na execução dos seguintes comandos:
	insert into MaioresSalarios (matricula, funcao, salario) 
	values (999, ‘Analista’, 3500);
	
Structured Query Language - Slide ‹nº›
29