BD II Lista de exercícios 1

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. 
 
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”. 
 
6. Obtenha a descrição da tabela FUNCIONARIO. 
 
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. 
 
8. Deseja-se uma lista (matrícula, nome e departamento) de todos os funcionários que estejam 
alocados aos departamentos A00, B01 ou C01. 
 
9. Inclua todos os funcionários do departamento D11 no departamento D01, acrescentando ao 
número da matrícula o valor 340. 
 
10. Aumente o salário de todos os funcionários em 10%. 
 
11. Deseja-se excluir todos os departamentos que não possuam funcionários alocados. 
 
12. Produza uma lista contendo o cargo, a média salarial e o número de funcionários grupados 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. 
 
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. 
 
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. 
 
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. 
 
16. Deseja-se saber quais os departamentos que não possuem funcionários (usar Minus). 
 
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). 
 
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. 
 
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). 
 
20. Determine a quantidade de funcionários por departamento separados por 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)