BD I - 17o Exercicio - RESPOSTAS(1)

Prévia do material em texto

CENTRO UNIVERSITÁRIO 7 DE SETEMBRO 
 Curso: Análise e Desenvolvimento de Sistemas 
Disciplina: Banco de Dados I 
Professor: Fernando Siqueira 
17º. Exercício – Group by, Subconsultas e Junção Avançada - 
RESPOSTAS 
1. O Esquema Relacional de um banco de dados que permite controlar as infrações ocorridas na 
cidade de Fortaleza está representado abaixo. Os requisitos levantados para esse projeto 
foram: 
1. Os veículos são identificados pela placa e estão classificados de acordo com o seu 
modelo, classe, cor predominante e ano de fabricação. 
2. Cada veículo possui um proprietário e nada impede que um proprietário possua 
nenhum ou vários veículos. 
3. Existem diversos tipos de infração, cujo valor será associado para cobrança na 
ocorrência da infração. 
 
MISSÃO INSTITUCIONAL 
“Contribuir para o desenvolvimento da sociedade, educando para a cidadania, propiciando a formação de 
profissionais competentes, felizes e éticos.” 
classe
id_classe
ds_classe
infracoes
id_infracao
local
data_hora
placa_veiculo
cod_infra
valor_infra
modelo
id_modelo
ds_modelo
proprietario
codigo
nome
endereco
sexo
data_nasc
telefone
renda_mensal
tipoinfracao
id_tipo
ds_tipo
valor
veiculo
placa
ano_fabric
cor
classe
modelo
cod_proprietario
UNI7 Banco de Dados I Prof. Fernando Siqueira 
2. Escreva os comandos em SQL, com base no banco de dados da 1ª. Questão, para as 
seguintes sentenças: 
a. Exibir o nome dos proprietários que não tem nenhuma infração 
SELECT P.NOME 
FROM PROPRIETARIO P 
WHERE P.CODIGO NOT IN ( 
 SELECT V.COD_PROPRIETARIO 
 FROM VEICULO V, INFRACOES I 
 WHERE V.PLACA = I.PLACA_VEICULO 
 ) 
SELECT P.NOME 
FROM PROPRIETARIO P 
WHERE NOT EXISTS ( 
 SELECT * 
 FROM VEICULO V, INFRACOES I 
 WHERE V.PLACA = I.PLACA_VEICULO AND 
 V.COD_PROPRIETARIO = P.CODIGO 
 ) 
b. Exibir o valor médio das infrações registradas, por data, cujo valor médio de infração 
seja maior R$200,00. 
c. Exibir a quantidade de veículos por classe e em ordem alfabética. 
d. Exibir o nome dos proprietários que possuem mais de dois veículos. 
e. Listar os tipos de infrações que não possuem nenhuma autuação (utilize join). 
f. Liste os modelos de carro que possuem valor total de infração maior que a média de 
todos os modelos. 
g. Liste os proprietários que possuem mais de um veículo 
SELECT P. NOME, COUNT(*) QTD_VEICULOS 
FROM PROPRIETARIO P,VEICULO V, MODELO M 
 WHERE V.COD_PROPRIETARIO = P.CODIGO AND 
 V.MODELO = M.ID_MODELO 
GROUP BY P.NOME 
HAVING COUNT(*)>1 
h. Liste os proprietários que não possuem veículos (apresente uma solução com IN e outra com 
EXISTS e outra com JOIN) 
SELECT P. NOME 
FROM PROPRIETARIO P 
WHERE P.CODIGO NOT IN (SELECT V.COD_PROPRIETARIO 
FROM VEICULO V) 
SELECT P. NOME 
FROM PROPRIETARIO P 
WHERE NOT EXISTS (SELECT V.COD_PROPRIETARIO 
FROM VEICULO V 
WHERE P.CODIGO = V.COD_PROPRIETARIO) 
 2