2.15. Consultas em SQL
Na linguagem SQL, o comando SELECT é utilizado para recuperação de dados das tabelas. A sintaxe geral deste comando é a seguinte:
SELECT [ ALL | DISTINCT [ ON ( expressão [, ...] ) ] ]
[ * | expressão [ [ AS ] rótulo ] [, ...] ]
[ FROM from_item [, ...] ]
[ WHERE condição ]
[ GROUP BY elemento_agrupamento [, ...] ]
[ HAVING condição [, ...] ]
[ ORDER BY expressão [ ASC | DESC ] [, ...] ]
[ LIMIT { quantidade | ALL } ]
[ OFFSET início [ ROW | ROWS ] ]
O comando SELECT é formado por várias cláusulas: SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY, LIMIT e OFFSET. Cada uma dessas cláusulas tem um papel importante na definição dos objetivos de uma consulta. Portanto, na sintaxe mostrada acima temos que:
Tudo que está entre os pares de colchetes (
[e]) é opcional.A cláusula
SELECTpermite especificar a lista de expressões, isto é, nomes de colunas ou fórmulas matemáticas ou chamadas de função ou até mesmo sub-consultas, que farão parte das linhas de saída da consulta. A palavra-chaveDISTINCTfaz com que linhas com valores duplicados sejam removidas do resultado, ficando apenas uma linha do grupo de linhas repetidas.DISTINCT ON ( expressão [, ...])tem um comportamento semelhante, mas considerando apenas a lista de expressões fornecida. A palavra-chaveALLinclui todas as linhas do resultado, que é o comportamento padrão e, portanto, pode ser omitida. A palavra-chaveASpossibilita renomear uma coluna ou expressão com um novorótulo. O caractere*é uma abreviação para a lista de todas as colunas dos conjuntos de dados presentes na cláusulaFROM.A cláusula
FROMespecifica uma ou mais tabelas como fonte dos dados da consulta. Caso múltiplas tabelas sejam especificadas, o resultado é um produto cartesiano (ouCROSS JOIN) de todas as tabelas envolvidas. No entanto, é muito comum o uso de uma cláusulaWHEREpara restringir as linhas retornadas a um subconjunto menor desse produto cartesiano. Vale ressaltar que na cláusulaFROM, ofrom_itempode ser o nome de uma tabela do banco de dados, o nome de uma view (visão), uma sub-consulta, ou até mesmo uma chamada de função que produza valores que são tratados como um conjunto de linhas. Portanto, a cláusulaFROMfaz o produto cartesiano dos conjuntos de dados informados.A cláusula
WHEREpermite definir umacondição, isto é, umaexpressão lógicaoupredicado, para filtrar o conjunto de linhas da consulta. As linhas que não satisfaçam esse predicado serão eliminadas do resultado.A cláusula
GROUP BYé utilizada para criar grupos de linhas que são condensadas em uma única linha através das operações de agregação tais como:SUM,MIN,MAX,AVG,COUNT, entre outras. Oelemento_agrupamentopode ser o nome de uma coluna ou uma expressão formada a partir das colunas. Também podemos usar os nomes das colunas de saída da consulta nessa cláusula ou até mesmo a posição ordinal da coluna de saída. Quando esta cláusula está presente, apenas as colunas listadas nela ou funções de agregação podem ser usadas na lista da cláusulaSELECT.A cláusula
HAVINGpermite definir umacondição, isto é, umaexpressão lógicaoupredicado, para filtrar o resutado dos grupos de linhas. Dessa maneira, as linhas resultantes de agrupamentos que não satisfaçam essa condição, são eliminadas do resultado final.A cláusula
ORDER BYordena o resultado final de acordo com a expressão fornecida. Lembrando que a expressão pode ser uma lista de colunas, expressões matemáticas, chamadas de função, ou a posição das colunas de saída de acordo com a lista da cláusulaSELECT.As cláusulas
LIMITeOFFSETpermitem, respectivamente, definir o número máximo de linhas a serem retornadas e o ponto onde esta contagem começa a valer.
Nota
A sintaxe do comando de seleção apresentada acima foi feita de maneira simplificada. Para a versão completa, consulte o seguite tópico do manual do PostgreSQL: SELECT.
2.15.1. Consultas Simples
Nesta seção veremos exemplos de consultas que envolvem uma única tabela como entrada.
1. Recuperar os dados do estudante Eduardo:
Solução:
SELECT *
FROM estudante
WHERE nome = 'Eduardo';
Saída:
matricula | nome | data_nascimento | genero | data_matricula
-----------+---------+-----------------+--------+----------------
1 | Eduardo | 1980-08-04 | M | 2019-01-01
(1 row)
Nessa consulta usamos três cláusulas:
SELECT: Nesta cláusula usamos o caracter especial*que é expandido para a lista de todas as colunas e, por isso, obtivemos uma linha com 05 valores como mostrado acima.FROM: Nesta cláusula especificamos a tabelaestudantecomo fonte da consulta.WHERE: Nesta cláusula especificamos uma expressão lógica que realiza o filtro das linhas desejadas. No exemplo acima, temos apenas um estudante com o nomeEduardoe, portanto, somente uma linha satisfaz a condiçãonome = 'Eduardo'.
2. Recuperar o número de matrícula, nome e a data de matrícula dos estudantes do gênero feminino (F):
Solução:
SELECT matricula, nome, data_matricula, genero
FROM estudante
WHERE genero = 'F';
Saída:
matricula | nome | data_matricula | genero
-----------+-----------+----------------+--------
2 | Maria | 2019-02-02 | F
4 | Luiza | 2020-01-05 | F
5 | Ana Maria | 2020-01-05 | F
6 | Ana Clara | 2020-01-05 | F
11 | Carla | 2019-01-05 | F
12 | Telma | 2020-01-06 | F
14 | Lucia | 2021-01-08 | F
15 | Tassiana | 2021-01-08 | F
21 | Joana | 2022-01-10 | F
25 | Marcia | 2022-01-11 | F
28 | Katia | 2022-01-12 | F
30 | Zoraide | 2022-01-12 | F
31 | Roberta | 2019-01-01 | F
34 | Joana | 2019-01-01 | F
35 | Josi | 2022-01-12 | F
(15 rows)
A cláusula SELECT da consulta acima contém um subconjunto das colunas da tabela estudante: matricula, nome, data_matricula e genero. Utilizamos o separador , para listar as colunas nessa cláusula. Essa cláusula nos permite, entre outra coisas, controlar a ordem de apresentação das colunas.
3. Recuperar o número de matrícula e os três primeiros caracteres do nome dos estudantes matriculados no ano de 2020 do gênero feminino (F):
Solução:
SELECT matricula, left(nome, 3) AS iniciais_nome
FROM estudante
WHERE (genero = 'F') AND (extract(year from data_matricula) = 2020);
Saída:
matricula | iniciais_nome
-----------+---------------
4 | Lui
5 | Ana
6 | Ana
12 | Tel
(4 rows)
Na consulta acima:
O segundo elemento da cláusula
SELECTutilizou uma função chamadaleftpara extrair no máximo três caracteres de um nome. Nesse elemento, também utilizamos a palavra-chaveASpara criar um novo rótulo para a coluna de saída:iniciais_nome.A clausula
WHEREpossui uma expressão usando o e-lógico (AND). Portanto, apenas as linhas que tenham simultaneamente o caracterFcomo gênero e a parte do ano da data de matricula igual a 2020 serão selecionadas no resultado final.
4. Recuperar o número de matrícula, o nome e a idade dos estudantes matriculados no ano de 2020 do gênero feminino (F):
Solução:
Para resolver esta consulta vamos precisar usar a função age, que computa um intervalo entre dois instantes de tempo. Considere o exemplo abaixo, onde usamos como referência o tempo do sistema atual (CURRENT_TIMESTAMP) e a data de 2000-02-01:
SELECT age(CURRENT_TIMESTAMP, '2000-02-01');
Saída:
age
-----------------------------------------
22 years 8 mons 13 days 19:42:10.241783
(1 row)
Aviso
O exemplo acima deverá retornar um valor diferente dependendo da data e hora em que você estará executando a consulta!
Para tomar apenas a quantidade de anos entre as duas datas, podemos usar a função extract, como mostrado abaixo:
SELECT extract( 'year' from age(CURRENT_TIMESTAMP, '2000-02-01') ) AS anos;
Saída:
anos
------
22
(1 row)
Agora estamos prontos para construir a consulta final:
SELECT matricula, nome, extract('year' from age(CURRENT_TIMESTAMP, data_nascimento)) AS idade
FROM estudante
WHERE (genero = 'F') AND (extract(year from data_matricula) = 2020);
Saída:
matricula | nome | idade
-----------+-----------+-------
4 | Luiza | 41
5 | Ana Maria | 41
6 | Ana Clara | 41
12 | Telma | 39
(4 rows)
5. Recuperar o número de matrícula e o nome dos estudantes cujo nome comece com as iniciais jo:
Solução:
SELECT matricula, nome
FROM estudante
WHERE lower(nome) LIKE 'jo%';
Saída:
matricula | nome
-----------+-------
8 | Jose
21 | Joana
34 | Joana
35 | Josi
(4 rows)
Essa consulta utiliza o operador LIKE, que pode ser usado com strings. Este operador retorna verdadeiro caso a string à esquerda satisfaça o padrão fornecido à direita. O caractere % é um caractere especial, casando com qualquer sequencia de zero ou mais caracteres. Veja mais exemplos de uso desse operador abaixo:
SELECT 'Gilberto' LIKE '%be%'; -- retorna verdadeiro
SELECT 'Gilberta' LIKE 'Gilbert_'; -- retorna verdadeiro
SELECT 'Gilbertas' LIKE 'Gilbert_'; -- retorna falso
6. Recuperar o número de matrícula e o nome dos estudantes que tenham número de matrícula no conjunto {1, 3, 5, 12, 14, 17}:
Solução:
SELECT matricula, nome
FROM estudante
WHERE matricula IN (1, 3, 5, 12, 14, 17);
Saída:
matricula | nome
-----------+-----------
1 | Eduardo
3 | Eugenio
5 | Ana Maria
12 | Telma
14 | Lucia
17 | Felipe
(6 rows)
7. Recuperar o número de matrícula e o nome dos estudantes que não estejam no conjunto de matrículas {1, 3, 5, 12, 14, 17}:
Solução:
SELECT matricula, nome
FROM estudante
WHERE matricula NOT IN (1, 3, 5, 12, 14, 17);
Saída:
matricula | nome
-----------+-----------
2 | Maria
4 | Luiza
6 | Ana Clara
... ...
34 | Joana
35 | Josi
36 | Antonio
37 | Zuleica
(31 rows)
2.15.2. Junção de Tabelas
Nesta seção veremos exemplos de consultas que envolvem duas ou mais tabelas como entrada.
8. Fazer o produto cartesiano entre as tabelas professor e disciplina:
Solução:
SELECT *
FROM professor, disciplina;
Saída:
codigo | nome | codigo | titulo | creditos | professor_codigo
--------+----------+--------+-------------------+----------+------------------
1 | Romildo | 1 | Matemática | 6 | 1
1 | Romildo | 2 | Fisica | 4 | 1
1 | Romildo | 3 | Bilogia | 2 | 2
1 | Romildo | 4 | Quimica | 3 | 3
1 | Romildo | 5 | Geografia | 2 | 4
1 | Romildo | 6 | Historia | 2 | 4
1 | Romildo | 7 | Lingua Portuguesa | 4 | 4
1 | Romildo | 8 | Lingua Inglesa | 2 | 5
... ... ... ... ... ...
6 | Cleiton | 8 | Lingua Inglesa | 2 | 5
6 | Cleiton | 9 | Lingua Francesa | 1 | 5
6 | Cleiton | 10 | Ciências | 1 | 4
(60 rows)
A cláusula FROM permite especificar uma lista de tabelas (ou itens de dados). Na consulta acima, a cláusula FROM realizou o produto cartesiano entre as duas tabelas. Como temos 06 professores e 10 discipllinas, o resultado contém 60 linhas, isto é, todas as linhas da tabela professor pareadas com todas as linhas da tabela disciplina.
Repare também que por termos usado o caractere * na cláusula SELECT, todas as colunas das duas tabelas participaram do resultado final. Na cláusula SELECT é possível controlar a lista de colunas de saída especificando o nome qualificado da coluna, isto é, o nome-tabela.nome-coluna:
SELECT professor.*, disciplina.titulo
FROM professor, disciplina;
Saída:
codigo | nome | titulo
--------+----------+-------------------
1 | Romildo | Matemática
1 | Romildo | Fisica
1 | Romildo | Bilogia
1 | Romildo | Quimica
1 | Romildo | Geografia
1 | Romildo | Historia
1 | Romildo | Lingua Portuguesa
1 | Romildo | Lingua Inglesa
... ... ...
6 | Cleiton | Lingua Inglesa
6 | Cleiton | Lingua Francesa
6 | Cleiton | Ciências
(60 rows)
Nesse último exemplo, o item professor.* na cláusula SELECT é expandido para todas as colunas da tabela professor, isto é, as colunas codigo e nome. Já a expressão disciplina.titulo indica que queremos a coluna titulo da tabela disciplina no resultado.
9. Juntar as linhas correlatas das tabelas professor e disciplina:
Nota
Chamamos este tipo de consulta de junção entre tabelas.
Solução:
SELECT *
FROM professor, disciplina
WHERE professor.codigo = disciplina.professor_codigo;
Saída:
codigo | nome | codigo | titulo | creditos | professor_codigo
--------+----------+--------+-------------------+----------+------------------
1 | Romildo | 1 | Matemática | 6 | 1
1 | Romildo | 2 | Fisica | 4 | 1
2 | Thales | 3 | Bilogia | 2 | 2
3 | Karine | 4 | Quimica | 3 | 3
4 | Tamara | 5 | Geografia | 2 | 4
4 | Tamara | 6 | Historia | 2 | 4
4 | Tamara | 7 | Lingua Portuguesa | 4 | 4
5 | Carolina | 8 | Lingua Inglesa | 2 | 5
5 | Carolina | 9 | Lingua Francesa | 1 | 5
4 | Tamara | 10 | Ciências | 1 | 4
(10 rows)
Repare na saída acima que agora os valores nas linhas para a primeira coluna codigo são iguais na coluna professor_codigo. Esse exemplo mostra como usar colunas relacionadas para busca da informação apropriada.
Outra forma de realizar a consulta acima é utilizar a palavra-chave INNER JOIN, como indicado abaixo:
SELECT *
FROM professor INNER JOIN disciplina ON professor.codigo = disciplina.professor_codigo;
Saída:
codigo | nome | codigo | titulo | creditos | professor_codigo
--------+----------+--------+-------------------+----------+------------------
1 | Romildo | 1 | Matemática | 6 | 1
1 | Romildo | 2 | Fisica | 4 | 1
2 | Thales | 3 | Bilogia | 2 | 2
3 | Karine | 4 | Quimica | 3 | 3
4 | Tamara | 5 | Geografia | 2 | 4
4 | Tamara | 6 | Historia | 2 | 4
4 | Tamara | 7 | Lingua Portuguesa | 4 | 4
5 | Carolina | 8 | Lingua Inglesa | 2 | 5
5 | Carolina | 9 | Lingua Francesa | 1 | 5
4 | Tamara | 10 | Ciências | 1 | 4
(10 rows)
10. Juntar as linhas correlatas das tabelas professor e disciplina, exibindo também algum professor que não esteja associado a disciplinas:
Solução:
Neste caso será necessário utilizar um tipo de junção (JOIN) que garanta que as linhas da tabela professor apareçam no resultado pelo menos uma vez, mesmo não havendo correspondência na tabela disciplina. O tipo de junção necessária é conhecida como LEFT OUTER JOIN:
SELECT *
FROM professor LEFT OUTER JOIN disciplina ON professor.codigo = disciplina.professor_codigo;
Saída:
codigo | nome | codigo | titulo | creditos | professor_codigo
--------+----------+--------+-------------------+----------+------------------
1 | Romildo | 1 | Matemática | 6 | 1
1 | Romildo | 2 | Fisica | 4 | 1
2 | Thales | 3 | Bilogia | 2 | 2
3 | Karine | 4 | Quimica | 3 | 3
4 | Tamara | 5 | Geografia | 2 | 4
4 | Tamara | 6 | Historia | 2 | 4
4 | Tamara | 7 | Lingua Portuguesa | 4 | 4
5 | Carolina | 8 | Lingua Inglesa | 2 | 5
5 | Carolina | 9 | Lingua Francesa | 1 | 5
4 | Tamara | 10 | Ciências | 1 | 4
6 | Cleiton | | | |
(11 rows)
Repare na saída acima que a linha (registro ou tupla) do professor Cleiton apareceu no resultado final, com as colunas da tabela disciplina preenchidas com valor nulo (NULL).
11. Liste os professores que não estão associados a disciplinas:
Solução:
Podemos utilizar uma estratégia semelhante à da consulta 10, usando LEFT OUTER JOIN entre as tabelas professor e disciplina, acrescentando um predicado (ou filtro) com a cláusula WHERE que verifique se o codigo da tabela disciplina é NULL:
SELECT *
FROM professor LEFT JOIN disciplina ON professor.codigo = disciplina.professor_codigo
WHERE disciplina.codigo IS NULL
ORDER BY nome;
Saída:
codigo | nome | codigo | titulo | creditos | professor_codigo
--------+---------+--------+--------+----------+------------------
6 | Cleiton | | | |
(1 row)
12. Liste as linhas correlatas das tabelas estudante e disciplina bem como aquelas que não tiverem correspondência:
Solução:
Neste caso precisamos utilizar o FULL OUTER JOIN entre as tabelas estudante, estudante_disciplina e disciplina, para que os estudantes que não estejam associados a disciplinas sejam listados, bem como as disciplinas que não tiverem estudantes associados:
SELECT *
FROM estudante FULL OUTER JOIN estudante_disciplina ON (estudante.matricula = estudante_disciplina.matricula)
FULL OUTER JOIN disciplina ON (estudante_disciplina.codigo = disciplina.codigo);
Saída:
matricula | nome | data_nascimento | genero | data_matricula | matricula | codigo | data | codigo | titulo | creditos | professor_codigo
-----------+-----------+-----------------+--------+----------------+-----------+--------+------------+--------+-------------------+----------+------------------
1 | Eduardo | 1980-08-04 | M | 2019-01-01 | 1 | 2 | 2019-06-01 | 2 | Fisica | 4 | 1
1 | Eduardo | 1980-08-04 | M | 2019-01-01 | 1 | 1 | 2019-06-01 | 1 | Matemática | 6 | 1
2 | Maria | 1980-04-03 | F | 2019-02-02 | 2 | 4 | 2019-06-01 | 4 | Quimica | 3 | 3
... ... ... ... ... ... ... ... ... ... ... ...
36 | Antonio | 1976-06-30 | M | 2019-01-01 | 36 | 5 | 2019-06-01 | 5 | Geografia | 2 | 4
37 | Zuleica | 1986-06-30 | M | 2019-01-01 | | | | | | |
| | | | | | | | 10 | Ciências | 1 | 4
(75 rows)
Repare na saída acima que a estudante Zuleica não se encontra matriculada em nenhuma disciplina e que a disciplina de Ciências não possui estudante associado.
13. Qual o nome das disciplinas cursadas pelo estudante Eduardo?
Solução:
SELECT estudante.matricula, nome, titulo
FROM estudante, estudante_disciplina, disciplina
WHERE estudante.matricula = estudante_disciplina.matricula
AND estudante_disciplina.codigo = disciplina.codigo
AND estudante.nome = 'Eduardo';
Saída:
matricula | nome | titulo
-----------+---------+------------
1 | Eduardo | Matemática
1 | Eduardo | Fisica
(2 rows)
14. Qual o nome dos professores do estudante Eduardo?
Solução:
SELECT estudante.matricula, estudante.nome AS nome_estudante, professor.nome AS nome_professor, titulo
FROM estudante, estudante_disciplina, disciplina, professor
WHERE estudante.matricula = estudante_disciplina.matricula
AND estudante_disciplina.codigo = disciplina.codigo
AND disciplina.professor_codigo = professor.codigo
AND estudante.nome = 'Eduardo';
Saída:
matricula | nome_estudante | nome_professor | titulo
-----------+----------------+----------------+------------
1 | Eduardo | Romildo | Matemática
1 | Eduardo | Romildo | Fisica
(2 rows)
Dica
Para mais detalhes das consultas com junção entre tabelas, consulte o manual do PostgreSQL nas seções 2.6. Joins Between Tables e 7.2. Table Expressions.
2.15.3. Consultas de Agregação
Temos vários operadores que trabalham com grupos de registros, sendo muito úteis para uso com a cláusula GROUP BY:
AVG: média dos valores da coluna.SUM: soma dos valores da coluna.COUNT: número de valores na coluna.MAX: maior valor na coluna.MIN: menor valor na coluna.
Nesta seção vamos explorar o uso das cláusulas GROUP BY e HAVING na construção de consultas que permitam realizar uma sumarização de valores a partir de grupos de linhas.
15. Quantos estudantes estão cadastrados?
Solução:
Neste caso precisamos apenas usar uma função de agregação que conte o número de linhas de uma tabela, tratando todas as linhas como um único grupo. A função de agregação COUNT pode ser usada para esta finalidade:
SELECT COUNT(*) AS num_estudantes
FROM estudante;
Saída:
num_estudantes
----------------
37
(1 row)
16. Quantas são as disciplinas?
Solução:
SELECT COUNT(*) AS num_disciplinas
FROM disciplina;
Saída:
num_disciplinas
-----------------
10
(1 row)
17. Quantos professores estão cadastrados?
Solução:
SELECT COUNT(*) AS num_professores
FROM professor;
Saída:
num_professores
-----------------
6
(1 row)
18. Qual o número de estudantes do sexo masculino e feminino?
Solução:
O número de estudantes do sexo masculino pode ser computado com a seguinte consulta:
SELECT COUNT(*) AS num_estudantes_masculino
FROM estudante
WHERE genero = 'M';
Saída:
num_estudantes_masculino
--------------------------
22
(1 row)
O número de estudantes do sexo feminino:
SELECT COUNT(*) AS num_estudantes_feminino
FROM estudante
WHERE genero = 'F';
Saída:
num_estudantes_feminino
-------------------------
15
(1 row)
Outra forma de computar os dois valores numa única consulta consiste em utilizar a cláusula GROUP BY utilizando os valores da coluna genero para criação de dois grupos de linhas, como mostrado na Figura 2.20:
Figura 2.20 - Tabela estudante com linhas agrupadas pelos valores da coluna genero
Desta forma, podemos construir a seguinte consulta:
SELECT genero, COUNT(*) AS num_estudantes
FROM estudante
GROUP BY genero;
Resultado:
genero | num_estudantes
--------+----------------
M | 22
F | 15
(2 rows)
19. Qual o número de estudantes matriculados em cada ano?
Solução:
Para esta consulta utilizaremos a cláusula GROUP BY com os valores de ano da coluna data_matricula. A Figura 2.21 mostra quais os grupos de linhas que serão gerados:
Figura 2.21 - Tabela estudante com linhas agrupadas pelos valores do ano da coluna data_matricula
Consulta:
SELECT extract(year from data_matricula) AS ano, COUNT(*) AS num_estudantes
FROM estudante
GROUP BY extract(year from data_matricula)
ORDER BY ano;
Saída:
ano | num_estudantes
------+----------------
2019 | 10
2020 | 7
2021 | 8
2022 | 12
(4 rows)
20. Quantas disciplinas cada estudante cursa?
Solução:
Para solução dessa consulta temos que considerar a diferença entre a função COUNT(*) e COUNT("nome-coluna"). A função COUNT(*) conta o número de registros em cada grupo independente de haver alguma coluna com valor NULL. A função COUNT("nome-coluna") permite definirmos a coluna que será utilizada para contagem e caso o valor seja nulo (NULL), essa linha não será considerada na contagem geral.
Além disso, temos que considerar que algum estudante pode não ter se matriculado em disciplinas. Neste caso precisaremos fazer um LEFT OUTER JOIN entre a tabela estudante e estudante_matricula.
Abaixo mostramos uma consulta onde usamos as duas formas da função de agregação COUNT:
SELECT matricula, COUNT("codigo") AS num_disciplinas, COUNT(*) AS num_disciplinas
FROM estudante LEFT OUTER JOIN estudante_disciplina USING (matricula)
GROUP BY matricula
ORDER BY matricula;
Saída:
matricula | num_disciplinas | num_disciplinas
-----------+-----------------+-----------------
1 | 2 | 2
2 | 3 | 3
3 | 1 | 1
... ... ...
35 | 1 | 1
36 | 4 | 4
37 | 0 | 1
(37 rows)
Nota
Repare a diferença do resultado das formas da função de agregação COUNT na linha destacada acima.
21. Quais estudantes cursam três ou mais disciplinas?
Solução:
Esta consulta precisará contar o número de disciplinas para cada estudante e em seguida aplicar um filtro no resultado do grupo. Isto indica que precisaremos utilizar a cláusula HAVING como mostrado abaixo:
SELECT matricula, COUNT("codigo") AS num_disciplinas, extract(year from data_matricula) AS ano
FROM estudante INNET JOIN estudante_disciplina USING (matricula)
GROUP BY matricula
HAVING COUNT("codigo") >= 3
ORDER BY matricula;
Resultado:
matricula | num_disciplinas | ano
-----------+-----------------+------
2 | 3 | 2019
6 | 3 | 2020
7 | 5 | 2020
8 | 3 | 2020
12 | 3 | 2020
15 | 4 | 2021
18 | 3 | 2021
19 | 3 | 2021
23 | 3 | 2022
36 | 4 | 2019
(10 rows)
22. Quais estudantes matriculados no ano de 2020 cursam três ou mais disciplinas?
Solução:
Esta consulta é muito parecida com a consulta 21, com a diferença de que precisaremos acrescentar a cláusula WHERE para selecionar os estudantes que sejam do ano de 2020:
SELECT matricula, COUNT("codigo") AS num_disciplinas
FROM estudante INNET JOIN estudante_disciplina USING (matricula)
WHERE extract(year from data_matricula) = 2020
GROUP BY matricula
HAVING COUNT("codigo") >= 3
ORDER BY matricula;
Resultado:
matricula | num_disciplinas
-----------+-----------------
6 | 3
7 | 5
8 | 3
12 | 3
(4 rows)
23. Qual o número de disciplinas oferecidas por cada professor?
Solução:
SELECT professor.codigo, professor.nome, COUNT(disciplina.codigo) AS num_disciplinas
FROM professor LEFT OUTER JOIN disciplina ON (professor.codigo = disciplina.professor_codigo)
GROUP BY professor.codigo, professor.nome
ORDER BY professor.codigo;
Resultado:
codigo | nome | num_disciplinas
--------+----------+-----------------
1 | Romildo | 2
2 | Thales | 1
3 | Karine | 1
4 | Tamara | 4
5 | Carolina | 2
6 | Cleiton | 0
(6 rows)
24. Quantos créditos cada estudante cursa?
Solução:
Para solução dessa consulta precisaremos das seguintes tabelas: estudante, estudante_disciplina e disciplina.
SELECT matricula, estudante.nome AS nome_estudante,
COUNT(disciplina.codigo) AS num_disciplinas,
SUM(creditos) AS total_creditos
FROM estudante LEFT OUTER JOIN estudante_disciplina USING (matricula)
LEFT OUTER JOIN disciplina USING (codigo)
GROUP BY matricula
ORDER BY matricula;
Resultado:
matricula | nome_estudante | num_disciplinas | total_creditos
-----------+----------------+-----------------+----------------
1 | Eduardo | 2 | 10
2 | Maria | 3 | 11
3 | Eugenio | 1 | 2
... ... ... ...
35 | Josi | 1 | 2
36 | Antonio | 4 | 10
37 | Zuleica | 0 |
(37 rows)
Repare no resultado acima que a linha destacada possui uma soma nula (NULL). A função SUM assim como as demais funções de agregação, com exceção de COUNT, retornam NULL quando todas as linhas possuem valores nulo. Se nosso intuito é não ter valores nulo no resultado, podemos utilizar a função COALESCE junto com a função SUM. Veja a nova versão da consulta:
SELECT matricula, estudante.nome AS nome_estudante,
COUNT(disciplina.codigo) AS num_disciplinas,
COALESCE(SUM(creditos), 0) AS total_creditos
FROM estudante LEFT OUTER JOIN estudante_disciplina USING (matricula)
LEFT OUTER JOIN disciplina USING (codigo)
GROUP BY matricula
ORDER BY matricula;
Resultado:
matricula | nome_estudante | num_disciplinas | total_creditos
-----------+----------------+-----------------+----------------
1 | Eduardo | 2 | 10
2 | Maria | 3 | 11
3 | Eugenio | 1 | 2
... ... ... ...
35 | Josi | 1 | 2
36 | Antonio | 4 | 10
37 | Zuleica | 0 | 0
(37 rows)
25. Qual estudante realizou o maior número de créditos e quantos são esses créditos?
Solução:
Se alterarmos a cláusula ORDER BY na consulta 24, podemos ordenar de maneira decrescente pela coluna total_creditos e usar a cláusula LIMIT para obter apenas a quantidade de linhas desejada:
SELECT matricula, estudante.nome AS nome_estudante,
COUNT(disciplina.codigo) AS num_disciplinas,
COALESCE(SUM(creditos), 0) AS total_creditos
FROM estudante LEFT OUTER JOIN estudante_disciplina USING (matricula)
LEFT OUTER JOIN disciplina USING (codigo)
GROUP BY matricula
ORDER BY total_creditos DESC
LIMIT 1;
Resultado:
matricula | nome_estudante | num_disciplinas | total_creditos
-----------+----------------+-----------------+----------------
7 | Manuel | 5 | 15
(1 row)
26. Quantos estudantes estão matriculados em cada disciplina no ano de 2020?
Solução:
Vamos começar construindo uma consulta que liste o código e título de cada disciplina bem como o número de matrículo do estudante associado:
SELECT disciplina.codigo, disciplina.titulo, matricula
FROM estudante_disciplina,
disciplina
WHERE estudante_disciplina.codigo = disciplina.codigo
AND extract(year from estudante_disciplina.data) = 2020;
Resultado:
codigo | titulo | matricula
--------+-------------------+-----------
7 | Lingua Portuguesa | 4
8 | Lingua Inglesa | 5
9 | Lingua Francesa | 5
5 | Geografia | 6
6 | Historia | 6
7 | Lingua Portuguesa | 6
1 | Matemática | 7
8 | Lingua Inglesa | 7
9 | Lingua Francesa | 7
6 | Historia | 7
2 | Fisica | 7
1 | Matemática | 8
2 | Fisica | 8
3 | Bilogia | 8
4 | Quimica | 9
5 | Geografia | 9
8 | Lingua Inglesa | 12
9 | Lingua Francesa | 12
1 | Matemática | 12
(19 rows)
A partir da consulta acima, podemos construir uma nova consulta incluindo uma cláusula GROUP BY utilizando as colunas codigo e titulo da tabela disciplina para criação dos grupos de linhas para então utilizar a função COUNT na cláusula SELECT para contar o número de linhas de cada grupo. Desta maneira, temos a seguinte consulta:
SELECT disciplina.codigo, disciplina.titulo, COUNT(*) AS num_estudantes
FROM estudante_disciplina,
disciplina
WHERE estudante_disciplina.codigo = disciplina.codigo
AND extract(year from estudante_disciplina.data) = 2020
GROUP BY disciplina.codigo, disciplina.titulo;
Resultado:
codigo | titulo | num_estudantes
--------+-------------------+----------------
1 | Matemática | 3
2 | Fisica | 2
3 | Bilogia | 1
4 | Quimica | 1
5 | Geografia | 2
6 | Historia | 2
7 | Lingua Portuguesa | 2
8 | Lingua Inglesa | 3
9 | Lingua Francesa | 3
(9 rows)
Podemos ainda ordenar o resultado pelas disciplinas mais cursadas e, em caso de empate, utilizar a ordem alfabética como critério de desempate:
SELECT disciplina.codigo, disciplina.titulo, COUNT(*) AS num_estudantes
FROM estudante_disciplina,
disciplina
WHERE estudante_disciplina.codigo = disciplina.codigo
AND extract(year from estudante_disciplina.data) = 2020
GROUP BY disciplina.codigo, disciplina.titulo
ORDER BY num_estudantes DESC, disciplina.titulo ASC;
Resultado:
codigo | titulo | num_estudantes
--------+-------------------+----------------
9 | Lingua Francesa | 3
8 | Lingua Inglesa | 3
1 | Matemática | 3
2 | Fisica | 2
5 | Geografia | 2
6 | Historia | 2
7 | Lingua Portuguesa | 2
3 | Bilogia | 1
4 | Quimica | 1
(9 rows)
Dica
Para mais detalhes das consultas de agregação, consulte o manual do PostgreSQL nas seções 2.7. Aggregate Functions e 9.21. Aggregate Functions.
2.15.4. Sub-Consultas (Sub-Queries ou Sub-Selects)
A clásula FROM permite incluirmos não somenente nomes de tabelas mas também consultas SQL, que são denominadas de sub-consultas (sub-queries ou sub-selects). Uma subconsulta deve ser rotulada, isto é, devemos dar a ela um nome. Para compreender o uso deste recurso, nesta seção vamos construir algumas consultas que ilustram o uso de sub-consultas.
27. Qual a média de créditos cursados pelos estudantes?
Solução:
Para criar uma consulta que atenda a esta pergunta, podemos utilizar a consulta 24 como uma subconsulta e então tirar a média com a função AVG:
SELECT AVG(total_creditos) AS media_creditos
FROM (
SELECT matricula, estudante.nome AS nome_estudante,
COUNT(disciplina.codigo) AS num_disciplinas,
COALESCE(SUM(creditos), 0) AS total_creditos
FROM estudante LEFT OUTER JOIN estudante_disciplina USING (matricula)
LEFT OUTER JOIN disciplina USING (codigo)
GROUP BY matricula
) AS creditos_estudante;
Resultado:
media_creditos
--------------------
5.7567567567567568
(1 row)
Nota
Repare que para usarmos uma subconsulta, utilizamos os parênteses para delimitá-la (...) e demos o rótulo creditos_estudante a essa essa subconsulta.
2.15.5. Funções de Janela (Window Functions)
Além das funções de agregação existem funções que operam em um conjunto relacionado de linhas. Essas funções são chamadas de funções de janela (window functions). Duas funções de janela muito úteis são: rank e dense_rank. Vamos explorar o uso da função dense_rank nessa seção.
28. Qual a posição (ranking) de cada aluno em relação ao número de créditos cursado ao longo dos anos?
Solução:
Vamos começar construindo uma consulta que liste o ano de matricula, número de matrícula, nome e total de créditos cursados por cada estudante:
SELECT extract(year from data_matricula) AS ano_matricula,
matricula,
estudante.nome AS nome_estudante,
COALESCE(SUM(creditos), 0) AS total_creditos
FROM estudante LEFT OUTER JOIN estudante_disciplina USING (matricula)
LEFT OUTER JOIN disciplina USING (codigo)
GROUP BY matricula
ORDER BY ano_matricula, matricula;
Resultado:
ano_matricula | matricula | nome_estudante | total_creditos
---------------+-----------+----------------+----------------
2019 | 1 | Eduardo | 10
2019 | 2 | Maria | 11
... ... ... ...
2022 | 30 | Zoraide | 4
2022 | 35 | Josi | 2
(37 rows)
Em seguida, vamos construir uma nova consulta que utilize a consulta acima como subconsulta para podermos utilizar a função de janela dense_rank para criar partições no resultado:
SELECT estudantes_creditos.*,
dense_rank() OVER (PARTITION BY ano_matricula ORDER BY total_creditos DESC) AS posicao_aluno
FROM (
SELECT extract(year from data_matricula) AS ano_matricula,
matricula,
estudante.nome AS nome_estudante,
COALESCE(SUM(creditos), 0) AS total_creditos
FROM estudante LEFT OUTER JOIN estudante_disciplina USING (matricula)
LEFT OUTER JOIN disciplina USING (codigo)
GROUP BY matricula
) AS estudantes_creditos
ORDER BY ano_matricula ASC, posicao_aluno ASC;
Resultado:
1 ano_matricula | matricula | nome_estudante | total_creditos | posicao_aluno
2---------------+-----------+----------------+----------------+---------------
3 2019 | 2 | Maria | 11 | 1
4 2019 | 36 | Antonio | 10 | 2
5 2019 | 1 | Eduardo | 10 | 2
6 2019 | 11 | Carla | 4 | 3
7 2019 | 34 | Joana | 4 | 3
8 2019 | 10 | Claudio | 2 | 4
9 2019 | 31 | Roberta | 2 | 4
10 2019 | 3 | Eugenio | 2 | 4
11 2019 | 32 | Roberto | 2 | 4
12 2019 | 37 | Zuleica | 0 | 5
13 2020 | 7 | Manuel | 15 | 1
14 2020 | 8 | Jose | 12 | 2
15 2020 | 12 | Telma | 9 | 3
16 2020 | 6 | Ana Clara | 8 | 4
17 2020 | 9 | Ricardo | 5 | 5
18 2020 | 4 | Luiza | 4 | 6
19 2020 | 5 | Ana Maria | 3 | 7
20 2021 | 15 | Tassiana | 10 | 1
21 2021 | 18 | Carlos | 7 | 2
22 2021 | 19 | Fabio | 7 | 2
23 2021 | 16 | Julio | 7 | 2
24 2021 | 17 | Felipe | 6 | 3
25 2021 | 13 | Gilberto | 6 | 3
26 2021 | 14 | Lucia | 3 | 4
27 2021 | 33 | Antonio | 2 | 5
28 2022 | 25 | Marcia | 10 | 1
29 2022 | 27 | Everaldo | 10 | 1
30 2022 | 26 | Luis | 10 | 1
31 2022 | 28 | Katia | 10 | 1
32 2022 | 23 | Gilberto | 7 | 2
33 2022 | 30 | Zoraide | 4 | 3
34 2022 | 24 | Marco | 3 | 4
35 2022 | 29 | Emiliano | 3 | 4
36 2022 | 35 | Josi | 2 | 5
37 2022 | 21 | Joana | 1 | 6
38 2022 | 20 | Mateus | 1 | 6
39 2022 | 22 | Manuel | 1 | 6
40(37 rows)
Repare na consulta que a função de janela dense_rank() particiona as linhas resultantes da subconsulta pela coluna de saída ano_matricula e cria um ranking baseado na ordem dentro de cada partição pelo valor da coluna total_creditos.
Dica
Refaça a consulta acima substituindo a função dense_rank pela função rank e analise o resultado.
29. Quais as disciplina mais cursadas em 2020?
Solução:
Vamos começar construindo uma consulta que nos forneça o total de alunos matriculados em cada disciplina no ano de 2020:
SELECT disciplina.codigo, disciplina.titulo, COUNT(*) AS num_estudantes
FROM disciplina LEFT JOIN estudante_disciplina USING (codigo)
WHERE extract(year from estudante_disciplina.data) = 2020
GROUP BY disciplina.codigo, disciplina.titulo
ORDER BY num_estudantes DESC, disciplina.titulo ASC;
Resultado:
codigo | titulo | num_estudantes
--------+-------------------+----------------
9 | Lingua Francesa | 3
8 | Lingua Inglesa | 3
1 | Matemática | 3
2 | Fisica | 2
5 | Geografia | 2
6 | Historia | 2
7 | Lingua Portuguesa | 2
3 | Bilogia | 1
4 | Quimica | 1
(9 rows)
Podemos usar uma função de janela para classificar as linhas. Para isso, precisamos de uma coluna que permita criar uma partição única na tabela. Uma maneira de fazer isso é introduzir artificialmente uma coluna extra com um valor inteiro constante:
SELECT disciplina.codigo, disciplina.titulo, COUNT(*) AS num_estudantes, 1 AS particao
FROM disciplina LEFT JOIN estudante_disciplina USING (codigo)
WHERE extract(year from estudante_disciplina.data) = 2020
GROUP BY disciplina.codigo, disciplina.titulo
ORDER BY num_estudantes DESC, disciplina.titulo ASC;
Resultado:
codigo | titulo | num_estudantes | particao
--------+-------------------+----------------+----------
9 | Lingua Francesa | 3 | 1
8 | Lingua Inglesa | 3 | 1
1 | Matemática | 3 | 1
2 | Fisica | 2 | 1
5 | Geografia | 2 | 1
6 | Historia | 2 | 1
7 | Lingua Portuguesa | 2 | 1
3 | Bilogia | 1 | 1
4 | Quimica | 1 | 1
(9 rows)
Agora podemos utilizar essa consulta como uma subconsulta, como mostrado abaixo:
SELECT codigo,
titulo,
num_estudantes,
dense_rank() OVER ( PARTITION BY particao ORDER BY num_estudantes DESC) AS posicao
FROM (
SELECT disciplina.codigo, disciplina.titulo, COUNT(*) AS num_estudantes, 1 AS particao
FROM disciplina LEFT JOIN estudante_disciplina USING (codigo)
WHERE extract(year from estudante_disciplina.data) = 2020
GROUP BY disciplina.codigo, disciplina.titulo
) AS subconsulta_rank;
Resultado:
codigo | titulo | num_estudantes | posicao
--------+-------------------+----------------+---------
1 | Matemática | 3 | 1
8 | Lingua Inglesa | 3 | 1
9 | Lingua Francesa | 3 | 1
5 | Geografia | 2 | 2
6 | Historia | 2 | 2
7 | Lingua Portuguesa | 2 | 2
2 | Fisica | 2 | 2
4 | Quimica | 1 | 3
3 | Bilogia | 1 | 3
(9 rows)
Finalmente, podemos criar mais um nível de consulta para selecionar apenas as linhas da posição 1:
SELECT *
FROM (
SELECT codigo,
titulo,
num_estudantes,
dense_rank() OVER ( PARTITION BY particao ORDER BY num_estudantes DESC) AS posicao
FROM (
SELECT disciplina.codigo, disciplina.titulo, COUNT(*) AS num_estudantes, 1 AS particao
FROM disciplina LEFT JOIN estudante_disciplina USING (codigo)
WHERE extract(year from estudante_disciplina.data) = 2020
GROUP BY disciplina.codigo, disciplina.titulo
) AS subconsulta_1
) AS subconsulta_2
WHERE posicao = 1;
Resultado:
codigo | titulo | num_estudantes | posicao
--------+-----------------+----------------+---------
1 | Matemática | 3 | 1
8 | Lingua Inglesa | 3 | 1
9 | Lingua Francesa | 3 | 1
(3 rows)
Dica
Para mais detalhes das consultas de janela, consulte o manual do PostgreSQL nas seções 3.5. Window Functions e 9.22. Window Functions.
2.15.6. Criando Tabelas a partir de Consultas
Podemos utilizar os comandos CREATE TABLE e SELECT em conjunto, para criar tabelas a partir do resultado de consultas.
A sintaxe básica desse comando é a seguinte:
CREATE TABLE nome-tabela AS consulta;
Vamos criar uma tabela chamada prof_disciplina a partir da relação entre professores e disciplinas:
CREATE TABLE prof_disciplina AS
SELECT professor.nome, disciplina.*
FROM professor INNER JOIN disciplina ON professor.codigo = disciplina.professor_codigo;
Se realizarmos uma consulta na nova tabela criada prof_disciplina, obteremos o seguinte resultado:
nome | codigo | titulo | creditos | professor_codigo
----------+--------+-------------------+----------+------------------
Romildo | 1 | Matemática | 6 | 1
Romildo | 2 | Fisica | 4 | 1
Thales | 3 | Bilogia | 2 | 2
Karine | 4 | Quimica | 3 | 3
Tamara | 5 | Geografia | 2 | 4
Tamara | 6 | Historia | 2 | 4
Tamara | 7 | Lingua Portuguesa | 4 | 4
Carolina | 8 | Lingua Inglesa | 2 | 5
Carolina | 9 | Lingua Francesa | 1 | 5
Tamara | 10 | Ciências | 1 | 4
(10 rows)