### INFO B - Caroline Campos Lima, 04

# B.D Session Two: Getting Better

[toc]

## DDL - Filme

### 1. Criar as tabelas respeitando os relacionamentos do DER.

CREATE TABLE tb_filme (
  id_filme          int PRIMARY KEY AUTO_INCREMENT,
  nm_filme          varchar(100),
  dt_lancamento     date,
  vl_avaliacao      decimal(15, 2),
  bt_disponivel     bool
);

CREATE TABLE tb_genero (
  id_genero     int PRIMARY KEY AUTO_INCREMENT,
  ds_genero     varchar(100),
  bt_ativo      bool
);

CREATE TABLE tb_filme_genero (
  id_filme_genero     int PRIMARY KEY AUTO_INCREMENT,
  id_filme            int,
  id_genero           int,
  FOREIGN KEY (id_filme) REFERENCES tb_filme (id_filme) ON DELETE CASCADE,
  FOREIGN KEY (id_genero) REFERENCES tb_genero (id_genero) ON DELETE CASCADE
);

### 2. Inserir 5 filmes, 5 gêneros, 10 vínculos de filmeXgênero nas tabelas. script

INSERT INTO tb_filme (nm_filme, dt_lancamento, vl_avaliacao, bt_disponivel)
     VALUES ('Sonic: O Filme', '2020-2-13', 9.0, true),
            ('Velozes e Furiosos 9', '2021-4-1', 9.6, true),
            ('Titanic: 20th Anniversary', '1998-1-16', 9.4, true),
            ('Crepúsculo', '2008-12-19', 8.9, true),
            ('Vingadores: Guerra Infinita', '2018-4-26', 9.3, true);

INSERT INTO tb_genero (ds_genero, bt_ativo)
     VALUES ('ação', true),
            ('romance', true),
            ('comédia', true),
            ('ficção', true),
            ('aventura', true);
            
INSERT INTO tb_filme_genero (id_filme, id_genero)
     VALUES (1, 4),
            (1, 3),
            (2, 1),
            (2, 5),
            (3, 1),
            (3, 2),  
            (4, 4),
            (4, 2),
            (5, 1),
            (5, 4);

### 3. Alterar 1 filme, 1 gênero, 1 vínculo de filmeXgênero das tabelas.

UPDATE tb_filme
   SET bt_disponivel = false 
 WHERE id_filme = 5;

UPDATE tb_genero
   SET ds_genero = 'drama'
 WHERE id_genero = 3;
 
UPDATE tb_filme_genero
   SET id_genero = 5
 WHERE id_filme_genero = 10;

### 4. Remover 1 filme, 1 gênero, 1 vínculo de filmeXgênero das tabelas.

DELETE FROM tb_filme
      WHERE id_filme = 3;

DELETE FROM tb_genero
      WHERE id_genero = 1;

DELETE FROM tb_filme_genero
      WHERE id_filme_genero = 5;

## DML - Filme

### 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_filme F ON FG.id_filme = F.id_filme
INNER JOIN tb_genero G ON FG.id_genero = G.id_genero
  ORDER BY nm_filme;

### 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.

    SELECT F.id_filme,
           F.nm_filme,
           G.id_genero,
           G.ds_genero
      FROM tb_filme_genero FG
INNER JOIN tb_filme F ON FG.id_filme = F.id_filme
INNER JOIN tb_genero G ON FG.id_genero = G.id_genero
     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_filme F ON FG.id_filme = F.id_filme
INNER JOIN tb_genero G ON FG.id_genero = G.id_genero
     WHERE F.bt_disponivel = true
       AND 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,
           F.dt_lancamento,
           F.bt_disponivel,
           G.ds_genero
      FROM tb_filme_genero FG
INNER JOIN tb_filme F ON FG.id_filme = F.id_filme
INNER JOIN tb_genero G ON FG.id_genero = G.id_genero
     WHERE F.bt_disponivel = true
       AND G.ds_genero IN ('romance', '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

-- Selecionar todos os campos aplicando o relacionamento nas tabelas, filtrando por filmes que começam com 'V' e o gênero igual a ação
    SELECT *
      FROM tb_filme_genero FG
INNER JOIN tb_filme F ON FG.id_filme = F.id_filme
INNER JOIN tb_genero G ON FG.id_genero = G.id_genero
     WHERE F.nm_filme LIKE 'V%'
       AND G.ds_genero LIKE 'ação';

-- Selecionar filme, lançamento e disponível aplicando o relacionamento nas tabelas, filtrando por data entre 01/01/2020 e 04/05/2021
    SELECT F.nm_filme,
           F.dt_lancamento,
           F.bt_disponivel
      FROM tb_filme_genero FG
INNER JOIN tb_filme F ON FG.id_filme = F.id_filme
INNER JOIN tb_genero G ON FG.id_genero = G.id_genero
     WHERE F.dt_lancamento BETWEEN '2020-01-01' AND '2021-05-04';
     
-- Selecionar filme, disponivel, genero e ativo aplicando o relacionamento nas tabelas, filtrando os filmes que não estão disponíveis e que o gênero é ativo
    SELECT F.nm_filme,
           F.bt_disponivel,
           G.ds_genero,
           G.bt_ativo
      FROM tb_filme_genero FG
INNER JOIN tb_filme F ON FG.id_filme = F.id_filme
INNER JOIN tb_genero G ON FG.id_genero = G.id_genero
     WHERE F.bt_disponivel = false
       AND G.bt_ativo = true;
       
-- Selecionar filme, avaliação e gênero aplicando o relacionamento nas tabelas, filtrando os que possuem avaliação menor que 9.4, ordenando por avaliação
    SELECT F.nm_filme,
           F.vl_avaliacao,
           G.ds_genero
      FROM tb_filme_genero FG
INNER JOIN tb_filme F ON FG.id_filme = F.id_filme
INNER JOIN tb_genero G ON FG.id_genero = G.id_genero
     WHERE F.vl_avaliacao < 9.4
  ORDER BY F.vl_avaliacao;
       
-- Selecionar todos os cmapos aplicando o relacionamento nas tabelas, filtrando os filmes que possuem descrição no nome (:)
    SELECT *
      FROM tb_filme_genero FG
INNER JOIN tb_filme F ON FG.id_filme = F.id_filme
INNER JOIN tb_genero G ON FG.id_genero = G.id_genero
     WHERE F.nm_filme LIKE "%:%";

## DDL - Aluno

### 1. Criar as tabelas respeitando os relacionamentos do DER.

CREATE TABLE tb_aluno (
  id_aluno          int PRIMARY KEY AUTO_INCREMENT,
  nm_turma          varchar(255),
  nr_ano_letivo     int,
  nm_aluno          varchar(255),
  nr_chamada        int,
  ds_sexo           varchar(20),
  tp_status         varchar(1)
);

CREATE TABLE tb_disciplina (
  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_nota           decimal(15, 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
);

### 2. Inserir 3 alunos, 5 disciplinas, e as notas de cada aluno nas disciplinas.

INSERT INTO tb_aluno (nm_turma, nr_ano_letivo, nm_aluno, nr_chamada, ds_sexo, tp_status)
     VALUES ('2°X', '2021', 'Nicolle Saraiva', 34, 'Feminino', 'F'), 
            ('1°L', '2021', 'Marcela Albuquerque', 36, 'Feminino', 'F'),
            ('2°X', '2021', 'Henry Pereira', 15, 'Masculino', 'F');
       
INSERT INTO tb_disciplina (nm_disciplina, nm_professor)
     VALUES ('Português', 'Alceu'),
            ('Matemática', 'Francisco'),
            ('Ciências', 'Nilza'),
            ('História', 'Renato'),
            ('Geografia', 'Joana');
            
INSERT INTO tb_boletim (id_aluno, id_disciplina, vl_nota, qtd_faltas)
     VALUES (1, 1, 7.6, 0),
            (1, 2, 9.8, 0),
            (1, 3, 8.5, 2),
            (1, 4, 2.0, 0),
            (1, 5, 6.9, 0),
            (2, 1, 10.0, 3),
            (2, 2, 10.0, 0),
            (2, 3, 9.6, 1),
            (2, 4, 4.3, 2),
            (2, 5, 9.8, 1),
            (3, 1, 6.0, 0),
            (3, 2, 9.6, 2),
            (3, 3, 8.5, 0),
            (3, 4, 9.3, 1),
            (3, 5, 8.4, 0);

### 3. Alterar 1 aluno, 1 disciplina, 2 notas.

UPDATE tb_aluno
   SET nm_aluno = 'Thaiana Martins' 
 WHERE id_aluno = 1;

UPDATE tb_disciplina
   SET nm_disciplina = 'Física'
 WHERE id_disciplina = 3;
 
UPDATE tb_boletim
   SET vl_nota = 7.5
 WHERE id_boletim = 6;

UPDATE tb_boletim
   SET vl_nota = 3.9
 WHERE id_boletim = 11;

### 4. Remover 1 nota, 1 disciplina, 1 aluno. ** FAÇA A REMOÇÃO DEPOIS DE TERMINAR OS EXERCÍCIOS DML ABAIXO

## DML - Aluno

### 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 B.id_aluno = A.id_aluno
INNER JOIN tb_disciplina D ON B.id_disciplina = D.id_disciplina
  ORDER BY 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_disciplina,
           B.vl_nota
      FROM tb_boletim B
INNER JOIN tb_aluno A ON B.id_aluno = A.id_aluno
INNER JOIN tb_disciplina D ON B.id_disciplina = D.id_disciplina
     WHERE A.nm_turma LIKE '2°X'
       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 B.id_aluno = A.id_aluno
INNER JOIN tb_disciplina D ON B.id_disciplina = D.id_disciplina
     WHERE B.vl_nota >= 5
       AND A.nr_ano_letivo = 2021
       AND A.nm_turma LIKE '2°X'
  ORDER BY A.nr_ano_letivo AND A.nm_turma AND A.nr_chamada;

### 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 B.id_aluno = A.id_aluno
INNER JOIN tb_disciplina D ON B.id_disciplina = D.id_disciplina
     WHERE B.vl_nota < 5
       AND A.nr_ano_letivo = 2021
       AND A.nm_turma LIKE '1°L';

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

-- Selecionar todos os campos aplicando o relacionamento nas tabelas, filtrando pelo número da chamada menor que 20 ordenando pelas notas mais altas
    SELECT *
      FROM tb_boletim B
INNER JOIN tb_aluno A ON B.id_aluno = A.id_aluno
INNER JOIN tb_disciplina D ON B.id_disciplina = D.id_disciplina
     WHERE A.nr_chamada < 20
  ORDER BY B.vl_nota DESC;

-- Selecionar aluno, turma, disciplina, nota aplicando o relacionamento nas tabelas, filtrando os alunos que passaram na matéria de história ordenando pelas notas maiores
    SELECT A.nm_aluno,
           A.nm_turma,
           D.nm_disciplina,
           B.vl_nota
      FROM tb_boletim B
INNER JOIN tb_aluno A ON B.id_aluno = A.id_aluno
INNER JOIN tb_disciplina D ON B.id_disciplina = D.id_disciplina
     WHERE B.vl_nota >= 5
       AND D.nm_disciplina = 'História'
  ORDER BY B.vl_nota DESC;
     
-- Selecionar aluno, disciplina, nota aplicando o relacionamento nas tabelas, filtrando os alunos que tiraram nota azul com os professores Alceu e Francisco
    SELECT A.nm_aluno,
           A.nm_turma,
           B.vl_nota
      FROM tb_boletim B
INNER JOIN tb_aluno A ON B.id_aluno = A.id_aluno
INNER JOIN tb_disciplina D ON B.id_disciplina = D.id_disciplina
     WHERE B.vl_nota >= 5
       AND D.nm_professor IN ('Alceu', 'Francisco');
       
-- Selecionar aluno, turma, disciplina e faltas aplicando o relacionamento nas tabelas, filtrando os alunos que não faltaram nas aulas
    SELECT A.nm_aluno,
           A.nm_turma,
           D.nm_disciplina,
           B.qtd_faltas
      FROM tb_boletim B
INNER JOIN tb_aluno A ON B.id_aluno = A.id_aluno
INNER JOIN tb_disciplina D ON B.id_disciplina = D.id_disciplina
     WHERE B.qtd_faltas = 0;
       
-- Selecionar aluno, turma, ano, sexo e nota aplicando o relacionamento nas tabelas, filtrando os alunos por ano e sexo, ordenando pelas piores notas
    SELECT A.nm_aluno,
           A.nm_turma,
           A.nr_ano_letivo,
           A.ds_sexo,
           B.vl_nota
      FROM tb_boletim B
INNER JOIN tb_aluno A ON B.id_aluno = A.id_aluno
INNER JOIN tb_disciplina D ON B.id_disciplina = D.id_disciplina
     WHERE A.nr_ano_letivo = 2021
       AND A.ds_sexo = 'Feminino'
  ORDER BY B.vl_nota;