CV.clcodigo, ​ ​PE.pecodigo,​ ​IP.prcodigo,​ ​IP.quantidade FROM CLIENTES_VIPS ​ ​CV,​ ​CLIENTES​ ​CL,​ ​PEDIDOS​ ​PE,​ ​ITENS_PEDIDOS​ ​IP WHERE

Fundamentos

​

​

de

​

​

Banco

​

​

de

​

​

Dados

Lista

​

​

05

​

​

–

​

​

Revisão

​

​

SQL

GABARITO

Considere​ ​os​ ​seguintes​ ​esquemas​ ​de​ ​relação.

(​

CV)

​

​

CLIENTES_VIPS

​​

​

(

​

clcodigo

​

:

​

​

integer,

​

​​

desconto

​

:real)

(​

CL)

​

​

CLIENTES

​​

​

(

clcodigo

​

​

:

​

​

integer,

​

​​

nome

:string,

​

​

​​

telefone

​

:string,

​

​​

cidade

​

:

​

​

string,

​​

​

dataNasc

​

:string);

(PE)

​

​

PEDIDOS

​

​​

(

​

pecodigo

​

:

​

integer,

​​

​​

clcodigo

​

:integer,

​​

​

dataEntrega

​

:string);

(IP)

​

​

ITENS_PEDIDOS

​

​​

(

​

pecodigo

​

:integer

​

,

​

​​

prcodigo

​

:integer,

​

​​

quantidade

​

:

​

​

integer;

​

​​​

​

valor

​

:real);

(PR)

​

​

PRODUTOS

​

​​

(

​

prcodigo

​

:integer,

​

​​

descrição

​

:integer)

(FP)

​

​

FORNECE_PRODUTOS

​

​​

(

​

prcodigo

​

:integer,

​

​​

fcodigo

​

:string);

Escreva

​

​

as

​

​

seguintes

​

​

consultas

​

​

em

​

​

SQL.

1. Para

​

​

cada

​

​

produto,

​

​

obtenha

​

​

o

​

​

código

​

​

do

​

​

produto,

​

​

nome

​

do

​

​

​

produto

​

​

e

​

​

a

​

​

quantidade

​

​

de

​

​

clientes

​

​

vips

​

​

que

​

​

fizeram

pedido

​

​

do

​

​

produto

​

​

em

​

​

quantidade

​

​

maior

​

​

do

​

​

que

​

​

10.

​

​​

​​

​

Caso

​

​

o

​

​

produto

​

​

não

​

satisfaça

​

​

​

a

​

​

condição

​

​

para

​

​

nenhum

cliente

​

​

vip,

​

​

colocar

​

​

ZERO

​

​​

​

na

​

​

coluna

​

​

correspondente

​

​

a

​

​

quantidade

​

​

de

​

​

clientes

​

​

vips.

Solução​ ​01:

SELECT​​​P.PRCODIGO,​​P.DESCRICAO,​​​COUNT​(​DISTINCT​​​CV.CLCODIGO) FROM​​​CLIENTES_VIPS​​CV

INNER​​​​JOIN​​​PEDIDOS​​PE​​​ON​​​CV.CLCODIGO​​=​​PE.CLCODIGO INNER​​​​JOIN​​​ITENS_PEDIDOS​​IP​​​ON​​​PE.PECODIGO=​​IP.PECODIGO

RIGHT​​​​JOIN​​​PRODUTOS​​P​​​ON​​​IP.PRCODIGO​=​ ​​P.PRCODIGO​​​AND​​​IP.QUANTIDADE​​>​​10 GROUP​​​​BY​​​P.PRCODIGO;

Solução​ ​02:

SELECT​​​P.PRCODIGO,​​P.DESCRICAO,​​​COUNT​(​DISTINCT​​​V.CLCODIGO) FROM​​​PRODUTOS​​P

LEFT​​​​JOIN​​​(

SELECT​​​CV.CLCODIGO,​​IP.PECODIGO,​​IP.PRCODIGO FROM​​​CLIENTES_VIPS​​CV

INNER​​​​JOIN​​​PEDIDOS​​PE​​​ON​​​CV.CLCODIGO​​=​​PE.CLCODIGO INNER​​​​JOIN​​​ITENS_PEDIDOS​​IP​​​ON​​​PE.PECODIGO=​​IP.PECODIGO WHERE​​​IP.QUANTIDADE​​>​​10)​V​ ​​​ON​​​P.PRCODIGO​​=​​V.PRCODIGO GROUP​​​​BY​​​P.PRCODIGO;

Solução​ ​03:

CREATE​​​​VIEW​​​Q1​​(cvcodigo,​pecodigo,​ ​​prcodigo,​​quantidade)​​​AS SELECT CV.clcodigo,​​PE.pecodigo,​​IP.prcodigo,​​IP.quantidade

FROM CLIENTES_VIPS​​CV,​​CLIENTES​​CL,​​PEDIDOS​​PE,​​ITENS_PEDIDOS​​IP WHERE CV.clcodigo​​=​​CL.clcodigo

AND CL.clcodigo​​=​​PE.clcodigo AND PE.pecodigo​​=​​IP.pecodigo

ORDER​​​​BY ​​CV.clcodigo,​​PE.pecodigo,​​IP.prcodigo,​​IP.quantidade; SELECT​​​​​​​​​​​​​PR.prcodigo,​​PR.descricao,​​​COUNT​(​DISTINCT​​​Q1.cvcodigo) FROM ​​​​​​​​​​​​​​​​PRODUTOS​​PR

LEFT​​​​JOIN​​​Q1

ON ​​​​​​​​​​​​​​​​PR.prcodigo​​=​​Q1.prcodigo AND ​​​​​​​​​​​​​​​​Q1.quantidade>10

2. Obtenha

​

​

o

​

​

código

​

​

e

​

​

descrição

​

​

dos

​

​

produtos

​

​

fornecidos

​

pelo

​

​

​

fornecedor

​

​

F231

​

​

e

​

que

​

​

​

não

​

​

foram

​

​

comprados

​

​

por

nenhum

​

​

cliente

​

​

vip.

Solução​​01:

SELECT​​​​​PR.prcodigo,​​PR.descricao

FROM ​​​​PRODUTOS​​PR,​FORNECE_PRODUTOS​ ​​FP,​​FORNECEDORES​​F WHERE​​​​​PR.prcodigo​​=​​FP.prcodigo

AND ​​​​FP.fcodigo​​=​​F.fcodigo AND ​​​​F.fnome​​=​​'F231'

AND ​​​​PR.prcodigo​​​NOT​​​​IN​​​(​SELECT​​​​DISTINCT​​​Q1.prcodigo ​​​​​​​​​​​​​​​​​​​​​​​FROM​​​Q1);

--Obs:​​Utilizamos​​a​​View​​Q1​​da​​questão​​anterior.

Solução​​02:

SELECT​​​P.PRCODIGO,​​P.DESCRICAO FROM​​​PRODUTOS​​P

INNER​​​​JOIN​​​FORNECE_PRODUTOS​​FP​​​ON​​​FP.PRCODIGO​​=​​P.PRCODIGO INNER​​​​JOIN​​​FORNECEDORES​​F​​​ON​​​FP.FCODIGO​​=​​F.FCODIGO

WHERE​​​F.FNOME​​=​​'F231'

AND​​​P.PRCODIGO​​​NOT​​​​IN​​​( SELECT​​​IP.PRCODIGO FROM​​​CLIENTES_VIPS​​CV

INNER​​​​JOIN​​​PEDIDOS​​PE​​​ON​​​CV.CLCODIGO​​=​​PE.CLCODIGO INNER​​​​JOIN​​​ITENS_PEDIDOS​​IP​​​ON​​​IP.PECODIGO​​=​​PE.PECODIGO);

Solução​​03:

SELECT​​​PR.prcodigo,​​PR.descricao

FROM​​​PRODUTOS​​PR,​FORNECE_PRODUTOS​ ​​FP,​​FORNECEDORES​​F WHERE​​​PR.prcodigo​​=​​FP.prcodigo

AND​​​FP.fcodigo​​=​​F.fcodigo AND​​​F.fnome​​=​​'F231' EXCEPT

SELECT​​​PR.prcodigo,​​PR.descricao

FROM​​​CLIENTES_VIPS​​CV,​​CLIENTES​​CL,​PEDIDOS​ ​​PE,​​ITENS_PEDIDOS​​IP,​​PRODUTOS​​PR WHERE​​​CV.clcodigo​​=​​CL.clcodigo

AND​​​CL.clcodigo​​=​​PE.clcodigo AND​​​PE.pecodigo​​=​​IP.pecodigo AND​​​IP.prcodigo​​=​​PR.prcodigo

3. Para

​

​

cada

​

​

PEDIDO,

​

​

obtenha

​

​

o

​

​

código

​

​

do

​

pédido,

​

​

​

nome

​

​

do

​

​

cliente

​

​

e

​

​

custo

​

​

dos

​

​

pedidos.

​

​​

​​

​​

​​

​

(obs

​

​

1:

​

​

O

​

​

custo

​

​

de

​

​

um

​

​

pedido

​

​

é

​

igual

​

​

​

ao

​

​

somatório

​

​

dos

​

​

valores

​

de

​

​

​

todos

​

​

os

​

​

itens

​

​

do

​

​

pedido)

Solução​ ​01:

SELECT​​​PE.pecodigo,​​CL.nome,​​​SUM​(IP.quantidade​*​IP.valor)​​​AS​​​Custo FROM​​​​​​​​​PEDIDOS​​PE,​​CLIENTES​​CL,​​ITENS_PEDIDOS​​IP

WHERE​​​PE.clcodigo​​=​​CL.clcodigo AND PE.pecodigo​​=​​IP.pecodigo GROUP​​​​BY​​​PE.pecodigo,​​CL.nome ORDER​​​​BY​​​PE.pecodigo;

Solução​ ​02:

SELECT​​​PE.PECODIGO,​​CL.NOME,​​​SUM​(IP.QUANTIDADE​*​IP.VALOR) FROM​​​PEDIDOS​​PE

INNER​​​​JOIN​​​CLIENTES​​CL​​​ON​​​CL.CLCODIGO​​=​​PE.CLCODIGO INNER​​​​JOIN​​​ITENS_PEDIDOS​​IP​​​ON​​​PE.PECODIGO​​=​​IP.PECODIGO GROUP​​​​BY​​​PE.PECODIGO,​​CL.NOME;

Solução​ ​03:

CREATE​​​​VIEW​​​Custo_Pedido(pecodigo,total)​​​AS SELECT​​​PE.pecodigo,​​sum(IP.valor​*​IP.quantidade) FROM​​​​​​​PEDIDOS​​PE,ITENS_PEDIDOS​​IP WHERE​​​​​PE.pecodigo​​=​​IP.pecodigo GROUP​​​​BY​​​PE.pecodigo;

SELECT​​​P.pecodigo,C.nome,CP.total

FROM​​​Custo_Pedido​​CP,​​CLIENTES​​C,​​PEDIDOS​​P WHERE​​​C.clcodigo​​=​​P.clcodigo​​​AND

​​​​​​​​​​​​P.pecodigo​​=​​CP.pecodigo

4. Obtenha

​

​

o

​

​

código

​

​

e

​

​

nome

​

​

dos

​

​

clientes

​

​

cuja

​

​

média

​

dos

​

​

​

custos

​

​

dos

​

​

seus

​

​

pedidos

​

​

é

​

​

maior

​

​

do

​

​

que

​

​

a

média

​

​

dos

​

​

pedidos.

Solução​ ​​ ​01:

CREATE​​​​VIEW​​​Q4​​(clcodigo,​nome,​ ​​pecodigo,​​custo)​​​AS

SELECT​​​​​CL.clcodigo,​​CL.nome,​​PE.pecodigo,​​​SUM​(IP.quantidade​*​IP.valor) FROM​​​​​​​​​​​PEDIDOS​​PE,​​CLIENTES​​CL,​​ITENS_PEDIDOS​​IP

WHERE​​​​​PE.clcodigo​​=​​CL.clcodigo AND ​​​​​​PE.pecodigo​​=​​IP.pecodigo

GROUP​​​​BY​​​CL.clcodigo,​​CL.nome,​​PE.pecodigo ORDER​​​​BY​​​CL.clcodigo,​​PE.pecodigo;

--Obs1:​ ​Essa​ ​view​ ​contem​ ​todos​ ​os​ ​pedidos​ ​de​ ​cada​ ​cliente,​ ​com​ ​seus​ ​respectivos​ ​custos. SELECT​​​​​​​​​​​​​Q4.clcodigo,​​Q4.nome

FROM ​​​​​​​​​​​​​​​​Q4

GROUP​​​​BY​​​Q4.clcodigo,​​Q4.nome

HAVING​​​​​​​​​​​AVG(Q4.custo)​​>​​(​SELECT​​​​AVG​(IP.valor​*​IP.quantidade)​​​​​​​FROM​​​​​​​ITENS_PEDIDOS​​IP); ORDER​​​​BY​​​Q4.clcodigo;

--​ ​Obs2:​ ​Aqui​ ​retorna​ ​quais​ ​os​ ​clientes​ ​possuem​ ​a​ ​média​ ​dos​ ​custos​ ​de​ ​seus​ ​pedidos​ ​maiores​ ​que​ ​a​ ​média​ ​geral​ ​de​ ​todos os​ ​custos​ ​de​ ​pedidos​ ​existentes.

Solução​ ​​ ​02:

CREATE​​​​VIEW​​​CUSTO_POR_PEDIDO(PECODIGO,​​CLCODIGO,​​CUSTO)​​​AS SELECT​​​PE.PECODIGO,​​CL.CLCODIGO,​​​SUM​(IP.QUANTIDADE*IP.VALOR) FROM​​​PEDIDOS​​PE

INNER​​​​JOIN​​​CLIENTES​​CL​​​ON​​​CL.CLCODIGO​​=​​PE.CLCODIGO INNER​​​​JOIN​​​ITENS_PEDIDOS​​IP​​​ON​​​PE.PECODIGO​​=​​IP.PECODIGO GROUP​​​​BY​​​PE.PECODIGO,​​CL.CLCODIGO;

SELECT​​​CL.CLCODIGO,​​CL.NOME FROM​​​CLIENTES​​CL

INNER​​​​JOIN​​​CUSTO_POR_PEDIDO​​V​​​ON​​​CL.CLCODIGO​​=​​V.CLCODIGO GROUP​​​​BY​​​CL.CLCODIGO

Solução​​​​03:

CREATE​​​​VIEW​​​VPed(pecodigo,clcodigo,​​total)​​​AS

SELECT​​​PE.pecodigo,PE.clcodigo,​SUM​(IP.quantidade​*​IP.valor) FROM​​​PEDIDOS​​PE,​​ITENS_PEDIDOS​​IP

WHERE​​​PE.pecodigo​​=​​IP.pecodigo GROUP​​​​BY​​​PE.pecodigo,​​PE.clcodigo;

SELECT​​​​DISTINCT​​​CL.clcodigo,​​CL.nome

FROM​​​CLIENTES​​CL,​​PEDIDOS​PE,​ ​​ITENS_PEDIDOS​​IP,​​VPed​​VP WHERE​​​CL.clcodigo​​=​​PE.pecodigo​AND​​ ​​​PE.pecodigo​​=​​IP.pecodigo​​​AND (​SELECT​​​​ROUND​(​AVG​(VP.total)::​numeric​,​​​​1)

FROM​​​VPed​​VP

WHERE​​​VP.clcodigo​​=​​CL.clcodigo

GROUP​​​​BY​​​VP.clcodigo)​​>​​(​SELECT​​​​ROUND​(​AVG​(VP.total)::​numeric​,​1)​ ​​​FROM​​​VPed​​VP);

5. Obtenha

​

​

o

​

​

código,

​

​

nome

​

​

e

​

​

cidade

​

​

do

​

cliente

​

​

​

que

​

​

fez

​

​

o

​

​

pedido

​

​

de

​

​

maior

​

​

custo.

Solução​​01:

SELECT​​​​​Q4.clcodigo,​​Q4.nome,​​CL.cidade FROM ​​​​​​​​Q4,​​CLIENTES​​CL

WHERE​​​​​Q4.clcodigo​​=​​CL.clcodigo

AND ​​​​​​Q4.custo​​>=​​​ALL​​​(​SELECT​​​Q4.custo​​​FROM​​​Q4);

Solução​​02:

SELECT​​​CL.CLCODIGO,​​CL.NOME,​​CL.CIDADE,​​V.CUSTO FROM​​​CLIENTES​​CL

INNER​​​​JOIN​​​CUSTO_POR_PEDIDO​​V​​​ON​​​CL.CLCODIGO​​=​​V.CLCODIGO WHERE​​​V.CUSTO​​=​​(​SELECT​​​​MAX​(CUSTO)​​​FROM​​​CUSTO_POR_PEDIDO​​V);

Solução​​03:

SELECT​​​CL.CLCODIGO,​​CL.NOME,​​CL.CIDADE FROM​​​CLIENTES​​CL

WHERE​​​CL.CLCODIGO​​​IN​​​( ​​​​​​​​​​​​​SELECT​​​V.CLCODIGO

​​​​​​​​​​​​​FROM​​​CUSTO_POR_PEDIDO​​V

​​​​​​​​​​​​​WHERE​​​V.CUSTO=(​SELECT​​​​MAX​(CUSTO)​​​FROM​​​CUSTO_POR_PEDIDO​​V)) Solução​​04:

SELECT​​​c.clcodigo,​​c.nome,​​c.cidade

FROM​​​(​SELECT​​​p.clcodigo,​​p.pecodigo,​​​SUM​​​(ip.valor*ip.quantidade)​​​AS​​​custo FROM​​​pedidos​​p

INNER​​​​JOIN​​​itens_pedidos​​ip​​on​​ip.pecodigo​​=​​p.pecodigo GROUP​​​​BY​​​p.clcodigo,​​p.pecodigo

ORDER​​​​BY​​​p.clcodigo,​​p.pecodigo)​​​AS​​​sub1

INNER​​​​JOIN​​​clientes​​c​​​ON​​​c.clcodigo​​=​​sub1.clcodigo GROUP​​​​BY​​​c.clcodigo

ORDER​​​​BY​​​​MAX​(sub1.custo) DESC​​​​LIMIT​​​1;

6.

​

​

Para

​

​

cada

​

​

PRODUTO,

​

​

obtenha

​

​

PARA

​

​

CADA

​

​

CLIENTE,

​

​

a

​

​

quantidade

​

total

​

​

​

que

​

​

esse

​

​

cliente

comprou

​

​

desse

​

​

produto,

​

​

considerando

​

​

todos

​

​

os

​

​

pedidos

​

do

​

​

​

cliente.

​

​

Na

​

​

resposta

​

​

deve

​

​

constar

​

​

todos

​

​

os

produtos

​

​

e

​

​

todos

​

​

os

​

​

clientes.

​

​

Para

​

​

os

​

​

produtos

​

​

que

​

​

não

​

​

foram

​

​

comprados

​

​

pelo

​

​

cliente,

​

​

então

​

​

a

quantidade

​

​

total

​

​

deverá

​

​

ser

​

​​

​

“zero”.

Solução​ ​01:

CREATE​​​​VIEW​​​Q6​​(clcodigo,​pecodigo,​ ​​prcodigo,​​quantidade)​​​AS SELECT​​​​​​​​​​​​​CL.clcodigo,​​PE.pecodigo,​​IP.prcodigo,​​IP.quantidade FROM​​​​​​​​​​​​​​​​​​​​​CLIENTES​​CL,​​PEDIDOS​​PE,​​ITENS_PEDIDOS​​IP WHERE​​​​​​​​​​​​​​​CL.clcodigo​​=​​PE.clcodigo

AND ​​​​​​​​​​​​​​​​PE.pecodigo​​=​​IP.pecodigo

ORDER​​​​BY​​​CL.clcodigo,​​PE.pecodigo,​​IP.prcodigo,​​IP.quantidade;

SELECT​​​​​V.prcodigo,​​V.descricao,​​V.clcodigo,​​​SUM​(Q6.quantidade) FROM ​​​​​​​​Q6

RIGHT​​​​JOIN​​​(​SELECT​​​P.prcodigo,​​P.descricao,​​CL.clcodigo ​​​​​​​​​​​​​​​​​​​​​​​​​FROM​​​PRODUTOS​​P,​​CLIENTES​​CL)​​V ​​​ON​​​Q6.prcodigo​​=​​V.prcodigo

AND​​​Q6.clcodigo​​=​​V.clcodigo

GROUP​​​​BY​​​V.prcodigo,​​V.descricao,​​V.clcodigo ORDER​​​​BY​​​V.prcodigo,​​V.descricao,​​V.clcodigo; Solução​ ​02:

SELECT​​​V.CLCODIGO,​​V.PRCODIGO,​​​SUM​(​COALESCE​(​IP​.QUANTIDADE,0)) FROM​​​ITENS_PEDIDOS​​IP

INNER​​​​JOIN​​​PEDIDOS​​PE​​​ON​​​PE.PECODIGO​​=​​IP.PECODIGO RIGHT​​​​JOIN​​​(

SELECT​​​P.PRCODIGO,​​CL.CLCODIGO FROM​​​PRODUTOS​​P,​​CLIENTES​​CL

)​​V​​​ON​​​IP.PRCODIGO​​=​​V.PRCODIGO​​​AND​​​PE.CLCODIGO​​=​​V.CLCODIGO GROUP​​​​BY​​​V.CLCODIGO,​​V.PRCODIGO

7. Obtenha

​

​

os

​

​

códigos

​

​

e

​

​

nomes

​

​

dos

​

​

clientes

​

​

que

​

​

que

​

​

compraram

​

​

todos

​

​

os

​

​

produtos

​

​

fornecidos

​

​

por

​

​

um

fornecedor

​

​

na

​

​

mesma

​

​

cidade

​

​

do

​

​

cliente.

Solução​ ​01:

SELECT​​​​DISTINCT​​​CL.CLCODIGO,​​CL.NOME FROM​​​CLIENTES​​CL

INNER​​​​JOIN​​​FORNECEDORES​​F​​​ON​​​CL.CIDADE​​=​​F.CIDADE WHERE​​​​NOT​​​​EXISTS​​​(

SELECT​​​*

FROM​​​FORNECE_PRODUTOS​​FP WHERE​​​FP.FCODIGO​​=​​F.FCODIGO AND​​​FP.PRCODIGO​​​NOT​​​​IN​​​(

SELECT​​​IP.PRCODIGO FROM​​​ITENS_PEDIDOS​​IP

INNER​​​​JOIN​​​PEDIDOS​​PE​​​ON​​​PE.PECODIGO​​=​​IP.PECODIGO WHERE​​​PE.CLCODIGO​​=​​CL.CLCODIGO

) );

Solução​ ​02:

CREATE​​​​VIEW​​​Q7​​(fcodigo,​​fcidade,​​qtdprodutos)​​​AS

SELECT​​​F.fcodigo,​​F.cidade,​​​COUNT​(​DISTINCT​​​FP.prcodigo) FROM​​​​​​​FORNECEDORES​​F,​​FORNECE_PRODUTOS​​FP WHERE​​​​​F.fcodigo​​=​​FP.fcodigo

GROUP​​​​BY​​​F.fcodigo;

CREATE​​​​VIEW​​​Q7A​​(clcodigo,​​nome,​​cidade,​​fcodigo,​​qtdprodutos)​​​AS

SELECT​​​CL.clcodigo,​​CL.nome,​​CL.cidade,​​FP.fcodigo,​​​COUNT​(​DISTINCT​​​FP.prcodigo)

FROM​​​CLIENTES​​CL,​​PEDIDOS​​PE,​​ITENS_PEDIDOS​​IP,​​PRODUTOS​​PR,​​FORNECE_PRODUTOS​​FP WHERE​​​CL.clcodigo​​=​​PE.clcodigo

AND​​​​​​​PE.pecodigo​​=​​IP.pecodigo AND​​​​​​​IP.prcodigo​​=​​PR.prcodigo AND​​​​​​​PR.prcodigo​​=​​FP.prcodigo GROUP​​​​BY​​​CL.clcodigo,​​FP.fcodigo;

SELECT​​​​DISTINCT​​​Q7A.clcodigo,​​Q7A.nome FROM​​​Q7A,​​Q7

Solução​​03:

SELECT​​​consulta1.clcodigo,​​consulta1.nome

FROM​​​((​SELECT​​​c.clcodigo,​​c.nome,​​c.cidade,​​​COUNT​(​DISTINCT​​​p.prcodigo)​​​AS​​​total ​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​FROM​​​clientes​​c​​​INNER​​​​JOIN​​​pedidos​​pe​​​ON​​​pe.clcodigo​​=​​c.clcodigo

INNER​​​​JOIN​​​itens_pedidos​​ip​​​ON​​​ip.pecodigo​​=​​pe.pecodigo INNER​​​​JOIN​​​produtos​​p​​​ON​​​p.prcodigo​​=​​ip.prcodigo GROUP​​​​BY​​​c.clcodigo,​​c.nome,​​c.cidade

ORDER​​​​BY​​​c.clcodigo,​​c.nome)​​​AS​​​consulta1

INNER​​​​JOIN​​​(​SELECT​​​f.cidade,​COUNT​​ ​(​DISTINCT​​​p.prcodigo)​​​AS​​​total_cidade FROM​​​fornecedores​​f

INNER​​​​JOIN​​​fornece_produtos​​fp​​​ON​​​fp.fcodigo​​=​​f.fcodigo INNER​​​​JOIN​​​produtos​​p​​ON​​p.prcodigo​​=​​fp.prcodigo GROUP​​​​BY​​​f.cidade

8.

​

​

Crie

​

​

a

​

​

Visão

​

​​

​​

​​

​

CLIENTES_ESPECIAIS

​

​

(

​

clcodigo

:integer,

​

​

​

nome:string,

​

​

telefone:string,

​

​

MCP:

​

​

real)

a

​

​

qual

​

​

contém

​

​

todos

​

​

os

​

​

clientes

​

que

​

​

​

satisfazem

​

​

a

​

uma

​

​

​

das

​

​

condições

​

​

abaixo:

(i)

tem

​

​

mais

​

​

de

​

​

10

​

​

pedidos

​

com

​

​

​

custo

​

​

maior

​

​

do

​

​

que

​

​

R$30.000,00.

(ii)

A

​

​

média

​

​

dos

​

​

custos

​

dos

​

​

​

seus

​

​

pedidos

​

​

é

​

​

maior

​

​

do

​

​

que

​

​

a

​

​

média

​

dos

​

​

​

pedidos

​

​

da

​

​

sua

​

​

cidade.

(**)

​

​​

​

MCP

​

​

é

​

​

a

​

​

média

​

dos

​

​

​

custos

​

​

dos

​

​​

​

pedidos

​

​

do

​

​

cliente.

Solução​​01:

CREATE​​​​VIEW​​​Q8A​​(clcodigo,​nome,​ ​​cidade,​​pecodigo,​​custo)​​​AS

SELECT​​​​​​​​​​​​​CL.clcodigo,​​CL.nome,​​CL.cidade,​​PE.pecodigo,​​​SUM​(IP.quantidade​*​IP.valor) FROM​​​​​​​​​​​​​​​​​​​CLIENTES​​CL,​​PEDIDOS​​PE,​​ITENS_PEDIDOS​​IP

WHERE​​​​​​​​​​​​​CL.clcodigo​​=​​PE.clcodigo AND​​​​​​​​​​​​​​​​​​​​​​​​​​​PE.pecodigo​​=​​IP.pecodigo GROUP​​​​BY​​​CL.clcodigo,​​PE.pecodigo ORDER​​​​BY​​​CL.clcodigo;

CREATE​​​​VIEW​​​Q8B​​(cidade,​​mcp_cidade)​​​AS SELECT​​​​​​​​​​​​​V1.cidade,​​​AVG​(V1.custo) FROM​​​​​​​​​​​​​​​​​​​​​Q8A​​V1

GROUP​​​​BY​​​V1.cidade;

CREATE​​​​VIEW​​​CLIENTES_ESPECIAIS​​(clcodigo,​​nome,​​telefone,​​mcp)​​​AS SELECT​​​CL.clcodigo,​​CL.nome,​​CL.telefone,​​​AVG​(V1.custo)

FROM​​​CLIENTES​​CL,​Q8A​ ​​V1,​​Q8B​​V2 WHERE​​​CL.clcodigo​​=​​V1.clcodigo AND​​​CL.cidade​​=​​V2.cidade GROUP​​​​BY​​​CL.clcodigo

Solução​​02:

CREATE​​​​VIEW​​​AUX_COD_1(CLCODIGO)​​​AS SELECT​​​CL.CLCODIGO

FROM​​​CUSTO_POR_PEDIDO​​V

INNER​​​​JOIN​​​CLIENTES​​CL​​​ON​​​V.CLCODIGO​​=​​CL.CLCODIGO WHERE​​​V.CUSTO​​>​​30000

GROUP​​​​BY​​​CL.CLCODIGO

HAVING​​​​COUNT​(V.PECODIGO)​​>​​10;

CREATE​​​​VIEW​​​AUX_COD_2(CLCODIGO)​​​AS SELECT​​​CL.CLCODIGO

FROM​​​CUSTO_POR_PEDIDO​​V

INNER​​​​JOIN​​​CLIENTES​​CL​​​ON​​​V.CLCODIGO​​=​​CL.CLCODIGO GROUP​​​​BY​​​CL.CLCODIGO

HAVING​​​​AVG​(CUSTO)​​>​​( SELECT​​​​AVG​(CUSTO)

FROM​​​CUSTO_POR_PEDIDO​​V2

INNER​​​​JOIN​​​CLIENTES​​CL2​​​ON​​​V2.CLCODIGO​​=​​CL2.CLCODIGO WHERE​​​CL.CIDADE​​=​​CL2.CIDADE);

CREATE​​​​VIEW​​​CLIENTES_ESPECIAIS(CLCODIGO,​​NOME,​​TELEFONE,​​MCP)​​​AS SELECT​​​CL.CLCODIGO,​​CL.NOME,​​CL.TELEFONE,​​​AVG​(V0.CUSTO)

FROM​​​CLIENTES​​CL

INNER​​​​JOIN​​​CUSTO_POR_PEDIDO​​V0​​​ON​​​V0.CLCODIGO​​=​​CL.CLCODIGO WHERE​​​CL.CLCODIGO​​​IN​​​(

SELECT​​​V.CLCODIGO​​​FROM​​​AUX_COD_1​​V) OR​​​CL.CLCODIGO​​​IN​​​(

SELECT​​​V.CLCODIGO​​​FROM​​​AUX_COD_2​​V) GROUP​​​​BY​​​CL.CLCODIGO;