# Session two: Getting better | Banco de Dados
**Autor**: Vitório Trindade Santana
**Turma**: Informática C
**Número**: 50

[toc]

## Modelagem 1: Catálogo de Filmes

### Create Script

CREATE TABLE TB_Genero (
    ID_Genero              int PRIMARY KEY AUTO_INCREMENT,
    DS_Genero              varchar (30),
    BT_Ativo               bool
    );
  

CREATE TABLE TB_Filmes (
    ID_Filmes              int PRIMARY KEY AUTO_INCREMENT,
    NM_Filme               varchar (30),
    DT_Lancamento          date,
    BT_Disponivel          bool,
    VL_Avaliacao           decimal (3,1)    
    );
  
CREATE TABLE TB_Filmes_Genero (
  ID_Filmes_Genero         int PRIMARY KEY AUTO_INCREMENT,
  ID_Filmes 			   int,
  ID_Genero                int,
  FOREIGN KEY (ID_Filmes) REFERENCES TB_Filmes (ID_Filmes) ON DELETE CASCADE,
  FOREIGN KEY (ID_Genero) REFERENCES TB_Genero (ID_Genero) ON DELETE CASCADE
  );

### Insert Script

INSERT INTO TB_Genero (DS_Genero, BT_Ativo)
VALUES ('Comédia', true),
       ('Romance', true),
       ('Ação', true),
       ('Ficção Científica', false),
       ('Biográfico', false);
         
INSERT INTO TB_Filmes (NM_Filme, DT_Lancamento, BT_Disponivel, VL_Avaliacao)
VALUES ('Deadpool', '2016-02-11', true, 3.9),
       ('O Auto da Compadecida', '2000-09-10', true, 7.6),
       ('John Wick - De volta ao jogo', '2014-11-27', false, 9.7),
       ('O jogo da imitação', '2015-01-08', false, 7.9),
       ('Sonic: O filme', '2020-02-13', true,  8.3);
              
INSERT INTO TB_Filmes_Genero (ID_Filmes, ID_Genero)
VALUES (1, 1),
       (1, 3),
       (1, 4),
       (2, 1),
       (3, 3),
       (4, 4),
       (4, 5),
       (5, 1),
       (5, 3),
       (5, 5);
              
              
UPDATE TB_Filmes 
   SET BT_Disponivel = true
 WHERE ID_Filmes = 3;
 
              
UPDATE TB_Genero 
   SET BT_Ativo = true
 WHERE ID_Genero = 4;
 
UPDATE TB_Filmes_Genero
   SET ID_Genero = 2
 WHERE ID_Filmes_Genero = 3;

UPDATE TB_Filmes_Genero
   SET ID_Genero = 4
 WHERE ID_Filmes_Genero = 10;
 
 DELETE 
   FROM TB_Filmes 
  WHERE ID_Filmes = 4;
 
 DELETE 
   FROM TB_Genero 
  WHERE ID_Genero = 5;
  
 DELETE 
   FROM TB_Filmes_Genero 
  WHERE ID_FIlmes_Genero = 6;
  
 DELETE 
   FROM TB_Filmes_Genero 
  WHERE ID_FIlmes_Genero = 7;

### Select Script

-- Selecionar fime, gênero aplicando o relacionamento nas tabelas, sem filtros, ordenando por filme. 

    SELECT F.NM_Filme,
           G.DS_Genero
      FROM TB_Filmes_Genero   FG
INNER JOIN TB_Genero          G
        ON G.ID_Genero        = FG.ID_Genero
INNER JOIN TB_Filmes          F
        ON F.ID_Filmes        = FG.ID_Filmes  
  ORDER BY F.NM_Filme;
  
-- Selecionar id do filme, filme, id do gênero e gênero aplicando o relacionamento nas tabelas, filtrando os que possuem avaliação maior que 4, ordenando por filme.  
  
    SELECT F.ID_Filmes,
           F.NM_Filme,
           G.ID_Genero,
           G.DS_Genero
      FROM TB_Filmes_Genero   FG
INNER JOIN TB_Genero          G
        ON G.ID_Genero        = FG.ID_Genero
INNER JOIN TB_Filmes          F
        ON F.ID_Filmes        = FG.ID_Filmes 
     WHERE F.VL_Avaliacao > 4
  ORDER BY F.NM_Filme;
  
 
-- Selecionar filme, avaliação, lançamento aplicando o relacionamento nas tabelas, filtrando os filmes disponíveis com gênero igual a ‘ação’, ordenando por avaliação.  
 
    SELECT F.NM_Filme,
           F.VL_Avaliacao,
           F.DT_Lancamento
      FROM TB_Filmes_Genero   FG
INNER JOIN TB_Genero          G
        ON G.ID_Genero        = FG.ID_Genero
INNER JOIN TB_Filmes          F
        ON F.ID_Filmes        = FG.ID_Filmes 
     WHERE F.BT_Disponivel = true
       AND G.DS_Genero = 'Ação'
  ORDER BY F.VL_Avaliacao;
  
-- Selecionar filme, avaliação, lançamento, disponível, gênero aplicando o relacionamento nas tabelas, filtrando os filmes que possuam os gêneros ‘romance’ e ‘comédia’.

    SELECT F.NM_Filme,
           F.VL_Avaliacao,
           F.DT_Lancamento,
           F.BT_Disponivel,
           G.DS_Genero
      FROM TB_Filmes_Genero   FG
INNER JOIN TB_Genero          G
        ON G.ID_Genero        = FG.ID_Genero
INNER JOIN TB_Filmes          F
        ON F.ID_Filmes        = FG.ID_Filmes 
     WHERE F.BT_Disponivel = true
       AND G.DS_Genero = 'Romance'
        OR G.DS_Genero = 'Comédia'; 
    
-- Consultas personalizadas
       
-- Consultar gênero, nome e disponibilidade dos filmes que estejam disponíveis e com uma avaliação entre 7 e 8.5.

    SELECT G.DS_Genero,
           F.NM_Filme,
           F.BT_Disponivel
      FROM TB_Filmes_Genero   FG
INNER JOIN TB_Genero          G
        ON G.ID_Genero        = FG.ID_Genero
INNER JOIN TB_Filmes          F
        ON F.ID_Filmes        = FG.ID_Filmes 
     WHERE F.BT_Disponivel = true
       AND F.VL_Avaliacao BETWEEN 7 AND 8.5;
       
-- Consultar pelo gênero, nome e avaliação onde o gênero seja comédia ou ação.

    SELECT G.DS_Genero,
           F.NM_Filme,
           F.Vl_Avaliacao
      FROM TB_Filmes_Genero   FG
INNER JOIN TB_Genero          G
        ON G.ID_Genero        = FG.ID_Genero
INNER JOIN TB_Filmes          F
        ON F.ID_Filmes        = FG.ID_Filmes 
     WHERE G.DS_Genero = 'Comédia'
        OR G.DS_Genero = 'Ação';
       
-- Consultar todos os filmes que tenham o gênero biográfico ou ação e que a avaliação seja maior que 7.5. 

    SELECT *
      FROM TB_Filmes_Genero   FG
INNER JOIN TB_Genero          G
        ON G.ID_Genero        = FG.ID_Genero
INNER JOIN TB_Filmes          F
        ON F.ID_Filmes        = FG.ID_Filmes 
     WHERE G.DS_Genero 
        IN('Biográfico', 'Ação')
       AND F.VL_Avaliacao > 7.5;
      
-- Selecionar gênero, nome, disponibilidade e lançamento dos filmes que tenham sua data de lançamento entre primeiro de janeiro de 2000 e primeiro de janeiro de 2007.

    SELECT G.DS_Genero,
    	   F.NM_Filme,
           F.BT_Disponivel,
           F.DT_Lancamento
      FROM TB_Filmes_Genero   FG
INNER JOIN TB_Genero          G
        ON G.ID_Genero        = FG.ID_Genero
INNER JOIN TB_Filmes          F
        ON F.ID_Filmes        = FG.ID_Filmes 
     WHERE F.DT_Lancamento 
   BETWEEN '2000-01-01' AND '2007-01-01';
      
-- Consultar todos os filmes que comecem com J, estejam disponíveis e tenham uma avaliação maior ou igual que 9.

    SELECT *
      FROM TB_Filmes_Genero   FG
INNER JOIN TB_Genero          G
        ON G.ID_Genero        = FG.ID_Genero
INNER JOIN TB_Filmes          F
        ON F.ID_Filmes        = FG.ID_Filmes 
     WHERE F.NM_Filme like 'J%'
       AND F.BT_Disponivel = true
       AND F.VL_Avaliacao >= 9;

## Modelagem 2: Turmas e alunos

### Create Script

CREATE TABLE TB_Disciplina (
  ID_Disciplina         int PRIMARY KEY AUTO_INCREMENT,
  NM_Disciplina         varchar (30),
  NM_Professor          varchar (40)
);
  
CREATE TABLE TB_Aluno (
  ID_Aluno               int PRIMARY KEY AUTO_INCREMENT,
  NM_Turma               varchar (50),
  NR_Ano_Letivo          int,
  NM_Aluno               varchar (40),
  DS_Sexo                varchar (15), 
  NR_Chamada             int,
  TP_Status              varchar(1)
  );
  
CREATE TABLE TB_Boletim (
  ID_Boletim             int PRIMARY KEY AUTO_INCREMENT,
  ID_Aluno               int,
  ID_Disciplina          int,
  VL_Nota                decimal (5,2),
  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
  );

### Insert Script

INSERT INTO TB_Disciplina (NM_Disciplina, NM_Professor)
VALUES ('Inglês', 'Gustavo'),
       ('Matemática', 'Tânia'),
       ('História', 'Roberto'),
       ('Ciências', 'Renata'),
       ('Artes', 'Giovanni');
         
INSERT INTO TB_Aluno (NM_Turma, NR_Ano_Letivo, NM_Aluno, DS_Sexo, NR_Chamada, TP_Status)
VALUES ('2°E', 2021, 'Vitório Trindade Santana', 'Masculino', 50, 'F'),
       ('9°A', 2019, 'Kauã Pansan Ferreira', 'Masculino', 32, 'F'),
       ('7°A', 2017, 'Thaís Felix Oliveira', 'Feminino', 43, 'F'),
       ('7°A', 2020, 'Fernando Araujo Miguel', 'Masculino', 22, 'D');
              
INSERT INTO TB_Boletim (ID_Aluno, ID_Disciplina, VL_Nota, QTD_Faltas)   VALUES (1, 1, 7, 2),
       (1, 3, 6, 0),
       (1, 4, 7, 5),
       (2, 1, 10, 0),
       (2, 2, 9, 1),
       (2, 4, 8, 0),
       (3, 1, 9, 0),
       (3, 2, 8, 0),
       (3, 4, 9, 0),
       (3, 5, 10, 2);
       
UPDATE TB_Disciplina 
   SET NM_Professor = 'Marcio Wallace'
 WHERE ID_Disciplina = 1;
               
UPDATE TB_Aluno 
   SET NR_Ano_Letivo = 2021
 WHERE ID_Aluno = 3;
 
              
UPDATE TB_Boletim
   SET VL_Nota = 4
 WHERE ID_Boletim = 6;
 
UPDATE TB_Boletim
   SET VL_Nota = 9
 WHERE ID_Boletim = 2;
                
DELETE 
  FROM TB_Disciplina
 WHERE ID_Disciplina = 5;
 
DELETE 
  FROM TB_Aluno
 WHERE ID_ALuno = 4;

DELETE 
  FROM TB_Boletim
 WHERE ID_Boletim = 10;

### Select Script

-- 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_Disciplina      D
        ON D.ID_Disciplina    = B.ID_Disciplina
INNER JOIN TB_Aluno           A
        ON A.ID_Aluno         = B.ID_Aluno   
  ORDER BY A.NM_Aluno;


-- 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_Disciplina      D
        ON D.ID_Disciplina    = B.ID_Disciplina
INNER JOIN TB_Aluno           A
        ON A.ID_Aluno         = B.ID_Aluno   
     WHERE A.NM_Turma = '2°E'
       AND NR_Ano_Letivo = 2021
  ORDER BY B.VL_Nota DESC;
   
-- 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_Disciplina      D
        ON D.ID_Disciplina    = B.ID_Disciplina
INNER JOIN TB_Aluno           A
        ON A.ID_Aluno         = B.ID_Aluno   
     WHERE B.VL_Nota >= 5
       AND A.NR_Ano_Letivo = 2021
       AND A.NM_Turma = '7°A'
  ORDER BY A.NR_Ano_Letivo,
           A.NM_Turma,
           A.NR_Chamada;
   
   
-- 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_Disciplina      D
        ON D.ID_Disciplina    = B.ID_Disciplina
INNER JOIN TB_Aluno           A
        ON A.ID_Aluno         = B.ID_Aluno   
     WHERE B.VL_Nota < 5
       AND A.NR_Ano_Letivo = 2019
       AND A.NM_Turma = '9°A';
       
       
-- Consultas personalizadas
       
-- Selecione nome e número da chamada da tabela boletim filtrando pelo sexo e ano letivo.

    SELECT A.NM_Aluno,
           A.NR_Chamada
      FROM TB_Boletim         B
INNER JOIN TB_Disciplina      D
        ON D.ID_Disciplina    = B.ID_Disciplina
INNER JOIN TB_Aluno           A
        ON A.ID_Aluno         = B.ID_Aluno   
     WHERE A.DS_Sexo = 'Masculino'
       AND A.NR_Ano_Letivo = 2021;
           
-- Selecione todos os campos da tabela boletim filtrando pela disciplina e pelo ano letivo. 

    SELECT *
      FROM TB_Boletim         B
INNER JOIN TB_Disciplina      D
        ON D.ID_Disciplina    = B.ID_Disciplina
INNER JOIN TB_Aluno           A
        ON A.ID_Aluno         = B.ID_Aluno   
     WHERE D.NM_Disciplina = 'História'
       AND A.NR_Ano_Letivo = 2021;
         
-- Selecione todos os campos da tabea boletim filtrando pelo sexo e a frequência.         

    SELECT *
      FROM TB_Boletim         B
INNER JOIN TB_Disciplina      D
        ON D.ID_Disciplina    = B.ID_Disciplina
INNER JOIN TB_Aluno           A
        ON A.ID_Aluno         = B.ID_Aluno   
     WHERE A.DS_Sexo = 'Masculino'
       AND A.TP_Status = 'F';
         
-- Selecione o nome, número de chamada e turma da tabela boletim filtrando pelo número da chamada ordenando a consulta pelo nome do aluno.

    SELECT A.NM_Aluno,
           A.NR_Chamada,
           A.NM_Turma
      FROM TB_Boletim         B
INNER JOIN TB_Disciplina      D
        ON D.ID_Disciplina    = B.ID_Disciplina
INNER JOIN TB_Aluno           A
        ON A.ID_Aluno         = B.ID_Aluno   
     WHERE A.NR_Chamada BETWEEN 25 AND 50
  ORDER BY A.NM_Aluno;
         
-- Selecione todos os campos da tabela boletim filtrando pelo ano letivo e pela frequência.

    SELECT *
      FROM TB_Boletim         B
INNER JOIN TB_Disciplina      D
        ON D.ID_Disciplina    = B.ID_Disciplina
INNER JOIN TB_Aluno           A
        ON A.ID_Aluno         = B.ID_Aluno   
     WHERE A.NR_Ano_Letivo = 2021
       AND A.TP_Status = 'F';