# Session Two: Getting Better | Banco de dados
>[color=purple]**Autor:** Letícia Rodrigues da Silva
**Turma:** IndoD
**Número:** 25

[TOC]

## Modelagem Aluno (N-N)

### Script das tabelas

CREATE TABLE tb_disciplina (
  id_disciplina			int primary key auto_increment, 
  nm_disciplina 	varchar(200),
  nm_professor  	varchar(200)
  );
  
  
  CREATE TABLE tb_aluno (
    id_aluno		int primary key auto_increment,
    nm_turma 		varchar(200),
    nr_ano_letivo	int,
    nm_aluno		varchar(200),
    nr_chamada		int,
    ds_sexo 		varchar(20),
    tp_status		varchar(10)
    );
    
    
    CREATE TABLE tb_boletim (
  id_boletim		int primary key auto_increment,
  id_aluno			int, 
  id_disciplina		int,
  vl_nota			decimal(10,1),
  qtd_faltas		int,
  FOREIGN KEY (id_aluno) REFERENCES tb_aluno(id_aluno) ON DELETE CASCADE,
  FOREIGN KEY (id_disciplina) REFERENCES tb_disciplina(id_disciplina) ON DELETE CASCADE
);

### Inserts das tabelas

INSERT INTO tb_aluno (nm_turma, nr_ano_letivo, nm_aluno, nr_chamada, ds_sexo, tp_status)
VALUES ('Informática', 2021, 'Angélica Nunes', 4, 'Feminino', 'A'),
	   ('Administração', 2020, 'Olivio Santos', 32, 'Masculino', 'R'),
       ('Informática', 2021, 'Joares Gomes', 28, 'Masculino', 'A');
       
       
INSERT INTO tb_disciplina (nm_disciplina, nm_professor)
VALUES ('Software', 'Bruno'),
	   ('Hardware', 'Roberto'),
       ('Moral Cristã', 'Conrado'),
       ('Matemática', 'Carlos'),
       ('Inglês', 'Clayton');
 
 
 INSERT INTO tb_boletim (id_aluno, id_disciplina, vl_nota, qtd_faltas)
 VALUES (1, 1, 10, 0),
 		(1, 2, 7.0, 2),
        (1, 3, 9.0, 1),
        (1, 4, 6.5, 0),
        (1, 5, 9.5, 3),
        (2, 1, 9.0, 0),
 		(2, 2, 8.0, 2),
        (2, 3, 6.5, 1),
        (2, 4, 5.0, 0),
        (2, 5, 7.0, 3),
        (3, 1, 8.0, 0),
 		(3, 2, 5.5, 2),
        (3, 3, 10, 1),
        (3, 4, 7.5, 0),
        (3, 5, 9.0, 3);

### Update e delete

        UPDATE tb_aluno
        SET nr_chamada = 29
        WHERE id_aluno = 2;
        
        UPDATE tb_disciplina
        SET nm_disciplina = 'Empreendedorismo'
        WHERE id_disciplina = 5;
        
        UPDATE tb_boletim
        SET vl_nota = '7.0'
        WHERE id_boletim = 8;
        
        UPDATE tb_boletim
        SET vl_nota = '10'
        WHERE id_boletim = 15;
        
        
        DELETE FROM tb_boletim  
        WHERE id_boletim = 7;
        
        DELETE FROM tb_disciplina  
        WHERE id_disciplina = 2;
        
        DELETE FROM tb_aluno 
        WHERE id_aluno = 3;

### 1. Selecionar aluno, turma, ano, disciplina, nota aplicando o relacionamento nas tabelas, sem filtros, ordenando por aluno.

SELECT a.nm_aluno,
	   a.nm_turma,
       a.nr_ano_letivo,
       d.nm_disciplina,
       b.vl_nota
FROM tb_boletim b
INNER JOIN tb_aluno a ON a.id_aluno = b.id_aluno
INNER JOIN tb_disciplina d ON d.id_disciplina = b.id_disciplina
ORDER BY a.id_aluno;

### 2. Selecionar aluno, turma, ano, disciplina, nota aplicando o relacionamento nas tabelas, filtrando por turma e ano letivo, ordenando por nota da maior para a menor.

SELECT a.nm_aluno,
	   a.nm_turma,
       a.nr_ano_letivo,
       d.nm_disciplina,
       b.vl_nota
FROM tb_boletim b
INNER JOIN tb_aluno a ON a.id_aluno = b.id_aluno
INNER JOIN tb_disciplina d ON d.id_disciplina = b.id_disciplina 
WHERE a.nm_turma= 'Informática' AND a.nr_ano_letivo = 2021
ORDER BY b.vl_nota DESC;

### 3. Selecionar aluno, turma, ano, disciplina, nota aplicando o relacionamento nas tabelas, filtrando os alunos aprovados por ano e turma, ordenando por ano, turma e chamada.

SELECT a.nm_aluno,
	   a.nm_turma,
       a.nr_ano_letivo,
       d.nm_disciplina,
       b.vl_nota
FROM tb_boletim b      
INNER JOIN tb_aluno a ON a.id_aluno = b.id_aluno
INNER JOIN tb_disciplina d ON d.id_disciplina = b.id_disciplina
WHERE a.tp_status = 'A' AND a.nr_ano_letivo = 2021 AND a.nm_turma = 'Informática'
ORDER BY a.nr_ano_letivo;

### 4. Selecionar aluno, turma, ano aplicando o relacionamento nas tabelas, filtrando os alunos reprovados por ano e turma.

SELECT a.nm_aluno,
	   a.nm_turma,
       a.nr_ano_letivo
FROM tb_boletim b
INNER JOIN tb_aluno a ON a.id_aluno = b.id_aluno
INNER JOIN tb_disciplina d ON d.id_disciplina = b.id_disciplina
WHERE a.tp_status = 'R';

### 5. Crie mais 5 consultas personalizadas conforme sua vontade que explore filtros como: >, >=, <, <=, =, <>, LIKE, IN, BETWEEN, Funções de Texto/Número/Data

--1--
SELECT a.nm_aluno,
	   b.vl_nota
FROM tb_boletim b 
INNER JOIN tb_aluno a ON a.id_aluno = b.id_aluno
INNER JOIN tb_disciplina d ON d.id_disciplina = b.id_disciplina
WHERE b.vl_nota BETWEEN 7 AND 10
ORDER BY b.vl_nota ASC;


--2--
SELECT a.nm_aluno,
	   d.nm_disciplina,
       d.nm_professor
FROM tb_boletim b
INNER JOIN tb_aluno a ON a.id_aluno = b.id_aluno
INNER JOIN tb_disciplina d ON d.id_disciplina = b.id_disciplina
WHERE d.nm_professor LIKE 'C%';


--3--
SELECT a.nm_turma,
	   a.nr_ano_letivo
FROM tb_boletim b
INNER JOIN tb_aluno a ON a.id_aluno = b.id_aluno
INNER JOIN tb_disciplina d ON d.id_disciplina = b.id_disciplina
WHERE a.nm_turma LIKE 'A%'
OR a.nr_ano_letivo <= 2020; 


--4--
SELECT substring(a.nm_aluno, 1, 8) 
FROM tb_boletim b
INNER JOIN tb_aluno a ON a.id_aluno = b.id_aluno
INNER JOIN tb_disciplina d ON d.id_disciplina = b.id_disciplina
WHERE d.nm_disciplina = 'Software';


--5--
SELECT a.nm_aluno,
	   a.nr_ano_letivo,
       b.vl_nota
FROM tb_boletim b
INNER JOIN tb_aluno a ON a.id_aluno = b.id_aluno
INNER JOIN tb_disciplina d ON d.id_disciplina = b.id_disciplina
WHERE b.vl_nota <= 6.5
OR a.nm_aluno = 2021;