T1.DPTO_ID = D.DPTO_ID GROUP BY
35. Assuntos relacionados: Banco de Dados, Consulta SQL, Operações de Composição de Relações,
Banca: Cesgranrio Instituição: BNDES
Cargo: Analista de Sistemas - Desenvolvimento Ano: 2008
Questão: 42
Observe as seguintes tabelas de um sistema: Pessoa (nomePessoa, endereço)
Filme (nomeFilme, estúdio, verba)
Trabalha (nomePessoa(FK), nomeFilme(FK), atividade, salário)
Um administrador de dados deseja fazer o seguinte relatório: Todas as pessoas, os lmes nos quais já trabalharam e o total recebido por cada lme. Se uma pessoa nunca trabalhou num lme, ela deve aparecer no relatório. A atividade que a pessoa exerceu no lme (ex: ator, diretor, câmera etc.) não é importante para o relatório.
Qual consulta retorna o conjunto resposta correto?
(a). SELECT P.nomePessoa, T.nomeFilme, sum(T.salário) total
FROM Pessoa P LEFT OUTER JOIN Trabalha T ON P.nomePessoa = T.nomePessoa
GROUP BY P.nomePessoa, T.nomeFilme
(b). SELECT T.nomePessoa, T.nomeFilme, sum(T.salário) total
FROM Pessoa P INNER JOIN Trabalha T ON P.nomePessoa = T.nomePessoa GROUP BY T.nomePessoa, T.nomeFilme
(c). SELECT P.nomePessoa, T.nomeFilme, sum(T.salário) total
FROM Pessoa P LEFT OUTER JOIN Trabalha T ON P.nomePessoa = T.nomePessoa
GROUP BY P.nomePessoa, T.nomeFilme HAVING sum(T.salário) > 0
(d). SELECT P.nomePessoa, F.nomeFilme, sum(T.salário) total
FROM Pessoa P INNER JOIN Trabalha T ON P.nomePessoa = T.nomePessoa INNER JOIN Filme F
ON F.nomeFilme = T.nomeFilme WHERE T.atividade IS NOT NULL AND T.salário > 0
GROUP BY P.nomePessoa, F.nomeFilme
(e). SELECT T.nomePessoa, T.nomeFilme, sum(T.salário) total
FROM Pessoa P LEFT OUTER JOIN Trabalha T ON P.nomePessoa = T.nomePessoa
ON F.nomeFilme = T.nomeFilme Solução:
Esta questão tenta explorar o conhecimento do candidato em relação às operações INNER JOIN e LEFT OUTER JOIN devido à restrição se uma pessoa nunca trabalhou num lme, ela deve aparecer no relatório.
A operação INNER JOIN é usada tipicamente na cláusula FROM para composição de relações. Sua sintaxe é <tabela A> INNER JOIN <tabela B> ON <condição desejada>. A operação INNER JOIN cria uma nova tabela, combinando os valores das duas tabelas (tabela A e tabela B) com base na condição desejada de junção das tabelas. A consulta com INNER JOIN compara cada linha da tabela A com cada linha da tabela B para encontrar todos os pares de linhas que satisfazem a condição desejada. Quando a condição desejada é satisfeita, valores da coluna para cada par de linha encontrado das tabelas A e B são combinados em uma linha da nova tabela. Note que, a operação INNER JOIN retorna as linhas das tabelas somente quando a condição desejada é satisfeita, isto é, se na tabela A existe uma linha que não satisfaz a condição desejada, esta linha não aparece no resultado da operação INNER JOIN.
Como na operação INNER JOIN, a operação LEFT OUTER JOIN também é usada ti- picamente na cláusula FROM para composição de relações. Sua sintaxe é <tabela A> LEFT OUTER JOIN <tabela B> ON <condição desejada>. O LEFT OUTER JOIN é processado conforme mostrado anteriormente para o INNER JOIN. Entretanto, o LEFT OUTER JOIN retorna todas as linhas da tabela da esquerda (tabela A), mesmo se a condi- ção desejada não encontrar nenhuma linha correspondente na tabela da direita (tabela B). Ou seja, o LEFT OUTER JOIN retorna todas linhas da tabela da esquerda, mais as linhas da tabela da direita que satisfazem a condição desejada ou, nulo caso não satisfaça. Se a tabela da esquerda retorna uma linha e a tabela da direita retornar mais que uma linha para a condição desejada, a linha da tabela esquerda será repetida para cada linha encontrada da tabela da direita.
Existem outras operações relacionadas à cláusula JOIN, como:
• EQUI-JOIN - mesma funcionalidade do operador INNER JOIN, mas aceita somente comparação de igualdade na condição desejada
• NATURAL JOIN - uma especialização da operação EQUI-JOIN, porém a condição desejada está implícita na operação. Por exemplo, SELECT * FROM employee NA- TURAL JOIN department
• CROSS JOIN - retorna o produto cartesiano do conjunto de linhas das tabelas asso- ciadas. É equivalente à operação INNER JOIN, onde a condição desejada é sempre verdadeira
• RIGHT OUTER JOIN - retorna todas as linhas da tabela da direita, mesmo se a condição desejada não encontra nenhuma linha correspondente na tabela da esquerda • FULL OUTER JOIN é uma combinação dos tipos das operações LEFT OUTER JOIN
e RIGHT INNER JOIN, isto é, ao resultado nal são adicionadas as linhas da tabela esquerda que não correspondem a nenhuma linha da tabela direita, e similarmente para as linhas da tabela da direita
Conforme explicado anteriormente, para a nossa consulta retorna o conjunto de resposta correto, devemos utilizar a operação LEFT OUTER JOIN para satisfazer a restrição se uma
pessoa nunca trabalhou num lme, ela deve aparecer no relatório, pois existe a possibilidade uma pessoa nunca ter trabalho em um lme. Então, a operação LEFT OUTER JOIN deve ser entre as tabelas P essoa e T rabalha, onde a tabela P essoa é a tabela esquerda, isto é, a consulta SQL deve conter a seguinte trecho: Pessoa P LEFT OUTER JOIN Trabalha T ON P.nomePessoa = T.nomePessoa. A seguir, analisamos as alternativas desta questão: (A) CORRETA
Esta consulta retorna o conjunto de resposta correto, pois faz uso da operação LEFT OU- TER JOIN entre as tabelas P essoa e T rabalha na cláusula FROM, sendo que a tabela P essoa está do lado esquerdo da operação, satisfazendo a restrição do enunciado. Além disso, esta consulta utiliza a cláusula GROUP BY para formar grupos de P.nomeP essoa e T.nomeP essoa. Isso é utilizado para somar os salários das pessoas que trabalharam nos lmes na cláusula SELECT (sum(T.salário)). E, por m, esta consulta exibe o resultado na cláusula SELECT P.nomePessoa, T.nomeFilme, sum(T.salário) total como o enunciado solicitou. Portanto, alternativa correta.
(B) ERRADA
Esta consulta não retorna o conjunto de resposta correto, pois faz uso da operação INNER JOIN na cláusula FROM, conforme explicado anteriormente. Então, alternativa errada. (C) ERRADA
Esta consulta é semelhante à consulta da alternativa (A), com a diferença do uso da cláusula HAVING com a condição sum(T.salário) > 0. A utilização desta condição na consulta, não satisfaz a restrição imposta pelo enunciado (se uma pessoa nunca trabalhou num lme, ela deve aparecer no relatório), pois o resultado nal não conterá as pessoas que nunca traba- lharam em um lme. Portanto, alternativa errada.
(D) ERRADA
Esta consulta não retorna o conjunto de resposta correto, pois faz uso da operação INNER JOIN na cláusula FROM, conforme explicado anteriormente. Então, alternativa errada. (E) ERRADA
Esta consulta faz uso duas vezes da operação LEFT OUTER JOIN. Uma nova tabela (tabela A) é produzida pela operação Pessoa P LEFT OUTER JOIN Trabalha T ON P.nomePessoa = T.nomePessoa. Esta nova tabela (tabela A) é utilizada para gerar outra nova tabela (tabela B) pela operação tabela A LEFT OUTER JOIN Filme F ON F.nomeFilme = T.nomeFilme. Observe que esta última operação é desnecessária, pois com a primeira ope- ração é possível gerar o conjunto resposta correto. A alternativa está errada, pois não utiliza a cláusula GROUP BY para formar grupos P.nomeP essoa e T.nomeP essoa para somar os salários das pessoas que trabalharam nos lmes na cláusula SELECT (sum(T.salário)).
36. Assuntos relacionados: Banco de Dados, SQL, JOIN, HAVING, Banca: Cesgranrio
Instituição: BNDES
Cargo: Analista de Sistemas - Desenvolvimento Ano: 2008
Questão: 49
Seja o seguinte grupo de tabelas de um sistema: fabricante (idfabricante, nome, endereço) peça (idmodelo, nome, descrição)
constrói (idmodelo (FK), idfabricante (FK), data, quantidade, cor) Um programador monta a seguinte consulta SQL:
SELECT f.nome, count(distinct c.idmodelo) as num FROM fabricante f \textbf{INNER JOIN} constroi c ON f.idfabricante = c.idfabricante
WHERE c.cor = 'VERMELHO' GROUP BY f.nome
HAVING count(distinct c.idmodelo) > 10 ORDER BY num DESC
Qual o retorno dessa consulta?
(a). Os nomes dos fabricantes e a respectiva quantidade total de peças construídas na cor vermelha, desde que, em cada data, a quantidade construída seja maior que 10; o relatório estará ordenado de forma descendente pela quantidade de peças construídas.
(b). Os nomes dos fabricantes que já construíram mais de 10 modelos diferentes de peças na cor vermelha e a quantidade de modelos diferentes, mostrando a lista ordenada de forma descendente pela quantidade.
(c). Os nomes dos fabricantes que já construíram pelo menos uma peça na cor verme- lha; o relatório estará ordenado de forma descendente na quantidade de modelos diferentes construídos.
(d). Todos os nomes dos fabricantes e a respectiva quantidade de modelos diferentes de peças vermelhas que já construíram; se um fabricante nunca construiu uma peça na cor vermelha, a contagem mostrará zero.
(e). Todos os nomes dos fabricantes e a respectiva quantidade de modelos diferentes construídos, não importando a quantidade de peças, cor ou a data da construção; o relatório estará ordenado de forma descendente na quantidade de peças.
Solução:
Para resolver esta questão, é necessário conhecer os conceitos de chave primária, chave es- trangeira e, por m, entender o que os principais comandos SQL fazem e o resultado deles sobre os dados manipulados de uma ou mais tabelas.
Por ser mais essencial, vamos começar entendendo o que são chave primária e estrangeira. Tabelas são coleções de dados que possuem identidade entre si pela semântica. Isso signi ca
que as linhas (ou tuplas) de uma tabela referem-se a coisas que guardam semelhança entre si. Entretanto, apesar das semelhanças, cada tupla é única e deve ser assim, para que os dados armazenados façam sentido à aplicação e ao usuário. Para garantir essa unicidade, foi desenvolvido o conceito de chave primária nos bancos de dados. A chave primária pode ser composta por um ou mais campos da tabela. O SGBD (Sistema Gerenciador de Banco de Dados) irá garantir que haverá apenas uma tupla com uma determinada combinação dos campos que compõe a chave primária.
Ilustrando como exemplo, podemos supor uma tabela que armazene os produtos de uma mercearia. A tabela, poderia ser:
produto (nome, quantidade, precoUnitario)
O campo nome foi utilizado como chave primária. Sabemos disso, pois, por convenção, os campos de uma tabela que aparecem sublinhados são os considerados como chave pri- mária. Nesse exemplo, o SGBD, ao tratar as operações de inserção permitiria que cada produto aparecesse uma vez na tabela. Isso quer dizer que, se por descuido, o usuário já tivesse cadastrado o produto 'banana' e tentasse cadastrar novamente 'banana' o sistema receberia um erro por chave duplicada. Repare que isso é extremamente útil. Na hora de fazer a contabilidade e de gerar relatórios de reposição de estoque, 'banana' é um único produto da mercearia e não queremos que ela apareça repetidamente.
Para entender o conceito de chave estrangeira, podemos utilizar o exemplo da mercearia também. Sabemos que, além do cadastro do estoque, o comerciante precisa manter o his- tórico de suas vendas, de modo que possa atualizar sua contabilidade e prestar contas ao governo. Para tal, podemos supor que o desenvolvedor do aplicativo de controle comercial tenha criado uma tabela que irá registrar todas as vendas feitas. Para isso, ele criará as tabelas:
venda (idVenda, data)
itemVenda (idVenda (FK), nomeProduto (FK), quantidade)
Veja que a tabela venda possui também um campo de chave primária. Como vendas não possuem nomes, podemos imaginar que esse campo pode ser numérico e sequencial, de modo a impedir que vendas sejam sobrescritas. A tabela itemVenda é criada por um motivo muito simples: repare que cada venda poderá ter mais de um item nela; o freguês pode levar, na mesma compra, 6 bananas e 3 maçãs. Se não tivéssemos a tabela itemVenda, para cada produto vendido teríamos que, além de armazenar o identi cador da venda, armazenar também a data dela. Isso causaria uma repetição de valores indesejada, aumentando des- necessariamente o tamanho do banco, além de prejudicar a velocidade de processamento de algumas consultas sobre ele.
Repare que a tabela itemVenda possui uma chave primária composta de 2 campos: id- Venda e nomeProduto. Ambas aparecem com um (FK) após seu nome. Isso indica que esses campos são chaves estrangeiras importadas de outras tabelas. no caso de idVenda, ela é importada da tabela venda, do campo de mesmo nome; nomeProduto vem da tabela produto, do campo 'nome'. O controle realizado pelo SGBD sobre chaves estrangeiras visa a garantir a integridade referencial entre os diversos dados armazenados no banco de da- dos. No caso de nosso exemplo, a existência das chaves estrangeiras na tabela itemVenda pretende garantir que só existam itens de venda para vendas que realmente existam e que
os produtos das vendas estejam cadastrados antes de serem vendidos. (Além desse controle, há também outros sobre a exclusão de itens em cascata, onde, por exemplo, a remoção de um produto poderia desencadear a deleção dos itens de venda cadastrados para ele). A chave primária composta, conforme o exemplo da tabela itemVenda tem a seguinte se- mântica: para cada item de venda cadastrado na tabela itemVenda só poderá existir uma única referência a um produto para cada venda distinta. Em outras palavras, na venda de número '10' poderá aparecer apenas uma vez o item 'banana'. Veja que isso não limita a compra do cliente a apenas uma banana, pois o campo 'quantidade' permite que várias bananas sejam vendidas para o cliente. A diferença estará apenas no armazenamento dessa informação no sistema, pois o item banana aparecerá apenas uma vez, com a quantidade comprada registrada no campo especí co para isso. Fica claro que essa medida evita que várias linhas para o mesmo produto sejam repetidas para expressar a mesma compra. Utili- zando esse artifício, economiza-se muito espaço, além de se aumentar também a velocidade das consultas, visto que, em qualquer hipótese, uma quantidade maior de linhas em uma tabela resultará, impreterivelmente, em maior tempo de consulta. (Este tipo de preocupa- ção com otimização de tempos de consulta e quantidade de armazenamento deu origem ao estudo das formas normais. Complemente seu aprendizado buscando por essa informação). Com essas informações, passamos a entender a estrutura das tabelas apresentadas:
fabricante (idfabricante, nome, endereço)
• Cada fabricante cadastrado será identi cado pelo seu id (idFabricante) respectivo. peça (idmodelo, nome, descrição)
• Cada peça cadastrada será identi cada pelo seu id (idModelo) respectivo. constrói (idmodelo (FK), idfabricante (FK), data, quantidade, cor)
• Cada linha da tabela constrói terá a quantidade e a cor de uma única peça fabricada por um fabricante especí co em uma determinada data. Além disso, em uma determinada data, um fabricante só produzirá uma determinada peça em uma única cor.
Com essas de nições, podemos passar à segunda parte da questão que envolve entender os comandos SQL. Nessa questão são utilizados os seguintes comandos:
• SELECT: indica quais os campos das tabelas que são listadas no comando FROM se deseja exibir;
• FROM: indica as tabelas que fazem parte da consulta em questão;
• INNER JOIN: esse comando, sempre que aparecer, estará entre duas tabelas e logo após o comando FROM. Ele é um indicador que informa que os dados da tabela anterior a ele serão cruzados com os dados da tabela posterior, utilizando a comparação descrita no campo ON. Este comando é um caso especí co do comando JOIN. O pre xo INNER indica que apenas os registros que casarem na comparação do campo ON deverão ser retornados;
• WHERE: de ne os ltros que deverão ser aplicados na consulta. De todos os registros resultantes da junção das tabelas envolvidas na consulta, apenas aqueles que atenderem às restrições descritas aqui deverão ser considerados para o resultado;
• GROUP BY: o resultado da consulta será agrupado pelo(s) campo(s) indicados neste comando. Este comando é utilizado em conjunto com funções de agregação, que, de
modo geral, geram estatísticas sobre outros campos que não os listados aqui. No caso de nosso exemplo, deseja-se contar os modelos diferentes resultantes da consulta agrupados pelo nome do fabricante;
• HAVING: esta é outra cláusula de restrição ao resultado da consulta. Aqui, pode-se de nir parâmetros para os campos agregados. A diferença primordial entre a cláusula HAVING e WHERE é que os ltros descritos na segunda são aplicados no cálculo da consulta enquanto que os da primeira são aplicados apenas ao resultado pronto. De um modo geral, ltros colocados em WHERE geram consultas mais rápidas do que aqueles de nidos em HAVING;
• ORDER BY: os campos aqui descritos serão utilizados como parâmetro de orde- nação do resultado da consulta. Por padrão, os campos são ordenados seguindo em que aparecem. Assim, o SGBD ordena o resultados sobre o primeiro campo, depois, considerando essa primeira ordem, sobre o segundo e assim por diante. A ordenação pode ser ascendente - do menor para o maior - utilizando o su xo ASC após o campo em questão, ou descendente - utilizando o su xo DESC. Por padrão, a ordenação é ascendente em todos os campos listados.
Vistos os comandos utilizados, basta agora que analisemos a consulta em questão combinando- a com as informações que são armazenadas nas tabelas de acordo com o estudo das chaves. Vejamos a Figura 6.
Figura 6: cláusulas SQL e suas interpretações. Portanto, a resposta da questão é a letra (B).
37. Assuntos relacionados: Banco de Dados, SQL, Consulta SQL, Banca: Cesgranrio
Instituição: IBGE
Cargo: Analista de Sistemas - Suporte Ano: 2010
Questão: 45
Considere uma tabela em um banco de dados relacional. A tabela apresenta três atributos do tipo texto, sendo CPF o campo chave.
Cliente(CPF, Nome, Endereco)
Considerando-se que homônimos são pessoas que possuem o mesmo nome, mas CPF dife- rentes, a expressão SQL que resulta na lista dos Clientes que têm homônimos, juntamente com o número de homônimos, é:
(a). SELECT DISTINCT C1.Nome, C1.CPF, COUNT(C2) FROM Cliente C1, Cliente C2
WHERE C1.Nome = C2.Nome AND C1.CPF <> C2.CPF (b). SELECT Nome, CPF,
(SELECT COUNT(*) FROM Cliente C1
WHERE C.Nome = C1.Nome AND C.CPF <> C1.CPF) FROM Cliente C
(c). SELECT Nome, CPF, COUNT(Cliente) FROM Cliente GROUP BY Nome, CPF
HAVING COUNT(*) > 1
(d). SELECT TOP 1 C1.Nome, C1.CPF, COUNT(*) FROM Cliente C1, Cliente C2
WHERE C1.Nome = C2.Nome AND C1.CPF <> C2.CPF (e). SELECT C1.Nome, C1.CPF, COUNT(*)
FROM Cliente C1, Cliente C2
WHERE C1.CPF <> C2.CPF AND C1.Nome = C2.Nome GROUP by C1.Nome, C1.CPF
HAVING count(*) > 0
Solução:
A resposta da questão é alternativa E. Para compreendê-la, vamos analisar cada uma das partes da consulta mostrada na alternativa. Para facilitar a explicação, abaixo a consulta é mostrada novamente, porém com as linhas numeradas.
1 SELECT C1.Nome, C1.CPF, COUNT(*) 2 FROM Cliente C1, Cliente C2
3 WHERE C1.CPF <> C2.CPF AND C1.Nome = C2.Nome 4 GROUP by C1.Nome, C1.CPF
Na linha 1, é utilizado o comando SELECT, que indica quais serão as colunas retornadas pela consulta. Foram selecionadas as colunas CPF, NOME. A terceira coluna que será re- tornada na consulta é a coluna COUNT(*).
Na linguagem SQL, o operador COUNT serve para contar a quantidade de ocorrências de uma determinada combinação de valores das colunas. Na consulta em questão, a expres- são COUNT(*) irá retornar a quantidade de vezes que uma determinada combinação dos valores da colunas NOME e CPF se repete no resultado da consulta.
Para que o operador COUNT funcione, é necessário ainda que se utilize a cláusula GROUP BY, enumerando a combinação de colunas que se deseja agrupar. Na consulta em questão, essa exigência é cumprida na linha 4.
Já na linha 2, temos a cláusula FROM, que é utilizada para enumerar as tabelas que serão necessárias para a execução da consulta. Como deseja-se descobrir os clientes homônimos, é necessário que se compare o nome de um cliente com o nome dos demais. Portanto, na cláusula FROM o programador teve que trabalhar com duas tabelas CLIENTE. Em outras palavras, na linha 2 gerou um produto cartesiano da tabela CLIENTE com ela mesma. Para restringir o produto cartesiano mencionado, na linha 3 o programador teve que adici- onar algumas restrições. Na primeira delas (C1.CPF <> C2.CPF), o programador garante que o nome de um dado cliente (que é identi cado unicamente pelo CPF) só será comparado com o nome dos demais, e não com seu próprio nome. Já na segunda restrição (C1.Nome = C2.Nome), o programador veri ca se os clientes possuem o mesmo nome.
Na linha 5 o programador faz uso da cláusula HAVING, que tem como função restringir os resultados em consultas que façam uso de operadores de agregação, como SUM, COUNT, MEDIA, DEVPAD, entre outros. Na consulta em questão, a expressão utilizada foi HA- VING count(*) > 0, que garante que a consulta só retornará combinações de NOME e CPF que possuam no mínimo 1 ocorrência. Ou seja, só retornará clientes que possuam no mínimo 1 homônimo.
Por m, vale ressaltar que a consulta retornará um registro para cada cliente que pos- suir um homônimo, contendo o CPF do cliente, o seu nome, e a quantidade de homônimos associada. Um exemplo do resultado que poderia ser retornado pela consulta segue abaixo:
JOSE DA SILVA 03556236920 2 JOSE DA SILVA 23698563222 2 JOSE DA SILVA 78946123362 2 MICHAEL PAULO 45632152875 1 MICHAEL PAULO 78542635985 1 ALEX DA COSTA 45896325644 3 ALEX DA COSTA 45823666999 3 ALEX DA COSTA 45236698888 3 ALEX DA COSTA 78542666666 3
38. Assuntos relacionados: Banco de Dados, SQL, Banca: ESAF
Instituição: Agência Nacional de Águas (ANA)