Questão 1
SELECT * FROM aluno
ORDER BY nome ASC, data_nascimento ASC;
Questão 3
SELECT nome, especialidade FROM professor
ORDER BY nome DESC;
Questão 3
SELECT * FROM curso
ORDER BY carga_horaria DESC;
Questão 4
SELECT status, COUNT(*) AS num_alunos
FROM matricula
GROUP BY status;
Questão 5
SELECT [Link] AS curso_nome, SUM(d.carga_horaria) AS carga_horaria_total
FROM curso c
JOIN curso d ON d.id_curso = c.id_curso
GROUP BY [Link];
Questão 6
SELECT [Link] FROM professor p
JOIN turma t ON t.id_professor = p.id_professor
GROUP BY [Link]
HAVING COUNT(t.id_turma) > 3;
Questão 7
SELECT [Link] FROM aluno a
JOIN matricula m ON m.id_aluno = a.id_aluno
GROUP BY [Link]
HAVING COUNT(m.id_disciplina) > 1
ORDER BY COUNT(m.id_disciplina);
Questão 8
SELECT [Link], c.carga_horaria
FROM curso c
WHERE carga_horaria > 2000; --ou
SELECT [Link], sum(d.carga_horaria) total_carga_horaria
FROM curso c
INNER JOIN disciplina d ON d.id_curso = c.id_curso
GROUP BY [Link]
HAVING sum(d.carga_horaria) > 2000;
Questão 9
SELECT [Link], count(t.id_professor) total_turmas
FROM turma t
INNER JOIN professor p ON p.id_professor = t.id_professor
GROUP BY [Link]
ORDER BY total_turmas DESC;
Questão 10
SELECT [Link], avg(d.carga_horaria) média_carga_horária_disciplinas
FROM curso c
INNER JOIN disciplina d ON d.id_curso = c.id_curso
GROUP BY [Link];
Questão 11
SELECT [Link], [Link], m.data_matricula
FROM aluno a
INNER JOIN matricula m ON m.id_aluno = a.id_aluno
ORDER BY [Link], m.data_matricula DESC;
Questão 12
SELECT [Link], 2024 - EXTRACT(YEAR FROM a.data_nascimento) idade
FROM aluno a
ORDER BY idade DESC;
Questão 13
SELECT [Link], count(m.id_aluno) numero_alunos
FROM disciplina d
INNER JOIN matricula m ON m.id_disciplina = d.id_disciplina
GROUP BY [Link]
ORDER BY numero_alunos DESC;
Questão 14
SELECT [Link], [Link], [Link]
FROM turma
JOIN disciplina ON turma.id_disciplina = disciplina.id_disciplina
JOIN professor ON turma.id_professor = professor.id_professor
ORDER BY [Link];
Questão 15
SELECT COUNT(*) as quantidade_disciplinas
FROM curso
WHERE curso.carga_horaria > 80;
Questão 16
SELECT [Link], COUNT(disciplina.id_disciplina) AS quantidade_disciplinas
FROM curso
JOIN disciplina ON curso.id_curso = disciplina.id_curso
GROUP BY [Link];
Questão 17
SELECT [Link] AS nome_professor
FROM professor
JOIN turma ON professor.id_professor = turma.id_professor
JOIN disciplina ON turma.id_disciplina = disciplina.id_disciplina
JOIN curso ON disciplina.id_curso = curso.id_curso
WHERE curso.carga_horaria > 100
GROUP BY [Link]
HAVING COUNT(disciplina.id_disciplina) > 2;
Questão 18
SELECT [Link]
FROM disciplina d
JOIN matricula m ON d.id_disciplina = m.id_disciplina
GROUP BY [Link]
HAVING COUNT(m.id_aluno) >= 5;
Questão 19
SELECT status, COUNT(id_aluno) as alunos
from matricula
GROUP by status
ORDER BY alunos desc
Questão 20
SELECT [Link] AS nome_professor, SUM(d.carga_horaria) AS hora_total
FROM professor p
INNER JOIN turma t ON p.id_professor = t.id_professor
INNER JOIN disciplina d ON t.id_disciplina = d.id_disciplina
GROUP BY [Link]
ORDER BY hora_total DESC;