Questão 1117495

• Banca: CESGRANRIO
• Órgão: FINEP
• Ano: 2014
• Provas: CESGRANRIO - 2014 - FINEP - Analista - Desenvolvimento de Sistemas
• Disciplina: Banco de Dados
• Assuntos: SQL

As tabelas abaixo pertencem ao esquema de um banco de dados de um supermercado.

CREATE TABLE PRODUTO (
       COD NUMBER(5) NOT NULL,
       DESCRICAO VARCHAR2(100) NOT NULL,
       PRECO NUMBER(8,2) NOT NULL,
       QTD_ESTOQUE NUMBER(5) ,
       TIPO NUMBER(1) NOT NULL,
       CONSTRAINT PRODUTO_PK PRIMARY KEY (COD))

CREATE TABLE ITEM (
       NUM_SERIE NUMBER(7) NOT NULL,
       COR VARCHAR2(20) NOT NULL,
       VOLTAGEM NUMBER(5) NOT NULL,
       COD_PROD NUMBER(5) NOT NULL,
       CONSTRAINT ITEM_PK PRIMARY KEY (NUM_SERIE),
       CONSTRAINT ITEM_FK FOREIGN KEY (COD_PROD)
              REFERENCES PRODUTO (COD))

Observações:
• A empresa comercializa produtos controlados por quantidade (por exemplo, caixa de sabão em pó, pacote de biscoito e lata de extrato de tomate) e produtos controlados por unidade (por exemplo, televisor, máquina de lavar roupa e liquidificador).

• A quantidade em estoque de um produto controlado por quantidade (TIPO=1) é obtida diretamente da coluna QTD_ESTOQUE.

• A quantidade em estoque de um produto controlado por unidade (TIPO=2) NÃO pode ser obtida diretamente da coluna QTD_ESTOQUE, pois, para esse tipo de produto, esta coluna irá conter o valor NULL.

• Cada linha da tabela ITEM contém informações sobre um item existente no estoque da empresa relativo a um tipo de produto controlado por unidade.

Qual consulta SQL irá exibir o código, a descrição e a quantidade em estoque relativos a cada um dos produtos comercializados pelo supermercado?

• A. SELECT COD, DESCRICAO, QTD_ESTOQUE
  FROM PRODUTO
  WHERE TIPO = 1
  UNION
  SELECT P.COD, P.DESCRICAO, COUNT(I.COD_PROD)
  FROM PRODUTO P,ITEM I
  WHERE TIPO = 2 AND P.COD=I.COD_PROD
  GROUP BY P.COD, P.DESCRICAO
• B. SELECT COD, DESCRICAO, QTD_ESTOQUE
  FROM PRODUTO
  WHERE TIPO = 1
  UNION
  SELECT P.COD, P.DESCRICAO, COUNT(I.COD_PROD)
  FROM PRODUTO P
  LEFT JOIN ITEM I
  ON P.COD=I.COD_PROD
  WHERE P.TIPO = 2
  GROUP BY P.COD, P.DESCRICAO
• C. SELECT P.COD, P.DESCRICAO, COUNT(DISTINCT P.TIPO)
  FROM PRODUTO P
  LEFT OUTER JOIN ITEM I
  ON P.COD=I.COD_PROD
  GROUP BY P.COD, P.DESCRICAO
• D. SELECT P.COD, P.DESCRICAO, SUM (DISTINCT P.TIPO)
  FROM PRODUTO P
  INNER JOIN ITEM I
  ON P.COD=I.COD_PROD
  GROUP BY P.COD, P.DESCRICAO
• E. SELECT COD, DESCRICAO, QTD_ESTOQUE
  FROM PRODUTO
  WHERE TIPO = 1
  UNION
  SELECT P.COD, P.DESCRICAO, COUNT(I.COD_PROD)
  FROM PRODUTO P
  RIGHT JOIN ITEM I
  ON P.COD=I.COD_PROD
  WHERE P.TIPO = 2
  GROUP BY P.COD,P.DESCRICAO

Gabarito: B

Comentários

• Questão bem interessante! Veja que temos produtos controlados por quantidade (tipo = 1) e produtos controlados por unidade (tipo = 2). Eles seguem lógicas distintas no registro de estoque - os produtos controlados por quantidade podem ter seu estoque extraído da própria tabela Produto, mas os produtos controlados por unidade requerem que se realize uma contagem de registros associados na tabela Item. Dada essa diferença, é interessante que usemos o UNION/UNION ALL e separemos as duas consultas. Vejamos:

  - Os produtos controlados por quantidade (tipo = 1) já têm a quantidade em estoque diretamente registrada em um atributo chamado QTD_ESTOQUE. Para estes, basta realizarmos a seguinte consulta:

  _SELECT COD, DESCRICAO, QTD_ESTOQUE

  _FROM PRODUTO

  _WHERE TIPO = 1

  - Já para os produtos controlados por quantidade (tipo = 2) requerem que associemos a tabela produto com a tabela item, através de uma junção à esquerda (pois pode haver produtos que não têm nenhuma unidade em estoque), e posteriormente realizemos o agrupamento por produto (GROUP BY) para realizar a contagem de registros associados na tabela item (função COUNT). Vai ficar assim:

  _SELECT P.COD, P.DESCRICAO, C OUNT(I.COD_PROD)

  _FROM PRODUTO P LEF JOIN ITEM I ON P.COD=I.COD_PROD

  _WHERE TIPO = 2

  _GROUP BY P.COD, P.DESCRICAO

  Para unirmos os resultados de ambas as consultas, obtendo assim o estoque de todos os produtos (tipos 1 e 2) da tabela, basta utilizarmos um UNION. Poderíamos também utilizar um UNION ALL e o resultado seria o mesmo, já que o atributo COD é chave primária de Produto, o que faz com que todas as tuplas do resultado sejam distintas entre si. Resposta letra B!