Programação de Banco de Dados
Linguagem SQL
Conteúdo
Introdução
SQL
• Agrupamento
• Ordenação
• Aninhamento
BD Exemplo – Mannino
Modelo Relacional – BD exemplo Livro Mannino
Aluno
Professor
Oferecimento
Curso
Matricula
Conteúdo BD
Aluno
Conteúdo BD
Professor
Conteúdo BD
Oferecimento
Conteúdo BD
Curso
Conteúdo BD
Matricula
SQL Básica
Consultas usando SELECT seguem o seguinte padrão
SELECT <lista de colunas e expressões usualmente envolvendo colunas>
FROM <lista de tabelas e operações de junção>
WHERE <lista de condições de linha com os conectivos lógicos AND, OR, NOT>
ORDER BY <lista de especificações de ordenação>
SQL Básica
Consultas usando SELECT seguem o seguinte padrão
SELECT <lista de colunas e expressões usualmente envolvendo colunas>
FROM <lista de tabelas e operações de junção>
WHERE <lista de condições de linha com os conectivos lógicos AND, OR, NOT>
GROUP BY <lista de colunas de agrupamento>
HAVING <lista de condições de grupo com os conectivos lógicos AND, OR, NOT>
ORDER BY <lista de especificações de ordenação>
Agrupamentos com GROUP BY
Agrupar em uma única coluna
• Resumir a média geral de notas de estudantes por
área de especialização
SELECT Especializacao, MediaAluno
FROM Aluno
Agrupamentos com GROUP BY
Agrupar em uma única coluna
• Resumir a média geral de notas de estudantes por
área de especialização
SELECT Especializacao, AVG(MediaAluno) AS MediaGeral
FROM Aluno
GROUP BY Especializacao
Agrupamentos com GROUP BY
Funções de agregação
Todas as funções de agregação do Postgres:
[Link]
Agrupamentos com GROUP BY
Contar linhas e colunas de valores únicos
• Resumir a quantidade de oferecimentos e os cursos
únicos por ano
SELECT AnoOfer, COUNT(*) AS QtdeOfer, COUNT(DISTINCT NumCurso) AS QtdeCursos
FROM Oferecimento
GROUP BY AnoOfer
Agrupamento condicional [1]
Agrupar com condição de linha
• Resumir a média geral de notas dos estudantes da
divisão superior (júnior – ‘JR’ e sênior – ‘SR’) por
área de especialização
SELECT Especializacao, AVG(MediaAluno) AS MediaGeral
FROM Aluno
WHERE Turma = 'JR' OR Turma = 'SR'
GROUP BY Especializacao
Agrupamento condicional [2]
Agrupar com Condições de Linha e Grupo
• Resumir a média geral de notas (GPA) dos estudantes da divisão
superior (júnior e sênior) por área de especialização. Listar
somente as especializações com média geral maior ou igual a 6,1
SELECT Especializacao, AVG(MediaAluno) AS MediaGeral
FROM Aluno
WHERE Turma IN ('JR', 'SR')
GROUP BY Especializacao
HAVING AVG(MediaAluno) >= 6.1
Agrupamento condicional [2]
Agrupar com Condições de Linha e Grupo
• Resumir a média geral de notas (GPA) dos estudantes da divisão
superior (júnior e sênior) por área de especialização. Listar
somente as especializações com média geral maior ou igual a 6,1
SELECT Especializacao, AVG(MediaAluno) AS MediaGeral
FROM Aluno Lembrete: a cláusula
WHERE Turma IN ('JR', 'SR') HAVING deve sempre ser
precedida pela cláusula
GROUP BY Especializacao
HAVING AVG(MediaAluno) >= 6.1
GROUP BY
Agrupamento em dois níveis
Agrupar em duas colunas
• Resumir a média geral de notas mínima e máxima
dos estudantes por área de especialização e turma
SELECT Especializacao, Turma, MIN(MediaAluno) AS MenorMedia,
MAX(MediaAluno) AS MaiorMedia
FROM Aluno
GROUP BY Especializacao, Turma
ORDER BY Especializacao, Turma
Agrupamentos e Junções
Combinar agrupamentos e junções
• Resumir o número de oferecimentos de curso SI por
descrição de curso
SELECT DescrCurso, COUNT(*) AS QtdeOferecimentos
FROM Curso JOIN Oferecimento
ON [Link] = [Link]
WHERE [Link] LIKE 'SI%'
GROUP BY DescrCurso
ORDER BY DescrCurso
Ver próximo slide para entender
melhor!
Agrupamentos e Junções
Combinar agrupamentos e junções
• Resumir o número de oferecimentos de curso SI por
descrição de curso
SELECT DescrCurso, [Link]
FROM Curso JOIN Oferecimento
ON [Link] = [Link]
ORDER BY DescrCurso
SELECT DescrCurso, COUNT(*) AS QtdeOferecimentos
FROM Curso JOIN Oferecimento
ON [Link] = [Link]
WHERE [Link] LIKE 'SI%'
GROUP BY DescrCurso
Exemplo detalhado
Listar o número do curso, o número de
oferecimentos e a média geral de notas dos
estudantes matriculados nos oferecimentos de curso
SI no outono, em que haja mais de um aluno
matriculado
Exemplo detalhado
Listar o número do curso, o número de
oferecimentos e a média geral de notas dos
estudantes matriculados nos oferecimentos de curso
SI no outono, em que haja mais de um aluno
matriculado
Oferecimento
Exemplo detalhado
Listar o número do curso, o número de
oferecimentos e a média geral de notas dos
estudantes matriculados? nos oferecimentos de
curso SI no outono, em que haja mais de um aluno
matriculado.
Oferecimento
Exemplo detalhado
Listar o número do curso, o número de
oferecimentos e a média geral de notas dos
estudantes matriculados nos oferecimentos de
curso SI no outono, em que haja mais de um aluno
matriculado
Oferecimento
Matricula
Exemplo detalhado
Listar o número do curso, o número de oferecimentos e
a média geral de notas dos estudantes matriculados nos
oferecimentos de curso SI no outono, em que haja mais
de um aluno matriculado.
Oferecimento
SELECT [Link], [Link]
FROM Oferecimento O
Exemplo detalhado
SELECT [Link], [Link]
FROM Oferecimento O
Exemplo detalhado
Listar o número do curso, o número de oferecimentos e
a média geral de notas dos estudantes matriculados nos
oferecimentos de curso SI no outono, em que haja mais
de um aluno matriculado.
Oferecimento
SELECT [Link], [Link], [Link]
FROM Oferecimento O
Exemplo detalhado
SELECT [Link], [Link], [Link]
FROM Oferecimento O
Exemplo detalhado
Listar o número do curso, o número de oferecimentos e
a média geral de notas dos estudantes matriculados nos
oferecimentos de curso SI no outono, em que haja mais
de um aluno matriculado.
Oferecimento
SELECT [Link], [Link], [Link]
FROM Oferecimento O
WHERE NumCurso LIKE 'SI%' AND TrimestreOfer = 'OUTONO'
Exemplo detalhado
SELECT [Link], [Link], [Link]
FROM Oferecimento O
WHERE NumCurso LIKE 'SI%' AND TrimestreOfer = 'OUTONO'
Exemplo detalhado
Listar o número do curso, o número de oferecimentos e
a média geral de notas dos estudantes matriculados nos
oferecimentos de curso SI no outono, em que haja mais
de um aluno matriculado.
Oferecimento
Matricula
SELECT [Link], [Link],
[Link], [Link], [Link],
[Link]
FROM Oferecimento O JOIN Matricula M
ON [Link] = [Link]
WHERE NumCurso LIKE 'SI%'
AND TrimestreOfer = 'OUTONO'
Exemplo detalhado
SELECT [Link], [Link], [Link], [Link], [Link], [Link]
FROM Oferecimento O JOIN Matricula M ON [Link] = [Link]
WHERE NumCurso LIKE 'SI%' AND TrimestreOfer = 'OUTONO'
Exemplo detalhado
Listar o número do curso, o número de oferecimentos e
a média geral de notas dos estudantes matriculados nos
oferecimentos de curso SI no outono, em que haja mais
de um alunos matriculados.
Oferecimento
Matricula
SELECT [Link], [Link], COUNT(*) as QuantAlunos
FROM Oferecimento O JOIN Matricula M
ON [Link] = [Link]
WHERE NumCurso LIKE 'SI%' AND TrimestreOfer = 'OUTONO'
GROUP BY [Link], [Link]
Exemplo detalhado
SELECT [Link], [Link], COUNT(*) as QuantAlunos
FROM Oferecimento O JOIN Matricula M
ON [Link] = [Link]
WHERE NumCurso LIKE 'SI%' AND TrimestreOfer = 'OUTONO'
GROUP BY [Link], [Link]
Exemplo detalhado
Listar o número do curso, o número de oferecimentos e
a média geral de notas dos estudantes matriculados nos
oferecimentos de curso SI no outono, em que haja mais
de um aluno matriculado.
Oferecimento
Matricula
SELECT [Link], [Link], COUNT(*) as QuantAlunos
FROM Oferecimento O JOIN Matricula M
ON [Link] = [Link]
WHERE NumCurso LIKE 'SI%' AND TrimestreOfer = 'OUTONO'
GROUP BY [Link], [Link]
HAVING COUNT(*) > 1
Exemplo detalhado
Listar o número do curso, o número de oferecimentos e
a média geral de notas dos estudantes matriculados nos
oferecimentos de curso SI no outono, em que haja mais
de um aluno matriculado.
Oferecimento
Matricula
SELECT [Link], [Link], AVG([Link]) AS Media
FROM Oferecimento O JOIN Matricula M
ON [Link] = [Link]
WHERE NumCurso LIKE 'SI%' AND TrimestreOfer = 'OUTONO'
GROUP BY [Link], [Link]
HAVING COUNT(*) > 1
Exemplo detalhado
SELECT [Link], [Link], AVG([Link]) AS Media
FROM Oferecimento O JOIN Matricula M ON [Link] = [Link]
WHERE NumCurso LIKE 'SI%' AND TrimestreOfer = 'OUTONO'
GROUP BY [Link], [Link]
HAVING COUNT(*) > 1
Consultas aninhadas
Algumas consultas precisam que os valores
existentes no banco de dados sejam buscados e
depois usados em uma condição de comparação
Essas consultas podem ser formuladas
convenientemente usando consultas aninhadas, que
são blocos select-from-where completos dentro
da cláusula WHERE de outra consulta
o operador de comparação IN , que compara um valor
v com um conjunto (ou multiconjunto) de valores V e
avalia como TRUE se v for um dos elementos em V
Consultas aninhadas
Listar os nomes de professores cujo estado seja ES
SELECT NomeProf, UFProf
FROM Professor
WHERE UFProf IN ('ES')
Listar os nomes de professores cujo estado não seja
ES
SELECT NomeProf, UFProf
FROM Professor
WHERE UFProf NOT IN ('ES')
Consultas aninhadas
Listar os nomes de professores cujo estado seja o
mesmo estado do prof RODRIGO
SELECT NomeProf, UFProf
FROM Professor
WHERE UFProf IN
(SELECT UFProf
FROM Professor
WHERE NomeProf = 'RODRIGO'
)
Exercício
Escreva a tabela resultante do retorno da consulta mais
interna
Escreva a tabela resultante do retorno da consulta completa
SELECT NomeProf, [Link], [Link]
FROM Professor JOIN Oferecimento O1
ON [Link] = [Link]
WHERE [Link] IN
(SELECT [Link]
FROM Professor JOIN Oferecimento O2
ON [Link] = [Link]
WHERE AnoOfer = 2006)
Escreva com suas palavras o quê a consulta abaixo faz
Exercício - Resposta
SELECT [Link]
FROM Professor JOIN Oferecimento O2
ON [Link] = [Link]
WHERE AnoOfer = 2006
Tabela resultante (professores que lecionaram
disciplinas em 2006)
professores que lecionaram
disciplinas em qualquer ano
SELECT [Link]
FROM Professor JOIN Oferecimento O2
ON [Link] = [Link]
WHERE AnoOfer = 2006
Exercício - Resposta
SELECT NomeProf, [Link], [Link]
FROM Professor JOIN Oferecimento O1
ON [Link] = [Link]
WHERE [Link] IN
(SELECT [Link]
FROM Professor JOIN Oferecimento O2
ON [Link] = [Link]
WHERE AnoOfer = 2006)
SELECT [Link], [Link], [Link], [Link]
FROM Professor P JOIN Oferecimento O1
ON [Link] = [Link]
ORDER BY [Link]
SELECT [Link]
FROM Professor P JOIN Oferecimento O2
ON [Link] = [Link]
WHERE AnoOfer = 2006
ORDER BY [Link]
Exercício - Resposta
Escreva com suas palavras o quê a consulta abaixo faz
SELECT NomeProf, [Link], [Link]
FROM Professor, Oferecimento O1
WHERE [Link] = [Link]
AND [Link] IN
(SELECT CPFProf
FROM Professor, Oferecimento O2
WHERE [Link] = [Link]
AND AnoOfer = 2006)
A consulta lista os nomes dos professores e o
número e ano do curso lecionado por eles cujo
supervisor lecionou disciplinas em 2006
Exercício
Dada a tabela,
desenvolva um
comando SELECT
que encontre a
idade do marinheiro
mais jovem que
tenha no mínimo 18
anos para cada nível
de avaliação com no
mínimo dois
marinheiros
Exercício - Resposta
SELECT [Link]ção, MIN([Link]) AS minIdade
FROM Marinheiros M
WHERE [Link] >=18
GROUP BY [Link]ção
HAVING COUNT(*) > 1
Exercício - Resposta
SELECT [Link]ção, MIN([Link]) AS minIdade
FROM Marinheiros M
WHERE [Link] >=18
GROUP BY [Link]ção
HAVING COUNT(*) > 1
Exercício - Resposta
SELECT [Link]ção, MIN([Link]) AS minIdade
FROM Marinheiros M
WHERE [Link] >=18
GROUP BY [Link]ção
HAVING COUNT(*) > 1
Exercício - Resposta
SELECT [Link]ção, MIN([Link]) AS minIdade
FROM Marinheiros M
WHERE [Link] >=18
GROUP BY [Link]ção
HAVING COUNT(*) > 1