Atividade Modelo Relacional Consultas Resolvidas

Objetivo

Esta atividade propiciará a revisão da teoria de banco de dados relacional através da prática.

Escopo

Controlar o armazenamento dos dados referentes a locação de fitas.da locadora LOCATUDO.

As fitas podem ser classificadas tanto como VHS quanto DVD.

Deverão ser armazenados os dados dos clientes como nome completo, CPF, carteira de identidade, data de nascimento e endereço completo (logradouro, CEP, bairro, cidade e unidade federativa), bem como os dados dos dependentes. Para os dependentes deverá ser armazenado o nome completo e a data de nascimento. Deverá ser permitido que um dependente seja incluído e excluído da lista de dependentes de um cliente em períodos aleatórios. Isto é, um determinado dependente pode estar possibilitado de alugar uma fita dentro de um período e em outro período estar impossibilitado. É muito importante identificar na locação quem realizou a mesma, isto é, foi o cliente titular ou um dependente. Caso seja um dependente é importante identificar se no período da locação o mesmo estava possibilitado ou impossibilitado de realizar locação.

Para a realização de campanhas de marketing o preço da diária de locação das fitas será realizado através de cores. Isto é, cada cor terá um valor de locação e consequentemente cada fita terá uma cor. Não é necessário armazenar o histórico de cores das fitas.

As fitas deverão receber uma classificação quanto ao gênero, sendo que uma fita poderá ter somente um gênero.

O cliente poderá em uma locação realizar a seleção de várias fitas. No momento da devolução o cliente poderá devolver algumas fitas e pagar o valor parcial referente a respectiva fita devolvida. O cliente poderá no mesmo dia realizar várias locações e devoluções da mesma fita.

Produtos

Produto 1 - Diagrama de entidade e relacionamento elaborado no DBDesigner. Valor: 40.

Produto 2 – Comandos DML e DDL elaborado nos SGBDs Oracle ou SQL Server ou MySQL. Valor: 60

Data de Entrega

Produto 1

Dia 27 de abril de 2009, apresentação em sala para discussão.

Dia 28 de abril de 2009 até as 23 horas e 59 minutos, entrega da versão final. Produto 2

Dia 5 de maio de 2009, apresentação em sala para discussão.

Instruções para execução do trabalho

O trabalho deverá ser executado por no máximo 3 alunos. A nomenclatura do trabalho deverá seguir as seguintes regras:

Chave Primária: Inicia com a sigla PK_, em seguida o nome da tabela. Exemplo: PK_FITA

Chave Estrangeira: Inicia com a sigla FK_, em seguida o nome da tabela de origem e o nome da tabela de destino, sendo ambas separadas pelo caractere underline “_”. No final é acrescentado _01. Caso o nome da tabela seja composto abreviá-lo usando as 4 primeiras letras de cada nome. Exemplos: FK_CLIENTE_DEPENDENTE_01, FK_FITA_ITEMLOCATITU_01.

Atributos: Iniciarão com o respectivo mnemônico, em seguida o significado do valor a ser armazenado. Exemplo: CD_TIPO_FITA.

Lista de Mnemônicos

Mnemônico Significado CD CÓDIGO NM NOME DT DATA VR VALOR NR NÚMERO DS DESCRIÇÃO SG SIGLA ID INDICADOR

Comandos SQL de consultas: A solução de cada item, deverá ser descrito logo abaixo ao enunciado. E a identação deverá respeitar o padrão descrito abaixo.

<TABULAÇÃO>SELECT <ENTER> <TABULAÇÃO><TABULAÇÃO>ATRIBUTO1,<ENTER> <TABULAÇÃO><TABULAÇÃO>ATRIBUTO2,<ENTER> <TABULAÇÃO><TABULAÇÃO>ATRIBUTOn,<ENTER> <TABULAÇÃO>FROM<ENTER> <TABULAÇÃO><TABULAÇÃO>TABELA1,<ENTER> <TABULAÇÃO><TABULAÇÃO>TABELA2,<ENTER> <TABULAÇÃO><TABULAÇÃO>TABELAn,<ENTER> <TABULAÇÃO>WHERE<ENTER> <TABULAÇÃO><TABULAÇÃO>EXPRESSAO1 OPERADOR_LÓGICO<ENTER> <TABULAÇÃO><TABULAÇÃO>EXPRESSAO2 OPERADOR_LÓGICO<ENTER> <TABULAÇÃO><TABULAÇÃO>EXPRESSAOn

As palavras entre os sinais de maior que e menor que não serão informadas no comando SQL.As mesmas representam a tecla ENTER e a tecla TAB.

Exemplo :

1 – Demonstrar o nome da fita, cujo código é igual a UM, e a data de locação. → Este é o enunciado SELECT

F.NOME, L.DATA FROM

LOCACAO L WHERE

F.CODFITA = 1 AND F.CODFITA = L.CODFITA

O grupo deverá entregar, digitado, para o e-mail moraisrocha@gmail.com. O e-mail deverá ser preenchido da seguinte forma:

Assunto : ATIVIDADE 1 – PRODUTO 1 - OTIMIZACAO DE BANCO DE DADOS Corpo do E-mail: NOME COMPLETO DE CADA INTEGRANTE DO GRUPO

Anexo: Arquivo XML com a solução do produto 1 e documento Word com a solução do produto 2.

Itens a serem resolvidos

1. Exibir o nome da fita, sua cor e seu preço.

SELECT A.NM_FILME, B.NM_COR, B.VR_COR FROM TB_FITA A, TB_COR B WHERE A.CD_COR = B.CD_COR

2. Exibir o nome das fitas, seu código, e sua data de locação, mas somente para as fitas que ainda não foram devolvidas.

SELECT A.NM_FILME, A.CD_FITA, B.DT_LOCACAO FROM TB_FITA A, TB_LOCACAO_TITULAR B, TB_ITEM_LOCACAO_TITULAR C WHERE

B.CD_LOCACAO = C.CD_LOCACAO AND A.CD_FITA = C.CD_FITA AND

C.DT_DEVOLUCAO IS NULL UNION ALL SELECT A.NM_FILME, A.CD_FITA, B.DT_LOCACAO FROM TB_FITA A, TB_LOCACAO_DEPENDENTE B, TB_ITEM_LOCACAO_DEPENDENTE C WHERE

A.CD_FITA = C.CD_FITA AND C.DT_DEVOLUCAO IS NULL

3. Exibir o nome das fitas, o nome de seu gênero, mas somente para as fitas que não estão locadas.

SELECT A.NM_FILME, B.NM_GENERO FROM TB_FITA A, TB_GENERO B WHERE

A.CD_GENERO = B.CD_GENERO AND NOT EXISTS (SELECT

1 FROM

TB_ITEM_LOCACAO_TITULAR D WHERE

D.DT_DEVOLUCAO IS NULL AND A.CD_FITA = D.CD_FITA) UNION ALL SELECT A.NM_FILME, B.NM_GENERO FROM TB_FITA A, TB_GENERO B WHERE

A.CD_GENERO = B.CD_GENERO AND NOT EXISTS (SELECT

1 FROM

TB_ITEM_LOCACAO_DEPENDENTE D WHERE

D.DT_DEVOLUCAO IS NULL AND A.CD_FITA = D.CD_FITA)

4. Exibir o nome do gênero das fitas e o valor pago por cada gênero no ano de 2008.

SELECT

G.NM_GENERO,

(T.VR_PAGO + U.VR_PAGO) VR_PAGO FROM TB_GENERO G, (SELECT A.CD_GENERO, SUM(C.VR_PAGO) VR_PAGO FROM TB_FITA A, TB_ITEM_LOCACAO_TITULAR C WHERE

C.DT_DEVOLUCAO IS NOT NULL GROUP BY A.CD_GENERO) T, (SELECT A.CD_GENERO, SUM(C.VR_PAGO) VR_PAGO FROM TB_FITA A, TB_ITEM_LOCACAO_DEPENDENTE C WHERE

A.CD_FITA = C.CD_FITA AND C.DT_DEVOLUCAO IS NOT NULL GROUP BY

A.CD_GENERO) U WHERE

T.CD_GENERO = G.CD_GENERO AND T.CD_GENERO = U.CD_GENERO

5. Exibir o código, nome e preço das fitas que não estão locadas e cujo preço seja maior do que R$ 3,00.

SELECT F.CD_FITA, F.NM_FILME, C.VR_COR FROM TB_FITA F, TB_COR C WHERE

C.CD_COR = F.CD_COR AND C.VR_COR > 3 AND

F.CD_FITA NOT IN (SELECT CD_FITA FROM TB_ITEM_LOCACAO_TITULAR WHERE DT_DEVOLUCAO IS NULL) UNION ALL SELECT F.CD_FITA, F.NM_FILME, C.VR_COR FROM TB_FITA F, TB_COR C WHERE

C.CD_COR = F.CD_COR AND C.VR_COR > 3 AND

F.CD_FITA NOT IN (SELECT CD_FITA FROM

TB_ITEM_LOCACAO_DEPENDENTE WHERE

6. Exibir a data de locação, a data de devolução, o código e nome da fita, código e nome do cliente, mas somente dos clientes de Minas Gerais.

SELECT LT.DT_LOCACAO, ILT.DT_DEVOLUCAO, F.CD_FITA, F.NM_FILME, C.CD_CLIENTE, C.NM_CLIENTE FROM TB_FITA F, TB_ITEM_LOCACAO_TITULAR ILT, TB_LOCACAO_TITULAR LT, TB_CLIENTE C, TB_CIDADE CD, TB_UNIDADE_FEDERATIVA UF WHERE

F.CD_FITA = ILT.CD_FITA AND

LT.CD_LOCACAO = ILT.CD_LOCACAO AND LT.CD_CLIENTE = C.CD_CLIENTE AND C.CD_CIDADE = CD.CD_CIDADE AND

CD.SG_UF = UF.CD_UNIDADE_FEDERATIVA AND UF.CD_UNIDADE_FEDERATIVA LIKE 'MG'

7. Exibir o código e nome das fitas de comédia alugadas por clientes do Rio de Janeiro. Exibir também o nome e código desses clientes. SELECT A.CD_FITA, A.NM_FILME, C.CD_CLIENTE, C.NM_CLIENTE FROM TB_FITA A, TB_CIDADE B, TB_CLIENTE C, TB_ITEM_LOCACAO_TITULAR D, TB_LOCACAO_TITULAR E, TB_GENERO F WHERE

A.CD_FITA = D.CD_FITA AND

E.CD_LOCACAO = D.CD_LOCACAO AND E.CD_CLIENTE = C.CD_CLIENTE AND C.CD_CIDADE = B.CD_CIDADE AND B.SG_UF = 'RJ' AND

A.CD_GENERO = F.CD_GENERO AND

UPPER(F.NM_GENERO) LIKE '%COM_DIA%' AND E.DT_LOCACAO IS NOT NULL

8. Exibir o nome da fita, seu código, a data de sua locação e a data de sua devolução. (Exibir também as fitas que nunca foram alugadas.)

SELECT A.NM_FILME, A.CD_FITA, B.DT_LOCACAO, C.DT_DEVOLUCAO FROM TB_FITA A, TB_LOCACAO_TITULAR B, TB_ITEM_LOCACAO_TITULAR C WHERE

A.CD_FITA = C.CD_FITA AND B.CD_LOCACAO = C.CD_LOCACAO UNION ALL SELECT A.NM_FILME, A.CD_FITA, B.DT_LOCACAO, C.DT_DEVOLUCAO FROM TB_FITA A, TB_LOCACAO_DEPENDENTE B, TB_ITEM_LOCACAO_DEPENDENTE C WHERE

A.CD_FITA = C.CD_FITA AND B.CD_LOCACAO = C.CD_LOCACAO UNION ALL SELECT A.NM_FILME, A.CD_FITA, NULL, NULL FROM TB_FITA A WHERE NOT EXISTS ( SELECT 1 FROM TB_ITEM_LOCACAO_DEPENDENTE C WHERE A.CD_FITA = C.CD_FITA) UNION ALL SELECT A.NM_FILME, A.CD_FITA, NULL, NULL FROM TB_FITA A WHERE NOT EXISTS ( SELECT 1

FROM

TB_ITEM_LOCACAO_TITULAR C WHERE

A.CD_FITA = C.CD_FITA)

9. Exibir o nome do cliente, seu código e o nome das fitas alugadas por ele.

SELECT DISTINCT A.NM_CLIENTE, A.CD_CLIENTE, B.NM_FILME FROM TB_CLIENTE A, TB_FITA B, TB_LOCACAO_TITULAR C, TB_ITEM_LOCACAO_TITULAR D WHERE

A.CD_CLIENTE = C.CD_CLIENTE AND C.CD_LOCACAO = D.CD_LOCACAO AND B.CD_FITA = D.CD_FITA UNION ALL SELECT DISTINCT A.NM_DEPENDENTE, A.CD_DEPENDENTE, B.NM_FILME FROM TB_DEPENDENTE A, TB_FITA B, TB_LOCACAO_DEPENDENTE C, TB_ITEM_LOCACAO_DEPENDENTE D WHERE

A.CD_DEPENDENTE = C.CD_DEPENDENTE AND C.CD_LOCACAO = D.CD_LOCACAO AND B.CD_FITA = D.CD_FITA

10. Exibir os clientes que nunca alugaram fitas.

SELECT

A.NM_CLIENTE FROM

TB_CLIENTE A LEFT OUTER JOIN TB_LOCACAO_TITULAR C ON (A.CD_CLIENTE = C.CD_CLIENTE) WHERE C.CD_CLIENTE IS NULL UNION ALL SELECT A.NM_DEPENDENTE FROM

TB_DEPENDENTE A LEFT OUTER JOIN TB_LOCACAO_DEPENDENTE C ON (A.CD_DEPENDENTE = C.CD_DEPENDENTE) WHERE

11. Exibir o código e nome das fitas que possuem o mesmo gênero das fitas que possuem a letra h em seu nome. SELECT A.CD_FITA, A.NM_FILME FROM TB_FITA A WHERE A.CD_GENERO IN ( SELECT DISTINCT B.CD_GENERO FROM TB_FITA B WHERE UPPER(B.NM_FILME) LIKE '%H%')

12. Exibir o código e nome dos clientes que moram no mesmo estado dos clientes que alugaram fitas no mês de outubro.

SELECT C.CD_CLIENTE, C.NM_CLIENTE FROM TB_CLIENTE C, TB_CIDADE D WHERE

C.CD_CIDADE = D.CD_CIDADE AND D.SG_UF IN ( SELECT DISTINCT C.SG_UF FROM TB_CLIENTE A, TB_LOCACAO_TITULAR B, TB_CIDADE C WHERE

A.CD_CIDADE = C.CD_CIDADE AND A.CD_CLIENTE = B.CD_CLIENTE AND

TO_NUMBER(TO_CHAR(B.DT_LOCACAO,'MM')) = 10 )

13. Exibir o código e nome das fitas que possuem mesma cor que as fitas de Suspense.

SELECT C.CD_FITA, C.NM_FILME FROM TB_FITA C WHERE EXISTS ( SELECT 1 FROM

TB_FITA A, TB_GENERO B WHERE

C.CD_COR = A.CD_COR AND

A.CD_GENERO = B.CD_GENERO AND UPPER(B.NM_GENERO) LIKE '%SUSPENSE%')

14. Exibir o código e nome das fitas mais alugadas do que a fita de código 260. Ordenar pelo nome da fita.

SELECT A.CD_FITA, A.NM_FILME FROM TB_FITA A, (SELECT B.CD_FITA, COUNT(1) QTDE FROM TB_ITEM_LOCACAO_TITULAR B GROUP BY B.CD_FITA) T, (SELECT B.CD_FITA, COUNT(1) QTDE FROM TB_ITEM_LOCACAO_DEPENDENTE B GROUP BY B.CD_FITA) D WHERE

A.CD_FITA = T.CD_FITA AND A.CD_FITA = D.CD_FITA GROUP BY

A.CD_FITA,

A.NM_FILME

HAVING SUM(T.QTDE + D.QTDE) > ( SELECT

(T.QTDE + U.QTDE) QTDE_TOTAL FROM (SELECT COUNT(1) QTDE FROM TB_ITEM_LOCACAO_TITULAR A WHERE A.CD_FITA = 260) T, (SELECT COUNT(1) QTDE FROM TB_ITEM_LOCACAO_DEPENDENTE A WHERE A.CD_FITA = 260) U )

SELECT A.CD_CLIENTE, A.NM_CLIENTE FROM TB_CLIENTE A, (SELECT B.CD_CLIENTE, COUNT(1) QTDE FROM TB_LOCACAO_TITULAR B GROUP BY B.CD_CLIENTE) T, (SELECT A.CD_CLIENTE_RESPONSAVEL CD_CLIENTE, COUNT(1) QTDE FROM TB_LOCACAO_DEPENDENTE B, TB_DEPENDENTE A WHERE B.CD_DEPENDENTE = A.CD_DEPENDENTE GROUP BY A.CD_CLIENTE_RESPONSAVEL) D WHERE

A.CD_CLIENTE = T.CD_CLIENTE AND A.CD_CLIENTE = D.CD_CLIENTE GROUP BY

A.CD_CLIENTE, A.NM_CLIENTE

HAVING SUM(T.QTDE + D.QTDE) > ( SELECT

(T.QTDE + U.QTDE) QTDE_TOTAL FROM (SELECT COUNT(1) QTDE FROM TB_LOCACAO_TITULAR A WHERE A.CD_CLIENTE = 160) T, (SELECT COUNT(1) QTDE FROM TB_LOCACAO_DEPENDENTE A, TB_DEPENDENTE B WHERE

A.CD_DEPENDENTE = B.CD_DEPENDENTE AND B.CD_CLIENTE_RESPONSAVEL = 160) U )

16. Exibir o valor total recebido pela locadora no ano de 2009.

SELECT

SUM(NVL(T.VR_PAGO,0) + NVL(D.VR_PAGO,0)) VR_PAGO FROM

(SELECT SUM(A.VR_PAGO) VR_PAGO FROM TB_ITEM_LOCACAO_TITULAR A WHERE TO_NUMBER(TO_CHAR(A.DT_DEVOLUCAO,'YYYY')) = 2009) T, (SELECT SUM(A.VR_PAGO) VR_PAGO FROM TB_ITEM_LOCACAO_DEPENDENTE A WHERE TO_NUMBER(TO_CHAR(A.DT_DEVOLUCAO,'YYYY')) = 2009) D

17. Exibir o nome dos clientes, o nome das fitas e o valor a ser pago para as fitas que estão alugadas, isto é, não foram devolvidas. Ordenar pelo nome do cliente.

SELECT A.NM_CLIENTE, B.NM_FILME, SUM(C.VR_LOCACAO) VR_A_PAGAR FROM TB_CLIENTE A, TB_FITA B, TB_ITEM_LOCACAO_TITULAR C, TB_LOCACAO_TITULAR D WHERE

A.CD_CLIENTE = D.CD_CLIENTE AND B.CD_FITA = C.CD_FITA AND

C.CD_LOCACAO = D.CD_LOCACAO AND C.DT_DEVOLUCAO IS NULL GROUP BY A.NM_CLIENTE, B.NM_FILME UNION ALL SELECT A.NM_DEPENDENTE NM_CLIENTE, B.NM_FILME, SUM(C.VR_LOCACAO) VR_A_PAGAR FROM TB_DEPENDENTE A, TB_FITA B, TB_ITEM_LOCACAO_DEPENDENTE C, TB_LOCACAO_DEPENDENTE D WHERE

A.CD_DEPENDENTE = D.CD_DEPENDENTE AND B.CD_FITA = C.CD_FITA AND

C.CD_LOCACAO = D.CD_LOCACAO AND C.DT_DEVOLUCAO IS NULL GROUP BY A.NM_DEPENDENTE, B.NM_FILME ORDER BY 1

18. Informar para cada cliente o nome, ordenado alfabeticamente, a quantidade de locações e o valor total pago pelas locações. SELECT A.NM_CLIENTE, NVL(SUM(C.VR_PAGO),0) VR_PAGO, COUNT(1) QTDE FROM TB_CLIENTE A, TB_LOCACAO_TITULAR B, TB_ITEM_LOCACAO_TITULAR C WHERE

A.CD_CLIENTE = B.CD_CLIENTE AND B.CD_LOCACAO = C.CD_LOCACAO GROUP BY A.NM_CLIENTE UNION ALL SELECT A.NM_DEPENDENTE NM_CLIENTE, NVL(SUM(C.VR_PAGO),0) VR_PAGO, COUNT(1) QTDE FROM TB_DEPENDENTE A, TB_LOCACAO_DEPENDENTE B, TB_ITEM_LOCACAO_DEPENDENTE C WHERE

A.CD_DEPENDENTE = B.CD_DEPENDENTE AND B.CD_LOCACAO = C.CD_LOCACAO

GROUP BY

A.NM_DEPENDENTE ORDER BY

NM_CLIENTE

19. Informar, por mês, o nome da fita e a quantidade da mesma que foi alugada.

SELECT

T.MES, B.NM_FILME,

(NVL(T.QTDE,0) + NVL(D.QTDE,0)) QTDE FROM TB_FITA B, (SELECT TO_NUMBER(TO_CHAR(C.DT_LOCACAO,'MM')) MES, A.CD_FITA, COUNT(1) QTDE FROM TB_LOCACAO_TITULAR C, TB_ITEM_LOCACAO_TITULAR A WHERE C.CD_LOCACAO = A.CD_LOCACAO GROUP BY TO_NUMBER(TO_CHAR(C.DT_LOCACAO,'MM')), A.CD_FITA) T, (SELECT

TO_NUMBER(TO_CHAR(C.DT_LOCACAO,'MM')) MES, A.CD_FITA, COUNT(1) QTDE FROM TB_LOCACAO_DEPENDENTE C, TB_ITEM_LOCACAO_DEPENDENTE A WHERE C.CD_LOCACAO = A.CD_LOCACAO GROUP BY TO_NUMBER(TO_CHAR(C.DT_LOCACAO,'MM')), A.CD_FITA) D WHERE

B.CD_FITA = T.CD_FITA AND B.CD_FITA = D.CD_FITA AND T.MES = D.MES

20. Informar o nome das fitas e os respectivos valores de locação na ordem decrescente.

SELECT A.NM_FILME, B.VR_COR FROM TB_FITA A, TB_COR B WHERE A.CD_COR = B.CD_COR ORDER BY B.VR_COR DESC