Universidade Federal do Ceará – UFC Departamento Computação – DC Fundamentos de Banco de Dados - FBD
Profª: Vânia Maria Ponte Vidal
LISTA DE EXERCÍCIOS 03 -
GABARITO
1) Esquema Entidade/Relacionamento do Banco De dados: Empresa
2) Comandos SQL para criação do Esquema do Banco de Dados
CREATE TABLE empregados ( cpf bigint, enome varchar(60), salario float, cargo varchar(20), cpf_supervisor bigint, dnumero integer, PRIMARY KEY (cpf) );
CREATE TABLE departamentos ( dnumero integer,
dnome varchar(60), cpf_gerente bigint, PRIMARY KEY (dnumero),
CONSTRAINT fk_cpf FOREIGN KEY(cpf_gerente) REFERENCES empregados(cpf) );
CREATE TABLE trabalha ( cpf_emp bigint,
pnumero integer, num_horas integer,
CONSTRAINT fk_cpfemp FOREIGN KEY(cpf_emp) REFERENCES empregados(cpf) );
CREATE TABLE projetos ( pnumero integer, pnome varchar(45), dnumero integer, PRIMARY KEY(pnumero),
CONSTRAINT fk_dnum FOREIGN KEY(dnumero) REFERENCES departamentos(dnumero)
);
3) Comandos para povoar o Banco de Dados (inserções): Insert Into empregados
values (049382234322,'João Silva', 2350, 'Programador', 2434332222, 1010); Insert Into empregados
values (586733922290,'Mario Silveira', 3500, 'Designer', 2434332222, 1010); Insert Into empregados
values (2434332222,'Aline Barros', 2350,'Supervisor(a)', NULL, 1010); Insert Into empregados
values (08797336112,'José Macêdo', 6000, 'Professor', 2434332222, 5); Insert Into empregados
values (78798558745,'Fernando Trinta', 5500, 'Professor', 08797336112, 5); Insert Into empregados
values (12358246598,'Roberto Carlos', 1500, 'Motorista', 08797336112, 2); Insert Into empregados
values (45685295125,'Lulu Santos', 4500, 'Caixa', 2434332222, 1); Insert Into empregados
values (55545685299,'Michel Telo', 4500, 'Vendedor', 2434332222, 1); Insert Into empregados
values (89887885245,'Ivete Sangalo', 8000, 'Gerente de Vendas', 2434332222, 1); Insert Into empregados
values (12558556559,'Carlinhos Brown', 2500, 'Operador', 08797336112, 2); Insert Into empregados
values (46548998785,'Wesley Safadão', 600, 'Estagiário', 12558556559, 2); Insert Into departamentos (dnumero, dnome, cpf_gerente)
values (1010, 'Pesquisa', 049382234322); Insert Into departamentos
values (1020, 'Ensino', 2434332222); Insert Into departamentos
values (5, 'Extensão', 2434332222); Insert Into departamentos
values (1, 'Vendas', 586733922290); Insert Into departamentos
Insert Into trabalha
values (049382234322, 2010, 90), (586733922290, 2020, 120); Insert Into trabalha
values (049382234322, 2020, 200); Insert Into trabalha
values (46548998785, 1234, 60), (12558556559, 1234, 45), (89887885245, 1234, 125), (55545685299, 1234, 200), (12358246598, 1234, 100), (78798558745, 1234, 30), (049382234322, 1234, 40);
Insert Into projetos (pnumero, pnome, dnumero) values (2010,'Alpha', 1010);
Insert Into projetos (pnumero, pnome, dnumero) values (2020,'Beta', 1020);
Insert Into projetos (pnumero, pnome, dnumero) values (1234,'Gama', 1);
4) Adição das chaves estrangeiras restantes:
ALTER TABLE empregados ADD CONSTRAINT fk_dnum FOREIGN KEY(dnumero) REFERENCES departamentos(dnumero);
ALTER TABLE trabalha ADD CONSTRAINT fk_pnum FOREIGN KEY(pnumero) REFERENCES projetos(pnumero);
5) Consultas SQL no Banco de Dados:
1. Selecione os empregados que trabalham no departamento 5 e ganham mais do que R$5.000. SELECT *
FROM EMPREGADOS E WHERE E.dnumero = 5 AND E.salario > 5000;
2. Liste os nomes dos supervisores dos empregados que ganham mais do que R$5.000. Solução A:
SELECT DISTINCT E1.enome
FROM EMPREGADOS E1, EMPREGADOS E2 WHERE E1.cpf = E2.cpf_supervisor
Solução B:
SELECT E1.enome FROM EMPREGADOS E1 WHERE E1.salario > 5000
AND E1.cpf IN (SELECT DISTINCT E2.cpf_supervisor FROM EMPREGADOS E2);
3. Liste o nome e salário de todos os empregados que ganham mais que o seu supervisor. SELECT E2.enome, E2.salario
FROM EMPREGADOS E1, EMPREGADOS E2 WHERE E1.cpf = E2.cpf_supervisor
AND E1.salario < E2.salario;
4. Obtenha o nome do gerente de cada projeto. SELECT DISTINCT E.enome
FROM EMPREGADOS E, PROJETOS P, DEPARTAMENTOS D WHERE D.cpf_gerente = E.cpf
5. Liste os nomes dos projetos que têm um empregado chamado “João Silva” que trabalha no projeto ou gerencia o departamento que controla o projeto.
SELECT DISTINCT P.pnome
FROM EMPREGADOS E, DEPARTAMENTOS D, PROJETOS P, TRABALHA T WHERE E.enome = 'João Silva'
AND (E.cpf = T.cpf_emp
AND T.pnumero = P.pnumero OR E.cpf = D.cpf_gerente AND D.dnumero = P.dnumero);
6. CPF dos empregados que não trabalham em nenhum projeto. SELECT E.cpf
FROM EMPREGADOS E
WHERE E.cpf NOT IN (SELECT T.cpf_emp FROM TRABALHA T);
7. CPF dos empregados que trabalham em pelo menos um projeto. Solução A:
SELECT E.cpf
FROM EMPREGADOS E
WHERE E.cpf IN (SELECT T.cpf_emp FROM TRABALHA T);
Solução B: SELECT E.cpf
FROM EMPREGADOS E
WHERE EXISTS (SELECT T.cpf_emp FROM TRABALHA T
WHERE T.cpf_emp = E.cpf);
8. CPF dos empregados que trabalham em todos os projetos. SELECT E.cpf
FROM EMPREGADOS E, TRABALHA T WHERE E.cpf = T.cpf_emp
GROUP BY E.cpf
HAVING COUNT(DISTINCT T.pnumero) = (SELECT COUNT(DISTINCT P.pnumero) FROM PROJETOS P);
9. Liste o nome e salário de todos os empregados e no caso de ser um gerente, liste o departamento que gerencia.
SELECT E.enome, E.salario, D.dnome FROM EMPREGADOS E
LEFT JOIN DEPARTAMENTOS D ON D.cpf_gerente = E.cpf;
10. Liste o nome dos empregados no departamento “Transporte” que tem o maior salário. SELECT E.enome
FROM EMPREGADOS E INNER JOIN DEPARTAMENTOS D ON D.dnumero = E.dnumero WHERE D.dnome = 'Transporte'
AND E.salario >= ALL (SELECT E.salario
FROM EMPREGADOS E INNER JOIN DEPARTAMENTOS D ON D.dnumero = E.dnumero WHERE D.dnome = 'Transporte');
11. Qual é a média de salário dos empregados no departamento “Vendas”? SELECT AVG(E.salario) AS Media_Sal
FROM EMPREGADOS E INNER JOIN DEPARTAMENTOS D ON E.dnumero = D.dnumero WHERE D.dnome = 'Vendas';
12. Para cada empregado obtenha o número de projetos que ele trabalha e o total de horas que trabalha nestes projetos.
Solução A:
SELECT E.cpf, COUNT(T.pnumero), SUM(T.num_horas) FROM EMPREGADOS E, TRABALHA T, PROJETOS P WHERE E.cpf = T.cpf_emp
AND T.pnumero = P.pnumero GROUP BY E.cpf;
Solução B - Mostra os empregados que não trabalham em projetos: SELECT E.cpf, COUNT(T.pnumero), SUM(T.num_horas)
FROM EMPREGADOS E LEFT JOIN TRABALHA T ON E.cpf = T.cpf_emp LEFT JOIN PROJETOS P ON T.pnumero = P.pnumero GROUP BY E.cpf;
13. Quantos empregados trabalham em mais de um projeto? CREATE VIEW Emp_NumPro (cpf, numPro) AS
SELECT E.cpf, COUNT(*)
FROM EMPREGADOS E, TRABALHA T WHERE E.cpf = T.cpf_emp
GROUP BY E.cpf; SELECT COUNT(*) FROM Emp_NumPro EN WHERE EN.numPro > 1;
14. Para cada projeto obtenha o número de empregados que trabalha no projeto e o total de horas alocadas para o projeto.
SELECT T.pnumero, COUNT(T.cpf_emp), SUM(T.num_horas) FROM TRABALHA T
15. Para cada projeto que tem mais de 5 empregados alocados, obtenha a média de horas que os empregados trabalham no projeto.
SELECT T.pnumero, COUNT(T.cpf_emp), AVG(T.num_horas) FROM TRABALHA T
GROUP BY T.pnumero
HAVING COUNT(T.cpf_emp) > 5;
16. Obtenha os projetos que tem mais empregados do que a média dos projetos e total de horas alocadas maior do que a média dos projetos.
SELECT T.pnumero, COUNT(T.cpf_emp), SUM(T.num_horas) FROM TRABALHA T
GROUP BY T.pnumero
HAVING COUNT(T.cpf_emp) > (SELECT COUNT(*) / COUNT(DISTINCT T1.pnumero) FROM TRABALHA T1)
AND SUM(T.num_horas) > (SELECT AVG(T2.num_horas) FROM TRABALHA T2);
17. Obtenha o nome e salário dos empregados que ganham um salário superior ao salário médio do respectivo departamento
CREATE VIEW MEDIA_SAL_DEP(dnumero, media_sal) AS SELECT E.dnumero, AVG(E.salario)
FROM EMPREGADOS E GROUP BY E.dnumero; SELECT E.enome, E.salario
FROM EMPREGADOS E, MEDIA_SAL_DEP M WHERE E.dnumero = M.dnumero
18. Obtenha o nome e salário dos empregados cujo salário médio do seu departamento é superior a media de salários do departamento 1.
SELECT E.enome, E.salario
FROM EMPREGADOS E, MEDIA_SAL_DEP M WHERE E.dnumero = M.dnumero
AND M.media_sal > (SELECT M.media_sal FROM MEDIA_SAL_DEP M WHERE M.dnumero = 1);
--Obs: Nessa questão usamos a View criada na questão anterior (17).
19. Obtenha o Cargo e media de salário dos cargos com salário médio mais elevados. Solução A – Mostra os 3 cargos com maiores médias de salário:
SELECT E.cargo, AVG(E.salario) FROM EMPREGADOS E GROUP BY E.cargo
ORDER BY AVG(E.salario) DESC LIMIT 3;
Solução B – Mostra somente o cargo com maior média de salário: SELECT E.cargo, AVG(E.salario)
FROM EMPREGADOS E GROUP BY E.cargo
HAVING AVG(E.salario) >= ALL (SELECT AVG(E1.salario) FROM EMPREGADOS E1 GROUP BY E1.cargo);
