# Session two: Getting better | Banco de Dados | 2° bimestre
**Nome** Kevin Ribeiro de Andrade
**Número** 27
**Turma** INFO A

## Tabela de relacionamento de filmes e afins
**DDL
1. Criar as tabelas respeitando os relacionamentos do DER.
2. Inserir 5 filmes, 5 gêneros, 10 vínculos de filmeXgênero nas tabelas.
3. Alterar 1 filme, 1 gênero, 1 vínculo de filmeXgênero das tabelas.
4. Remover 1 filme, 1 gênero, 1 vínculo de filmeXgênero das tabelas.**

CREATE TABLE TB_GENERO (
  
  id_genero int primary key auto_increment,
  ds_genero varchar (250),
  bt_ativo bool
  
);

CREATE TABLE TB_FILME(
  id_filme int primary key auto_increment,
  nm_filme varchar (255),
  dt_lancamento date,
  vl_avaliacao decimal(15,2),
  bt_disponivel bool
  
  );


CREATE TABLE TB_FILME_GENERO(
  id_filme_genero int primary key auto_increment,
  id_filme int,
  id_genero int, 
  
  
  FOREIGN KEY (id_genero) REFERENCES TB_GENERO (id_genero) ON DELETE CASCADE,
  FOREIGN KEY (id_filme) REFERENCES TB_FILME (id_filme) ON DELETE CASCADE
  
  
  ); 
  
INSERT INTO TB_GENERO( ds_genero, bt_ativo)
VALUES ('Ação', true),
        ('Drama', true),
        ('Aventura', true),
        ('terror', false),
        ('suspense', true),
        ('comédia', true),
        ('Romance', true);

INSERT INTO TB_FILME (nm_filme, dt_lancamento, vl_avaliacao, bt_disponivel)
VALUES ('Jujutsu kaisen o filme', '2021-12-21', 0, false),
       ('No game no life - 0', '2018-05-17', 9.1, true),
       ('Eternos', '2022-03-16', 0, false),
       ('viuva negra', '2021-05-13', 8.6, false),
       ('Batman- reboot', '2022-07-18', 0, false);

INSERT INTO TB_FILME_GENERO(id_filme, id_genero)
VALUES (1, 1),
        (2, 3),
        (2, 1),
        (3, 1),
        (5, 1),
        (2, 6),
        (1, 7),
        (1, 4),
        (5, 5),
        (5, 7);
        
        
        UPDATE TB_FILME_GENERO FG
        INNER JOIN TB_GENERO G
        ON G.id_genero = FG.id_genero
        INNER JOIN TB_FILME F
        ON F.id_filme = FG.id_filme
        SET FG.id_genero = 2
        WHERE FG.id_filme_genero = 1;
        
        
        
        UPDATE TB_FILME F
       SET F.nm_filme = 'Sou eu'
       WHERE F.id_filme = 2;
       
       UPDATE TB_GENERO G
       SET G.ds_genero = 'horror'
       WHERE G.id_genero = 5;
       
       
       DELETE 
       FROM TB_FILME_GENERO 
       /*INNER JOIN TB_GENERO G
       ON G.id_genero = F.id_genero
       INNER JOIN TB_FILME F
       ON F.id_filme = F.id_filme*/
       WHERE id_filme_genero = 7;
       
       
       DELETE
       FROM TB_FILME 
       WHERE id_filme = 3;
       
       DELETE
       FROM TB_GENERO
       WHERE id_genero = 7;

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

SELECT F.nm_filme,
       G.ds_genero
FROM TB_FILME_GENERO FG
INNER JOIN TB_GENERO G          
ON G.id_genero = FG.id_genero
INNER JOIN TB_FILME F
ON  F.id_filme = FG.id_filme
ORDER
BY FG.id_filme desc;

**2. 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.**

ou

SELECT FG.id_filme,
       F.nm_filme,
       FG.id_genero,
       G.ds_genero
FROM TB_FILME_GENERO FG
INNER JOIN TB_GENERO G          
ON G.id_genero = FG.id_genero
INNER JOIN TB_FILME F
ON  F.id_filme = FG.id_filme
WHERE F.vl_avaliacao >= 4
ORDER
BY F.nm_filme;

**3. 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_FILME_GENERO FG
INNER JOIN TB_GENERO G          
ON G.id_genero = FG.id_genero
INNER JOIN TB_FILME F
ON  F.id_filme = FG.id_filme
WHERE G.ds_genero = "Ação"
ORDER
BY F.vl_avaliacao;

**4. 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,
       G.ds_genero,
       F.dt_lancamento,
      bt_disponivel
FROM TB_FILME_GENERO FG
INNER JOIN TB_GENERO G          
ON G.id_genero = FG.id_genero
INNER JOIN TB_FILME F
ON  F.id_filme = FG.id_filme
WHERE G.ds_genero = "Romance"
AND G.ds_genero = "Comédia"

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

SELECT F.nm_filme,
       F.vl_avaliacao,
       F.dt_lancamento,
       F.bt_disponivel,
       G.ds_genero
FROM TB_FILME_GENERO FG
INNER JOIN TB_GENERO G          
ON G.id_genero = FG.id_genero
INNER JOIN TB_FILME F
ON  F.id_filme = FG.id_filme
WHERE FG.id_genero = 6
AND FG.id_genero = 7;


SELECT FG.id_filme,
       F.nm_filme,
       F.vl_avaliacao,
       F.dt_lancamento,
       F.bt_disponivel,
       G.ds_genero
FROM TB_FILME_GENERO FG
INNER JOIN TB_GENERO G          
ON G.id_genero = FG.id_genero
INNER JOIN TB_FILME F
ON  F.id_filme = FG.id_filme
WHERE F.vl_avaliacao BETWEEN  6
AND 7;

SELECT FG.id_filme_genero,
       F.nm_filme,
       G.ds_genero
FROM TB_FILME_GENERO FG
INNER JOIN TB_GENERO G
ON G.id_genero = FG.id_genero
INNER JOIN TB_FILME F
ON F.id_filme = FG.id_filme
WHERE F.nm_filme NOT LIKE ('E%');

SELECT F.nm_filme,
       G.ds_genero,
       F.vl_avaliacao,
       F.dt_lancamento,
       Substring (F.nm_filme, 0, 5)
FROM TB_FILME_GENERO FG
INNER JOIN TB_GENERO G
ON G.id_genero = FG.id_genero
INNER JOIN TB_FILME F
ON F.id_filme = FG.id_filme;

SELECT  FG.id_filme_genero,
        F.nm_filme,
        G.ds_genero,
        Dayofweek(F.dt_lancamento)
FROM TB_FILME_GENERO FG
INNER JOIN TB_GENERO G
ON G.id_genero = FG.id_genero
INNER JOIN TB_FILME F
ON F.id_filme = FG.id_filme;

SELECT F.nm_filme,
       G.ds_genero,
       F.vl_avaliacao,
       F.bt_disponivel,
       concat(F.nm_filme, G.ds_genero)
FROM TB_FILME_GENERO FG
INNER JOIN TB_GENERO G
ON G.id_genero = FG.id_genero
INNER JOIN TB_FILME F
ON F.id_filme = FG.id_filme;

## Tabela de alunos e afins

**DDL
1. Criar as tabelas respeitando os relacionamentos do DER.
2. Inserir 3 alunos, 5 disciplinas, e as notas de cada aluno nas disciplinas.
3. Alterar 1 aluno, 1 disciplina, 2 notas.
4. Remover 1 nota, 1 disciplina, 1 aluno**

CREATE TABLE TB_DISCPLINA(
  id_discplina int primary key auto_increment not null,
  nm_discplina varchar (250) not null,
  nm_professor varchar (250) null
  
  );
  
  
  
  CREATE TABLE TB_ALUNO(
    id_aluno int primary key auto_increment,
    nm_turma varchar (250) not null,
    nr_ano_letivo long not null,
    nm_aluno varchar (250) not null,
    ds_sexo varchar (20) not null,
    nr_chamada long not null,
    tp_status varchar (2) not null
    
    );
    
    
    
    CREATE TABLE TB_BOLETIM(
      id_boletim int primary key auto_increment not null,
      id_aluno int not null,
      id_discplina int not null,
      vl_nota decimal(15,2) null,
      qtd_faltas long not null,
      
      FOREIGN KEY (id_aluno) REFERENCES TB_ALUNO (id_aluno) ON DELETE CASCADE,
      FOREIGN KEY (id_discplina) REFERENCES TB_DISCPLINA (id_discplina) ON DELETE CASCADE
      
      );
  
  
  INSERT INTO TB_DISCPLINA(nm_discplina, nm_professor)
 VALUES ('Matematica', 'Alexandra de Ddraviski'),
        ('Ciencias', 'Mauricio de souza'),
        ('Português', 'Alex Silva de Rockets'),
        ('Tecnologia', 'Alice da marlix'),
        ('Historia', 'Krista lenz');
        
        
        INSERT INTO TB_ALUNO (nm_turma, nr_ano_letivo, nm_aluno, ds_sexo, nr_chamada, tp_status)
        VALUES ( '2° ano A', 2021, 'Roberto costa', 'M', 28, 'F'),
               ('3° ano A', 2021, 'Aliciete souza pinto', 'F', 1, 'F'),
               ('7° ano E', 2021, 'Chitoge Kirisaki', 'F', 7, 'T'),
               ('1° série F', 2021, 'Aufasto Dramika Dourges', 'M', 2, 'FA'),
               ('1° ano J', 2021, 'Alioto de Fagundes', 'M', 4, 'T');
               
               INSERT INTO TB_BOLETIM (id_aluno, id_discplina, vl_nota, qtd_faltas)
               VALUES (1, 5, 1.3, 10),
                       (3, 4, 5.6, 4),
                       (2, 2, 7.8, 2),
                       (5, 5, 9.1, 6),
                       (4, 3, 10, 3);
                       
                       
               UPDATE TB_BOLETIM B 
               INNER JOIN TB_ALUNO A
               ON A.id_aluno = B.id_aluno
               INNER JOIN TB_DISCPLINA D
               ON D.id_discplina = B.id_discplina
               SET B.vl_nota = 4
               WHERE B.id_discplina = 5;
               
               
               UPDATE TB_BOLETIM B
               INNER JOIN TB_ALUNO A
               ON A.id_aluno = B.id_aluno
               INNER JOIN TB_DISCPLINA D
               ON D.id_discplina = B.id_discplina
               SET D.nm_discplina = 'C#'
               WHERE D.id_discplina = 3;
               
               UPDATE TB_BOLETIM B
               INNER JOIN TB_ALUNO A
               ON A.id_aluno = B.id_aluno
               INNER JOIN TB_DISCPLINA D
               ON D.id_discplina = B.id_discplina
               SET A.nm_aluno = 'Falcas Alcântara'
               WHERE A.id_aluno = 3;
             
             
             DELETE
             FROM TB_BOLETIM
             WHERE id_boletim = 1;
             
             DELETE 
             FROM TB_ALUNO
             WHERE id_aluno = 2;
             
             DELETE 
             FROM TB_DISCPLINA
             WHERE  id_discplina = 5;

**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_discplina,
      B.vl_nota
      FROM TB_BOLETIM B
      INNER JOIN TB_ALUNO A
      ON A.id_aluno = B.id_aluno
      INNER JOIN TB_DISCPLINA D
      ON D.id_discplina = B.id_discplina 
      ORDER
       BY A.nm_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_discplina,
      B.vl_nota
      FROM TB_BOLETIM B
      INNER JOIN TB_ALUNO A
      ON A.id_aluno = B.id_aluno
      INNER JOIN TB_DISCPLINA D
      ON D.id_discplina = B.id_discplina 
      WHERE A.nm_turma  + A.nr_ano_letivo
      ORDER
       BY A.nm_aluno;
       
       select A.nm_aluno,
       A.nm_turma,
      A.nr_ano_letivo,
       D.nm_discplina,
      B.vl_nota
      FROM TB_BOLETIM B
      INNER JOIN TB_ALUNO A
      ON A.id_aluno = B.id_aluno
      INNER JOIN TB_DISCPLINA D
      ON D.id_discplina = B.id_discplina 
      WHERE A.nm_turma = '2° ano A'
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_discplina,
      B.vl_nota
      FROM TB_BOLETIM B
      INNER JOIN TB_ALUNO A
      ON A.id_aluno = B.id_aluno
      INNER JOIN TB_DISCPLINA D
      ON D.id_discplina = B.id_discplina 
      WHERE B.vl_nota >= 5
AND  A.nr_ano_letivo + A.nm_turma
      ORDER
       BY A.nr_ano_letivo + A.nr_chamada + A.nm_turma;

**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_DISCPLINA D
      ON D.id_discplina = B.id_discplina 
      WHERE B.vl_nota <= 5
AND  A.nr_ano_letivo + A.nm_turma;

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

select A.nm_aluno,
       A.nm_turma,
      A.nr_ano_letivo,
       D.nm_discplina,
      B.vl_nota
      FROM TB_BOLETIM B
      INNER JOIN TB_ALUNO A
      ON A.id_aluno = B.id_aluno
      INNER JOIN TB_DISCPLINA D
      ON D.id_discplina = B.id_discplina 
      WHERE B.vl_nota BETWEEN 9
AND  10;

SELECT A.nm_aluno,
       A.nm_turma,
       D.nm_discplina
From TB_BOLETIM B
INNER JOIN TB_ALUNO A
ON A.id_aluno = B.id_aluno
INNER JOIN TB_DISCPLINA D
ON D.id_discplina = B.id_discplina
WHERE D.nm_discplina IN ('historia');
       
       
       SELECT A.nm_aluno,
              A.nm_turma,
              D.nm_discplina
 FROM TB_BOLETIM B
INNER JOIN TB_ALUNO A
ON A.id_aluno = B.id_aluno
INNER JOIN TB_DISCPLINA D
ON D.id_discplina = B.id_discplina
WHERE D.nm_discplina NOT IN ('Banco de dados');

SELECT A.nm_aluno,
       A.nm_turma,
      sign(B.vl_nota)
       FROM TB_BOLETIM B
INNER JOIN TB_ALUNO A
ON A.id_aluno = B.id_aluno
INNER JOIN TB_DISCPLINA D
ON D.id_discplina = B.id_discplina;

      SELECT A.nm_aluno,
             A.nm_turma,
             B.qtd_faltas           
FROM TB_BOLETIM B
INNER JOIN TB_ALUNO A
ON A.id_aluno = B.id_aluno
INNER JOIN TB_DISCPLINA D
ON D.id_discplina = B.id_discplina
WHERE instr(A.nm_aluno, 'B');