# SESSION DE BANCO PARTE 2
## SCHEMA
```sql=
CREATE TABLE TB_ALUNOS (
id_aluno int primary key auto_increment,
nm_turma varchar (255),
nr_ano_letivo int,
nm_nome varchar (255),
nr_chamada int,
ds_sexo varchar (255),
tp_status varchar (1)
);
CREATE TABLE TB_DISCIPLINAS (
id_disciplina int primary key auto_increment,
nm_disciplina varchar (255),
nm_professor varchar (255)
);
CREATE TABLE TB_BOLETIM (
id_boletim int primary key auto_increment,
id_aluno int,
id_disciplina int,
vl_notas decimal(10,2),
qtd_faltas int,
foreign key (id_aluno) references TB_ALUNOS (id_aluno),
foreign key (id_disciplina) references TB_DISCIPLINAS (id_disciplina)
);
INSERT INTO TB_ALUNOS (nm_turma, nr_ano_letivo, nm_nome, nr_chamada, ds_sexo, tp_status)
VALUES
('A', 2020, "Joseph de Jesus", 18, "masculino", 'R'),
('B', 2021, "Brunex de Oliva", 4, "masculino", 'R'),
('A', 2021, "Arquímedes da Silva", 1, "masculino", 'A');
INSERT INTO TB_DISCIPLINAS
(nm_disciplina, nm_professor)
VALUES
("matemática", "Célio"),
("programação", "Roberto"),
("português", "Silvia"),
("inglês", "Ana"),
("história", "Peter");
INSERT INTO TB_BOLETIM
(id_aluno, id_disciplina, vl_notas, qtd_faltas)
VALUES
(1, 1, 5.5, 7),
(1, 2, 8, 3),
(1, 3, 10, 1),
(1, 4, 2, 15),
(1, 5, 1, 20),
(2, 1, 9.5, 3),
(2,2,3,10),
(2,3,4,12),
(2,4,0,20),
(2,5,3,12),
(3,1,10,0),
(3,2,9.5,2),
(3,3,8,1),
(3,4,10,4),
(3,5,9,2);
```
## QUERY
```sql=
/* DML 1 */
SELECT
a.nm_nome,a.nm_turma, a.nr_ano_letivo, d.nm_disciplina, b.vl_notas
FROM TB_BOLETIM b
INNER JOIN TB_ALUNOS a ON a.id_aluno = b.id_aluno
INNER JOIN TB_DISCIPLINAS d ON d.id_disciplina = b.id_disciplina
ORDER BY a.nm_nome;
/* DML 2 */
SELECT
a.nm_nome,a.nm_turma, a.nr_ano_letivo, d.nm_disciplina, b.vl_notas
FROM TB_BOLETIM b
INNER JOIN TB_ALUNOS a ON a.id_aluno = b.id_aluno
INNER JOIN TB_DISCIPLINAS d ON d.id_disciplina = b.id_disciplina
ORDER BY b.vl_notas DESC;
/* DML 3 */
SELECT
a.nm_nome,a.nm_turma, a.nr_ano_letivo, d.nm_disciplina, b.vl_notas
FROM TB_BOLETIM b
INNER JOIN TB_ALUNOS a ON a.id_aluno = b.id_aluno
INNER JOIN TB_DISCIPLINAS d ON d.id_disciplina = b.id_disciplina
WHERE a.tp_status = "A"
ORDER BY a.nr_ano_letivo AND a.nm_turma AND a.nr_chamada;
/* DML 4 */
SELECT
a.nm_nome,a.nm_turma, a.nr_ano_letivo
FROM TB_BOLETIM b
INNER JOIN TB_ALUNOS a ON a.id_aluno = b.id_aluno
INNER JOIN TB_DISCIPLINAS d ON d.id_disciplina = b.id_disciplina
WHERE a.tp_status = "R";
```
{"metaMigratedAt":"2023-06-16T00:10:19.759Z","metaMigratedFrom":"Content","title":"SESSION DE BANCO PARTE 2","breaks":true,"contributors":"[{\"id\":\"4c424d8f-f274-42df-9402-f6014d7ba673\",\"add\":2522,\"del\":0}]"}