# Session one: Recomeçar | 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),
  ID_Genero              int, 
  DT_Lancamento          date,
  BT_Disponivel          bool,
  VL_Avaliacao           decimal (3,1),
  FOREIGN KEY (ID_Genero) references TB_Genero (ID_Genero)
  );

> Insert Script

INSERT INTO TB_Genero (DS_Genero, BT_Ativo)
  values ('Comédia', true),
         ('Terror', false),
         ('Ação', true),
         ('Ficção Científica', true),
         ('Biográfico', false);
         
  INSERT INTO TB_Filmes (NM_Filme, ID_Genero, DT_Lancamento, BT_Disponivel, VL_Avaliacao)
       values ('Deadpool', 1, '2016-02-11', false, 6.8),
              ('O Auto da Compadecida', 1, '2000-09-10', true, 7.6),
              ('John Wick - De volta ao jogo', 5, '2014-11-27', true, 9.7),
              ('O jogo da imitação', 5, '2015-01-08', false, 8.9),
              ('Venom', 4, '2018-10-04', true, 7.4),
              ('O Cavaleiro das Trevas', 3, '2008-07-18', true, 9.6),
              ('O pequenino', 1, '2006-07-13', true, 8.0),
              ('Shrek', 1, '2001-06-22', true, 10),
              ('Aves de Rapina', 3, '2020-02-6', false, 4.9),
              ('Sonic: O filme', 4, '2020-02-13', true,  8.3);
              
              
UPDATE TB_Filmes 
   SET BT_Disponivel = true
 WHERE ID_Filmes = 4;
 
UPDATE TB_Filmes 
   SET ID_Genero = 3
 WHERE ID_Filmes = 3;
 
DELETE FROM TB_Filmes
      WHERE ID_Filmes = 9;
      
DELETE FROM TB_Filmes
      WHERE ID_Filmes = 1;

> Select Script

--Consultar todos os filmes com gênero igual a ‘Ação’.

SELECT *
      FROM TB_Filmes   F
INNER JOIN TB_Genero   G
        ON F.ID_Genero = G.ID_Genero
     WHERE G.DS_Genero = "Ação";
 
--Consultar todos os filmes que possuam o nome e gênero contendo a letra ‘a’.

     SELECT *
      FROM TB_Filmes   F
INNER JOIN TB_Genero   G
        ON F.ID_Genero = G.ID_Genero
     WHERE F.NM_Filme like "%a%"
       AND G.DS_Genero like "%a%";
       
-- Consultar todos os filmes que estejam disponíveis e com avaliação maior que 8.

       SELECT *
      FROM TB_Filmes   F
INNER JOIN TB_Genero   G
        ON F.ID_Genero = G.ID_Genero
     WHERE F.BT_Disponivel = true
       AND F.VL_Avaliacao > 8;
       
--Consultar todos os filmes que foram lançados entre 2010 e 2020 com nome de filme menor que 10 caracteres.

       SELECT *
      FROM TB_Filmes   F
INNER JOIN TB_Genero   G
        ON F.ID_Genero = G.ID_Genero
     WHERE F.DT_Lancamento BETWEEN '2010-01-01' AND '2020-01-01'
       AND LENGTH(F.NM_Filme) < 10;
       
       
-- 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   F
INNER JOIN TB_Genero   G
        ON F.ID_Genero = G.ID_Genero
     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çao e  a avaliação esteja entre 7.5 e 10.

    SELECT G.DS_Genero,
           F.NM_Filme,
           F.Vl_Avaliacao
      FROM TB_Filmes   F
INNER JOIN TB_Genero   G
        ON F.ID_Genero = G.ID_Genero
     WHERE G.DS_Genero = 'Comédia'
        OR G.DS_Genero = 'Ação'
       AND F.VL_Avaliacao BETWEEN 7.5 AND 10;
       
-- Consultar todos os filmes que tenham o gênero biográfico ou ação e que a avaliação seja maior que 8.5. 
    SELECT *
      FROM TB_Filmes   F
INNER JOIN TB_Genero   G
        ON F.ID_Genero = G.ID_Genero 
     WHERE G.DS_Genero IN('Biográfico', 'Ação')
       AND F.VL_Avaliacao > 8.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   F
INNER JOIN TB_Genero   G
        ON F.ID_Genero = G.ID_Genero
     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   F
INNER JOIN TB_Genero   G
        ON F.ID_Genero = G.ID_Genero
     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_Turma (
  ID_Turma              int primary key auto_increment,
  NM_Turma              varchar (30),
  NR_Ano_Letivo         int,
  NM_Curso              varchar (30)
  );
  

CREATE TABLE TB_Aluno (
  ID_Aluno               int primary key auto_increment,
  ID_Turma               int,
  DS_Sexo                varchar (15), 
  NM_Aluno               varchar (40),
  NR_Chamada             int,
  TP_Status              varchar(1),
  FOREIGN KEY (ID_Turma) references TB_Turma (ID_Turma)
  );

> Insert Script

   INSERT INTO TB_Turma (NM_Turma, NR_Ano_Letivo, NM_Curso)
  values ('InfoC', 2021, 'Informática'),
         ('2°E', 2021, 'Ensino Médio'),
         ('EletroB', 2021, 'Eletrônica'),
         ('9°D', 2019, 'Ensino Fundamental'),
         ('7°A', 2017, 'Ensino Fundamental');
         
  INSERT INTO TB_Aluno (ID_Turma, DS_Sexo, NM_Aluno, NR_Chamada, TP_Status)
       values (1, 'Masculino', 'Vitório Trindade Santana', 50, 'F'),
              (2, 'Masculino', 'Renan Coutinho Nunes', 33, 'F'),
              (1, 'Masculino', 'Kauã Pansan Ferreira Costa', 32, 'F'),
              (3, 'Feminino', 'Maria Fernandes Costa', 28, 'D'),
              (5, 'Feminino', 'Thaís Felix Oliveira', 43, 'F'),
              (3, 'Masculino', 'Miguel Gaspar Rodrigues', 29, 'F'),
              (2, 'Masculino', 'Matheus Gomes Colavope', 24, 'F'),
              (4, 'Feminino', 'Ana Silva Fagundes', 43, 'D'),
              (2, 'Masculino', 'Gabriel Pereira', 14, 'D'),
              (5, 'Feminino', 'Giulia Costa Pinto', 19, 'F');
              
 UPDATE TB_Aluno 
   SET NR_Chamada = 3
 WHERE ID_Aluno = 8;
 
UPDATE TB_Aluno 
   SET TP_Status = 'D'
 WHERE ID_Aluno = 2;
 
DELETE FROM TB_Aluno
      WHERE ID_Aluno = 7;
      
DELETE FROM TB_Aluno
      WHERE ID_Aluno = 4;

> Select Script

--Selecionar turma, nome e chamada da tabela alunos filtrando por turma.

    SELECT T.NM_Turma,
           A.NM_Aluno,
           A.NR_Chamada
      FROM TB_Aluno    A 
INNER JOIN TB_Turma    T
        ON A.ID_Turma = T.ID_Turma
     WHERE T.NM_Turma = "InfoC";
     
-- Selecionar todos campos da tabela alunos filtrando por nome e turma de forma parcial.

    SELECT *
      FROM TB_Aluno    A 
INNER JOIN TB_Turma    T
        ON A.ID_Turma = T.ID_Turma
     WHERE A.NM_Aluno like 'G%'
       AND T.NM_Turma like '%A%';
     
     
-- Selecione ano letivo, turma, nome, chamada, sexo da tabela alunos filtrando os frequentes por turma e ano letivo.

     SELECT T.NR_Ano_Letivo,
            T.NM_Turma,
            A.NM_Aluno,
            A.NR_Chamada,
            A.DS_Sexo
      FROM TB_Aluno    A 
INNER JOIN TB_Turma    T
        ON A.ID_Turma = T.ID_Turma
     WHERE A.TP_Status = 'F'
       AND T.NR_Ano_Letivo = 2021;
       
       
-- Selecione nome, sexo da tabela alunos filtrando por ano, turma e sexo.

        SELECT A.NM_Aluno,
               A.DS_Sexo
          FROM TB_Aluno    A 
    INNER JOIN TB_Turma    T
            ON A.ID_Turma = T.ID_Turma
         WHERE T.NR_Ano_Letivo = 2019
           AND T.NM_Turma = '9°D'
           AND A.DS_Sexo = 'Feminino';
       
-- Selecione nome e número da chamada da tabela alunos filtrando pelo sexo e ano letivo.

        SELECT A.NM_Aluno,
               A.NR_Chamada
          FROM TB_Aluno   A
    INNER JOIN TB_Turma   T
            ON A.ID_Turma = T.ID_Turma
         WHERE A.DS_Sexo = 'Masculino'
           AND T.NR_Ano_Letivo = 2021;
           
-- Selecione todos os campos da tabela aluno filtrando pelo curso e pelo ano letivo.          
        SELECT *
          FROM TB_Aluno   A
    INNER JOIN TB_Turma   T
            ON A.ID_Turma = T.ID_Turma
         WHERE T.NM_Curso = 'Ensino Fundamental'
           AND T.NR_Ano_Letivo BETWEEN 2017 AND 2019;
         
-- Selecione todos os campos da tabea aluno filtrando pelo sexo e a frequência.         

         SELECT *
          FROM TB_Aluno   A
    INNER JOIN TB_Turma   T
            ON A.ID_Turma = T.ID_Turma
         WHERE A.DS_Sexo = 'Masculino'
           AND A.TP_Status = 'D';
         
-- Selecione o nome, número de chamada e turma da tabela aluno filtrando pelo número da chamada.

        SELECT A.NM_Aluno,
               A.NR_Chamada,
               T.NM_Turma
          FROM TB_Aluno   A
    INNER JOIN TB_Turma   T
            ON A.ID_Turma = T.ID_Turma
         WHERE A.NR_Chamada BETWEEN 25 AND 50;
         
-- Selecione todos os campos da tabela aluno filtrando pelo ano letivo e pela frequência.

         SELECT *
          FROM TB_Aluno   A
    INNER JOIN TB_Turma   T
            ON A.ID_Turma = T.ID_Turma
         WHERE T.NR_Ano_Letivo = 2021
           AND A.TP_Status = 'F';