Lista de Exercícios
SQL/Consultas Complexas, Triggers e Views
1. Recupere os nomes de todos os funcionários que não possuem supervisores.
select pnome, unome
from funcionario
where cpf_supervisor is null;
2. Recupere os nomes dos funcionários que não possuem nenhum dependente.
select pnome, unome
from funcionario
where not exists (select *
from dependente
where cpf=fcpf);
3. Escreva uma visão que contenha o primeiro e o último nome do funcionário, o nome do
projeto e o número de horas que o funcionário trabalha em cada projeto. vTRABALHA_EM
Pnome Unome Projnome Horas
create view vTRABALHA_EM
as select pnome, unome, projnome, horas
from funcionario, trabalha_EM, projeto
where cpf=fcpf and pnr= projnumero;
4. Escreva uma visão que contenha o nome, o número de funcionários e o total dos salários
de cada departamento.
vDEPT_INFO
DEPT_NOME NO_EMPS TOTAL_SAL
create view vDEPT_INFO
as select dnome as dept_nome, count(*) as no_emps, sum(salario) as total_sal
from departamento, funcionario
where dnumero=dnr
group by dnome;
5. Recupere na visão vTRABALHA_EM o último nome de todos os funcionários que
trabalham no ‘ProdutoX’.
select unome
from vTRABALHA_EM
where projnome= 'ProdutoX';
6. Altere o nome do projeto ‘ProdutoX’ para ‘ProdutoW’ na visão vTRABALHA_EM.
update vTRABALHA_EM
set projnome='ProdutoW'
where projnome= 'ProdutoX';
7. Exclua a visão vTRABALHA_EM.
drop view vTRABALHA_EM;
8. Crie uma asserção onde o salário de um funcionário não pode ser maior que o salário de
seu superior.
create assertion limite_salario
check (not exists
(select *
from funcionario E, funcionario S //Obs: MySQL não suporta asserções
where E.salario > S.salario and E.Cpf=S. Cpf_supervisor));
9. Para cada departamento, recupere o seu número, o número de funcionários que nele
trabalham, a média e o total de seus salários.
select dnr as departamento, count(*) as quantidade,
avg(salario) as media, sum(salario) as total
from funcionario
group by dnr;
10. Inclua o um novo atributo para a tabela departamento chamado total_sal. Crie um trigger
para atualizar o valor total dos salários do departamento em que um novo funcionário é
lotado.
alter table departamento add column total_sal decimal(10,2);
update departamento set total_sal=0;
delimiter $$
create trigger t1
after insert on funcionario
for each row
begin
update departamento set total_sal = total_sal + new.salario where
dnumero=new.dnr;
end$$
delimiter ;