BD II Lista de exercícios 1 corrigida

Prévia do material em texto

1º lista de exercícios 
Professor: Carlos José Rocha Silva 
Disciplina: Banco de Dados II 
Aula 03: Linguagem de Banco de Dados SQL 
 
Aluno: _________________________________________ Período: _______ 
 
Para melhor compreensão e fixação do conteúdo apresentado nas aulas 01 e 02 e com base no 
seu conhecimento adquirido dos seus estudos. Na situação hipotética (minimundo) apresentada 
abaixo crie o modelo de relacional (DER) e físico do exercício proposto na aula 02 de BD II. 
Sistema de controle de projetos: Você foi contratado para informatizar o controle de projetos de 
uma empresa de médio porte, sua atividade fim é prestação de serviço através de locação de mão 
de obra. Esta empresa fornece serviços engenharia civil, desenvolvimento de software, 
manutenção industrial entre outras. Para isso ela precisa controlar os FUNCIONÁRIOS, em quais 
DEPARTAMENTOS e PROJETOS eles estão alocados. E para cada PROJETO quais são as suas 
ATIVIDADES. Do FUNCIONÁRIO deseja-se saber a Matricula, o Nome, o Sobrenome, 
Departamento, Ramal, Data de Admissão, Número do Cargo, Registro Geral (RG), Sexo Data de 
Nascimento, Salário, Nome da foto. Do PROJETO Código, Nome, Departamento, Responsável, 
Quantidade de equipamentos, Data de início e de fim do projeto. Do DEPARTAMENTO o código, 
o nome, o gerente e se este departamento está sob o controle de outro. Das ATIVIDADES código, 
descrição e sigla. 
 
1. Crie o modelo relacional(DER). 
 
2. Crie um novo Usuário chamado DESENV; 
 
3. Baseado no modelo proposto acima resolva as seguintes questões: 
 
4. Deseja-se uma lista que contenha número da matrícula, primeiro e último nome e cargo de todos 
os funcionários do sexo feminino que tenham cargo superior a 55. 
SELECT CD_MAT, NM_FUNC, NM_SOBRENOME, NR_CARGO 
 FROM FUNCIONARIO 
 WHERE IN_SEXO = 'F' 
 AND NR_CARGO > 55; 
 
5. Deseja-se uma lista que contenha número de matrícula, nome e sobrenome do funcionário em 
cujo sobrenome não seja encontrada a letra “E”. 
SELECT CD_MAT, NM_FUNC, NM_SOBRENOME 
 FROM FUNCIONARIO 
 WHERE NM_SOBRENOME NOT LIKE '%E%'; 
 
6. Obtenha a descrição da tabela FUNCIONARIO. 
describe FUNCIONARIO 
 
A descrição apresenta o nome da coluna no banco de dados (Name), seu tipo e tamanho (Type) e 
a indicação de obrigatoriedade de preenchimento (Null). Sabemos, agora, que quando uma coluna 
não é preenchida, não ocupa espaço na linha que é gravada no banco de dados; por isso, diz que 
seu conteúdo é NULL (desconhecido, ausente). Na tabela FUNCIONARIO, apenas a coluna 
Matrícula deve ser preenchida obrigatoriamente; as demais colunas podem ser informadas ou não 
a tempo de cadastramento. 
 
7. Deseja-se uma lista contendo os códigos de departamento para os departamentos que possuam 
funcionários. Na listagem-resposta, não deve haver repetição de código de departamento. 
SELECT DISTINCT CD_DEPTO 
 FROM FUNCIONARIO 
 WHERE CD_DEPTO IS NOT NULL 
 
No exercício, são selecionados apenas os funcionários que estão alocados a algum departamento, 
ou seja, cujo código do departamento está preenchido. 
 
8. Deseja-se uma lista (matrícula, nome e departamento) de todos os funcionários que estejam 
alocados aos departamentos A00, B01 ou C01. 
SELECT CD_MAT, NM_FUNC, CD_DEPTO 
FROM FUNCIONARIO 
WHERE CD_DEPTO IN ('A00', 'B01', 'C01'); 
 
Faça outros testes usando BETWEEN, substituindo IN por OR e tentando realizar os 
exercícios acima com variáveis de substituição. 
 
9. Inclua todos os funcionários do departamento D11 no departamento D01, acrescentando ao 
número da matrícula o valor 340. 
 
INSERT INTO FUNCIONARIO 
SELECT CD_MAT + 340, NM_FUNC, NM_SOBRENOME, 'D01', NR_RAMAL, DT_ADM, NR_CARGO, 
 NR_RG, IN_SEXO, DT_NASC, VL_SAL, NM_FOTO 
 FROM FUNCIONARIO 
 WHERE CD_DEPTO = 'D11'; 
 
10. Aumente o salário de todos os funcionários em 10%. 
UPDATE FUNCIONARIO SET VL_SAL = VL_SAL * 1.1; 
 
11. Deseja-se excluir todos os departamentos que não possuam funcionários alocados. 
DELETE FROM DEPTO 
 WHERE CD_DEPTO NOT IN (SELECT DISTINCT CD_DEPTO 
 FROM FUNCIONARIO WHERE CD_DEPTO IS NOT NULL); 
 
12. Produza uma lista contendo o cargo, a média salarial e o número de funcionários agrupados por 
cargo para os departamentos D01, D11, D21 e E11. Todos os cargos com menos de três 
funcionários devem ser excluídos do resultado. A lista deve vir ordenada descendentemente por 
média salarial. 
SELECT NR_CARGO, AVG(VL_SAL), COUNT(*) 
 FROM FUNCIONARIO 
WHERE CD_DEPTO IN ('D01', 'D11', 'D21', 'E11') 
GROUP BY NR_CARGO 
HAVING COUNT(*) < 3 
ORDER BY 2 DESC; 
 
13. Deseja-se uma lista contendo o nome do projeto, o nome das atividades e tempo de duração 
de cada atividade (fornecido em número de horas). Ordene o resultado por projeto e atividade. 
Listagem-resposta 2.54A 
SELECT PROJ.NM_PROJ “Projeto”, ATIV.TX_DESCRICAO “Atividade”, 
 TRUNC((PRJATV.DT_FIM - PRJATV.DT_INI) * 24) Horas 
 FROM PROJ, ATIV, PRJATV 
WHERE PRJATV.CD_PROJ = PROJ.CD_PROJ 
AND PRJATV.CD_ATIV = ATIV.CD_ATIV 
ORDER BY PROJ.NM_PROJ, ATIV.TX_DESCRICAO; 
 
 
Listagem-resposta 2.54B 
SELECT PROJ.NM_PROJ “Projeto”, ATIV.TX_DESCRICAO “Atividade”, 
 TRUNC((PRJATV.DT_FIM - PRJATV.DT_INI) * 24) Horas 
 FROM PROJETO_ATIVIDADE PRJATV 
 JOIN 
 ATIVIDADE ATIV ON (ATIV.CD_ATIV = PRJATV.CD_ATIV) 
 JOIN 
 PROJETO PROJ ON(PROJ.CD_PROJ = PRJATV.CD_PROJ) 
ORDER BY PROJ.NM_PROJ, ATIV.TX_DESCRICAO; 
 
A subtração de datas fornece o resultado em número de dias (parte inteira). Multiplicamos por 24 para 
transformar dias para horas e truncamos o resultado para que não fossem apresentados minutos e 
segundos. Observe que neste segundo resultado não usamos a cláusula Where pois não havia qualquer 
restrição específica, apenas as cláusulas relativas ao Join. 
 
14. Deseja-se uma lista contendo o código e nome de cada departamento, nome e sobrenome do 
gerente, ordenado por código de departamento. 
Resposta 255A 
SELECT D.CD_DEPTO, D.NM_DEPTO, F.NM_FUNC, F.NM_SOBRENOME 
 FROM DEPTO D, FUNCIONARIO F 
WHERE D.CD_GERENTE = F.CD_MAT 
 ORDER BY 1; 
 
Resposta B 
SELECT D.CD_DEPTO, D.NM_DEPTO, F.NM_FUNC, F.NM_SOBRENOME 
 FROM DEPTO D 
 JOIN 
 FUNCIONARIO F ON(D.CD_GERENTE = F.CD_MAT) 
 ORDER BY 1; 
 
15. Deseja-se uma lista contendo o nome do projeto, o nome do departamento responsável e o 
nome do funcionário responsável, desde que ele trabalhe no departamento responsável pelo 
projeto. 
SELECT P.NM_PROJ Projeto, D.NM_DEPTO Departamento, F.NM_FUNC Responsavel 
 FROM PROJ P, DEPTO D, FUNC F 
WHERE P.CD_DEPTO = D.CD_DEPTO 
 AND P.CD_DEPTO = F.CD_DEPTO 
 AND P.CD_RESP = F.CD_MAT 
UNION 
SELECT P.NM_PROJ, D.NM_DEPTO, 'Outro Depto' 
 FROM PROJ P, DEPTO D, FUNC F 
WHERE P.CD_DEPTO = D.CD_DEPTO 
 AND P.CD_DEPTO <> F.CD_DEPTO 
 AND P.CD_RESP = F.CD_MAT; 
 
Neste exercício, precisamos realizar uma operação de união, pois no primeiro Select estabelecemos uma 
restrição de tal forma que só seriam apresentados os projetos em que o responsável trabalhasse no 
mesmo departamento. Mas foi solicitado que apenas o nome do funcionário fosse omitido. Por esse 
motivo, unimos o resultado do primeiro Select com o segundo, em que foram selecionados os projetos em 
que o responsável não trabalhava no departamento responsável. Para que este exercício tenha conteúdo, 
devemos alterar uma linha na tabela de projeto para criar a condição adequada. 
 
 
16. Deseja-se saber quais os departamentos que não possuem funcionários (usar Minus). 
SELECT CD_DEPTO, NM_DEPTO 
 FROM DEPTO 
MINUS 
SELECT DEPTO.CD_DEPTO, DEPTO.NM_DEPTO 
 FROM DEPTO, FUNC 
WHERE FUNC.CD_DEPTO = DEPTO.CD_DEPTO; 
 
17. Deseja-se saber quais os departamentos (código e nome) responsáveis por mais de dois 
projetos e que, simultaneamente, possuam mais de três funcionários (usar Intersect).SELECT D.CD_DEPTO, D.NM_DEPTO 
 FROM PROJ, DEPTO D 
WHERE PROJ.CD_DEPTO = D.CD_DEPTO 
GROUP BY D.CD_DEPTO, D.NM_DEPTO 
HAVING COUNT(*) > 2 
INTERSECT 
SELECT D.CD_DEPTO, D.NM_DEPTO 
 FROM FUNC, DEPTO D 
WHERE FUNC.CD_DEPTO = D.CD_DEPTO 
GROUP BY D.CD_DEPTO, D.NM_DEPTO 
HAVING COUNT(*) > 3; 
 
18. Produza uma lista contendo nome, sobrenome, cargo e salário de todos os funcionários do sexo 
feminino que ganhem mais que todos os funcionários do sexo masculino. 
SELECT NM_FUNC, NM_SOBRENOME, NR_CARGO, VL_SAL 
 FROM FUNCIONARIO 
WHERE IN_SEXO = 'F' 
 AND VL_SAL > (SELECT MAX(VL_SAL) FROM FUNCIONARIO WHERE IN_SEXO = 'M'); 
 
19. Deseja-se obter a média de idade, maior e menor salário e média salarial dos funcionários do 
sexo feminino, grupando por grau de instrução e cargo (somente dos cargos com mais de duas 
funcionárias). 
SELECT NR_CARGO “CARGO”, NR_GIT “GRAU”, 
 AVG(TRUNC((SYSDATE - DT_ADM)/365.25)) “MÉDIA IDADE”, 
 MAX(VL_SAL) “MAIOR SALÁRIO”, 
 MIN(VL_SAL) “MENOR SALÁRIO”, 
 AVG(VL_SAL) “MÉDIA SALARIAL” 
 FROM FUNCIONARIO 
WHERE IN_SEXO = 'F' 
 AND NR_CARGO IN (SELECT NR_CARGO 
FROM FUNCIONARIO 
WHERE IN_SEXO = 'F' 
GROUP BY NR_CARGO 
HAVING COUNT(*) > 2) 
GROUP BY NR_GIT, NR_CARGO; 
 
20. Determine a quantidade de funcionários por departamento separados por sexo. 
SELECT CD_DEPTO “DEPTO”, IN_SEXO “SEXO”, COUNT(*) “QTD” 
 FROM FUNCIONARIO 
GROUP BY CD_DEPTO, IN_SEXO; 
 
 
Sugestão para nome de campos(atributos) e tabelas(entidades): 
Tabela: 
 DEPTO (Departamento) 
Campos: 
 CD_DEPTO CHAR(3) NOT NULL 
 NM_DEPTO VARCHAR2(40) 
 CD_GERENTE NUMBER(5) 
 CD_DEPTO_CTB CHAR(3) 
 
Tabela: 
 FUNC (Funcionário) 
Campos: 
 CD_MAT NUMBER(5) NOT NULL 
 NM_FUNC VARCHAR2(12) 
 NM_SOBRENOME VARCHAR2(12) 
 CD_DEPTO CHAR(3) 
 NR_RAMAL NUMBER(4) 
 DT_ADM DATE 
 NR_CARGO NUMBER(3) 
 NR_RG NUMBER(15) 
 IN_SEXO VARCHAR2(1) 
 DT_NASC DATE 
 VL_SAL NUMBER(9,2) 
 NM_FOTO VARCHAR2(100) 
 
Tabela: 
 PROJ (Projeto) 
Campos: 
 CD_PROJ CHAR(6) NOT NULL 
 NM_PROJ VARCHAR2(30) NOT NULL 
 CD_DEPTO CHAR(3) NOT NULL 
 CD_RESP NUMBER(5) NOT NULL 
 QT_EQP NUMBER(02) 
 DT_INI DATE 
 DT_FIM DATE 
 
Tabela: 
 ATIV (Atividade) 
Campos: 
 CD_ATIV NUMBER(3) 
 NM_SIGLA VARCHAR2(12) 
 TX_DESCRICAO VARCHAR2(30)