Banco de Dados

Introdução a BD

​

Prof. Marcelo Iury de Sousa Oliveira

marcelo@ci.ufpb.br

http://sites.google.com/site/marceloiury

Banco de Dados

Aula 8 – SQL: Consultas Aninhadas e Conjuntos

​

(Baseado no material da Profa. Bernadette Lóscio)

​

​

​

​

​

• Identificar todos os alunos que têm médias melhores do que a do aluno cuja Matrícula é 'V002’.
• Precisamos de duas consultas:
• SELECT * FROM notas WHERE matricula = 'V002’;
• SELECT a.matricula, a.nome, b.media

FROM alunos a, notas b WHERE a.matricula = b. matricula

AND b.media >80;

2

Motivação

3

Motivação

Vamos aninhar consultas?

Exemplo

​

• Professor(nome, siape, categoria, salario, cod_depto)
• Disciplina(nome, cod_disciplina, carga_horária)
• Departamento(nome_depto, numero_depto)
• Ministra(siape, cod_disciplina, periodo)

Banco de Dados de Exemplo

Insert into Disciplina values ('Artes das Trevas', 'IF971', '60h') ;

Insert into Disciplina values ('Trato de Criaturas Mágicas', 'IF972', '60h');

Insert into Disciplina values ('Defesa Contra Artes das Trevas', 'IF973', '75h');

Insert into Disciplina values ('Poções', 'IF974', '75h');

Insert into Disciplina values ('Herbologia', 'IF975', '60h');

​

Insert into Departamento values ('Magia', 'D003');

Insert into Departamento values ('Ervas e Plantas', 'D001');

Insert into Departamento values ('Criaturas Mágicas', 'D001');

​

Insert into Professor values ('Severo Snape', '16710', 'Adjunto 1', 1000.00, 'D003') ;

Insert into Professor values ('Remo Lupin', '16720', 'Adjunto 2', 2000.00, 'D003');

Insert into Professor values ('Rúbeo Hagrid', '16730', 'Adjunto 1', 1500.00, 'D001');

Insert into Professor values ('Horácio Slughorn', '16740', 'Adjunto 1', 1500.00, 'D003');

Insert into Professor values ('Pomona Sprout', '16750', 'Adjunto 1', 1500.00, 'D002');

Insert into Professor values ('Guilhermina Grubbly-Plank', '16760', 'Adjunto 1', 1500.00, 'D001');

​

Insert into Ministra values ('16710', 'IF971', '2020.1');

Insert into Ministra values ('16710', 'IF971', '2020.2');

Insert into Ministra values ('16730', 'IF972', '2020.1');

Insert into Ministra values ('16730', 'IF972', '2020.2');

Insert into Ministra values ('16720', 'IF973', '2020.1');

Insert into Ministra values ('16720', 'IF973', '2020.2');

Insert into Ministra values ('16740', 'IF974', '2020.1');

Insert into Ministra values ('16760', 'IF975', '2020.2');

Insert into Ministra values ('16760', 'IF975', '2021.1');

​

​

​

​

​

​

​

​

​

Banco de Dados de Exemplo

https://docs.google.com/document/d/1V_8KfV0HjSR8epXMxdJIGHe-oZKC2UPiM4mxQtQPhZ0/edit?usp=sharing

Consultas aninhadas

• Consultas aninhadas, também conhecidas como subconsulta, é uma consulta SQL aninhada dentro de uma consulta maior
• O resultado de uma consulta é utilizado por outra consulta, de forma encadeada e no mesmo comando SQL

Consultas Aninhadas

Consultas aninhadas

• Uma subconsulta pode ocorrer em:
• Uma cláusula SELECT (Projeção)
• Uma cláusula FROM
• Uma cláusula WHERE
• Geralmente, é adicionada na cláusula WHERE de outra instrução SQL SELECT.

Consultas Aninhadas

https://www.ibm.com/docs/en/informix-servers/12.10?topic=statements-subqueries-in-from-clause

​

https://www.ibm.com/docs/en/informix-servers/12.10?topic=statements-subqueries-in-projection-clause

​

Consultas aninhadas

• Subconsultas devem ser escritas entre parênteses ( )
• Existem 3 tipos de subconsultas
• ESCALAR → Retornam um único valor
• ÚNICA LINHA → Retornam várias colunas, mas apenas uma única linha é obtida
• TABELA → Retornam uma ou mais colunas e múltiplas linhas

​

Tipos de Consultas Aninhadas

Consultas aninhadas

SELECT siape, nome, salario

FROM Professor

WHERE cod_depto = (SELECT numero_depto

FROM Departamento

WHERE nome_depto = ‘Magia’);

Consultas Aninhadas

Cláusulas ANY e SOME

• A condição de comparação ANY é utilizada para comparar um valor a uma lista ou subconsulta.
• Alguns SGBDS não dão suporte a lista de valores
• Ele deve ser precedido por =, !=, >, <, <=, >= e seguido por uma lista ou subconsulta.
• O SOME e o ANY fazem exatamente a mesma coisa e podem ser utilizados sem distinção.
• Essas expressões devem ser usadas com subconsultas que produzem uma única coluna de números.

​

ANY e SOME

Cláusulas ANY e SOME

• Com ANY, a condição será verdadeira se for satisfeita por quaisquer valores produzidos pela subconsulta.
• Se a subconsulta estiver vazia, ANY retornará false.

​

ANY e SOME

Cláusulas ANY e SOME

​

​

ANY e SOME

SELECT siape, nome, salario

FROM Professor

WHERE salario > ANY (SELECT salario FROM Professor

WHERE cod_depto = ‘D001’);

Cláusula ALL

• A condição de comparação ALL é utilizada para comparar um valor a uma lista ou subconsulta.
• Alguns SGBDS não dão suporte a lista de valores
• Deve ser precedida por =, !=, >, <, <=, >= e seguida por uma lista ou subconsulta.
• Essa expressão deve ser usada com subconsultas que produzem uma única coluna de números.

​

ALL

Cláusula ALL

• Com ALL, a condição só será verdadeira se for satisfeita por todos os valores produzidos pela subconsulta.
• Se a subconsulta estiver vazia, ALL retornará true

ALL

Cláusula ALL

SELECT siape, nome, salario

FROM Professor

WHERE salario > ALL (SELECT salario FROM Professor

WHERE cod_depto = ‘D003’);

ALL

Cláusula IN

• Permite especificar múltiplos valores na cláusula WHERE.
• A cláusula é utilizada para comparar um valor a uma lista ou subconsulta.
• Procura um valor em um subconjunto.

​

IN

Cláusula IN

IN

SELECT siape, nome, salario

FROM Professor

WHERE cod_depto IN (SELECT numero_depto FROM departamento

WHERE nome_depto = ‘Magia’);

EXISTS e NOT EXISTS

• Foram projetadas para uso apenas com subconsultas.
• Sempre que uma condição na cláusula WHERE de uma consulta aninhada referencia algum atributo de uma relação em uma consulta externa, dizemos que essas duas consultas estão correlacionadas.

Exists e Not Exists

EXISTS e NOT EXISTS

• Foram projetadas para uso apenas com subconsultas.
• A cláusula EXISTS faz uma verificação se existe algum resultado para a subconsulta informada.
• Caso haja, o resultado da consulta principal é exibido.

Exists e Not Exists

EXISTS e NOT EXISTS

• É muito comum sua utilização quando se deseja trazer resultados onde um valor específico existe dentro de outra tabela.
• Ele é equivalente ao operador IN. Contudo, para volumes maiores de dados, o EXISTs tende a ser mais eficiente
• Exemplos:
• Recuperar produtos que tiveram alguma venda.
• Listar todos os clientes que compraram algo de qualquer uma das lojas

​

Exists e Not Exists

EXISTS e NOT EXISTS

• Consultas correlacionadas: a condição na cláusula WHERE de uma consulta aninhada referencia algum atributo de uma relação em uma consulta externa

​

​

​

SELECT P.siape, P.nome, P.salario

FROM Professor P

WHERE EXISTS (SELECT M.* FROM Ministra M

WHERE P.siape = M.siape AND M.cod_disciplina = ‘IF971’);

Exists e Not Exists

EXISTS e NOT EXISTS

SELECT P.siape, P.nome, P.salario

FROM Professor P

WHERE EXISTS (SELECT M.* FROM Ministra M

WHERE P.siape = M.siape AND M.cod_disciplina = ‘IF971’);

Exists e Not Exists

EXISTS e NOT EXISTS

• EXISTS verifica se o resultado de uma consulta aninhada correlacionada é vazio.
• Recupera cada tupla da relação da consulta interna que se relaciona com a tupla da consulta externa.
• EXISTS
• Retorna TRUE ⇔ existe pelo menos uma linha produzida pela subconsulta.
• Retorna FALSE ⇔ a subconsulta produz uma tabela resultante vazia.

​

Exists e Not Exists

EXISTS e NOT EXISTS

• NOT EXISTS funciona de maneira semelhante ao EXISTS, sendo a negação!

​

Exists e Not Exists

SELECT P.siape, P.nome, P.salario

FROM Professor P

WHERE NOT EXISTS (SELECT D.numero_depto FROM Departamento D WHERE P.cod_depto = D.numero_depto AND D.nome_depto = ‘Magia’);

Consultas aninhadas – IMPORTANTE!!!

• A cláusula ORDER BY não pode ser usada em uma subconsulta
• A lista de atributos especificados no SELECT de uma subconsulta deve conter um único elemento (exceto para EXISTS)
• Nomes de atributos especificados na subconsulta estão associados à tabelas listadas na cláusula FROM da mesma
• É possível referir-se a uma tabela da cláusula FROM da consulta mais externa utilizando qualificadores de atributos (referência com “alias”).
• Quando a subconsulta é um dos operandos envolvidos em uma comparação, ela deve aparecer no lado direito da comparação.

​

Exists e Not Exists

Consultas aninhadas

• A subconsulta também pode ser aninhada dentro de uma instrução SELECT, INSERT, UPDATE ou DELETE ou dentro de outra subconsulta.

​

​

Consultas Aninhadas

https://www.w3resource.com/sql/insert-statement/insert-using-subqueries.php

​

https://www.w3resource.com/sql/update-statement/update-using-subqueries.php

​

https://www.w3resource.com/sql/delete-statement/delete-with-subqueries.php

​

Funções de Agregação

• Utilização de funções sobre conjuntos
• Disparadas a partir do SELECT
• São funções que resumem as informações de várias tuplas em uma única tupla.

Funções de Agregação

Funções de Agregação

• MAX : Retorna o valor máximo entre o conjunto de valores de uma coluna em uma relação.

​

• MIN : Retorna o valor mínimo entre o conjunto de valores de uma coluna em uma relação.

​

• SUM: Retorna a soma dos valores de uma coluna em uma relação.

​

• COUNT: Retorna o número de tuplas de uma relação.

​

• AVG: Retorna a média aritmética dos valores de uma coluna em uma relação.

​

Funções de Agregação

Funções de Agregação

MAX

SELECT MAX(P.salario) FROM Professor P, Departamento D WHERE P.cod_depto = D.numero_depto AND D.nome_depto = ‘Magia’;

Funções de Agregação

COUNT

SELECT COUNT(*) FROM Professor P, Departamento D WHERE D.numero_depto = P.cod_depto

AND D.nome_depto = ‘Magia’

AND P.salario > 1000

Funções de Agregação

SUM

SELECT SUM(P.salario) FROM Professor P, Departamento D WHERE D.numero_depto = P.cod_depto

AND D.nome_depto = ‘Magia’

Funções de Agregação

AVG

SELECT AVG(P.salario) FROM Professor P, Departamento D WHERE D.numero_depto = P.cod_depto

AND D.nome_depto = ‘Magia’

Funções de Agregação

• Funções agregadas podem estar em consultas aninhadas.

​

Funções de Agregação e Consultas Aninhadas

SELECT P.nome, P.salario FROM Professor P WHERE p.salario IN (SELECT MAX(salario) FROM Professor)

Funções de Agregação

• Aplicação de funções de agregação à subgrupos de tuplas
• Por exemplo:
• Qual salário médio dos professores em cada departamento?
• Qual é o número de professores que trabalham em cada departamento?
• É preciso particionar a relação em grupos de tuplas que possuem o mesmo valor para determinados atributos, que são chamados de atributos agrupamento
• As funções de agregação são aplicadas a cada grupo separadamente

Funções de Agregação e Grupos de Dados

Cláusula GROUP BY

• A cláusula GROUP BY tem a finalidade de agrupar tuplas especificando os atributos de agrupamento.
• Esses atributos também devem aparecer na cláusula SELECT.
• Somente as funções de agregação podem aparecer no SELECT e não aparecer no GROUP BY.

​

Group By

Cláusula GROUP BY

SELECT Sexo, Count(*) FROM Professor

GROUP BY Sexo;

Group By

Cláusulas GROUP BY

Group By

SELECT cod_depto, COUNT(*), AVG(salario)

FROM Professor

GROUP BY cod_depto

Cláusula GROUP BY

• As tuplas da tabela Professor são divididas em grupos: um grupo correspondente a cada departamento (cada valor de cod_depto).
• As funções COUNT e AVG são aplicadas a cada grupo de tuplas.
• Observe que a cláusula SELECT contém apenas o atributo de agrupamento e as funções de agregação a serem aplicadas nos grupos.

​

​

​

​

​

​

​

​

​

​

​

​

​

​

​

​

​

​

​

Resultado da consulta

Group By

Cláusula GROUP BY

​

Se houver valores nulos no atributo de agrupamento (Professor sem departamento), é criado um grupo separado para todas as tuplas com valor NULL no atributo de agrupamento.

​

Group By

Cláusula HAVING

• A cláusula HAVING é usada para especificar condições de filtragem em grupos de registros ou agregações.
• A cláusula HAVING deve ser utilizada em conjunto com o GROUP BY
• Somente os grupos que satisfazem a condição do HAVING são recuperados

​

Having

Cláusula HAVING

Having

SELECT P.siape, P.nome, COUNT(*)

FROM Professor P, Ministra M

WHERE P.siape = M.siape

GROUP BY P.siape, P.nome

HAVING COUNT(*) > 2

Cláusulas GROUP BY e HAVING

• Tanto o GROUP BY quanto o HAVING são aplicados somente após a cláusula WHERE, após as condições de junção e demais condições.
• Primeiro, o SGBD seleciona as tuplas do WHERE e depois aplica HAVING.

​

ATTENTION PLEASE!

Considere as seguintes relações:

Professor (nome, siape, categoria, salario, cod_depto)

Disciplina (nome, cod_disciplina, carga_horária)

Departamento (nome_depto, numero_depto)

Ministra (siape, cod_disciplina, periodo)

Escreva as seguintes consultas em SQL:

• Recupere a média de salário dos professores que são Adjunto 1.
• Recupere a quantidade de disciplinas com carga horária de 60h.
• Recupere o nome dos professores juntamente com o nome das disciplinas que eles ministraram em 2020.2.
• Recupere o nome dos professores que nunca ministraram disciplinas.
• Recupere o nome dos professores que não ministraram disciplinas em 2020.1. (use EXISTS/NOT EXISTS)

​

Exercícios

• Recupere o nome das disciplinas que foram ofertadas pelo menos duas vezes.
• Retorne a quantidade de professores do departamento D001 que ministram a disciplina IF971
• Recupere os dados dos professores que já ministraram todas as disciplinas.
• Recupere os nomes das disciplinas que já foram ministradas por mais de um professor.
• Para cada departamento, recupere o numero do depto e o valor do maior salário do professor Adjunto I .
• Retorne o nome dos professores que ministram tanto a disciplina IF971 quanto a disciplina IF972.

​

Exercícios