Trabalho - JOINS

Prévia do material em texto

Acadêmico(a): Data: 
 
 
 
 
1. (5.0 pontos) Implemente as consultas abaixo considerando o modelo conceitual acima e utilizando para realizar a junção SOMENTE os comandos de JOINs. 
(a) Listar os nomes dos assinantes, seguido dos dados do endereço e os telefones correspondentes. 
(b) Listar os nomes dos assinantes, seguido do seu ramo, ordenados por ramo e posteriormente por nome. 
(c) Listar os assinantes do município de Pelotas que são do tipo residencial. 
(d) Listar os nomes dos assinantes que possuem mais de um telefone. 
(e) Listar os nomes dos assinantes seguido do número do telefone, tipo de assinante comercial, com endereço em Natal ou João Câmara. 
 
 
2. (5.0 pontos) Implemente as consultas abaixo considerando o modelo físico acima e utilizando para realizar a junção SOMENTE os comandos de JOINs. 
(a) Exiba a placa e o nome dos donos de todos os veículos. 
(b) Exiba o CPF e o nome do cliente que possui o veiculo de placa “JJJ-2020”. 
(c) Exiba a placa, o ano e a cor do veículo que possui o código de estacionamento 1 
(d) Exiba a placa, o ano do veículo e a descrição de seu modelo, se ele possuir ano a partir de 2000. 
(e) Exiba o endereço, a data de entrada e de saída dos estacionamentos do veículo de placa “JEG- 1010”. 
(f) Exiba a quantidade de vezes que os veículos de cor verde estacionaram. 
(g) Liste as placas, os horários de entrada e saída dos veículos de cor verde. 
(h) Exiba o nome do cliente que possui o veículo cujo código do estacionamento é 2. 
(i) Liste todos os estacionamentos do veículo de placa “JJJ-2020”. 
(j) Exiba a descrição do modelo do veículo cujo código do estacionamento é 2. 
 
 
 
 
Comandos SQL trabalho-JOINS 
Questão 1 
CREATE TABLE ramo_atividade( 
cd_ramo int, 
ds_ramo varchar(50), 
CONSTRAINT PK_ramo PRIMARY KEY (cd_ramo) 
); 
 
 
CREATE TABLE tipo_assinante( 
cd_tipo int, 
ds_tipo varchar(50), 
CONSTRAINT PK_tipo PRIMARY KEY (cd_tipo) 
); 
 
 
CREATE TABLE municipio( 
cd_municipio int, 
ds_municipio varchar(50), 
CONSTRAINT PK_municipio PRIMARY KEY (cd_municipio) 
); 
 
 
CREATE TABLE assinante( 
cd_assinante int, 
nm_assinante varchar(50), 
cd_ramo int, 
cd_tipo int, 
CONSTRAINT PK_assinante PRIMARY KEY (cd_assinante), 
CONSTRAINT FK_assinante1 FOREIGN KEY (cd_ramo) REFERENCES ramo_atividade (cd_ramo), 
CONSTRAINT FK_assinante2 FOREIGN KEY (cd_tipo) REFERENCES tipo_assinante (cd_tipo) 
); 
 
 
CREATE TABLE endereco( 
cd_endereco int, 
ds_endereco varchar(50), 
complemento varchar(50), 
bairro varchar(50), 
CEP varchar(50), 
cd_assinante int, 
cd_municipio int, 
CONSTRAINT PK_endereco PRIMARY KEY (cd_endereco), 
CONSTRAINT FK_end FOREIGN KEY (cd_assinante) REFERENCES assinante (cd_assinante), 
CONSTRAINT FK_end2 FOREIGN KEY (cd_municipio) REFERENCES municipio (cd_municipio) 
); 
 
 
CREATE TABLE telefone( 
cd_fone int, 
ddd varchar(3), 
n_fone varchar(10), 
cd_endereco int, 
CONSTRAINT PK_telefone PRIMARY KEY (cd_fone), 
CONSTRAINT FK_fone FOREIGN KEY (cd_endereco) REFERENCES endereco (cd_endereco) 
); 
 
 
INSERT INTO tipo_assinante (cd_tipo,ds_tipo) VALUES (1, 'Bronze'), 
(2, 'Prata'), 
(3, 'Ouro'), 
(4, 'Diamante'); 
 
 
 
INSERT INTO ramo_atividade (cd_ramo,ds_ramo) VALUES (1, 'Lojista'), 
(2, 'Medico'), 
(3, 'Empresario'), 
(4,'Manicure'), 
(5,'Jogador de Lol'); 
 
 
INSERT INTO municipio (cd_municipio,ds_municipio) VALUES (1, 'São José dos Campos'), 
(2, 'Joao Camara'), 
(3, 'Natal'), 
(4, 'Floripa'), 
(5, 'Pelotas'); 
 
 
INSERT INTO assinante (cd_assinante, nm_assinante,cd_ramo,cd_tipo) VALUES (1, 'Fallen', 1, 
3), 
(2, 'Fer', 2, 1), 
(3, 'Taco', 3, 2), 
(4, 'Meyern', 4, 4), 
(5, 'Kng', 5, 1); 
 
 
INSERT INTO endereco 
(cd_endereco,ds_endereco,complemento,bairro,cep,cd_assinante,cd_municipio) VALUES (1, 
'Av. Carlos Ferreira', 'Presidio', 'Enseada de Brito', '88138-804', 1, 1), 
(2, 'Servidao Teixeira', 'Casa', 'Praia de Fora', '12345-804', 2, 2), 
(3, 'Av. Augusto Ferreira', 'Casa', 'Passa Vinte', '23456-804', 3, 4), 
(4, 'Rua Ferreira', 'Casa', 'Guarda do Embau', '34567-804', 4, 5), 
(5, 'Rua Leoberto Leal', 'Apto 503', 'Pinheira', '80539-804', 5, 5); 
 
 
INSERT INTO telefone (cd_fone,ddd,n_fone,cd_endereco) VALUES (1, '48', '99995258', 1), 
(2, '53', '84050000', 5), 
(3, '47', '95455555', 3), 
(4, '61', '70707070', 2); 
 
 
 
 
 
 
 
--QUESTAO A) 
SELECT A.nm_assinante, E.ds_endereco, E.complemento,E.bairro,E.cep,m.ds_municipio , 
T.ddd, T.n_fone FROM assinante A 
INNER JOIN endereco E ON A.cd_assinante = E.cd_assinante 
INNER JOIN municipio M ON M.cd_municipio = E.cd_municipio 
INNER JOIN telefone T ON E.cd_endereco = T.cd_endereco; 
 
--QUESTAO B) 
SELECT A.nm_assinante, R.ds_ramo FROM assinante A INNER JOIN ramo_atividade R ON 
R.cd_ramo = A.cd_ramo ORDER BY ds_ramo, nm_assinante; 
 
 
--QUESTAO C) 
SELECT A.* FROM assinante A 
INNER JOIN endereco E ON a.cd_assinante = E.cd_assinante 
INNER JOIN municipio M ON M.cd_municipio = E.cd_municipio 
INNER JOIN tipo_assinante TP ON TP.cd_tipo = A.cd_tipo 
WHERE ds_municipio = 'Pelotas' AND ds_tipo = 'Residencial'; 
 
--QUESTAO D) 
SELECT A.nm_assinante FROM assinante A 
INNER JOIN endereco E ON A.cd_assinante = E.cd_assinante 
INNER JOIN telefone T ON E.cd_endereco = T.cd_endereco 
GROUP BY A.nm_assinante 
HAVING COUNT(T.cd_endereco) > 1; 
 
 
 
--QUESTAO E) 
SELECT A.nm_assinante,T.ddd,T.n_fone FROM assinante A 
INNER JOIN endereco E ON A.cd_assinante = E.cd_assinante 
INNER JOIN municipio M ON M.cd_municipio = E.cd_municipio 
INNER JOIN telefone T ON E.cd_endereco = T.cd_endereco 
INNER JOIN tipo_assinante TA ON TA.cd_tipo = A.cd_tipo 
WHERE (ds_municipio = 'Natal' OR ds_municipio = 'Joao Camara'); 
 
 
 
Questão 2 
 
 
CREATE TABLE cliente( 
cpf INTEGER PRIMARY KEY NOT NULL, 
nome VARCHAR(60), 
dtNasc DATE 
) 
CREATE TABLE veiculo( 
placa VARCHAR(8) PRIMARY KEY NOT NULL, 
cor VARCHAR(20), 
cliente_cpf INTEGER, 
modelo_codMod INTEGER 
) 
ALTER TABLE veiculo 
ADD CONSTRAINT fk_clienteCpf_veiculo FOREIGN KEY (cliente_cpf) REFERENCES 
cliente(cpf) 
ON UPDATE CASCADE 
ON DELETE NO ACTION; 
 
CREATE TABLE modelo( 
codMod SERIAL PRIMARY KEY NOT NULL, 
desc_2 VARCHAR(40) 
) 
 
 
 
ALTER TABLE veiculo 
ADD CONSTRAINT fk_veiculo_modelo FOREIGN KEY (modelo_codMod) REFERENCES 
modelo(codMod) 
ON UPDATE CASCADE 
ON DELETE NO ACTION; 
CREATE TABLE estaciona( 
cod SERIAL PRIMARY KEY NOT NULL, 
patio_num INTEGER, 
veiculo_placa VARCHAR(8), 
dtEntrada VARCHAR(10), 
dtSaida VARCHAR(10), 
hsEntrada VARCHAR(10), 
hsSaida VARCHAR(10) 
) 
CREATE TABLE patio( 
num SERIAL PRIMARY KEY NOT NULL, 
endereco VARCHAR (40), 
capacidade INTEGER 
) 
ALTER TABLE estaciona 
ADD CONSTRAINT fk_pationum_num FOREIGN KEY (patio_num) REFERENCES 
patio(num) 
ON UPDATE CASCADE 
ON DELETE NO ACTION; 
ALTER TABLE estaciona 
ADD CONSTRAINT fk_veiculoplaca_placa FOREIGN KEY (veiculo_placa) REFERENCES 
veiculo(placa) 
ON UPDATE CASCADE 
ON DELETE NO ACTION; 
 
 
 
INSERT INTO CLIENTE(nome, cpf, dtNasc) VALUES ('Joao, 123456, '27/06/1999'), 
('Jorge', 234567, '25/05/1895'), 
('Fake', 345678, '27/08/2000'), 
('Tico', 456789, '10/12/2003'), 
('Meyern', 567891, '05/09/1999'), 
('Fer', 678912, '01/01/2001'), 
('Kng', 789123, '15/06/2013'); 
 
 
INSERT INTO MODELO(codmod,desc_2) VALUES 
(1,'GOL'),(2,'GOLF'),(3,'RENEGADE'),(4,'350Z'),(5,'CORSA'),(6,'PALIO'),(7,'SAVEIRO'); 
 
 
INSERT INTO VEICULO (placa, cor, cliente_cpf, modelo_codmod, ano) VALUES ('ABC- 
2020','PRATA',123456,1,2015), 
('CDE-3030','BRANCO',234567,2,2019), 
('JJJ-2020','PRETO',345678,3,2012), 
('EFG-1515','AMARELO',456789,4,2011), 
('GHI-2332','CARAMELO',567891,5,1990), 
('JKL-0015','AZUL',678912,6,1995), 
('JEG-1010','VERDE',789123,7,2005); 
 
 
INSERT INTO PATIO(endereco,capacidade) VALUES ('RUA COLOMBU',25),('RUA 
ROMEU',30),('RUA JOTA JUNIOR',35); 
 
 
INSERT INTO 
ESTACIONA(PATIO_NUM,VEICULO_PLACA,DTENTRADA,DTSAIDA,HSENTRADA,HSSAIDA) 
VALUES 
(3,'ABC-2020','05/04/2020','03/05/2020','07:12','10:25')(1,'CDE-3030','01/03/2020','01/03/2020','09:30','14:25'), 
(3,'JJJ-2020','03/10/2020','07/10/2020','11:35','11:57'), 
(2,'EFG-1515','06/06/2020','06/06/2020','19:15','20:30'), 
(2,'GHI-2332','30/04/2020','01/05/2020','20:50','22:05'), 
(1,'JKL-0015','08/08/2020','18/03/2021','19:15','22:05'), 
(2,'JEG-1010','12/05/2020','13/06/2020','15:30','19:00'); 
--QUESTÃO A) 
SELECT C.NOME, C.CPF,V.PLACA FROM CLIENTE C 
INNER JOIN VEICULO V ON V.CLIENTE_CPF = C.CPF; 
 
--QUESTÃO B) 
SELECT C.CPF,C.NOME FROM CLIENTE C 
INNER JOIN VEICULO V ON V.CLIENTE_CPF = C.CPF 
WHERE PLACA = 'JJJ-2020'; 
--QUESTÃO C) 
SELECT V.PLACA,V.ANO,V.COR FROM VEICULO V 
INNER JOIN ESTACIONA E ON V.PLACA = E.VEICULO_PLACA 
WHERE COD = 1; 
--QUESTÃO D) 
SELECT V.PLACA,V.ANO, M.DESC_2 FROM VEICULO V 
INNER JOIN MODELO M ON V.MODELO_CODMOD = M.CODMOD 
WHERE ANO >= 2000; 
--QUESTÃO E) 
SELECT P.ENDERECO,E.DTENTRADA,E.DTSAIDA FROM PATIO P 
INNER JOIN ESTACIONA E ON E.PATIO_NUM = P.NUM 
WHERE E.VEICULO_PLACA = 'JJJ-2020'; 
--QUESTÃO F) 
SELECT COUNT(V.COR) AS QUANTIDADE_CARROS_VERDES FROM VEICULO V 
INNER JOIN ESTACIONA E ON V.PLACA = E.VEICULO_PLACA 
WHERE V.COR = 'VERDE'; 
 
 
 
 
 
--QUESTÃO G) 
SELECT V.PLACA,E.HSENTRADA,E.HSSAIDA FROM VEICULO V 
INNER JOIN ESTACIONA E ON V.PLACA = E.VEICULO_PLACA 
WHERE V.COR = 'VERDE'; 
--QUESTÃO H) 
SELECT C.NOME FROM CLIENTE C 
INNER JOIN VEICULO V ON V.CLIENTE_CPF = C.CPF 
INNER JOIN ESTACIONA E ON V.PLACA = E.VEICULO_PLACA 
WHERE COD = 2; 
--QUESTÃO I) 
SELECT V.PLACA,E.PATIO_NUM,E.HSENTRADA,E.HSSAIDA, E.DTENTRADA, E.DTSAIDA 
FROM VEICULO V 
INNER JOIN ESTACIONA E ON V.PLACA = E.VEICULO_PLACA 
WHERE V.PLACA = 'JJJ-2020'; 
 
--QUESTÃO J) 
SELECT M.DESC_2 FROM MODELO M 
INNER JOIN VEICULO V ON V.MODELO_CODMOD = M.CODMOD 
INNER JOIN ESTACIONA E ON V.PLACA = E.VEICULO_PLACA 
WHERE COD = 2;