Cap. 7 2007-1

Prévia do material em texto

Faculdade de Ciências Aplicadas e Sociais de Petrolina 
Curso de Ciência da Computação 
Disciplina: Banco de Dados 
Profa. Rossana de Paula Junqueira Almeida 
 
Lista de Exercícios – Capítulo 7 
 
1. Considere a seguinte estrutura de banco de dados: 
 
 
 
Crie uma linha de comando usando cada uma das expressões abaixo: 
 
a) CREATE DATABASE 
b) CREATE TABLE 
c) DROP TABLE 
d) INSERT INTO 
e) DELETE FROM 
f) UPDATE 
g) SELECT FROM e WHERE 
h) SELECT com ORDER BY DESC 
i) SELECT com NOT LIKE 
 
2. Considere a seguinte estrutura de banco de dados: 
 
 
 
Para cada uma das seguintes consultas dê a expressão em SQL: 
 
a) Selecione os títulos e preços da tabela livros. 
b) Selecione os títulos, Autores e a Categoria (nome) dos livros. 
c) Selecione os títulos, Autores e a Categoria (nome) dos livros onde os preços sejam menores que 50.00. 
d) Selecione nome, DDD e fone dos clientes que têm no nome “ana”. 
e) Selecione o código, titulo, autor e preço dos livros que têm preço entre 50.00 e 90.00. 
f) Selecione título, autor e preço dos livros que têm preço igual a 30.00. 
g) Selecione título e data de lançamento dos livros que form lançados após 23/06/2000. 
h) Selecione o título, autor e a Categoria (nome) dos livros que pertencem à categoria Internet. 
i) Selecione nome, endereço, cidade, bairro, nome do estado e cep do cliente cujo código seja igual a 01. 
j) Selecione o valor total dos preços dos livros. 
 
 
3. Considere a seguinte estrutura de banco de dados: 
 
 Empregado(nome_empregado, rua, cidade) 
 Trabalha(nome_empregado, nome_companhia, salário) 
 Companhia(nome_companhia, cidade) 
 
a) Encontre o nome de todos os empregados que trabalham para a “First Bank Corporation” 
b) Encontre todos os nomes das cidades dos empregados que trabalham na “First Bank Corporation” 
c) Encontre os nomes, endereço e cidade da residência de todos os empregados da “First Bank Corporation” que 
ganham mais de dez mil reais por ano. 
d) Encontre os nomes de todos os empregados que moram na mesma cidade da companhia em que trabalham. 
e) Encontre os nomes de todos os empregados que não trabalham para a First Bank Corporation. 
 
4. Considere a seguinte estrutura de banco de dados: 
 
Agência = (nome_agência, cidade_agência, fundos) 
Conta = (nome_agência, número_conta, saldo) 
Depositante = (nome_cliente, número_conta, cidade_cliente) 
 
a) Retorne o nome dos clientes que possuem saldo maior que R$ 1000,00. 
b) Retorne o nome dos clientes que possuem contas em agências com fundos maiores de que R$ 50.000,00 
c) Retorne o nome dos clientes que possuem contas em agências com fundos maiores de que R$ 50.000,00 e 
que possuem saldo maior de que R$ 1000,00 
d) Retorne o nome dos clientes que possuem contas em agências localizadas na cidade onde residem. 
 
 
5) Considere a seguinte estrutura de banco de dados: 
 
Empregado (NumEmp, Nome, Posto, Chefe, Salário, Comissão, CodDep) 
 CodDep referencia Departamento 
Departamento (NumDep, Nome, Cidade) 
 
Valores dos campos: 
 
Empregado 
NumEmp 
(Integer) 
Nome 
(Varchar 40) 
Salario 
(Decimal 9,2) 
Comissao 
(Decimal 9,2) 
CodDep 
(Integer) 
1 João 300 240 1 
2 Maria 260 120 2 
3 Francisco 260 320 1 
4 José 400 100 1 
5 Tereza 340 300 2 
 
Departamento 
NumDep 
(Integer) 
Nome 
(Varchar 40) 
Cidade 
(Varchar 30)
1 Recursos Humanos Petrolina 
2 Informática Juazeiro 
3 Financeiro Petrolina 
 
Para cada uma das seguintes consultas dê a expressão em SQL: 
 
a) Mostre o nome e a cidade do departamento 2. 
b) Seleccione o salário e a comissão dos empregados cuja comissão excedeu o salário. 
c) Mostre os nomes de todos os departamentos que ficam em Petrolina. 
d) Mostre o nome e número de departamento dos empregados que trabalham nos departamentos 1 e 2. 
e) Mostre o número e nome de todos os empregados, ordenado por número de empregado. 
f) Para cada departamento, mostre o número de departamento, total de empregados e total dos salários 
completos. 
g) Para cada departamento, mostre quanto se paga por ano a vendedores. 
h) Mostre o número do departamento e o total de vendedores de cada departameto. 
i) Calcule o número médio de empregados por departamento. 
j) Liste o nome e salário dos empregados não pertencentes ao departamento 2, que têm o mesmo 
salário dos empregados da departamento 2. 
k) Para cada empregado, mostre o nome e todos os campos do departamento onde trabalha. 
 
6) Considere a seguinte estrutura de banco de dados: 
 
Marca (CodMarca, Nome, PaisOrigem) 
Modelo (CodModelo, Nome, Tipo, Cor, Cidade, CodMarca) 
 CodMarca referencia Marca 
Filial (CodFilial, Nome, Cidade) 
Disponiveis (CodModelo, CodFilial, Data, Quantidade) 
 CodModelo refencia Modelo 
 CodFilial referencia Filial 
 
Valores dos campos: 
 
Marca 
CodMarca 
(Integer) 
Nome 
(Varchar 40) 
PaisOrigem 
(Varchar 20) 
1 VW Brasil 
2 GM Brasil 
3 Ford Brasil 
4 Fiat Brasil 
 
Modelo 
CodModelo 
(Integer) 
Nome 
(Varchar 40) 
Tipo 
(Varchar 20) 
Cor 
(Varchar 15)
Cidade 
(Varchar 20)
CodMarca
(Integer) 
1 Gol Power Preto Petrolina 1 
2 Palio Flex Vermelho Petrolina 4 
3 EcoSport Flex Prata Juazeiro 3 
4 Fox SportLine Prata Petrolina 1 
 
 
Filial 
CodFilial 
(Integer) 
Nome 
(Varchar 40) 
Cidade 
(Varchar 30) 
1 Grande Rio Petrolina 
2 Multicar Juazeiro 
3 Pomal Petrolina 
 
Disponiveis 
CodModelo 
(Integer) 
CodFilial 
(Integer) 
Data 
(Date) 
Quantidade 
(Integer) 
1 1 02/04/2006 6 
3 2 06/04/2006 4 
4 1 07/04/2006 8 
 
Para cada uma das seguintes consultas dê a expressão em SQL: 
 
a) Obter todos os dados de todas as filiais; 
b) Obter o código das marcas que estão disponíveis na filial “2”; 
c) Obter as cores predominantes dos modelos da marca “Ford”; 
d) Obter os códigos dos modelos e sua respectivas marcas, mas somente para modelos disponíveis em 
lojas que estejam situadas em “Petrolina”; 
e) Obter os códigos das filiais que não expõem nenhum carro de cor predominante “Prata” e de marcas 
do “Brasil”; 
f) Obter os códigos das filiais que possuem todos os carros que a marca “VW” produz; 
g) Obter os códigos daqueles modelos que encontram-se disponíveis em todas as filiais de “Petrolina”; 
h) Obter os códigos das marcas que tem determinado modelo disponível em todas as filiais; 
i) Obter os códigos das filiais que tenham disponível algum modelo de uma marca que fabrique modelos 
com cor predominante “vermelha”. 
j) Obter a quantidade de carros de cada marca que estão disponíveis nas filiais de "Petrolina". 
 
7) Considere a seguinte estrutura de banco de dados: 
 
Artigos (CodArtigo, Titulo, Autor, instituicao, OrgaoFinanciador, Nota) 
Eventos (CodEvento, Evento, Local, Mes, NumParticipantes, Instituicao) 
Aceitos (CodEvento, CodArtigo) 
 CodEvento referencia Eventos 
 CodArtigo referencia Artigos 
 
Valores dos campos: 
 
Artigos 
CodArtigo 
(Integer) 
Titulo 
(Varchar 60) 
Autor 
(Varchar 40) 
Instituicao 
(Varchar 6)
OrgaoFinanciador 
(Varchar 6) 
Nota 
(Integer)
1 Sistemas de Banco de Dados João da Silva Facape Facape 8 
2 Redes sem Fio Maria Alves UFPE CRA 9 
3 Engenharia de Software José Pereira Facape Codevasf 10 
4 Projeto de Banco de Dados Carlos Heuser UNEB Prefeitura de Juazeiro 7 
 
 
Eventos 
CodEvento 
(Integer) 
Evento 
(Varchar 40) 
Local 
(Varchar 20) 
Mês 
(Varchar 10) 
NumParticipantes 
(Integer) 
Instituicao 
(Varchar 6) 
1 Encontro 
nacional de BD 
Recife Agosto 1200 UFPE 
2 SBRC Belo Horizonte Julho 3500 UFMG 
3 E-Info Petrolina Maio 450 Facape 
 
 
Aceitos 
CodEvento 
(Integer) 
CodArtigo 
(Integer) 
1 1 
1 3 
2 2 
3 3 
 
Para cada uma das seguintes consultas dê a expressão em SQL: 
 
a) Selecionar o título e o autor de todosos trabalhos da FACAPE aceitos para apresentação em algum 
evento; 
b) Selecionar, por ordem de nota decrescente, a média das notas dos trabalhos de cada instituição e o nome 
da instituição; 
c) Selecionar o título e o autor dos trabalhos que foram aceitos para eventos organizados pela própria 
instituição; 
d) Selecionar a programação de trabalhos que serão apresentados no mês de Agosto, ordenado por título de 
trabalho; 
e) Selecionar o nome dos autores e o número de trabalhos de sua autoria que foram aceitos para 
apresentação em qualquer evento; 
f) Selecionar os nomes dos eventos, os títulos dos trabalhos aceitos para estes eventos, os órgãos 
financiadores dos trabalhos e os locais onde os eventos ocorrerão; 
g) Selecionar os eventos onde foram aceitos trabalhos da FACAPE, mas não foram aceitos trabalhos da 
UNEB; 
h) Selecionar o número total de participantes em todos os eventos realizados em Recife; 
i) Selecionar o número de artigos que contém a letra ‘a’ em seu título. 
 
8) De acordo com as tabelas abaixo do banco de dados Acadêmico.db, escreva o código SQL para cada sentença 
abaixo: 
 
Aluno (CodAluno, Nome, Endereço, Cidade) 
Disciplina (CodDisciplina, Nome, CH) 
Professor (CodProfessor, Nome, Endereço, Cidade) 
Turma (CodDisciplina, CodTurma, CodProfessor, Ano, Horário) 
Historico (CodAluno, CodDisciplina, CodTurma, CodProfessor, Ano, Frequencia, Nota) 
 
 a. Encontre o CodAluno dos alunos onde CodDiscplina seja igual a 1, o Ano igual a 2005 e Nota menor 
que 5. 
b. Forneça o CodAluno, a Frequencia e a Nota dos alunos com nota menor que 5 na disciplina 1 em 2005. 
c. Forneça o nome dos professores da disciplina 1 em 2005. 
d. Encontre o nome e o endereço dos alunos e professores de Petrolina. 
e. Forneça a quantidade de alunos que fizeram a disciplina 1 em 2005. 
f. Encontre a disciplina com maior média de notas. 
g. Insira as seguintes informações no BD: o aluno Aldo da Silva com CodAluno: 021 morador da Av. Brasil, 
55 Campinas. 
h. Remova todas as informações referentes ao aluno “Jorge dos Santos” do sistema. 
i. Escreva o comando para criar a tabela de Professor. 
j. Escreva o comando para apagar o Banco de Dados.