Avaliação parcial Banco de Dados I - 2019.2 - 1.2

Prévia do material em texto

Arquitetura de Computadores 
 
Prof. Paulo Alexandre Serra Coucello da Fonseca 
 
 
BD-I-2019-2-1 Página : 1 
 
Avaliação parcial do 2º. bimestre 
(2019-2) 
 
Desenvolver os “scripts” de criação das tabelas (com 
Primary Key e Foreign Key), de inserção de dados nas 
tabelas (no mínimo 15 linhas por tabela) e os respectivos 
comandos de consulta dos dados (SQL´s). 
 
Avaliação será efetuada da seguinte forma: 
 
1) Enviar no email: paulo.fonseca@uniron.edu.br, o(s) 
arquivo(s) dos comandos (Criação das Tabelas, Inserts e 
Selects) conforme detalhamento das atividades abaixo. 
2) Será efetuada a execução/validação dos scripts em banco 
de dados MySQL, ou seja, será testado o comando no banco 
de dados (necessidade dos scripts estarem corretos). 
3) Não serão aceitos envios com data posterior à data acima. 
 
Qualquer dúvida, reportar no mesmo e-mail. 
 
 
 
Arquitetura de Computadores 
 
Prof. Paulo Alexandre Serra Coucello da Fonseca 
 
 
BD-I-2019-2-1 Página : 2 
 
Atividade 1 
 
1) Criar as seguintes tabelas de acordo com a estrutura abaixo: 
 
 EMPR (Tabela Empregados) 
 DEPT (Tabela Departamentos) 
 PROJETO (Tabela Projetos) 
 
Seguem as descrições das tabelas: 
a) EMPR  Tabela de Empregados 
 
Coluna Tipo Tam Nulo Descrição 
MATR CHAR 6 não Matricula única do empregado 
NOME VARCHAR 12 não Primeiro nome 
SOBRENOME VARCHAR 15 não Sobrenome 
DEPT CHAR 3 Código de departamento do empregado 
FONE CHAR 14 Numero de telefone 
DINADIM DATE Data de admissão 
CARGO CHAR 10 Cargo do empregado 
NIVELED NUMBER 3 Educação formal em anos 
SEXO CHAR 1 M = masc F= fem. 
DATANAS DATE Data de nascimento. 
SALARIO NUMBER 9,2 Salário Anual 
BONUS NUMBER 9,2 Bônus Anual 
COMIS NUMBER 9,2 Comissão Anual 
 
b) DEPT  Tabela de Departamentos 
 
Coluna Tipo Tam Nulo Descrição 
DCODIGO CHAR 3 não Código único do departamento 
DNOME VARCHAR 36 não Nome do departamento 
GERENTE CHAR 6 Matrícula do Gerente 
DSUPER CHAR 3 Depto a qual está subordinado 
 
 
 
 
 
Arquitetura de Computadores 
 
Prof. Paulo Alexandre Serra Coucello da Fonseca 
 
 
BD-I-2019-2-1 Página : 3 
 
c) PROJETO  Tabela de Projetos 
 
Coluna Tipo Tam Nulo Descrição 
PCODIGO CHAR 6 não Código único do Projeto 
PNOME VARCHAR 24 não Nome do projeto 
DCODIGO CHAR 3 não Código do Departamento 
RESP CHAR 6 não Matrícula do Responsável 
EQUIPE NUMBER 5 Número de Empregados no Projeto 
DATAINI DATE Data de início 
DATAFIM DATE Data do final 
PSUPER CHAR 6 Projeto Principal (se subprojeto) 
 
2) Criar os comandos para fazer a inserção de dados nas tabelas em questão (mínimo 
de 15 registros por tabela) 
 
3) Criar os SELECT necessários para responder às questões abaixo: 
 
1. Mostre o sobrenome, primeiro nome, departamentos, data de nascimento, data de 
admissão e salário de todos os empregados que ganham mais que $30.000 por ano 
(considerar como filtro o prazo de um ano, por exemplo 01/01/2017 a 31/12/2017) 
2. Liste todas as informações de qualquer departamento cujo gerente seja 
desconhecido. 
3. Mostre o sobrenome, primeiro nome, departamento, data de nascimento, data de 
admissão e salário de todos os empregados que ganhem menos de $20.000 por ano. 
Classifique o resultado pelo sobrenome e primeiro nome. 
4. Liste tudo sobre os departamentos subordinados ao departamento A00. 
5. Liste o código e o nome dos departamentos que apresentam “SERVIÇO” compondo 
seu nome. 
6. Mostre a matricula, sobrenome, departamento e telefone dos empregados cujo código 
de departamento estejam compreendidos entre D11 e D21 (inclusive). 
7. Produza uma lista dos empregados nos departamentos B0l, C0l e D01, mostrando o 
sobrenome, departamento e rendimento (salário + comissão) Liste a saída em ordem 
descendente de rendimento dentro de cada departamento. 
8. Mostre o sobrenome, salário anual e departamento dos empregados com salário 
mensal maior que $3000. Classifique a lista pelo sobrenome. 
 
 
Arquitetura de Computadores 
 
Prof. Paulo Alexandre Serra Coucello da Fonseca 
 
 
BD-I-2019-2-1 Página : 4 
 
9. Produza uma lista de todos os empregados cujo departamento somente com “E”. 
Mostre a matricula, primeiro nome e sobrenome. Classifique pelo sobrenome. 
10. Produza uma lista dos homens cujo salário mensal seja menor do que $1600. Mostre 
a matricula, sobrenome e salário mensal. Classifique cm ordem descendente de salário. 
11. Para cada representante de vendas (REPVENDA), apresente a comissão em 
porcentagem do total de rendimento (salário, bônus e comissão). Liste nome e 
porcentagem. 
12. Mostre todas as informações referentes aos departamentos “E01” e departamentos 
subordinados ao departamento “E01”. 
13. Liste o sobrenome, salário, função e nível de educação de qualquer empregado 
que se enquadre numa das seguintes condições: 
• Salário maior que $40.000; 
• Função gerente com nível menor que 16. 
14. Mostre a soma dos salários de toda a companhia, salário médio, salário mínimo e 
o salário máximo. 
15. Qual é o primeiro sobrenome em ordem alfabética? 
16. Quantos departamentos possuem empregados? 
17. Mostre o salário médio para cada função. 
18. Mostre o sobrenome e função dos empregados dos departamentos cujo nome inclua 
“PLAN”. 
 
 
 
 
Arquitetura de Computadores 
 
Prof. Paulo Alexandre Serra Coucello da Fonseca 
 
 
BD-I-2019-2-1 Página : 5 
 
Atividade 2 
 
1) Criar as seguintes tabelas de acordo com a estrutura abaixo: 
 
 CENCUSTO (Centros de Custo) 
 CLIENTES (Tabela de Clientes) 
 RECEBER (Tabela de Contas a Receber) 
 
Seguem as descrições das tabelas: 
a) CENCUSTO  Tabela de Centros de Custo 
 
Coluna Tipo Tam Nulo Descrição 
pCOD_CUSTO INT 3 Não Código único do centro de custo 
DESCRICAO VARCHAR 30 Não Descrição do centro de custo 
CNPJ CHAR 18 Não CNPJ do Centro de Custo 
RESPONSAV CHAR 18 Responsável pelo centro de custo 
 
b) CLIENTES  Tabela de Clientes 
 
Coluna Tipo Tam Nulo Descrição 
pCOD_CLI INT 7 não Matricula única do Cliente 
NOME VARCHAR 20 não Primeiro nome 
SOBRENOME VARCHAR 20 não Sobrenome 
LOGRADOURO VARCHAR 20 não Endereço do cliente 
NUM_LOGR CHAR 6 não Número do endereço do cliente 
BAIRRO VARCHAR 20 não Bairro do endereço 
CIDADE VARCHAR 20 não Cidade do endereço 
CEP CHAR 9 não CEP do endereço 
UF CHAR 2 não UF do endereço 
FONE_DDD INT 3 não DDD do telefone do cliente 
FONE_NUM CHAR 9 não Telefone do cliente 
SEXO CHAR 1 M = masc F= fem. 
DATA_NASC DATE Data de nascimento 
EMAIL VARCHAR 30 E-mail do cliente 
ATIVO BOOLEAN Se o cliente está ativo ou não. 
 
 
 
Arquitetura de Computadores 
 
Prof. Paulo Alexandre Serra Coucello da Fonseca 
 
 
BD-I-2019-2-1 Página : 6 
 
c) VENDAS  Tabela de Vendas 
 
Coluna Tipo Tam Nulo Descrição 
pCOD_VENDA INT 7 não Código único da venda 
COD_CLI INT 7 não Código do Cliente 
COD_CUSTO INT 3 não Código do Centro de Custo 
DATA_VENDA DATE não Data da venda 
DATA_VCTO DATE não Data do vencimento do pagamento 
DATA_PAG DATE Data do pagamento da venda 
VALOR_VENDA NUMBER 9,2 não Valor total da venda 
VALOR_DESC NUMBER 9,2 Valor do desconto 
VALOR_LIQUID NUMBER 9,2 Valor líquido da venda 
FORMAPAG INT 2 Não Forma de pagamento: 
0 – Dinheiro 
1 – Cartão Débito 
2 – Cartão de Crédito 
 
2) Criar os comandos para fazer a inserção de dados nas tabelas em questão (mínimo 
de 15 registros por tabela) 
 
3) Criar os SELECT necessários para responder às questões abaixo: 
 
1. Mostre o sobrenome, primeironome, data de nascimento de todos os clientes que 
compram menos que $10.000 por ano (considerar como filtro o prazo de um ano, por 
exemplo 01/01/2018 a 31/12/2018) 
2. Liste todas as informações de qualquer centro de custo cujo responsável seja 
desconhecido. 
3. Mostre a data de nascimento, o sobrenome, primeiro nome e valor líquido de venda 
de todos os clientes que compram mais de $20.000 por ano. Classifique o resultado pela 
data de nascimento, sobrenome e primeiro nome. 
4. Liste todas as vendas efetuadas em dinheiro. 
5. Liste todas as vendas do cliente com código igual a 7, efetuadas no cartão de débito. 
6. Liste todos os clientes cujo sobrenome comece com a letra “A”. 
7. Produza uma lista dos clientes da cidade de Porto Velho e do bairro CENTRO, 
mostrando o nome e o sobrenome. Liste a saída em ordem do nome. 
 
 
Arquitetura de Computadores 
 
Prof. Paulo Alexandre Serra Coucello da Fonseca 
 
 
BD-I-2019-2-1 Página : 7 
 
8. Mostre a cidade, o nome e o sobrenome de todos os clientes do estado do Acre (AC), 
ordenados pela cidade e pelo nome. 
9. Produza uma lista do nome e telefone de todos os clientes que fizeram compras à 
vista (DATA_VCTO = DATA_VENDA). 
10. Produza uma lista das vendas que estejam vencidas na data 31/10/2019, indicando 
o nome do cliente e quantos dias está atrasada. 
11. Qual é o primeiro sobrenome em ordem alfabética? 
12. Quantos centros de custo possuem vendas? 
13. Mostre a venda média anual por centro de custo. 
14. Relacione todas as vendas pagas em que foi usado cartão de crédito ou cartão de 
débito. 
15. Relacione o nome do cliente e as vendas dele que foram feitas com desconto. 
16. Liste as vendas feitas para pessoas do sexo masculino. 
17. Liste as vendas feitas para pessoas do sexo feminino, em cartão de crédito e com 
valor superior a 1000,00 (mil reais). 
18. Relacione as vendas feitas pelos centros de custo 01 ou 02 cuja forma de pagamento 
foi feita em dinheiro ou cartão de débito.