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 ;