# Recomeçar | SQL
**Autor:** Matheus Rafael Morato Rocha
**Turma:** InfoC
**Número:** 40

[TOC]

## Modelagem 1: Netflix
> Create script

create table tb_genero (
  id_genero		 int primary key auto_increment,
  ds_genero		 varchar(200),
  bt_ativo 		 bool
 );
 
 create table tb_filme (
   id_filme 		int primary key auto_increment,
   nm_filme 		varchar(200),
   id_genero 		int,
   vl_avaliacao 	decimal(15,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),
	   ("drama",true),
       ("romance",true),
       ("guerra",true),
       ("terror",true);
       
insert into tb_filme (nm_filme,id_genero,vl_avaliacao,bt_disponivel,dt_lancamento)
values ("Velozes e Furiosos",1,4.5,true,'2010-12-10'),
	   ("Até o Último Homem",2,5.0,true,'2016-01-26'),
       ("John Wick: Um Novo Dia Para Matar",1,4.8,true,'2017-08-20'),
       ("O Diabo de Cada Dia",2,3.9,true,'2020-09-11'),
       ("Por Lugares Incríveis",4,3.4,true,'2020-01-28'),
       ("Adoráveis Mulheres",3,3.4,true,'2019-01-09'),
       ("Uma Sombra na Nuvem",4,5.0,false,'2020-12-31'),
       ("Midway - Batalha em Alto Mar",4,4.5,false,'2019-11-21'),
       ("Bad Boys para Sempre",1,5.0,true,'2020-01-30'),
       ("Doutor Sono",5,3.9,true,'2019,-11-7');


SET vl_avaliacao = 5
WHERE id_filme = 4;

UPDATE tb_filme
SET bt_disponivel = false
where id_filme = 5;

DELETE FROM tb_filme
where id_filme = 4;

DELETE FROM tb_filme
where id_filme = 7;

> Select script

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

    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
     WHERE G.ds_genero = 'ação';

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

    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
     WHERE G.ds_genero like 'a%'
     OR F.nm_filme like 'a%';

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

    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
     WHERE 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 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
     WHERE F.dt_lancamento between '2010-01-01' and '2020-01-01'
   		OR LENGTH (nm_filme) = 10;
    

-- Consulta personalizada 1

    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
     WHERE G.ds_genero in ('Guerra','drama','terror')
  ORDER BY vl_avaliacao asc;
     
-- Consulta personalizada 2

    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
     WHERE F.vl_avaliacao <> 5
     ORDER BY vl_avaliacao asc;

-- Consulta personalizada 3

    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
     WHERE F.bt_disponivel = true
  ORDER BY G.ds_genero,
           F.nm_filme;
     
-- Consulta personalizada 4

    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
     WHERE F.nm_filme LIKE 'B%'
     	OR F.nm_filme LIKE 'D%'
        OR F.nm_filme LIKE 'A%'
       AND F.bt_disponivel 	= true
       AND G.bt_ativo = true
  ORDER BY G.ds_genero,
           F.nm_filme;

        
-- Consulta personalizada 5
        
 
    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
     WHERE F.dt_lancamento BETWEEN '2015-01-01' and '2021-01-01'
  ORDER BY F.dt_lancamento DESC;

**DB Designer**

>[color=darkblue]

## Modelagem 2: Aluno

> Create script

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

> Insert script

insert into tb_turma (nm_turma,nr_ano_letivo,nm_curso)
values ("A",1,"Inglês"),
       ("B",1,"Espanhol"),
       ("C",3,"Informatica"),
       ("D",2,"Administração"),
       ("E",1,"Secretariado");

insert into tb_aluno (id_turma,ds_sexo,nm_aluno,nr_chamada,tp_status)
values (1,"Masculino","Richard Santos da Silva",35,"F"),
	   (5,"Feminino","Maria Eduarda Vargas",25,"F"),
       (4,"Masculino","Carlos Cabral da Silva",8,"D"),
       (2,"Feminino","Letica Helena Rocha",20,"F"),
       (4,"Masculino","Roger Carlos de Souza",38,"D"),
       (3,"Feminino","Laysa Rodriges Freias",19,"F"),
       (1,"Masculino","Vinicius de Morais",40,"F"),
       (2,"Masculino","Pedro Carlos Senna",38,"D"),
       (3,"Feminino","Helana Luiza Moreira",15,"F"),
       (5,"Feminino","Luiza de Franco Neves",12,"F");

UPDATE tb_aluno
SET tp_status = "D"
WHERE id_aluno = 9;

UPDATE tb_aluno
SET id_turma = 2
WHERE id_aluno = 10;  

DELETE FROM tb_aluno
where id_aluno = 8;

DELETE FROM tb_aluno
where id_aluno = 3;

> Select script

        
-- Selecionar turma, nome e chamada da tabela alunos filtrando por turma.     
        
    SELECT  G.nm_turma,
    		F.nm_aluno,
            F.nr_chamada       
      FROM tb_turma    G
INNER JOIN tb_aluno    F
        ON G.id_turma = F.id_turma
        WHERE G.nm_turma = "Inglês";
        
-- Selecionar todos campos da tabela alunos filtrando por nome e turma de forma parcial .

    SELECT  G.nm_turma,
   	    	G.nr_ano_letivo,
            G.nm_curso,
            F.ds_sexo,
            F.nm_aluno,
            F.nr_chamada,
            F.tp_status       
      FROM tb_turma    G
INNER JOIN tb_aluno    F
        ON G.id_turma = F.id_turma
       WHERE G.nm_turma Like '%A%'
       AND F.nm_aluno LIKE '%H%';
       
        
-- Selecione ano letivo, turma, nome, chamada, sexo da tabela alunos filtrando os  frequentes por turma e ano letivo.


    SELECT	G.nr_ano_letivo,
    		G.nm_turma,
            F.nm_aluno,
            F.nr_chamada,
            F.ds_sexo       
      FROM tb_turma    G
INNER JOIN tb_aluno    F
        ON G.id_turma = F.id_turma
     WHERE F.tp_status = "F"
        AND G.nr_ano_letivo = 1;
     
-- Selecione nome, sexo da tabela alunos filtrando por ano, turma e sexo

    SELECT F.nm_aluno,
    	    F.ds_sexo                  
      FROM tb_turma    G
INNER JOIN tb_aluno    F
        ON G.id_turma = F.id_turma
    WHERE G.nr_ano_letivo = 1
    AND G.nm_turma like '%A%'
    AND F.ds_sexo = "Masculino";
  
-- Consulta Personalizada 1

    SELECT  G.nm_curso,
            F.ds_sexo,
            F.nm_aluno,
            F.nr_chamada       
      FROM tb_turma    G
INNER JOIN tb_aluno    F
        ON G.id_turma = F.id_turma
     WHERE G.nm_curso = "Informatica";

-- Consulta Personalizada 2

    SELECT  G.nm_turma,
   	    	G.nr_ano_letivo,
            G.nm_curso,
            F.nm_aluno
      FROM tb_turma    G
INNER JOIN tb_aluno    F
        ON G.id_turma = F.id_turma
     WHERE F.nr_chamada BETWEEN 20 AND 50;

-- Consulta Personalizada 3

    SELECT  G.nm_turma,
            G.nm_curso,
            F.nm_aluno,
            F.nr_chamada,
            F.tp_status
      FROM tb_turma    G
INNER JOIN tb_aluno    F
        ON G.id_turma = F.id_turma
     WHERE nm_curso IN ('Inglês','Espanhol','Secretariado');
     
-- Consulta Personalizada 4

    SELECT  G.nm_turma,
            G.nm_curso,
            F.nm_aluno,
            F.nr_chamada
      FROM tb_turma    G
INNER JOIN tb_aluno    F
        ON G.id_turma = F.id_turma
     WHERE nm_aluno  LIKE 'L%'
        OR nm_aluno  LIKE 'R%';
	  
-- Consulta Personalizada 5

    SELECT  G.nm_turma,
            G.nm_curso,
            F.nm_aluno,
            F.ds_sexo,
            F.nr_chamada            
      FROM tb_turma    G
INNER JOIN tb_aluno    F
        ON G.id_turma = F.id_turma
     WHERE nr_chamada <= 30;

**DB Designer**

>[color=darkblue]