respostas_sql

Prévia do material em texto

Para os exercícios abaixo, utilize o script EMPRESA.SQL disponível no portal da disciplina para 
criar o esquema e popular o banco de dados EMPRESA apresentado em aula. 
1. Elabore e execute consultas SQL para obter os seguintes dados:
(a) todos os dados dos empregados trabalham no departamento com o nome 'Pesquisa' 
(b) nome completo e CPF dos empregados que são gerentes de departamento 
(c) nome completo e CPF dos empregados que trabalham em algum projeto
(d) nome completo e CPF dos empregados supervisores e gerentes de departamento
(e) nome completo e CPF dos empregados supervisores ou gerentes de departamento 
(f) nome completo e CPF dos empregados supervisores e não gerentes de departamento 
(g) nome completo, CPF e salário dos empregados do sexo feminino são gerentes de departamento 
(h) nome completo, CPF e data de nascimento dos empregados do sexo feminino que são 
supervisores de outros funcionários 
(i) nome completo e CPF dos empregados que possuem supervisores mais jovens
(j) nome completo e CPF dos empregados que possuem dependentes mais velhos 
2. Elabore e execute consultas SQL equivalentes às seguintes consultas em álgebra relacional:
(a) π Pnome, Unome (FUNCIONARIO) 
(b) Sexo=”F” (FUNCIONARIO) 𝜎
(c) π Pnome, Unome, Salario ( Sexo=”F” (FUNCIONARIO) )𝜎
(d) TRABALHA_EM x PROJETO
(e) π Dnome, Pnome, Unome ( FUNCIONARIO Cpf=Cpf_gerente DEPARTAMENTO ) ⨝
(f) Dnr CONTAGEM Cpf , MÉDIA Salario, MÁXIMO Salario, MÍNIMO Salario 
(FUNCIONARIO) 
(g) π Cpf_supervisor (FUNCIONARIO) π Cpf_gerente (DEPARTAMENTO) 
3. Elabore comandos SQL para realizar as seguintes alterações no banco de dados:
(a) Cadastrar um novo funcionário com os seguintes dados. Nome: Joaquim Alves, Cpf: 
82922312321, Datanasc: 12/01/1980, Salario: 40000, Departamento: Administração 
(b) Alterar o gerente do departamento de pesquisa para o funcionário João Silva. 
(c) Atualizar o supervisor do funcionário João Silva para Jorge Brito
(d) Adicionar R$ 1000 ao salário dos funcionários do sexo feminino
(e) Excluir o funcionário Ronaldo Lima 
(f) Aumentar em 5% os salários dos funcionários que possuem o menor salário em cada 
departamento 
4. Elabore e execute consultas SQL para obter os seguintes dados:
(a) dados do(s) departamento(s) (número, nome do departamento, CPF e nome completo do 
gerente) que possuem a folha de pagamento (soma dos salários dos funcionários) mais elevada 
(b) dados dos funcionários que possuem mais de um filho/filha 
(c) dados dos funcionários que não possuem dependentes 
(d) dados dos projetos que estão localizados em localidades diferentes das do departamento do 
projeto
(e) dados dos funcionários que possuem dependentes maiores de idade 
(f) dados do(s) projeto(s) com o maior número de horas de trabalho alocadas 
(g) dados dos funcionários do departamento ‘Pesquisa’ ordenados por idade 
(h) dados do(s) funcionário(s) mais idosos de cada sexo 
(i) quantitativo de funcionários agrupado pelas seguintes faixas etárias: 18- 35, 36-60, acima de 60 
(j) lista dos departamentos da empresa e para cada departamento a soma dos salários dos 
funcionários e o custo adicional de um aumento de 10% no salário dos funcionários do sexo 
feminino.
(k) lista dos supervisores cujo salário é mais de 50% maior que algum supervisionado 
(l) lista dos departamentos junto com o nome e CPF do funcionário com maior salário
(m) lista dos projetos com o nome e CPF do(s) funcionário(s) que trabalha(m) mais horas no projeto
(n) lista de cada departamento cujo salário médio dos funcionários seja maior do que 30000, 
incluindo o nome do departamento e o número de funcionários
5. Descreva qual o resultado da consulta:
(a) SELECT Dnumero, Dnome FROM DEPARTAMENTO WHERE Dnumero IN (SELECT 
Dnumero FROM LOCALIZACAO_DEP WHERE Dlocal = 'São Paulo') 
(b) SELECT Dnome, AVG(Salario) FROM DEPARTAMENTO, FUNCIONARIO WHERE Dnr = 
Dnumero GROUP BY Dnumero, Dnome 
(c)SELECT DISTINCT Dnome FROM DEPARTAMENTO AS DF JOIN FUNCIONARIO AS F 
ON DF.Dnumero = F.Dnr WHERE F.Salario > (SELECT Salario FROM FUNCIONARIO AS GER
WHERE GER.Cpf = DF.Cpf_gerente) 
(d) SELECT Dnome, EXTRACT(YEAR FROM AVG(AGE(CURRENT_DATE, Datanasc))) 
FROM FUNCIONARIO, DEPARTAMENTO WHERE Dnr = Dnumero GROUP BY Dnome
(e) SELECT Nome_dependente, EXTRACT(YEAR FROM AGE(CURRENT_DATE, Datanasc)) 
AS Idade FROM DEPENDENTE WHERE Fcpf IN (SELECT Cpf FROM FUNCIONARIO 
WHERE Dnr IN (SELECT Dnumero FROM DEPARTAMENTO WHERE Dnome = 'Pesquisa')) 
(f) SELECT Dnome, AVG(F.Salario), FM.Cpf, FM.Pnome, FM.Unome, FM.Salario FROM 
DEPARTAMENTO AS D, FUNCIONARIO AS F, FUNCIONARIO AS FM WHERE F.Dnr = 
D.Dnumero AND FM.Dnr = D.Dnumero AND FM.Salario = (SELECT MAX(FI.Salario) FROM 
FUNCIONARIO AS FI WHERE FI.Dnr = D.Dnumero) GROUP BY Dnumero, Dnome, FM.Cpf, 
FM.Pnome, FM.Unome, FM.Salario 6. Reescreva as seguintes consultas SQL dos itens (a) ao (e) do
exercício 5 sem fazer uso de subconsultas: 
RESPOSTAS:
1.a.
SELECT * FROM FUNCIONARIO WHERE Dnr = ( SELECT Dnumero FROM 
DEPARTAMENTO WHERE Dnome='Pesquisa' );
b.
SELECT Pnome, Minicial, Unome, Cpf FROM FUNCIONARIO WHERE Cpf IN ( SELECT 
Cpf_gerente FROM DEPARTAMENTO );
c.
SELECT DISTINCT Pnome, Minicial, Unome, Cpf FROM FUNCIONARIO, 
TRABALHA_EM WHERE Fcpf = Cpf;
d.
SELECT DISTINCT SUPER.Pnome, SUPER.Minicial, SUPER.Unome, SUPER.Cpf
FROM FUNCIONARIO AS FUNC, FUNCIONARIO AS SUPER, DEPARTAMENTO
WHERE FUNC.Cpf_supervisor = SUPER.Cpf AND Cpf_gerente = SUPER.cpf;
e.
SELECT DISTINCT SUPER.Pnome, SUPER.Minicial, SUPER.Unome, SUPER.Cpf
FROM FUNCIONARIO AS FUNC, FUNCIONARIO AS SUPER, DEPARTAMENTO
WHERE FUNC.Cpf_supervisor = SUPER.Cpf OR Cpf_gerente = SUPER.cpf;
f.
SELECT DISTINCT SUPER.Pnome, SUPER.Minicial, SUPER.Unome, SUPER.Cpf
FROM FUNCIONARIO AS FUNC, FUNCIONARIO AS SUPER
WHERE FUNC.Cpf_supervisor = SUPER.Cpf AND NOT SUPER.cpf IN (SELECT 
Cpf_gerente FROM DEPARTAMENTO);
g.
SELECT DISTINCT Pnome, Minicial, Unome, Cpf
FROM FUNCIONARIO, DEPARTAMENTO
WHERE Cpf_gerente = cpf AND Sexo = 'F';
h.
SELECT DISTINCT SUPER.Pnome, SUPER.Minicial, SUPER.Unome, SUPER.Cpf, 
SUPER.Datanasc
FROM FUNCIONARIO AS FUNC, FUNCIONARIO AS SUPER
WHERE FUNC.Cpf_supervisor = SUPER.Cpf AND SUPER.Sexo = 'F';
i.
SELECT DISTINCT FUNC.Pnome, FUNC.Minicial, FUNC.Unome, FUNC.Cpf
FROM FUNCIONARIO AS FUNC, FUNCIONARIO AS SUPER
WHERE FUNC.Cpf_supervisor = SUPER.Cpf AND SUPER.Datanasc<FUNC.Datanasc;
j.
SELECT DISTINCT Pnome, Minicial, Unome, Cpf
FROM FUNCIONARIO, DEPENDENTE
WHERE Fcpf=cpf AND FUNCIONARIO.Datanasc < DEPENDENTE.Datanasc;
2.a.
SELECT Pnome, Unome FROM FUNCIONARIO;
b.
SELECT * FROM FUNCIONARIO WHERE Sexo='F';
c.
SELECT Pnome, Unome, Salario FROM FUNCIONARIO WHERE Sexo='F';
d.
SELECT * FROM PROJETO, TRABALHA_EM;
e.
SELECT Dnome, Pnome, Unome FROM FUNCIONARIO, DEPARTAMENTO WHERE Cpf
= Cpf_gerente;
f.
SELECT Dnr, COUNT(Cpf), AVG(Salario), MAX(Salario), MIN(Salario) FROM 
FUNCIONARIO GROUP BY Dnr;
g.
SELECT Cpf_supervisor AS cpf FROM FUNCIONARIO INTERSECT SELECT 
Cpf_gerente AS cpf FROM DEPARTAMENTO;
3. a.
INSERT INTO FUNCIONARIO (Pnome, Unome, Cpf, Datanasc, Salario, Dnr)
VALUES ('Joaquim', 'Alves', '82922312321', '12/01/1980', '40000', '4');
b.
UPDATE DEPARTAMENTO
SET Cpf_gerente = '12345678966', Data_inicio_gerente = '2020-08-10'
WHERE Dnumero = 5;
c.
UPDATE FUNCIONARIO
SET Cpf_supervisor = '88866555576'
WHERE cpf = '12345678966';
d.
UPDATE FUNCIONARIO
SET Salario = Salario + 1000
WHERE Sexo='F';
e.
DELETE FROM FUNCIONARIO
WHERE Cpf = '66688444476';
f.
UPDATE FUNCIONARIO
SET Salario = Salario * 1.05
WHERE Salario IN ( SELECT MIN(Salario) 
FROM DEPARTAMENTO, FUNCIONARIO 
WHERE Dnr = Dnumero GROUP BY Dnr );
4. a.
SELECT Dnumero, Dnome, Cpf_gerente
FROM DEPARTAMENTO, FUNCIONARIO
WHERE Dnr = Dnumero
GROUP BY Dnumero
ORDER BY SUM(Salario) DESC
limit 1;
b.
SELECT * FROM FUNCIONARIO
WHERE Cpf IN (
SELECT Fcpf FROM DEPENDENTE 
WHERE (Parentesco = 'Filho' OR Parentesco = 'Filha')
GROUP BY Fcpf
HAVING COUNT(Fcpf) > 1);
c.
SELECT * FROM FUNCIONARIO
WHERE Cpf NOT IN (
SELECT Fcpf FROM DEPENDENTE );
d.
SELECT * FROM PROJETO AS Pro
WHERE Projlocal NOT IN (SELECT Dlocal FROM LOCALIZACAO_DEP AS LO
WHERE Pro.Dnum = LO.Dnumero);
e.
SELECT * FROM FUNCIONARIO
WHERE Cpf IN (
SELECT Fcpf FROM DEPENDENTE 
WHERE extract(year from age(CURRENT_DATE, Datanasc))>=18);
f.
SELECT * FROM PROJETO
WHERE Projnumero IN (
SELECT Pnr FROM TRABALHA_EM
GROUP BY Pnr
HAVING SUM(Horas) >= ALL(SELECT SUM(Horas) FROM TRABALHA_EM 
GROUP BY Pnr));
g.
SELECT * FROM FUNCIONARIO
WHERE Dnr = '5'
ORDER BY Datanasc;
h.
SELECT * FROM FUNCIONARIO
WHERE Datanasc in (
SELECT MAX(Datanasc) FROM FUNCIONARIO
WHERE Sexo IS NOT NULL
GROUP BY Sexo);
5-
a.
Listar o nome e número de cada departamento localizado em São Paulo.
b.
Listar o nome e a média salarial dos funcionários de cada departamento.
c.
Listar o nome de cada departamento que possua algum funcionário com salário maior que
o salário do gerente.
d.
Listar o nome e média das idades dos funcionários de cada departamento. 
e.
Listar o nome e a idade dos dependentes dos funcionários que trabalham no 
departamento ‘pesquisa’. 
f.
Listar o nome, média salarial, nome, cpf e salário do funcionário que possui o maior 
salário de cada departamento. 
6-
a.
SELECT D.Dnumero, Dnome FROM DEPARTAMENTO AS D, LOCALIZACAO_DEP AS L
WHERE D.Dnumero = L.Dnumero AND Dlocal = 'São Paulo';
b.
SELECT Dnome, AVG(Salario) FROM DEPARTAMENTO, FUNCIONARIO
WHERE Dnr = Dnumero GROUP BY Dnumero, Dnome
c.
SELECT DISTINCT Dnome FROM DEPARTAMENTO AS DF JOIN FUNCIONARIO AS F 
ON DF.Dnumero = F.Dnr JOIN FUNCIONARIO AS GER ON GER.Cpf = DF.Cpf_gerente 
WHERE F.Salario > GER.Salario;
d.
SELECT Dnome, EXTRACT(YEAR FROM AVG(AGE(CURRENT_DATE, Datanasc)))
FROM FUNCIONARIO, DEPARTAMENTO
WHERE Dnr = Dnumero GROUP BY Dnome
e.
SELECT Nome_dependente, EXTRACT(YEAR FROM AGE(CURRENT_DATE, 
D.Datanasc)) AS Idade
FROM DEPENDENTE AS D, FUNCIONARIO AS F, DEPARTAMENTO AS E
WHERE D.Fcpf = F.Cpf AND Dnr = Dnumero AND Dnome = 'Pesquisa';
f.
SELECT Dnome, AVG(F.Salario), FM.Cpf, FM.Pnome, FM.Unome, FM.Salario
FROM DEPARTAMENTO AS D, FUNCIONARIO AS F, FUNCIONARIO AS FM, 
FUNCIONARIO AS FI
WHERE F.Dnr = D.Dnumero AND FM.Dnr = D.Dnumero AND FI.Dnr = D.Dnumero
GROUP BY Dnumero, Dnome, FM.Cpf, FM.Pnome, FM.Unome, FM.Salario
HAVING FM.Salario = MAX(FI.Salario);