Questão 748096

• Banca: CESGRANRIO
• Órgão: Petrobras
• Ano: 2012
• Provas: CESGRANRIO - 2012 - Petrobras - Analista de Sistemas Júnior - Engenharia de Software-2012
• Disciplina: Banco de Dados
• Assuntos: SQL

Considere as informações a seguir para responder às questões de nos 31 a 33. As tabelas são utilizadas para descrever um banco de dados que armazena dados sobre linhas de ônibus, motoristas e viagens por eles realizadas.

CREATE TABLE MOTORISTA (     
     MATRICULA NUMBER(7,0) NOT NULL, 
    NOME VARCHAR2(50) NOT NULL,
    CPF NUMBER(11,0) NOT NULL,  
    CNH VARCHAR2(15) NOT NULL,
    CONSTRAINT MOTORISTA_PK PRIMARY KEY (MATRICULA),
    CONSTRAINT MOTORISTA_UK1 UNIQUE (CPF),
    CONSTRAINT MOTORISTA_UK2 UNIQUE (CNH))

CREATE TABLE LINHA (
    NUMERO CHAR(5) NOT NULL,
    ORIGEM VARCHAR2(50) NOT NULL,
    DESTINO VARCHAR2(50) NOT NULL,
    CONSTRAINT LINHA_PK PRIMARY KEY (NUMERO)
)

CREATE TABLE VIAGEM (
    MAT_MOT NUMBER (7,0) NOT NULL,
    NUM_LINHA CHAR(5) NOT NULL,
    INICIO DATE NOT NULL,
    FINAL DATE,
    CONSTRAINT VIAGEM_PK PRIMARY KEY (MAT_MOT,NUM_LINHA,INICIO),
    CONSTRAINT VIAGEM_FK1 FOREIGN KEY (MAT_MOT) REFERENCES MOTORISTA (MATRICULA),
    CONSTRAINT VIAGEM_FK2 FOREIGN KEY (NUM_LINHA) REFERENCES LINHA (NUMERO))

Considere os parâmetros a seguir:

• Para o cálculo da média de viagens, devem ser levados em conta apenas os dias em que o motorista realizou pelo menos uma viagem, ao invés dos 31 dias do mês de março.

• As viagens não finalizadas não devem ser levadas em conta.

• Apenas o início da viagem precisa ocorrer no mês de março de 2012.

• A função TO_CHAR(INICIO,’DD’) retorna o dia do mês (ex: 15).

Qual consulta permite exibir o CPF do motorista e o número médio de viagens diárias que cada um deles realizou no mês de março de 2012?

• A.  SELECT M.CPF,COUNT(*)/COUNT(DISTINCT TO_CHAR(INICIO,’DD’))
            FROM VIAGEM V,MOTORISTA M
            WHERE M.MATRICULA=V.MAT_MOT AND INICIO >= ‘01-03-2012’ AND
            INICIO < ‘01-04-2012’ AND FINAL IS NOT NULL
            GROUP BY M.CPF,TO_CHAR(INICIO,’DD’)
  
  
  
• B.  SELECT M.CPF,AVG(TO_CHAR(INICIO,’DD’)) 
            FROM VIAGEM V,MOTORISTA M
            WHERE M.MATRICULA=V.MAT_MOT AND INICIO >= ‘01-03-2012’ AND
            INICIO < ‘01-04-2012’ AND FINAL IS NOT NULL
            GROUP BY M.CPF,TO_CHAR(INICIO,’DD’)
  
  
  
• C.  SELECT M.CPF,COUNT(*)/COUNT(DISTINCT TO_CHAR(INICIO,’DD’))
            FROM VIAGEM V,MOTORISTA M
            WHERE M.MATRICULA=V.MAT_MOT AND INICIO >= ‘01-03-2012’ AND
            INICIO < ‘01-04-2012’
            GROUP BY M.MATRICULA
  
  
  
• D.  SELECT M.CPF,AVG(TO_CHAR(INICIO,’DD’)) 
            FROM VIAGEM V,MOTORISTA M
            WHERE M.MATRICULA=V.MAT_MOT AND INICIO >= ‘01-03-2012’ AND
            INICIO < ‘01-04-2012’
            GROUP BY M.CPF
  
  
  
• E.  SELECT M.CPF,COUNT(*)/COUNT(DISTINCT TO_CHAR(INICIO,’DD’))
            FROM VIAGEM V,MOTORISTA M
            WHERE M.MATRICULA=V.MAT_MOT AND INICIO >= ‘01-03-2012’ AND
            INICIO < ‘01-04-2012’ AND FINAL IS NOT NULL
            GROUP BY M.CPF
  

Gabarito: E

Comentários

• alguém pode me explicar o porque a letra B está errada?
• Salvio, a letra B está errada, pois:
  
    - o agrupamento deve ser feito apenas pelo CPF, para que seja mostrado a média diária de cada motorista. Na questão B está sendo agrupado por motorista e data.
    - A função AVG(TO_CHAR(INICIO, 'DD')) está somando as datas e dividindo pela quantidade, por exemplo: se o motorista fez 4 viagens no dia 15, está retornando 15, pois ele soma o dia do mês 15 + 15 + 15 + 15 e divide por 4, resultando no próprio dia 15. Além de que a função AVG deve receber como parâmetro valores numéricos e aqui está sendo passado caracteres.
  
  A resposta E está correta, pois:
  
    - está trazendo apenas as viagens completas (FINAL IS NOT NULL).
    - está agrupando apenas pelo CPF do motorista (GROUP BY M.CPF).
    - está mostando o CPF do motorista e a média diária de viagens que o motorista fez no mês, onde COUNT(*) conta todas as viagens realizadas pelo motorista e divide por todos os dias que o motorista fez pelo menos 1 viagem COUNT(DISTINCT TO_CHAR(INICIO, 'DD')).