Exercícios Práticos de Consulta SQL com MySQL

Este documento apresenta uma coleção de desafios de consulta SQL, utilizando um esquema de banco de dados modelado para gerenciar informações acadêmicas. O objetivo é praticar diversas operações de consulta em MySQL, desde as mais básicas até as mais comlpexas, abordando junções, subconsultas, agregações e funções de janela.

Estrutura do Banco de Dados

O banco de dados será composto por cinco tabelas principais:

class (Turmas): Armazena informações sobre as turmas.
student (Alunos): Contém dados dos alunos, com referência à turma.
teacher (Professores): Lista os professores disponíveis.
course (Cursos/Disciplinas): Detalha as disciplinas oferecidas e seus professores responsáveis.
score (Notas): Registra as notas dos alunos em cada disciplina.

Configuração do Ambiente e Dados Iniciais

Criação do Banco de Dados

Para começar, crie o banco de dados chamado escola_db com codificação UTF-8:

CREATE DATABASE escola_db CHARSET = 'utf8mb4' COLLATE = 'utf8mb4_unicode_ci';
USE escola_db;

Definição das Tabelas

As tabelas e seus respectivos esquemas são criados com chaves primárias e estrangeiras para manter a integridade referencial:

CREATE TABLE class (
 cid INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
 caption VARCHAR(20) NOT NULL
) DEFAULT CHARSET = 'utf8mb4';

CREATE TABLE student (
 sid INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
 sname VARCHAR(20) NOT NULL,
 gender VARCHAR(10),
 class_id INT,
 CONSTRAINT fk_turma_aluno FOREIGN KEY (class_id) REFERENCES class (cid)
) DEFAULT CHARSET = 'utf8mb4';

CREATE TABLE teacher (
 tid INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
 tname VARCHAR(32) NOT NULL
) DEFAULT CHARSET = 'utf8mb4';

CREATE TABLE course (
 cid INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
 cname VARCHAR(20) NOT NULL,
 teacher_id INT, -- Alterado de 'tearch_id' para 'teacher_id' para melhor legibilidade
 CONSTRAINT fk_professor_curso FOREIGN KEY (teacher_id) REFERENCES teacher (tid)
) DEFAULT CHARSET = 'utf8mb4';

CREATE TABLE score (
 sid INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
 student_id INT,
 course_id INT, -- Alterado de 'corse_id' para 'course_id' para melhor legibilidade
 number INT,
 CONSTRAINT fk_nota_aluno FOREIGN KEY (student_id) REFERENCES student (sid),
 CONSTRAINT fk_nota_curso FOREIGN KEY (course_id) REFERENCES course (cid)
) DEFAULT CHARSET = 'utf8mb4';

Adição de Restrição de Unicidade

Uma restrição única é adicionada à tabela score para garantir que um aluno não tenha mais de uma nota para a mesma disciplina:

ALTER TABLE score
 ADD UNIQUE idx_aluno_curso (student_id, course_id);

Exercícios de Consulta SQL

1. Comparar Notas Entre Disciplinas

Encontre os IDs dos alunos que obtiveram uma nota maior na disciplina de "Biologia" do que na disciplina de "Física".

SELECT
   s_biologia.student_id
FROM
   score AS s_biologia
JOIN
   course AS c_biologia ON s_biologia.course_id = c_biologia.cid
JOIN
   score AS s_fisica ON s_biologia.student_id = s_fisica.student_id
JOIN
   course AS c_fisica ON s_fisica.course_id = c_fisica.cid
WHERE
   c_biologia.cname = 'Biologia'
   AND c_fisica.cname = 'Física'
   AND s_biologia.number > s_fisica.number;

2. Alunos com Média Acima de um Limite

Liste os IDs e as médias dos alunos cuja média geral de notas seja superior a 60.

SELECT
   n.student_id AS ID_Aluno,
   AVG(n.number) AS MediaGeral
FROM
   score n
GROUP BY
   n.student_id
HAVING
   AVG(n.number) > 60;

3. Sumário de Alunos: Disciplinas e Total de Notas

Para cada aluno, exiba seu ID, nome, o número total de disciplinas que cursou e a soma total de suas notas.

SELECT
   a.sid AS ID_Aluno,
   a.sname AS Nome_Aluno,
   COUNT(n.course_id) AS Total_Disciplinas,
   SUM(n.number) AS Soma_Total_Notas
FROM
   student a
LEFT JOIN
   score n ON a.sid = n.student_id
GROUP BY
   a.sid, a.sname;

4. Contagem de Professores por Sobrenome

Calcule quantos professores têm o sobrenome "Silva".

SELECT
   COUNT(tid) AS Total_Professores_Silva
FROM
   teacher
WHERE
   tname LIKE 'Silva%';

5. Alunos que Não Cursaram Disciplinas de um Professor Específico

Encontre os IDs e nomes dos alunos que nunca cursaram nenhuma disciplina ministrada pelo "Prof. Costa".

SELECT
   a.sid AS ID_Aluno,
   a.sname AS Nome_Aluno
FROM
   student a
WHERE
   a.sid NOT IN (
       SELECT DISTINCT s.student_id
       FROM score s
       JOIN course c ON s.course_id = c.cid
       JOIN teacher t ON c.teacher_id = t.tid
       WHERE t.tname = 'Prof. Costa'
   );

6. Alunos que Cursaram Múltiplas Disciplinas Específicas

Liste os IDs e nomes dos alunos que cursaram tanto a disciplina de ID '2' quanto a disciplina de ID '3'.

SELECT
   s.sid AS ID_Aluno,
   s.sname AS Nome_Aluno
FROM
   student s
JOIN
   score n ON s.sid = n.student_id
WHERE
   n.course_id IN (2, 3)
GROUP BY
   s.sid, s.sname
HAVING
   COUNT(DISTINCT n.course_id) = 2;

7. Alunos que Cursaram Todas as Disciplinas de um Professor Específico

Identifique os IDs e nomes dos alunos que cursaram todas as disciplinas ministradas pelo "Prof. Almeida".

SELECT
   a.sid AS ID_Aluno,
   a.sname AS Nome_Aluno
FROM
   student a
WHERE NOT EXISTS (
   SELECT c.cid
   FROM course c
   JOIN teacher t ON c.teacher_id = t.tid
   WHERE t.tname = 'Prof. Almeida'
   EXCEPT
   SELECT n.course_id
   FROM score n
   WHERE n.student_id = a.sid
);
-- Nota: O operador EXCEPT não está disponível em MySQL. Uma alternativa é usar LEFT JOIN com IS NULL ou subconsultas.
-- Segue uma implementação alternativa para MySQL sem EXCEPT:
SELECT
   a.sid AS ID_Aluno,
   a.sname AS Nome_Aluno
FROM
   student a
WHERE
   a.sid IN (
       SELECT n.student_id
       FROM score n
       JOIN course c ON n.course_id = c.cid
       JOIN teacher t ON c.teacher_id = t.tid
       WHERE t.tname = 'Prof. Almeida'
       GROUP BY n.student_id
       HAVING COUNT(DISTINCT n.course_id) = (
           SELECT COUNT(c_all.cid)
           FROM course c_all
           JOIN teacher t_all ON c_all.teacher_id = t_all.tid
           WHERE t_all.tname = 'Prof. Almeida'
       )
   );

9. Comparação de Notas para Duas Disciplinas (Repetição do problema 1 com ID de disciplina)

Liste os IDs e nomes dos alunos que obtiveram uma nota mais baixa na disciplina de ID '2' do que na disciplina de ID '1'.

SELECT
   a.sid AS ID_Aluno,
   a.sname AS Nome_Aluno
FROM
   student a
JOIN
   score s1 ON a.sid = s1.student_id AND s1.course_id = 1
JOIN
   score s2 ON a.sid = s2.student_id AND s2.course_id = 2
WHERE
   s2.number < s1.number;

10. Alunos com Pelo Menos Uma Nota Abaixo de 60

Encontre os IDs e nomes dos alunos que têm pelo menos uma nota inferior a 60.

SELECT DISTINCT
   a.sid AS ID_Aluno,
   a.sname AS Nome_Aluno
FROM
   student a
JOIN
   score n ON a.sid = n.student_id
WHERE
   n.number < 60;

11. Alunos que Não Cursaram Todas as Disciplinas

Liste os IDs e nomes dos alunos que não cursaram todas as disciplinas disponíveis.

SELECT
   a.sid AS ID_Aluno,
   a.sname AS Nome_Aluno
FROM
   student a
LEFT JOIN
   (SELECT student_id, COUNT(DISTINCT course_id) AS num_courses FROM score GROUP BY student_id) AS aluno_cursos ON a.sid = aluno_cursos.student_id
WHERE
   aluno_cursos.num_courses IS NULL OR aluno_cursos.num_courses < (SELECT COUNT(cid) FROM course);

12. Alunos com Pelo Menos uma Disciplina em Comum com Aluno '1'

Encontre os IDs e nomes de outros alunos (exceto o aluno de ID '1') que cursaram pelo menos uma disciplina em comum com o aluno de ID '1'.

SELECT DISTINCT
   a.sid AS ID_Aluno,
   a.sname AS Nome_Aluno
FROM
   student a
JOIN
   score n_outros ON a.sid = n_outros.student_id
WHERE
   a.sid != 1 AND n_outros.course_id IN (
       SELECT course_id
       FROM score
       WHERE student_id = 1
   )
GROUP BY a.sid, a.sname; -- Agrupar para garantir um resultado por aluno, se necessário

13. Alunos que Cursaram Todas as Disciplinas do Aluno '2'

Encontre os IDs e nomes de outros alunos (exceto o aluno de ID '2') que cursaram todas as disciplinas que o aluno de ID '2' cursou.

SELECT
   s.sid AS ID_Aluno,
   s.sname AS Nome_Aluno
FROM
   student s
WHERE
   s.sid != 2 AND NOT EXISTS (
       SELECT n_aluno2.course_id
       FROM score n_aluno2
       WHERE n_aluno2.student_id = 2
       AND n_aluno2.course_id NOT IN (
           SELECT n_outro_aluno.course_id
           FROM score n_outro_aluno
           WHERE n_outro_aluno.student_id = s.sid
       )
   );

14. Alunos com Exatamente as Mesmas Disciplinas do Aluno '1'

Encontre os IDs e nomes de outros alunos que cursaram exatamente o mesmo conjunto de disciplinas que o aluno de ID '1'.

SELECT
   a.sid AS ID_Aluno,
   a.sname AS Nome_Aluno
FROM
   student a
WHERE
   a.sid != 1 AND (
       SELECT GROUP_CONCAT(n.course_id ORDER BY n.course_id)
       FROM score n
       WHERE n.student_id = a.sid
   ) = (
       SELECT GROUP_CONCAT(n_aluno1.course_id ORDER BY n_aluno1.course_id)
       FROM score n_aluno1
       WHERE n_aluno1.student_id = 1
   );

15. Excluir Registros de Notas de um Professor Específico

Exclua todos os registros da tabela score correspondentes a disciplinas ministradas pelo "Prof. Silva".

DELETE FROM score
WHERE score.course_id IN (
   SELECT c.cid
   FROM course c
   JOIN teacher t ON c.teacher_id = t.tid
   WHERE t.tname = 'Prof. Silva'
);

16. Inserir Notas Médias para Disciplinas Não Cursadas

Insira novos registros na tabela score para alunos que ainda não cursaram a disciplina de ID '2'. A nota atribuída deve ser a média atual das notas da disciplina de ID '2'.

INSERT INTO score (student_id, course_id, number)
SELECT
   a.sid,
   2, -- ID da disciplina
   (SELECT AVG(s_existente.number) FROM score s_existente WHERE s_existente.course_id = 2)
FROM
   student a
WHERE
   a.sid NOT IN (
       SELECT s_cursado.student_id
       FROM score s_cursado
       WHERE s_cursado.course_id = 2
   );

17. Tabela Dinâmica de Notas e Médias por Aluno

Exiba as notas de "Matemática", "Física" e "História" para cada aluno, juntamente com o número de disciplinas válidas e a média geral das notas, ordenado pela média geral (ascendente).

SELECT
   s.sid AS ID_Aluno,
   s.sname AS Nome_Aluno,
   MAX(CASE WHEN c.cname = 'Matemática' THEN sc.number ELSE NULL END) AS Matematica,
   MAX(CASE WHEN c.cname = 'Física' THEN sc.number ELSE NULL END) AS Fisica,
   MAX(CASE WHEN c.cname = 'História' THEN sc.number ELSE NULL END) AS Historia,
   COUNT(sc.course_id) AS Disciplinas_Cursadas,
   AVG(sc.number) AS Media_Ponderada
FROM
   student s
JOIN
   score sc ON s.sid = sc.student_id
JOIN
   course c ON sc.course_id = c.cid
WHERE
   c.cname IN ('Matemática', 'Física', 'História')
GROUP BY
   s.sid, s.sname
ORDER BY
   Media_Ponderada ASC;

18. Notas Máximas e Mínimas por Disciplina

Para cada disciplina, mostre o ID da disciplina, o nome da disciplina, a nota mais alta e a nota mais baixa obtidas.

SELECT
   c.cid AS ID_Disciplina,
   c.cname AS Nome_Disciplina,
   MAX(n.number) AS Nota_Maxima,
   MIN(n.number) AS Nota_Minima
FROM
   course c
LEFT JOIN
   score n ON c.cid = n.course_id
GROUP BY
   c.cid, c.cname;

19. Média de Notas e Taxa de Aprovação por Disciplina

Exiba a média de notas e a porcentagem de aprovação (notas > 60) para cada disciplina, ordenado pela média de notas (ascendente) e depois pela porcentagem de aprovação (descendente).

SELECT
   c.cid AS ID_Disciplina,
   c.cname AS Nome_Disciplina,
   AVG(n.number) AS Media_Notas,
   (SUM(CASE WHEN n.number >= 60 THEN 1 ELSE 0 END) / COUNT(n.number)) * 100 AS Percentual_Aprovacao
FROM
   course c
LEFT JOIN
   score n ON c.cid = n.course_id
GROUP BY
   c.cid, c.cname
ORDER BY
   Media_Notas ASC, Percentual_Aprovacao DESC;

20. Média de Notas por Disciplina com Nome do Professor

Mostre a média de notas de cada disciplina juntamente com o nome do professor responsável, ordenado pela média (descendente).

SELECT
   c.cname AS Nome_Disciplina,
   t.tname AS Nome_Professor,
   AVG(n.number) AS Media_Disciplina
FROM
   score n
JOIN
   course c ON n.course_id = c.cid
JOIN
   teacher t ON c.teacher_id = t.tid
GROUP BY
   c.cid, c.cname, t.tname
ORDER BY
   Media_Disciplina DESC;

21. Top 3 Notas por Disciplina (sem considerar empates)

Para cada disciplina, liste as três notas mais altas.

-- Requer MySQL 8.0+ para ROW_NUMBER()
SELECT
   ID_Disciplina,
   Nome_Disciplina,
   ID_Aluno,
   Nota
FROM (
   SELECT
       c.cid AS ID_Disciplina,
       c.cname AS Nome_Disciplina,
       s.student_id AS ID_Aluno,
       s.number AS Nota,
       ROW_NUMBER() OVER (PARTITION BY s.course_id ORDER BY s.number DESC) AS rnk
   FROM
       score s
   JOIN
       course c ON s.course_id = c.cid
) AS ranked_scores
WHERE
   rnk <= 3
ORDER BY
   ID_Disciplina, Nota DESC;

22. Contagem de Alunos por Disciplina

Conte o número de alunos que cursaram cada disciplina.

SELECT
   c.cid AS ID_Disciplina,
   c.cname AS Nome_Disciplina,
   COUNT(DISTINCT n.student_id) AS Total_Alunos
FROM
   course c
LEFT JOIN
   score n ON c.cid = n.course_id
GROUP BY
   c.cid, c.cname;

23. Alunos que Cursaram Apenas Uma Disciplina

Liste os IDs e nomes de todos os alunos que cursaram exatamente uma única disciplina.

SELECT
   a.sid AS ID_Aluno,
   a.sname AS Nome_Aluno
FROM
   student a
JOIN
   score n ON a.sid = n.student_id
GROUP BY
   a.sid, a.sname
HAVING
   COUNT(n.course_id) = 1;

24. Contagem de Alunos por Gênero

Determine o número de alunos masculinos e femininos separadamente.

SELECT
   gender AS Genero,
   COUNT(sid) AS Total_Alunos
FROM
   student
GROUP BY
   gender;

25. Alunos com Sobrenome "Silva"

Liste todos os alunos cujo nome comece com "Silva".

SELECT
   sid AS ID_Aluno,
   sname AS Nome_Aluno,
   gender AS Genero
FROM
   student
WHERE
   sname LIKE 'Silva%';

26. Alunos com Nomes Duplicados

Encontre nomes de alunos que se repetem e conte quantas vezes cada nome aparece.

SELECT
   sname AS Nome_Aluno,
   COUNT(sid) AS Numero_Ocorrencias
FROM
   student
GROUP BY
   sname
HAVING
   COUNT(sid) > 1;

27. Média de Notas por Disciplina com Ordenação Específica

Calcule a média de notas para cada disciplina. Ordene os resultados pela média (ascendente) e, em caso de empate, pelo ID da disciplina (descendente).

SELECT
   c.cid AS ID_Disciplina,
   c.cname AS Nome_Disciplina,
   AVG(IFNULL(n.number, 0)) AS Media_Disciplina
FROM
   course c
LEFT JOIN
   score n ON c.cid = n.course_id
GROUP BY
   c.cid, c.cname
ORDER BY
   Media_Disciplina ASC, ID_Disciplina DESC;

28. Alunos com Média Geral Acima de 85

Liste os IDs, nomes e a média geral de todos os alunos com média de notas superior a 85.

SELECT
   a.sid AS ID_Aluno,
   a.sname AS Nome_Aluno,
   AVG(n.number) AS Media_Geral
FROM
   student a
JOIN
   score n ON a.sid = n.student_id
GROUP BY
   a.sid, a.sname
HAVING
   AVG(n.number) > 85;

29. Alunos com Nota Baixa em "Matemática"

Encontre os nomes e notas dos alunos que tiraram menos de 60 na disciplina de "Matemática".

SELECT
   a.sname AS Nome_Aluno,
   n.number AS Nota
FROM
   student a
JOIN
   score n ON a.sid = n.student_id
JOIN
   course c ON n.course_id = c.cid
WHERE
   c.cname = 'Matemática' AND n.number < 60;

30. Alunos com Nota Alta em uma Disciplina Específica

Liste os IDs e nomes dos alunos que obtiveram mais de 80 na disciplina de ID '3'.

SELECT
   a.sid AS ID_Aluno,
   a.sname AS Nome_Aluno,
   n.number AS Nota
FROM
   student a
JOIN
   score n ON a.sid = n.student_id
WHERE
   n.course_id = 3 AND n.number > 80;

31. Total de Alunos que Cursaram Disciplinas

Calcule o número total de alunos que cursaram pelo menos uma disciplina.

SELECT
   COUNT(DISTINCT student_id) AS Alunos_com_Disciplinas
FROM
   score;

32. Aluno com a Maior Nota em Disciplinas de um Professor Específico

Encontre o nome e a nota mais alta do aluno que cursou uma disciplina do "Prof. Almeida", considerando apenas as disciplinas desse professor.

SELECT
   a.sname AS Nome_Aluno,
   n.number AS Nota_Mais_Alta
FROM
   score n
JOIN
   student a ON n.student_id = a.sid
JOIN
   course c ON n.course_id = c.cid
JOIN
   teacher t ON c.teacher_id = t.tid
WHERE
   t.tname = 'Prof. Almeida'
ORDER BY
   n.number DESC
LIMIT 1;

33. Disciplinas e Suas Contagens de Alunos

Exiba cada disciplina e o número de alunos que a cursaram.

SELECT
   c.cid AS ID_Disciplina,
   c.cname AS Nome_Disciplina,
   COUNT(n.student_id) AS Total_Alunos
FROM
   course c
LEFT JOIN
   score n ON c.cid = n.course_id
GROUP BY
   c.cid, c.cname;

34. Alunos com a Mesma Nota em Diferentes Disciplinas

Identifique alunos que obtiveram a mesma nota em duas disciplinas distintas.

SELECT
   s1.student_id AS ID_Aluno,
   s1.course_id AS ID_Disciplina_1,
   s2.course_id AS ID_Disciplina_2,
   s1.number AS Nota_Comum
FROM
   score s1
JOIN
   score s2 ON s1.student_id = s2.student_id
WHERE
   s1.course_id < s2.course_id AND s1.number = s2.number;

35. Top 2 Notas por Disciplina

Para cada disciplina, liste as duas notas mais altas (assumindo MySQL 8.0+).

SELECT
   ID_Disciplina,
   Nome_Disciplina,
   ID_Aluno,
   Nota
FROM (
   SELECT
       c.cid AS ID_Disciplina,
       c.cname AS Nome_Disciplina,
       s.student_id AS ID_Aluno,
       s.number AS Nota,
       DENSE_RANK() OVER (PARTITION BY s.course_id ORDER BY s.number DESC) AS ranking_nota
   FROM
       score s
   JOIN
       course c ON s.course_id = c.cid
) AS notas_classificadas
WHERE
   ranking_nota <= 2
ORDER BY
   ID_Disciplina, Nota DESC;

36. Alunos que Cursaram Pelo Menos Duas Disciplinas

Encontre os IDs dos alunos que cursaram duas ou mais disciplinas.

SELECT
   student_id AS ID_Aluno
FROM
   score
GROUP BY
   student_id
HAVING
   COUNT(course_id) >= 2;

37. Disciplinas Cursadas por Todos os Alunos

Liste os IDs e nomes das disciplinas que foram cursadas por todos os alunos.

SELECT
   c.cid AS ID_Disciplina,
   c.cname AS Nome_Disciplina
FROM
   course c
WHERE
   (SELECT COUNT(DISTINCT s.student_id) FROM score s WHERE s.course_id = c.cid) = (SELECT COUNT(sid) FROM student);

38. Alunos que Não Cursaram Nenhuma Disciplina de um Professor Específico

Liste os nomes dos alunos que não cursaram nenhuma disciplina do "Prof. Costa".

SELECT
   a.sname AS Nome_Aluno
FROM
   student a
WHERE
   a.sid NOT IN (
       SELECT DISTINCT s.student_id
       FROM score s
       JOIN course c ON s.course_id = c.cid
       JOIN teacher t ON c.teacher_id = t.tid
       WHERE t.tname = 'Prof. Costa'
   );

39. Alunos com Mais de Duas Notas Insuficientes

Encontre os IDs dos alunos que têm mais de duas notas abaixo de 60 e sua média geral.

SELECT
   student_id AS ID_Aluno,
   AVG(number) AS Media_Geral
FROM
   score
WHERE
   number < 60
GROUP BY
   student_id
HAVING
   COUNT(number) > 2;

40. Alunos com Notas Baixas em Disciplina Específica, Ordenados

Liste os IDs e notas dos alunos que tiraram menos de 60 na disciplina de ID '4', ordenado pela nota (descendente).

SELECT
   student_id AS ID_Aluno,
   number AS Nota
FROM
   score
WHERE
   course_id = 4 AND number < 60
ORDER BY
   number DESC;

41. Remover uma Nota Específica

Exclua a nota do aluno de ID '2' na disciplina de ID '1'.

DELETE FROM score
WHERE student_id = 2 AND course_id = 1;

Tags: MySQL SQL ConsultasSQL BancoDeDados DDL

Publicado em 7-15 22:39

Doido Dev