ATIVIDADE 4 - Disciplina BANCO DE DADOS - Curso JOGOS DIGITAIS

Prévia do material em texto

QUESTÃO 1
A criação de consultas em um banco de dados através do comando SELECT pode utilizar diversos recursos, ordenação, agrupamentos, filtros e outros. Um destes recursos, que permite consultas mais avançadas, é o uso de subconsultas em que um comando de SELECT pode conter um outro comando SELECT em alguma de suas outras cláusulas.
Em um sistema de uma loja, imagine que existe uma tabela de COMPRAS com os seguintes dados.
 
 
 
	NOME
	DATA
	VLR_COMPRA
	Renato Nogueira
	12/03/2019
	35.60
	Renato Nogueira
	21/03/2019
	120.70
	Renato Nogueira
	25/04/2019
	81.50
	Rodrigo Gonçalves
	14/03/2019
	57.80
	Rodrigo Gonçalves
	04/04/2019
	31.00
	Vanessa Aquino
	16/03/2019
	23.70
	Vanessa Aquino
	10/04/2019
	92.50
 
                        Fonte: Elaborado pelo Autor (2019)
 
Para avaliar o volume de vendas dos clientes, o setor de marketing solicitou uma listagem com os clientes e o seu volume total de compras e ordenados pelo volume de compras em ordem decrescente. A consulta que reflete esta necessidade do setor de marketing é
Resposta correta. A alternativa está correta. Perceba que a subconsulta realiza a totalização de vendas e abrevia o somatório SUM(VLR_COMPRA) como TOTAL. Este campo é o empregado na ordenação com a palavra DESC, em ordem decrescente, os maiores primeiro.   
· SELECT *
            FROM   (SELECT          NOME, AVG(VLR_COMPRA) as TOTAL
                        FROM              COMPRAS) AS VOLUME_COMPRAS             ORDER BY TOTAL DESC
· Resposta correta:
SELECT *
            FROM   (SELECT          NOME, SUM(VLR_COMPRA) as TOTAL
                        FROM              COMPRAS) AS VOLUME_COMPRAS             ORDER BY TOTAL DESC
· SELECT *
            FROM   (SELECT          NOME, SUM(VLR_COMPRA) as TOTAL
                        FROM              COMPRAS) AS VOLUME_COMPRAS             ORDER BY VLR_COMPRA ASC
· SELECT *
            FROM   (SELECT          NOME, SUM(VLR_COMPRA) as TOTAL
                        FROM              COMPRAS) AS VOLUME_COMPRAS             ORDER BY VLR_COMPRA DESC
· SELECT *
            FROM   (SELECT          NOME, AVG(VLR_COMPRA) as TOTAL
                        FROM              COMPRAS) AS VOLUME_COMPRAS             ORDER BY VLR_COMPRA DESC
QUESTÃO 2
O comando SELECT é bastante versátil e por meio das suas cláusulas de WHERE, ORDER BY, GROUP BY, JOIN permite especificar filtros, ordenações, agrupamentos e junções respectivamente. Estes e outros recursos oferecem suporte ao usuário para escrever suas consultas.
Em relação a algumas das cláusulas, comandos e palavras chave para escrita dos comandos, realize a associação entre os itens a seguir e as afirmações na sequência:
 
I.     HAVING
II.    DESC
III.   AVG
IV.  LEFT
 
Associe as funções de agregação com as afirmações sobre estes comandos:
 
(     ) Permite especificar qual o tipo de junção deve ser executada
(     ) Estabelece ordem decrescente na cláusula ORDER BY
(     ) Função de agregação que pode ser usada com GROUP BY
(     ) Permite editar filtros em consultas com GROUP BY
 
Assinale a alternativa que apresenta a sequência correta:
Resposta correta. A alternativa está correta. Comentando na sequência. O item IV, LEFT é usado junto com OUTER JOIN (LEFT OUTER JOIN) para especificar um certo tipo de junção. O item II (DESC) é usado com os campos na cláusula ORDER BY para especificar o ordenação decrescente. Caso a ordenação seja crescente, a palavra seria ASC. Item III (AVG) é de fato uma função de agregação para cálculo de média. E o item I (HAVING) permite filtrar valores provenientes de cálculo de função agregada em consultas que possuem o group by.
· II, III, I, IV
· Resposta correta:
IV, II, III, I
· III, I, IV, II
· I, III, II, IVI
· I, IV, II, III
QUESTÃO 3
O banco de dados serve de fonte para atender às diversas demandas de setores informatizados por um determinado sistema, por exemplo, um setor de recursos humanos pode recuperar o total da folha de pagamento, um setor de vendas pode requisitar a quantidade de produtos vendidos por categoria e um setor de marketing pode requisitar alguns relatórios sobre os clientes.
Em um sistema de locação de automóveis, a tabela de cliente apresenta o seguinte formato e com uma breve amostra de dados. 
 
	Cod
	Nome
	Sexo
	Idade
	Cidade
	2
	Ademar
	M
	22
	São Paulo
	1
	Fabrício
	M
	41
	Salvador
	5
	Joelma
	F
	60
	Rio de Janeiro
	4
	Carlos
	M
	21
	São Paulo
	3
	Adriana
	F
	30
	Curitiba
 
O setor de marketing está avaliando as regiões onde a empresa atua e o volume de clientes em cada região. Daí, surgiu a necessidade de recuperar as cidades nesta tabela e para cada cidade, o total de clientes existentes. A consulta que melhor atende à esta necessidade é:
Resposta correta. A alternativa está correta. Para atender a esta demanda de para cada cidade recuperar a quantidade de clientes é necessário realizar uma consulta com agrupamento (CROUP BY), no caso um agrupamento por cidade e contabilizando (COUNT) valores de outros campos. 
· SELECT        CIDADE, SUM(COD)                 FROM CLIENTE           GROUP BY CIDADE
· SELECT       CIDADE, COUNT(COD)            FROM CLIENTE           ORDER BY CIDADE 
· Resposta correta:
SELECT      CIDADE, COUNT(COD)            FROM CLIENTE           GROUP BY CIDADE
· SELECT       CIDADE, NOME                        FROM CLIENTE           ORDER BY CIDADE
· SELECT       CIDADE, SUM(NOME)              FROM CLIENTE           GROUP BY CIDADE
QUESTÃO 4
Em um servidor de dados, os dados estão armazenados em tabelas e pelas questões de normalização, cada tabela trata especificamente dados relacionados com suas respectivas entidades. Quando ocorre a necessidade por informações mais abrangentes, os dados de tabelas diferentes precisam compor um resultado conjunto e o mecanismo que permite combinar dados de várias tabelas são as junções que podem ser de vários tipos.
Considerando uma parte do modelo de um sistema de clínica médica, referente aos médicos e especialidades, tem-se as seguintes tabelas:
           
MEDICO (COD_MEDICO, CRM, NOME, CONTATO, COD_ESPECIALIDADE)
            ESPECIALIDADE (COD_ESPECIALIDADE, DCR_ESPECIALIDADE)
 
Todo médico está associado a uma especialidade, mas na clínica podem existir especialidades que não possuem médicos. A consulta que recupera todas as especialidades com os médicos relacionados, recuperando mesmo as especialidades sem médico cadastrado é:
Resposta correta. A alternativa está correta. As tabelas escritas no join estão na sequencia Medico primeiro (esquerda) e Especialidade depois (direita) e a junção foi feita pela direita (RIGHT OUTER JOIN), portanto usando a tabela especialidade como base para junção. 
· SELECT       *           FROM   MEDICO as M LEFT OUTER JOIN ESPECIALIDADE as E     ON M.COD_ESPECIALIDADE = E.COD_ESPECIALIDADE
· SELECT       *           FROM   MEDICO as M FULL JOIN ESPECIALIDADE as E     ON M.COD_ESPECIALIDADE = E.COD_ESPECIALIDADE
· Resposta correta:
SELECT      *           FROM   MEDICO as M RIGHT OUTER JOIN ESPECIALIDADE as E     ON E.COD_ESPECIALIDADE = M.COD_ESPECIALIDADE
· SELECT        *           FROM   ESPECIALIDADE as E RIGHT OUTER JOIN MEDICO as M     ON M.COD_ESPECIALIDADE = E.COD_ESPECIALIDADE
· SELECT       *           FROM   MEDICO as M INNER JOIN ESPECIALIDADE as E     ON M.COD_ESPECIALIDADE = E.COD_ESPECIALIDADE
QUESTÃO 5
As funções de agregação são úteis para contabilizar, totalizar ou avaliar registros em consultas construídas com o comando SELECT, com ou sem a cláusula GROUP BY. Quando usada sem agrupamentos, considera todos os registros dentro de um conjunto de resultados. Quando usada com group by, resumem os registros de um agrupamento em um valor conforme o objetivo da função.
Em relação às funções de agregação, realize a associação de cada uma delas com as afirmações na sequência:
 
I.    SUM
II.    COUNT
III.   AVG
IV.   MAX
 
Associe as funções de agregação com as afirmações sobre estes comandos
 
(     ) Podem ser aplicadas tendo como parâmetro o “*”
(     ) Retorna o maior valor de um conjunto de dados
(     ) Usadapara totalizar valores
(     ) Usada para o cálculo de média de valores
 
 
Assinale a alternativa que apresenta a sequência correta:
Resposta correta. A alternativa está correta. Comentando na sequência. O item II, COUNT pode de fato ser usado na forma count(*) pois ele realiza uma contagem de registros.O item IV, MAX, de fato calcula o valor máximo dentro de um conjunto de valores. SUM (Item I) é usado para totalizar, fazer somatório de valores. E AVG (item III) é uma abreviação de average, que representa a média de um conjunto de valores.
· Resposta correta:
II, IV, I, III
· I, IV, II, III
· II, I, IV, III
· III, I, II, IV
· III, IV, I, II
QUESTÃO 6
Dados em tabelas diferentes podem compor um conjunto de resultados em um comando de SELECT através das junções. Alguns tipos de junções são as junções internas e as junções externas, estas últimas podem ser pela esquerda ou pela direita.
Em um sistema de uma universidade existe o cadastro de Alunos e o de Cursos, onde em aluno pode conter apenas um curso e em curso pode conter vários cursos. Em uma consulta foi feita uma junção (join) entre as tabelas Alunos e Cursos e na consulta SELECT, Alunos e Cursos estão escritas nesta ordem (Alunos primeiro, antes de Cursos). O resultado da consulta está expresso na tabela a seguir:
	Matrícula
	Nome
	Cod_Curso
	Dcr_Curso
	322871
	Joana Vieira
	2
	Engenharia Elétrica
	322609
	Márcio Nogueira
	2
	Engenharia Elétrica
	212320
	Cristiano Azevedo
	12
	Direito
	212201
	Lúcia Vergamini
	12
	Direito
	NULL
	NULL
	4
	Gestão Ambiental
Quadro - Resultado de consulta com junção entre Alunos e Cursos
Fonte: Elaborado pelo autor (2019).
Qual foi a junção realizada nesta consulta?
Resposta correta. A resposta está correta pois perceba que na última linha existe o curso Gestão Ambiental que não possui aluno correspondente, e o curso está a direita na escrita do SELECT porque aparece depois de aluno (está à esquerda). E se o campo de aluno ficou NULL é por que os valores direita prevaleceram (right outer join).
· Inner Join
· Join
· Full Join
· Left Outer Join
· Resposta correta:
Right Outer Join
QUESTÃO 7
Os bancos de dados permitem que dados básicos, provenientes dos processos operacionais de um negócio, possam ser processados e sumarizados para se obter informações de nível gerencial e estratégico. Por exemplo, a quantidade de carros locados por semana em uma locadora, a quantidade de alunos matriculados em uma escola, além de outras situações ilustram o que pode ser processado facilmente com consultas via SELECT.
Uma determinada Fintech realiza empréstimos para pessoas de uma comunidade e a tabela EMPRESTIMO mostrada a seguir apresenta os registros de empréstimos realizados:
	CPF
	Data
	Sexo
	Valor
	02311220012
	10/04/2019
	M
	R$ 1.200,00
	91311770028
	13/04/2019
	F
	R$ 3.500,00
	84911560025
	21/04/2019
	F
	R$ 4.100,00
	73923560458
	23/04/2019
	M
	R$ 1.000,00
	64821569228
	02/05/2019
	M
	R$ 1.600,00
 
A consulta que calcula o total de empréstimo em reais realizado pela Fintech é
Resposta correta. A alternativa está correta pois basta que seja usada a função SUM associada ao campo VALOR totalizando toda a tabela (sem group by).  
· SELECT       count(valor)       from  EMPRESTIMO
· SELECT       SUM(valor)       from  EMPRESTIMO GROUP BY SEXO
· Resposta correta:
SELECT      SUM(valor)       from  EMPRESTIMO
· SELECT       count(valor)       from  EMPRESTIMO GROUP BY SEXO
· SELECT       AVG(valor)        from  EMPRESTIMO
QUESTÃO 8
Uma cláusula “group by”, parte do comando SELECT, permite criar agrupamentos, isto é, vários registros com os mesmos valores são combinados em uma linha e os valores dos outros campos podem ser resumidos através de funções de agregação, gerando cálculo de totais, contagens, médias e outros. 
Em um sistema de livraria, existe uma tabela LIVRO que contém os seguintes campos:
           
LIVRO (COD_LIVRO, TITULO, AUTOR, VALOR)
Para recuperar a lista dos autores com o total de títulos de sua autoria presentes na livraria e o valor médio dos valores de seus títulos, a consulta deve ser:
Resposta correta. A alternativa está correta pois o agrupamento (group by) ocorre por AUTOR que é o campo junto ao SELECT e é requisitado contagem e média por autor tratadas com as funções de contagem (COUNT(*) ) e a média (AVG(VALOR) ) aplicadas corretamente.
· SELECT       TITULO, SUM(*), AVG(VALOR)                         FROM LIVRO GROUP BY TITULO
· SELECT       AUTOR, COUNT(*), MEDIA(VALOR)     FROM LIVRO GROUP BY AUTOR
· Resposta correta:
SELECT      AUTOR, COUNT(*), AVG(VALOR)         FROM LIVRO GROUP BY AUTOR
· SELECT       TITULO, COUNT(*), AVG(VALOR)         FROM LIVRO GROUP BY TITULO
· SELECT       AUTOR, SUM(*), MEDIA(VALOR)          FROM LIVRO GROUP BY AUTOR
QUESTÃO 9
As views permitem simplificar a forma como as consultas podem ser feitas pois uma view abstrai a complexidade das consultas, por exemplo consultas com muitas junções, funções de agregação e outras. Daí, uma consulta utiliza a view como se fosse uma tabela virtual cuja definição é baseada na consulta mais complexa.
 
Em um determinado sistema de compras, foi criada a seguinte view. 
CREATE VIEW     functionbodyOnloadHandler(){ if(typeof(parent.feedHtmlOnLoadFn)=='function'){ parent.feedHtmlOnLoadFn(this); } returntrue; } window.onload=bodyOnloadHandler; return; " /> COMPRAS_CLIENTES_VW  
AS          (NOME,                 BAIRRO,       DATA_NASC,         SEXO,                COD_CIDADE,    CIDADE,       
DATA_COMPRA,     VALOR)  AS   SELECT             CLI.NOME,                    CLI.BAIRRO,                 CLI.DATA_NASC,          
CLI.SEXO,             CID.COD_CIDADE,       CID.DCR_CIDADE,              CMP.DATA_COMPRA,  CMP.VLR_COMPRA FROM             
CLIENTE CLI                INNER JOIN     COMPRA CMP                       ON CLI.COD_CLIENTE = CMP.COD_CLIENTE                
INNER JOIN     CIDADE AS CID                       ON CLI.COD_CIDADE = CID.COD_CIDADE
Assinale qual sentença apresenta uma consulta correta feita com o uso da View.
Resposta correta. A alternativa está correta pois o campo NOME está definido como um dos campos da VIEW no cabeçalho do CREATE VIEW e o count(*) vai contar quantas compras por cliente, via recurso de GROUP BY.   
· SELECT  NOME, VLR_COMPRA   FROM   COMPRAS_CLIENTES_VW
· Resposta correta:
SELECT  NOME, COUNT(*)           FROM   COMPRAS_CLIENTES_VW GROUP BY NOME
· SELECT  NOME, VLR_COMPRA   FROM   CLIENTE
· SELECT  NOME, CIDADE              FROM   CLIENTE
· SELECT  NOME, DCR_CIDADE    FROM   COMPRAS_CLIENTES_VW
QUESTÃO 10
A modelagem de um banco de dados permite compreender como as tabelas estão relacionadas entre si, permite identificar chaves primárias e as chaves estrangeiras envolvidas nos relacionamentos e assim serve de suporte para construção de consultas, especificamente para saber quais campos usar em junções.
 
Em um banco de dados, a parte referente ao cadastro de produtos associado ao itens de compras (tabela PRODUTO_COMPRA) está mostrado a seguir.
            Fonte: Elaborado pelo Autor (2019)
 
Em certo setor da empresa, houve a necessidade de recuperar todos os produtos (com sua descrição e a descrição da sua categoria) que possuem compra e apenas aqueles que de fato possuem compras associadas e que possuem a categoria cadastrada. A cláusula de junção após a palavra chave FROM na consulta deve ser:
Resposta correta. A alternativa está correta pois pela especificação da consulta, que envolve recuperar todos os produtos e apenas os produtos que possuem compras associadas e com as categorias cadastradas deve ser expressa como INNER JOIN tanto entre as tabelas CATEGORIA e PRODUTO quanto também com PRODUTO_COMPRA e que está sendo feita nesta resposta.
· FROM           (CATEGORIA as C LEFT OUTER JOIN PRODUTO as P
.ON C.COD_CATEGORIA = P.COD_CATEGORIA)
INNER JOIN PODUTO_COMPRA AS PC ON P.COD_PRODUTO = PC.COD_PRODUTO)
· FROM           (CATEGORIA as C INNER JOIN PRODUTO as P
.ON C.COD_CATEGORIA = P.COD_CATEGORIA)
LEFT OUTER JOIN PODUTO_COMPRA AS PC ON P.COD_PRODUTO = PC.COD_PRODUTO)· FROM           (CATEGORIA as C RIGHT OUTER JOIN PRODUTO as P
.ON C.COD_CATEGORIA = P.COD_CATEGORIA)
RIGHT OUTER JOIN PODUTO_COMPRA AS PC ON P.COD_PRODUTO = PC.COD_PRODUTO)
· FROM           (CATEGORIA as C RIGHT OUTER JOIN PRODUTO as P
.ON C.COD_CATEGORIA = P.COD_CATEGORIA)
INNER JOIN PODUTO_COMPRA AS PC ON P.COD_PRODUTO = PC.COD_PRODUTO)
· Resposta correta:
FROM         (CATEGORIA as C INNER JOIN PRODUTO as P
.ON C.COD_CATEGORIA = P.COD_CATEGORIA)
INNER JOIN PODUTO_COMPRA AS PC ON P.COD_PRODUTO = PC.COD_PRODUTO)