### INFO B - Caroline Campos Lima, 04

# B.D Session One: Recomeçar

[toc]

## Modelagem Filme

CREATE TABLE tb_genero (
  id_genero     int primary key auto_increment,
  ds_genero     varchar(100),
  bt_ativo      bool
);

CREATE TABLE tb_filme (
  id_filme          int primary key auto_increment,
  nm_filme          varchar(100),
  id_genero         int,
  vl_avaliacao      decimal(10, 2),
  bt_disponivel     bool,
  dt_lancamento     date,
  foreign key (id_genero) references tb_genero (id_genero)
);

> 1. Inserir 10 registros na tabela.

INSERT INTO tb_genero (ds_genero, bt_ativo)
     VALUES ('ação', true),
            ('romance', true),
            ('comédia', true);  
  
INSERT INTO tb_filme (nm_filme, id_genero, vl_avaliacao, bt_disponivel, dt_lancamento)
     VALUES ('Sonic: O Filme', 3, 9.0, true, '2020-2-13'),
            ('Velozes e Furiosos 9', 1, 9.6, true, '2021-4-1'),
            ('Titanic: 20th Anniversary', 2, 9.4, true, '1998-1-16'),
            ('Moana - Um Mar de Aventuras', 3, 8.8, true, '2017-1-5'),
            ('Para Todos os Garotos que Já Amei', 2, 9.1, true, '2018-8-17'),
            ('Crepúsculo', 2, 8.9, true, '2008-12-19'),
            ('Vingadores: Guerra Infinita', 1, 9.3, true, '2018-4-26');

> 2. Alterar 2 registros da tabela.

UPDATE tb_genero
   SET ds_genero = 'infantil'
 WHERE id_genero = 3;
 
UPDATE tb_filme
   SET vl_avaliacao = 9.6
 WHERE id_genero = 5;

> 3. Remover 2 registros da tabela.

DELETE FROM tb_filme 
      WHERE id_filme = 3;
      
DELETE FROM tb_filme 
      WHERE id_filme = 5;

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

    SELECT *
      FROM tb_filme F
INNER JOIN tb_genero G
        ON F.id_genero = G.id_genero
     WHERE G.ds_genero = 'ação';

> 5. Consultar todos os filmes que possuam o nome e gênero contendo a letra ‘a’.

    SELECT *
      FROM tb_filme 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%';

> 6. Consultar todos os filmes que estejam disponíveis e com avaliação maior que 8.

    SELECT *
      FROM tb_filme F
INNER JOIN tb_genero G
        ON F.id_genero = G.id_genero
     WHERE F.bt_disponivel = true
       AND F.vl_avaliacao > 8;

> 7. Consultar todos os filmes que foram lançados entre 2010 e 2020 com nome de filme menor que 10 caracteres.

    SELECT *
      FROM tb_filme F
INNER JOIN tb_genero G
        ON F.id_genero = G.id_genero
     WHERE F.dt_lancamento BETWEEN '2010-1-1' AND '2020-12-31'
       AND ;

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

8.1 > Consulta pelos campos do nome, genero e lançamento do filme em que o filme esta a disponível e o gênero for infantil 
    SELECT nm_filme,
           dt_lancamento,
           ds_genero
      FROM tb_filme f
INNER JOIN tb_genero g
        ON f.id_genero = g.id_genero
     WHERE g.ds_genero LIKE "infantil"
       AND f.bt_disponivel = true;
     
8.2 > Consulta pelos filmes que possuem avaliação entre 8,9 e 9,5
    SELECT *
      FROM tb_filme f
INNER JOIN tb_genero g
        ON f.id_genero = g.id_genero
     WHERE f.vl_avaliacao BETWEEN 8.9 AND 9.5;
     
8.3 > Consulta pelos campos do nome do filme, se esta disponível, data de lançamento, gênero e se esta ativo todos os filmes que possuem mais de um nome 
    SELECT nm_filme,
           bt_disponivel
           dt_lancamento,
           ds_genero,
           bt_ativo
      FROM tb_filme f
INNER JOIN tb_genero g
        ON f.id_genero = g.id_genero
     WHERE f.nm_filme LIKE "% %";
     
8.4 > Consulta por todos os campos em que o filme foi lançado no 1º bimestre do ano e ordenado por ordem decrescente
    SELECT *
      FROM tb_filme f
INNER JOIN tb_genero g
        ON f.id_genero = g.id_genero
     WHERE month (f.dt_lancamento) IN (1, 2)
  ORDER BY nm_filme DESC; 
     
8.5 > Consulta por filmes que tem o genero romance ou ação ou que tem avaliação menor que 8,8
    SELECT *
      FROM tb_filme f
INNER JOIN tb_genero g
        ON f.id_genero = g.id_genero
     WHERE g.ds_genero IN ('romance', 'ação')
        OR f.vl_avaliacao < 8.8; 

## Modelagem Aluno

CREATE TABLE tb_turma (
  id_turma          int primary key auto_increment,
  nm_turma          varchar(255),
  nr_ano_letivo     int,
  nm_curso          varchar(255)
);

CREATE TABLE tb_aluno (
  id_aluno       int primary key auto_increment,
  id_turma       int,
  ds_sexo        varchar(20),
  nm_aluno       varchar(255),
  nr_chamada     int,
  tp_status      varchar(1),
  foreign key (id_turma) references tb_turma (id_turma)
);

> 1. Inserir 10 registros na tabela.

INSERT INTO tb_turma (nm_turma, nr_ano_letivo, nm_curso)
     VALUES ('InfoA', 2021, 'Informática'),
            ('InfoB', 2021, 'Informática'),
            ('AdmA', 2021, 'Administração');
            
INSERT INTO tb_aluno (id_turma, ds_sexo, nm_aluno, nr_chamada, tp_status)
     VALUES (2, 'Masculino', 'Gabriel Campos', 16, 'F'),
            (3, 'Masculino', 'Leonardo Guedes', 37, 'F'),
            (1, 'Feminino', 'Jhenifer Nogueira', 22, 'F'),
            (3, 'Feminino', 'Isabela Souza', 17, 'F'),
            (2, 'Feminino', 'Lohana Soares', 36, 'D'),
            (1, 'Masculino', 'Diogo Moreira', 7, 'D'),
            (2, 'Feminino', 'Karine Freitas', 29, 'F');

> 2. Alterar 2 registros da tabela.

UPDATE tb_turma
   SET nm_curso = 'Informática'
 WHERE id_turma = 3;
 
UPDATE tb_turma
   SET nm_turma = 'InfoC'
 WHERE id_turma = 3;

> 3. Remover 2 registros da tabela.

DELETE FROM tb_aluno
      WHERE id_aluno = 4;
      
DELETE FROM tb_aluno
      WHERE id_aluno = 7;

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

    SELECT nm_turma,
           nm_aluno,
           nr_chamada
      FROM tb_aluno a
INNER JOIN tb_turma t
        ON a.id_turma = t.id_turma
     WHERE t.nm_turma = "InfoB";

> 5. 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 "%a%"
       AND t.nm_turma LIKE "InfoC";

> 6. Selecione ano letivo, turma, nome, chamada, sexo da tabela alunos filtrando os frequentes por turma e ano letivo.

    SELECT nr_ano_letivo,
           nm_turma,
           nm_aluno,
           nr_chamada,
           ds_sexo
      FROM tb_aluno a
INNER JOIN tb_turma t
        ON a.id_turma = t.id_turma
     WHERE a.tp_status LIKE "F"
       AND t.nr_ano_letivo = 2021;

> 7. Selecione nome, sexo da tabela alunos filtrando por ano, turma e sexo.

    SELECT nm_aluno,
           ds_sexo
      FROM tb_aluno a
INNER JOIN tb_turma t
        ON a.id_turma = t.id_turma
     WHERE t.nr_ano_letivo = 2021
       AND t.nm_turma LIKE "InfoA"
       AND a.ds_sexo LIKE "Feminino";

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

8.1 > Consulta por todos os campos da tabela em que o sexo do aluno seja masculino
    SELECT *
      FROM tb_aluno a
INNER JOIN tb_turma t
        ON a.id_turma = t.id_turma
     WHERE a.ds_sexo LIKE "Masculino";
     
8.2 > Consulta pelos campos do nome do aluno, número de chamada e nome da turma em que o número do aluno seja maior ou igual a 20 e que for do curso de informática
    SELECT nm_aluno,
           nr_chamada,
           nm_turma
      FROM tb_aluno a
INNER JOIN tb_turma t
        ON a.id_turma = t.id_turma
     WHERE a.nr_chamada >= 20
       AND t.nm_curso LIKE "Informática";
     
8.3 > Consulta por todos os campos em que o último sobrenome do aluno termina com "a"
    SELECT *
      FROM tb_aluno a
INNER JOIN tb_turma t
        ON a.id_turma = t.id_turma
     WHERE a.nm_aluno LIKE "%a";
     
8.4 > Consulta pelo sexo, nome, turma e curso do aluno em que ele desistiu curso
    SELECT ds_sexo,
           nm_aluno,
           nm_turma,
           nm_curso
      FROM tb_aluno a
INNER JOIN tb_turma t
        ON a.id_turma = t.id_turma
     WHERE a.tp_status LIKE "D";
     
8.5 > Consulta por alunos do sexo masculino que são da turma de informática e que seu número seja menor que 25
    SELECT *
      FROM tb_aluno a
INNER JOIN tb_turma t
        ON a.id_turma = t.id_turma
     WHERE t.nm_turma LIKE "Info%"
       AND a.ds_sexo LIKE "Masculino"
       AND nr_chamada < 25;