GABARITO CarroVelho (1)

Prévia do material em texto

Professor Flávio
1) Considerando cada uma das tabelas relacionadas a seguir, crie o Banco de Dados Carro 
Velho. Em seguida, elabore as instruções necessárias para realizar a inserção dos dados 
apresentados nas respectivas tabelas.
BD Carro Velho
FABRICANTE (codFabricante, nomeFabricante)
AUTOMOVEIS (codAuto, codFabricante, modelo, ano, pais, preço_tabela)
 O atributo codFabricante da tabela AUTOMOVEIS referencia a chave primária da tabela 
FABRICANTE
REVENDEDORAS (CGC, nome, cidade, estado)
CONSUMIDORES (Identidade, nome, sobrenome)
NEGOCIOS (codComprador, codRevenda, codAuto, Data, Preço)
 O atributo codComprador da tabela NEGOCIOS referencia a chave primária da tabela 
CONSUMIDORES
 O atributo codRevenda da tabela NEGOCIOS referencia a chave primária da tabela 
REVENDEDORAS
 O atributo codAuto da tabela NEGOCIOS referencia a chave primária da tabela 
AUTOMOVEIS
FABRICANTE
codFabric
ante
nomeFabricante
1 Volkswagen
2 Peugeot
3 Chevrolet
4 Citroen
5 Ford
6 Fiat
AUTOMOVEIS
codAu
to
codFabrica
nte
modelo ano pais preco_tabel
a
1 1 Gol 2000 Brasil 25.000
2 1 Golf 2005 Argentina 39.000
3 5 Ka 1990 Brasil 15.000
4 3 Corsa Sedã 1995 Brasil 12.500
5 5 Fiesta 2003 Argentina 20.000
6 3 Corsa Hatch 1995 Argentina 10.000
REVENDEDORAS
CGC Nome cidade Estado
121215 Garagem Auto-Estrada Palmas TO
154956 Siga Bem Paraíso TO
147896 Vá adiante Goiânia GO
1/5
Professor Flávio
CONSUMIDORES
Identidade Nome Sobrenome
159753 José Da Conceição
158741 Paulo Da Silva Cunha
789564 Maria José de Paula
498534 Joana Maranhão da Silvéria
NEGOCIOS 
codComprad
or
codRevenda codAuto Data Preço
158741 121215 3 15/05/2005 17.500
498534 154956 1 15/07/2007 28.000
159753 147896 4 10/05/2008 15.500
498534 147896 2 15/06/2008 42.000
2) Utilizando a instrução SELECT, escreva as seguintes consultas:
a) Obtenha o modelo de todos os veículos 
b) Listar todos os veículos que tenham valor de tabela superior a 18.000
c) Listar o modelo e o nome do fabricante de cada automóvel existente na base de 
dados, assim como seu país de fabricação
d) Listar o modelo e o ano de todos os carros adquiridos pelo consumidor de código 
‘498534’
e) Listar os estados onde se vende o modelo ‘Corsa Sedã’
f) Listar o nome das revendedoras que venderam pelo menos um carro em 2008
g) Listar os carros cuja diferença entre o preço de venda e o preço de tabela será 
superior a 1.500 reais
h) Listar os consumidores (identidade e nome) que compram apenas carros 
Argentinos
Material adaptado da ULBRA – Prof. Fernando Luiz de Oliveira
2/5
Professor Flávio
CREATE TABLE FABRICANTE(
codFabricante int,
nomeFabricante varchar (50),
primary key(codFabricante));
CREATE TABLE AUTOMOVEIS(
codAuto int,
codFabricante int,
modelo varchar(50),
pais varchar (30), 
ano int,
preco_tabela float,
primary key(codAuto),
foreign key(codFabricante) REFERENCES FABRICANTE(codFabricante));
CREATE TABLE REVENDEDORAS(
CGC varchar(10) ,
nome varchar (50),
cidade varchar(50),
estado varchar(50),
primary key(CGC));
CREATE TABLE CONSUMIDORES(
identidade char(10),
nome varchar (50),
sobrenome varchar(50),
primary key(identidade));
CREATE TABLE NEGOCIOS(
codComprador char(10) not null,
codRevenda varchar(10) not null,
codAuto int not null,
dataCompra date not null,
preco float,
primary key(codComprador,codRevenda, codAuto),
foreign key(codComprador) REFERENCES CONSUMIDORES(identidade),
foreign key(codAuto) REFERENCES AUTOMOVEIS(codAuto),
foreign key(codRevenda) REFERENCES REVENDEDORAS(CGC));
3/5
Professor Flávio
insert into FABRICANTE(codFabricante, nomeFabricante) VALUES
(1, 'Volkswagen'),
(2, 'Peugeot'),
(3, 'Chevrolet'),
(4, 'Citroen'),
(5, 'Ford'),
(6, 'Fiat');
insert into AUTOMOVEIS(codAuto, codFabricante, modelo, ano, pais, 
preco_tabela) VALUES
(1, 1, 'Gol', 2000,'Brasil', 25000),
(2, 1, 'Golf', 2005,'Argentina', 39000),
(3, 5, 'Ka', 1990, 'Brasil', 15000),
(4, 3, 'Corsa Seda', 1995, 'Brasil', 12500),
(5, 5, 'Fiesta', 2003, 'Argentina', 20000),
(6, 3, 'Corsa Hatch', 1995, 'Argentina', 10000);
insert into REVENDEDORAS VALUES
(121215, 'Garagem Auto-Estrada','Palmas','TO'),
(154956, 'Siga Bem', 'Paraiso', 'TO'),
(147896, 'Vá adiante', 'Goiânia', 'GO');
insert into CONSUMIDORES VALUES
(159753, 'Jose', 'Da Conceicao'),
(158741, 'Paulo', 'Da Silva Cunha'),
(789564, 'Maria', 'Jose de Paula'),
(498534, 'Joana', 'Maranhao da Silveria');
insert into NEGOCIOS VALUES
(158741, 121215, 3, '15/05/2005', 17500),
(498534, 154956, 1, '15/07/2007', 28000),
(159753, 147896, 4, '10/05/2008', 15500),
(498534, 147896, 2, '15/06/2008', 42000);
a) Obtenha o modelo de todos os veículos 
select modelo 
from AUTOMOVEIS;
b) Listar todos os veículos que tenham valor de tabela superior a 18.000
select * 
from AUTOMOVEIS 
where preco_tabela > 18000;
c) Listar o modelo e o nome do fabricante de cada automóvel existente na base de
dados, assim como seu país de fabricação
select A.pais,A.modelo, F.nomeFabricante
from AUTOMOVEIS AS A, FABRICANTE AS F
where A.codFabricante = F.codFabricante
4/5
Professor Flávio
d) Listar o modelo e o ano de todos os carros adquiridos pelo consumidor de 
código 498534
Select A.modelo, A.ano
from AUTOMOVEIS AS A
inner join NEGOCIOS AS N ON (N.codAuto = A.codAuto)
where N.codComprador = '498534';
OU
SELECT A.modelo, A.ano
FROM Automoveis A, NegociosN
WHERE A.codAuto=N.codAuto AND N.codComprador='498534';
e) Listar os estados onde se vende o modelo ‘Corsa Sedã’
select R.estado
from REVENDEDORAS AS R 
inner join NEGOCIOS AS N on (N.codRevenda = R.CGC)
inner join AUTOMOVEIS AS A on (N.codAuto = A.codAuto)
where A.modelo = 'Corsa Seda';
OU
SELECT R.estado
FROM Revendedoras R, Negocios N,Automoveis A
WHERE N.codRevenda = R.CGC AND A.codAuto = N.codAuto AND A.modelo = 'Corsa 
Seda';
f) Listar o nome das revendedoras que venderam pelo menos um carro em 2008
select R.nome
from REVENDEDORAS AS R innerjoin NEGOCIOS ON (codRevenda = CGC)
where DATE_PART('YEAR', dataCompra ) >= 2008;
OU
Select R.nome
from REVENDEDORAS AS R innerjoin NEGOCIOS ON (codRevenda = CGC)
where dataCompra>= '01-01-2008' AND dataCompra<= '31-12-2008';
g) Listar os carros cuja diferença entre o preço de venda e o preço de tabela será 
superior a 1.500 reais
select A.*
from AUTOMOVEIS AS A, NEGOCIOS AS N
where A.codAuto = N.codAuto AND 
(N.preco - A.preco_tabela) > 1500;
h) Listar os consumidores (identidade e nome) que compram apenas carros 
Argentinos
select c.nome, c.identidade
from CONSUMIDORES AS c,NEGOCIOS AS n,AUTOMOVEIS AS a
where a.codAuto = n.codAuto AND n.codComprador= c.identidade 
AND a.pais = 'Argentina';
5/5