# Session One: Recomeçar | SQL
Por: Isabelly de Souza Nascimento, 18 - INFO D
[toc]

## Modelagem 1: Sistema de um catálogo de filme no estilo Netflix

>Create Script

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)
   );

>Insert Script

   insert into tb_genero(ds_genero, bt_ativo)
   values('Ação', true);
   
   insert into tb_genero(ds_genero, bt_ativo)
   values('Romance', true);
   
   insert into tb_genero(ds_genero, bt_ativo)
   values('Terror', true);
   
   insert into tb_genero(ds_genero, bt_ativo)
   values('Drama', true);
   
    insert into tb_genero(ds_genero, bt_ativo)
   values('Ficção Científica', true);
   
   
   insert into tb_filme(nm_filme, id_genero, vl_avaliacao, bt_disponivel, dt_lancamento)
   values('O Assassino: O Primeiro Alvo', 1, 8.0, true,'2017-09-11');
   
   insert into tb_filme(nm_filme, id_genero, vl_avaliacao, bt_disponivel, dt_lancamento)
   values('Resgate', 1, 9.0, true,'2020-04-24');
   
   insert into tb_filme(nm_filme, id_genero, vl_avaliacao, bt_disponivel, dt_lancamento)
   values('Com Amor, Simon', 2, 9.4, true,'2018-03-22');
   
    insert into tb_filme(nm_filme, id_genero, vl_avaliacao, bt_disponivel, dt_lancamento)
   values('Me Chame Pelo Seu Nome', 4, 9.2, true,'2018-01-08');
   
    insert into tb_filme(nm_filme, id_genero, vl_avaliacao, bt_disponivel, dt_lancamento)
   values('Fragmentado', 3, 8.9, true,'2017-03-17');
   
   
    insert into tb_filme(nm_filme, id_genero, vl_avaliacao, bt_disponivel, dt_lancamento)
   values('Mentes Sombrias', 5, 7.4, true,'2018-08-16');
   
    insert into tb_filme(nm_filme, id_genero, vl_avaliacao, bt_disponivel, dt_lancamento)
   values('O Ódio que Você Semeia', 4, 9.3, true,'2018-12-06');
   
   
    insert into tb_filme(nm_filme, id_genero, vl_avaliacao, bt_disponivel, dt_lancamento)
   values('Até o Último Homem', 4, 9.5, true,'2017-01-26');
   
    insert into tb_filme(nm_filme, id_genero, vl_avaliacao, bt_disponivel, dt_lancamento)
   values('Grese — Nos Tempos da Brilhantina', 2, 8.9, true,'1978-09-14');
   
   
    insert into tb_filme(nm_filme, id_genero, vl_avaliacao, bt_disponivel, dt_lancamento)
   values('Nos', 3, 7.4, true,'2019-03-21');

>Update Script

  UPDATE tb_filme
  SET nm_filme ='Nós',
      bt_disponivel = false
  WHERE id_filme = 10;
  
  UPDATE tb_filme
  SET nm_filme ='Grease — Nos Tempos da Brilhantina',
      vl_avaliacao = 3.9
  WHERE id_filme = 9;

>Delete Script

  --DELETE FROM tb_filme
  --WHERE id_filme = 1;
  
  --DELETE FROM tb_filme
  --WHERE id_filme = 7;

>Select Script

select g.ds_genero,
       f.nm_filme,
       f.vl_avaliacao,
       f.bt_disponivel,
       f.dt_lancamento
    from tb_filme f
inner join tb_genero g
    on f.id_genero = g.id_genero;
 
 
    select f.nm_filme
    from tb_filme f
inner join tb_genero g
    on f.id_genero = g.id_genero
  where g.ds_genero = 'Ação';  
  
  
 select f.nm_filme
    from tb_filme f
inner join tb_genero g
    on f.id_genero = g.id_genero
  where g.ds_genero like '%a%'
  and f.nm_filme like '%a%';  
  
  
select f.nm_filme
    from tb_filme f
inner join tb_genero g
    on f.id_genero = g.id_genero
  where f.vl_avaliacao >= 8.0;  
  
    
    
select f.nm_filme
    from tb_filme f
inner join tb_genero g
    on f.id_genero = g.id_genero
    where dt_lancamento between ('2010-1-1') and ('2020-12-31')
    and character_length(nm_filme) < 10;
    
    
select f.nm_filme
    from tb_filme f
inner join tb_genero g
    on f.id_genero = g.id_genero
  where g.ds_genero not like '%a';
  
  
   select f.nm_filme
    from tb_filme f
inner join tb_genero g
    on f.id_genero = g.id_genero
    where day(dt_lancamento) between 03 and 15;  
  
  
   select f.nm_filme
    from tb_filme f
inner join tb_genero g
    on f.id_genero = g.id_genero
  where year(f.dt_lancamento) in (2018);
  
  
   select f.nm_filme,
   ceiling(vl_avaliacao),
   floor(vl_avaliacao)
    from tb_filme f
inner join tb_genero g
    on f.id_genero = g.id_genero
 order
 by vl_avaliacao;  
  
  
   select f.nm_filme
    from tb_filme f
inner join tb_genero g
    on f.id_genero = g.id_genero
  where g.ds_genero not like '%a%'
  and f.nm_filme like '%a%';

## Modelagem 2: Sistema de gerencia de informações alunos de uma escola

>Create Script

create table tb_turma(
  id_turma        int primary key auto_increment,
  nm_turma        varchar(1),
  nr_ano_letivo   int,
  nm_curso        varchar(100)
  );
  
create table tb_aluno(
  id_aluno       int primary key auto_increment,
  id_turma       int,
  ds_sexo        varchar(100),
  nm_aluno       varchar(100),
  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('A', 2019, 'Informática');
   
   insert into tb_turma(nm_turma, nr_ano_letivo, nm_curso)
   values('B', 2021, 'Informática');
   
   insert into tb_turma(nm_turma, nr_ano_letivo, nm_curso)
   values('E', 2020, 'Informática');
   
   insert into tb_turma(nm_turma, nr_ano_letivo, nm_curso)
   values('D', 2018, 'Informática');
   
   
   
   insert into tb_aluno(id_turma, ds_sexo, nm_aluno, nr_chamada, tp_status)
   values( 3, 'Feminino', 'Ana Carolina Souza da Cruz', 01, 'F');
   
    insert into tb_aluno(id_turma, ds_sexo, nm_aluno, nr_chamada, tp_status)
   values( 4, 'Feminino', 'Laisa Yukare de Lima Tokuno', 27, 'F');
   
    insert into tb_aluno(id_turma, ds_sexo, nm_aluno, nr_chamada, tp_status)
   values( 3, 'Masculino', 'Luccas Nunes ', 30, 'D');
   
    insert into tb_aluno(id_turma, ds_sexo, nm_aluno, nr_chamada, tp_status)
   values( 2, 'Feminino', 'Melissa Teixeira Soares', 32, 'F');
   
    insert into tb_aluno(id_turma, ds_sexo, nm_aluno, nr_chamada, tp_status)
   values( 1, 'Masculino', 'Gabriel Lima Rodrigues', 14, 'F');
   
    insert into tb_aluno(id_turma, ds_sexo, nm_aluno, nr_chamada, tp_status)
   values( 4, 'Masculino', 'Ryan Rodrigues Oliveira', 45, 'F');
   
    insert into tb_aluno(id_turma, ds_sexo, nm_aluno, nr_chamada, tp_status)
   values( 3, 'Masculino', 'Kauã Lucas Santos', 25, 'D');
   
    insert into tb_aluno(id_turma, ds_sexo, nm_aluno, nr_chamada, tp_status)
   values( 1, 'Feminino', 'Marina Pires Carneiro', 30, 'F');
   
    insert into tb_aluno(id_turma, ds_sexo, nm_aluno, nr_chamada, tp_status)
   values( 2, 'Feminino', 'Beatriz de Lourdes dos Santos', 04, 'F');
   
    insert into tb_aluno(id_turma, ds_sexo, nm_aluno, nr_chamada, tp_status)
   values( 4, 'Masculino', 'Jhean Medeiros Lopes', 23, 'D');

>Update Script

    UPDATE tb_turma
  SET nm_turma ='C'
  WHERE id_turma = 3;
  
  UPDATE tb_aluno
  SET nm_aluno ='Beatriz de Lourdes dos Santos da Silveira',
      nr_chamada = 05
  WHERE id_aluno = 9;

>Delete Script

  --DELETE FROM tb_aluno
  --WHERE id_aluno = 4;
  
  --DELETE FROM tb_aluno
  --WHERE id_aluno = 8;

>Select Script

select t.nm_turma,
       t.nr_ano_letivo,
       t.nm_curso,
       a.ds_sexo,
       a.nm_aluno,
       a.nr_chamada,
       a.tp_status
    from tb_aluno a
inner join tb_turma t
    on a.id_turma = t.id_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
    order
    by t.nm_turma;
    
    
    
  select * from tb_aluno a
inner join tb_turma t
    on a.id_turma = t.id_turma
    where t.nm_turma = 'C'
    order
    by a.nm_aluno;
    
    
    
    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 >= 2020
    order
    by t.nm_turma;
    
    
    
    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.nm_turma between 'B' and 'D'
    and a.ds_sexo = 'masculino'
    order by  t.nr_ano_letivo;
    
    
    
        select a.nr_chamada
    from tb_aluno a
inner join tb_turma t
    on a.id_turma = t.id_turma
    where  a.nm_aluno not like '%u%';
    
    
      
     select a.nm_aluno
    from tb_aluno a
inner join tb_turma t
    on a.id_turma = t.id_turma
    where a.nr_chamada >= 30;
    
    
    
      
  select a.nm_aluno,
        length(a.nm_aluno)
    from tb_aluno a
inner join tb_turma t
    on a.id_turma = t.id_turma;
    
    
    
       select a.nm_aluno
    from tb_aluno a
inner join tb_turma t
    on a.id_turma = t.id_turma
    where  a.nm_aluno like '%a%'
    and  a.tp_status = 'D';  
    
    
    
       
       select a.nm_aluno
    from tb_aluno a
inner join tb_turma t
    on a.id_turma = t.id_turma
    where  a.nm_aluno not like '%i%'
    or   a.ds_sexo = 'Masculino';