Dominando Consultas SQL para Análise de Cursos de Estudantes - Guia Completo

Introdução

Neste projeto, você aprenderá como executar várias consultas SQL em um banco de dados de cursos de estudantes. O banco de dados consiste em três tabelas: Tabela de Estudantes (S), Tabela de Cursos (C) e Tabela de Cursos de Estudantes (SC). Você aprenderá como recuperar e manipular dados dessas tabelas para realizar diferentes tarefas.

👀 Visualização

Imagem de visualização do banco de dados SQL

🎯 Tarefas

Neste projeto, você aprenderá:

Como listar os nomes de todos os estudantes que não fizeram cursos ministrados pelo professor "Daniel"
Como listar os números e nomes dos estudantes que foram reprovados (nota < 60) em dois ou mais cursos
Como listar os nomes de todos os estudantes que fizeram os cursos "11" e "12"
Como listar os números dos estudantes cujas notas no curso "11" são maiores do que as do curso "12", e ordená-los em ordem crescente
Como listar os nomes de todos os estudantes que são mais velhos que "John Davis"
Como listar o número do estudante com a maior nota entre todos os estudantes que fizeram o curso "12"
Como listar os nomes de todos os estudantes, juntamente com os IDs dos cursos selecionados e as notas
Como listar os números dos estudantes e as notas totais (alias como: SG) dos estudantes que fizeram quatro ou mais cursos, ordenados em ordem decrescente de notas totais

🏆 Conquistas

Após concluir este projeto, você será capaz de:

Entender como escrever consultas SQL complexas para recuperar e manipular dados de um banco de dados relacional
Aplicar várias técnicas SQL, como subconsultas, joins (junções), agregações e ordenação, para resolver problemas de análise de dados do mundo real
Desenvolver habilidades SQL que são essenciais para a tomada de decisões baseada em dados e resolução de problemas

Iniciar MySQL e Importar Dados

Nesta etapa, você aprenderá como iniciar o serviço MySQL e importar os dados necessários para o banco de dados.

Inicie o serviço MySQL usando o comando sudo sem nenhuma senha:

sudo service mysql start

Acesse o cliente MySQL usando o comando sudo sem nenhuma senha:

sudo mysql

Importe os dados do script personnel.sql fornecido para o banco de dados MySQL:

SOURCE /home/labex/project/initdb.sql

Adicionar Código da Consulta

Nesta etapa, você aprenderá como listar os nomes de todos os estudantes que não fizeram cursos ministrados pelo professor "Daniel". Siga as etapas abaixo para concluir esta etapa:

Abra o arquivo answer.sql no diretório /home/labex/project.
Adicione a instrução SQL abaixo do comentário:
Localize o comentário -- List the names of all students who have not taken courses taught by the "Daniel" teacher.
```
SELECT s.sname
FROM S s
WHERE NOT EXISTS (
  SELECT 1
  FROM SC sc
  JOIN C c ON sc.cno = c.cno
  WHERE c.tname = 'Daniel' AND sc.sno = s.sno
);
```
Esta instrução SQL primeiro seleciona a coluna sname da tabela S. Em seguida, usa uma cláusula NOT EXISTS para verificar se existem registros na tabela SC que possuem um ID de curso (cno) que corresponde a um curso ministrado pelo professor "Daniel", e o ID do estudante (sno) corresponde ao estudante atual. Se nenhum desses registros existir, o nome do estudante será incluído no resultado.
Localize o comentário -- List the student numbers and names of students who have failed (grade < 60) in two or more courses.
```
SELECT s.sno, s.sname
FROM S s
JOIN SC sc ON s.sno = sc.sno
WHERE sc.grade < 60
GROUP BY s.sno, s.sname
HAVING COUNT(sc.sno) >= 2;
```
Esta instrução SQL primeiro junta as tabelas S e SC para obter as informações do estudante e suas notas nos cursos. Em seguida, filtra os registros onde a nota é menor que 60, agrupa os resultados por número e nome do estudante e, finalmente, seleciona apenas os estudantes que foram reprovados em dois ou mais cursos usando a cláusula HAVING.
Localize o comentário -- List the names of all students who have taken both course "11" and course "12".
```
 SELECT s.sname
 FROM S s
 JOIN SC sc ON s.sno = sc.sno
 WHERE sc.cno IN (11, 12)
 GROUP BY s.sno
 HAVING COUNT(DISTINCT sc.cno) = 2
 ORDER BY s.sno;
```
Esta instrução SQL primeiro junta as tabelas S e SC para obter as informações do estudante e seus IDs de curso. Em seguida, filtra os registros onde o ID do curso é 11 ou 12, agrupa os resultados por número do estudante e seleciona apenas os estudantes que fizeram ambos os cursos usando a cláusula HAVING. Finalmente, ordena os resultados por número do estudante.
Localize o comentário -- List the student numbers of all students whose grades in course "11" are higher than those in course "12", and sort them in ascending order.
```
SELECT sc11.sno
FROM SC sc11
JOIN SC sc12 ON sc11.sno = sc12.sno AND sc12.cno = 12
WHERE sc11.cno = 11 AND sc11.grade > sc12.grade
ORDER BY sc11.sno;
```
Esta instrução SQL primeiro junta a tabela SC a ela mesma para obter as informações de nota tanto para o curso 11 quanto para o curso 12. Em seguida, filtra os registros onde o ID do curso é 11 e a nota é maior que a nota do curso 12, e seleciona o número do estudante. Finalmente, ordena os resultados por número do estudante.
Localize o comentário -- List the names of all students who are older than "John Davis".
```
SELECT s.sname
FROM S s, (SELECT sage FROM S WHERE sname = 'John Davis') AS zs
WHERE s.sage > zs.sage;
```
Esta instrução SQL primeiro seleciona o valor sage para o estudante com o nome "John Davis" e o armazena em uma subconsulta zs. Em seguida, seleciona o sname da tabela S onde a idade do estudante (sage) é maior que a idade de "John Davis".
Localize o comentário -- List the student number of the student with the highest grade among all students who have taken course "12".
```
SELECT sc.sno
FROM SC sc
WHERE sc.cno = 12 AND sc.grade = (
  SELECT MAX(grade)
  FROM SC
  WHERE cno = 12
);
```
Esta instrução SQL primeiro seleciona o sno da tabela SC onde o ID do curso é 12 e a nota é igual à nota máxima entre todos os estudantes que fizeram o curso 12. Isso nos dará o número do estudante com a maior nota no curso 12.
Localize o comentário -- List the names of all students, along with their selected course IDs and grades.
```
SELECT s.sname, sc.cno, sc.grade
FROM S s
LEFT JOIN SC sc ON s.sno = sc.sno;
```
Esta instrução SQL executa uma junção à esquerda (left join) entre as tabelas S e SC, que incluirá todos os estudantes da tabela S, mesmo que não tenham registros na tabela SC. Isso garantirá que todos os nomes dos estudantes sejam incluídos no resultado, juntamente com seus IDs de curso e notas (se tiverem alguma).
Localize o comentário -- List the student numbers and total grades (alias as: SG) of students who have taken four or more courses, sorted in descending order of total grades.
```
SELECT sc.sno, SUM(sc.grade) AS SG
FROM SC sc
GROUP BY sc.sno
HAVING COUNT(sc.cno) >= 4
ORDER BY SG DESC;
```
Esta instrução SQL primeiro agrupa a tabela SC por número do estudante (sno) e calcula a soma das notas de cada estudante (apelidada de SG). Em seguida, filtra os resultados para incluir apenas os estudantes que fizeram quatro ou mais cursos usando a cláusula HAVING. Finalmente, ordena os resultados pelas notas totais em ordem decrescente.

Executar o Script SQL

Nesta etapa final, você executará o script SQL que criou nas etapas anteriores.

No cliente MySQL, execute o seguinte comando para executar o script answer.sql:

SOURCE /home/labex/project/answer.sql

Isso executará as consultas SQL no arquivo answer.sql e exibirá os resultados.

Parabéns! Você concluiu o projeto. Você deve ver a seguinte saída:

MariaDB [student]> SOURCE /home/labex/project/answer.sql
+----------------+
| sname          |
+----------------+
| Michael Brown  |
| William Wilson |
| Richard Taylor |
| Charles Thomas |
+----------------+
4 rows in set (0.013 sec)

+-----+---------------+
| sno | sname         |
+-----+---------------+
|   1 | James Johnson |
|   6 | David Moore   |
+-----+---------------+
2 rows in set (0.000 sec)

+----------------+
| sname          |
+----------------+
| James Johnson  |
| Michael Brown  |
| John Davis     |
| Robert Miller  |
| William Wilson |
| David Moore    |
| Richard Taylor |
+----------------+
7 rows in set (0.000 sec)

+-----+
| sno |
+-----+
|   2 |
|   3 |
|   4 |
|   7 |
+-----+
4 rows in set (0.001 sec)

+----------------+
| sname          |
+----------------+
| William Wilson |
| Richard Taylor |
+----------------+
2 rows in set (0.013 sec)

+-----+
| sno |
+-----+
|   5 |
|   8 |
+-----+
2 rows in set (0.000 sec)

+-----------------+------+-------+
| sname           | cno  | grade |
+-----------------+------+-------+
| James Johnson   |   11 |    50 |
| James Johnson   |   12 |    80 |
| James Johnson   |   13 |    50 |
| James Johnson   |   14 |    50 |
| James Johnson   |   15 |    50 |
| Michael Brown   |   11 |    70 |
| Michael Brown   |   12 |    65 |
| Michael Brown   |   15 |    70 |
| John Davis      |   11 |    90 |
| John Davis      |   12 |    70 |
| John Davis      |   14 |    80 |
| John Davis      |   15 |    90 |
| Robert Miller   |   11 |    80 |
| Robert Miller   |   12 |    50 |
| Robert Miller   |   13 |    70 |
| Robert Miller   |   14 |    62 |
| Robert Miller   |   15 |    80 |
| William Wilson  |   11 |    40 |
| William Wilson  |   12 |    90 |
| William Wilson  |   13 |    60 |
| William Wilson  |   15 |    61 |
| David Moore     |   11 |    30 |
| David Moore     |   12 |    50 |
| David Moore     |   13 |    40 |
| David Moore     |   14 |    30 |
| David Moore     |   15 |    35 |
| Richard Taylor  |   11 |    90 |
| Richard Taylor  |   12 |    80 |
| Richard Taylor  |   13 |    80 |
| Richard Taylor  |   15 |    80 |
| Joseph Anderson |   12 |    90 |
| Joseph Anderson |   14 |    70 |
| Joseph Anderson |   15 |    95 |
| Charles Thomas  | NULL |  NULL |
+-----------------+------+-------+
34 rows in set (0.001 sec)

+-----+------+
| sno | SG   |
+-----+------+
|   4 |  342 |
|   7 |  330 |
|   3 |  330 |
|   1 |  280 |
|   5 |  251 |
|   6 |  185 |
+-----+------+
6 rows in set (0.000 sec)

Resumo

Parabéns! Você concluiu este projeto. Você pode praticar mais laboratórios no LabEx para aprimorar suas habilidades.