# Banco de Dados | 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),
   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),
  foreign key (id_filme) references tb_filme  (id_filme)
);

       
       
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,vl_avaliacao,bt_disponivel)
values ("Velozes e Furiosos",6,true),
	   ("Até o Último Homem",8,true),
       ("John Wick: Um Novo Dia Para Matar",10,true),
       ("O Diabo de Cada Dia",4,true),
       ("Por Lugares Incríveis",9,true);
       
insert into tb_filme_genero (id_filme,id_genero)
values (1,1),
	   (2,4),
	   (3,1),
	   (4,2),
	   (5,3),
       (5,5),
       (1,5),
       (4,2),
       (3,5),
       (5,1);
       
       
UPDATE tb_filme_genero
SET id_genero = 4
WHERE id_filme = 5;       

DELETE FROM tb_filme_genero
WHERE id_filme = 4;

> 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,vl_avaliacao,bt_disponivel)
values ("Velozes e Furiosos",6,true),
	   ("Até o Último Homem",8,true),
       ("John Wick: Um Novo Dia Para Matar",10,true),
       ("O Diabo de Cada Dia",4,true),
       ("Por Lugares Incríveis",9,true);
       
insert into tb_filme_genero (id_filme,id_genero)
values (1,1),
	   (2,4),
	   (3,1),
	   (4,2),
	   (5,3),
       (5,5),
       (1,5),
       (4,2),
       (3,5),
       (5,1);
       
       
UPDATE tb_filme_genero
SET id_genero = 4
WHERE id_filme = 5;

UPDATE tb_genero
SET ds_genero = "Rock"
WHERE id_genero = 2;

UPDATE tb_filme 
SET nm_filme = "WHATSAPP 2"
WHERE id_filme = 2;


DELETE FROM tb_filme_genero
WHERE id_filme = 9;

DELETE FROM tb_genero 
WHERE id_genero = 5;

DELETE FROM tb_filme
WHERE id_filme = 5;

> Select script

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

SELECT     G.ds_genero,
           F.nm_filme
      FROM  tb_filme_genero as  FG
INNER JOIN	tb_filme 		as  F
 	  JOIN	tb_genero		as  G 
		ON FG.id_genero = G.id_genero 
       AND FG.id_filme = F.id_filme
  ORDER BY  nm_filme;
    
-- 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     FG.id_filme,
		   F.nm_filme,
           FG.id_genero,
           G.ds_genero
      FROM  tb_filme_genero as  FG
INNER JOIN	tb_filme 		as  F
 	  JOIN	tb_genero		as  G 
		ON FG.id_genero = G.id_genero
       AND FG.id_filme = F.id_filme
     WHERE F.vl_avaliacao = 4
  ORDER BY nm_filme;

-- 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 as  FG
INNER JOIN	tb_filme 		as  F
 	  JOIN	tb_genero		as  G 
		ON FG.id_genero = G.id_genero
       AND FG.id_filme = F.id_filme
     WHERE G.ds_genero = 'ação'
       AND F.bt_disponivel = true
  ORDER BY F.vl_avaliacao;
       
-- 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,
           G.bt_ativo,
           G.ds_genero
      FROM  tb_filme_genero as  FG
INNER JOIN	tb_filme 		as  F
 	  JOIN	tb_genero		as  G 
		ON FG.id_genero = G.id_genero
       AND FG.id_filme = F.id_filme
     WHERE G.ds_genero in ('romance','comedia');
        
-- Consulta personalizada 1

SELECT     G.ds_genero,
           F.nm_filme,
           F.vl_avaliacao,
           F.bt_disponivel
      FROM  tb_filme_genero as  FG
INNER JOIN	tb_filme 		as  F
 	  JOIN	tb_genero		as  G 
		ON FG.id_genero = G.id_genero
       AND FG.id_filme = F.id_filme
     WHERE G.ds_genero in ('guerra','drama','terror')
  ORDER BY vl_avaliacao;    
  
  -- Consulta personalizada 2

SELECT     G.ds_genero,
           F.nm_filme,
           F.vl_avaliacao,
           F.bt_disponivel
      FROM  tb_filme_genero as  FG
INNER JOIN	tb_filme 		as  F
 	  JOIN	tb_genero		as  G 
		ON FG.id_genero = G.id_genero
       AND FG.id_filme = F.id_filme
     WHERE F.vl_avaliacao BETWEEN 5 AND 8
  ORDER BY F.nm_filme;  
  
   -- Consulta personalizada 3
   
 SELECT    G.ds_genero,  
           F.nm_filme,
           F.bt_disponivel
      FROM  tb_filme_genero as  FG
INNER JOIN	tb_filme 		as  F
 	  JOIN	tb_genero		as  G 
		ON FG.id_genero = G.id_genero
       AND FG.id_filme = F.id_filme
     WHERE F.vl_avaliacao BETWEEN 5 AND 8
  ORDER BY F.nm_filme;   
  
    -- Consulta personalizada 4
    
     SELECT G.ds_genero,  
           F.nm_filme,
           F.bt_disponivel
      FROM  tb_filme_genero as  FG
INNER JOIN	tb_filme 		as  F
 	  JOIN	tb_genero		as  G 
		ON FG.id_genero = G.id_genero
       AND FG.id_filme = F.id_filme
     WHERE F.vl_avaliacao BETWEEN 5 AND 8
       AND LENGTH (F.nm_filme) <= 9
       AND F.bt_disponivel = true
        OR F.dt_lancamento between '2010-01-01' and '2020-01-01'
 ORDER BY F.nm_filme;   

  
    -- Consulta personalizada 5

     SELECT G.ds_genero,  
           F.nm_filme,
           F.bt_disponivel
      FROM  tb_filme_genero as  FG
INNER JOIN	tb_filme 		as  F
 	  JOIN	tb_genero		as  G 
		ON FG.id_genero = G.id_genero
       AND FG.id_filme = F.id_filme
     WHERE LENGTH (F.nm_filme) <= 9
       AND F.bt_disponivel = true
 ORDER BY F.nm_filme ASC;  

## Modelagem 2: Aluno

> Create script

create table tb_aluno (
  id_aluno 			int primary key auto_increment,
  nm_turma			varchar(200),
  nr_ano_letivo 	int,
  nm_aluno			varchar(200),
  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(200),
  nm_professor 		varchar(200)
);
  
create table tb_boletim (
  id_boletim		int primary key auto_increment,
  id_aluno 			int,
  id_disciplina 	int,
  vl_nota			int,
  qtd_faltas 		int,
  foreign key (id_aluno ) references tb_aluno (id_aluno),
  foreign key (id_disciplina) references tb_disciplina  (id_disciplina)
);      

> Insert script

insert into tb_aluno (nm_turma,nr_ano_letivo,nm_aluno,nr_chamada,ds_sexo,tp_status)
values ("2 ano",2,"Matheus Rafael",27,"Masculino","F"),
	   ("1 ano",1,"Eduardo Guilherme ",40,"Masculino","F"),	
       ("3 ano",3,"Jheniffer Fernandes",20,"Feminino","D");
       
insert into tb_disciplina (nm_disciplina,nm_professor)
values ("Física","José Silva"),
	   ("Matematica","Bruno de Oliveira"),
       ("Geografia","Ewerton Rocha"),
       ("Português","Elisabete Rosa"),
       ("Inglês","Leticia de Santos");
       
insert into tb_boletim (id_aluno,id_disciplina,vl_nota,qtd_faltas)
values (1,1,4,2),(1,2,5,3),(1,3,6,5),(1,4,8,2),(1,5,2,10);
       
insert into tb_boletim (id_aluno,id_disciplina,vl_nota,qtd_faltas)
values (2,1,4,10),(2,2,6,2),(2,3,10,8),(2,4,4,2)(2,5,2,0);
       
insert into tb_boletim (id_aluno,id_disciplina,vl_nota,qtd_faltas)
values (3,1,10,0),(3,2,6,2),(3,3,1,8),(3,4,4,2),(3,5,8,0);      
		       
		
UPDATE tb_aluno
SET nr_chamada = 10
WHERE id_aluno  = 2;        
  
UPDATE tb_disciplina
SET nm_professor = "Alex Rodrigues"
WHERE id_disciplina  = 2;  

UPDATE tb_boletim
SET vl_nota = 4
WHERE id_disciplina  = 2;  

DELETE FROM tb_aluno
WHERE id_aluno = 1;

DELETE FROM tb_disciplina
WHERE id_disciplina = 2;

DELETE FROM tb_boletim
WHERE vl_nota = 1;

> Select script

SELECT     A.nm_aluno,
           A.nm_turma,
           A.nr_chamada,
           A.ds_sexo,
           A.nm_aluno,
           D.nm_disciplina,
           D.nm_professor,
           B.vl_nota,
           B.qtd_faltas
      FROM  tb_boletim 		as  B
INNER JOIN	tb_aluno 		as  A
 	  JOIN	tb_disciplina	as  D
		ON B.id_disciplina = D.id_disciplina
       AND B.id_aluno = A.id_aluno;

-- 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 		as  B
INNER JOIN	tb_aluno 		as  A
 	  JOIN	tb_disciplina	as  D
		ON B.id_disciplina = D.id_disciplina
       AND B.id_aluno = A.id_aluno
  ORDER BY A.nm_aluno;
  
-- 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 		as  B
INNER JOIN	tb_aluno 		as  A
 	  JOIN	tb_disciplina	as  D
		ON B.id_disciplina = D.id_disciplina
       AND B.id_aluno = A.id_aluno
  WHERE A.nm_aluno = "2 ano"
  	 OR A.nr_ano_letivo = 2
  ORDER BY B.vl_nota DESC;
  
  -- 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 		as  B
INNER JOIN	tb_aluno 		as  A
 	  JOIN	tb_disciplina	as  D
		ON B.id_disciplina = D.id_disciplina
       AND B.id_aluno = A.id_aluno
       WHERE  B.vl_nota >= 5
       AND A.nr_ano_letivo = 3
       AND A.nm_turma = "3 ano"
       ORDER BY A.nm_turma,A.nr_chamada;
       
       
-- Consulta personalizada 1

  SELECT   A.nm_aluno,
           A.nm_turma,
           A.nr_ano_letivo,
           D.nm_disciplina,
           D.nm_professor,
           B.qtd_faltas, 
           B.vl_nota
      FROM  tb_boletim 		as  B
INNER JOIN	tb_aluno 		as  A
 	  JOIN	tb_disciplina	as  D
		ON B.id_disciplina = D.id_disciplina
       AND B.id_aluno = A.id_aluno
       WHERE A.nr_ano_letivo = 2
       AND A.nm_aluno LIKE 'M%'
       AND B.qtd_faltas <= 5
       ORDER BY B.qtd_faltas ASC;
       
-- Consulta personalizada 2

  SELECT   A.nm_aluno,
           A.nm_turma,
           D.nm_disciplina,
           B.vl_nota
      FROM  tb_boletim 		as  B
INNER JOIN	tb_aluno 		as  A
 	  JOIN	tb_disciplina	as  D
		ON B.id_disciplina = D.id_disciplina
       AND B.id_aluno = A.id_aluno
       WHERE A.nr_ano_letivo BETWEEN 1 AND 2
       AND LENGTH (D.nm_disciplina) >= 7
       AND B.vl_nota IN (5,10,2);
       
-- Consulta personalizada 3

SELECT     A.nm_aluno,
           A.nm_turma,
           A.nr_chamada,
           A.ds_sexo,
           A.nm_aluno
      FROM  tb_boletim 		as  B
INNER JOIN	tb_aluno 		as  A
 	  JOIN	tb_disciplina	as  D
		ON B.id_disciplina = D.id_disciplina
       AND B.id_aluno = A.id_aluno
       WHERE A.nr_chamada <= 27
       	 AND A.ds_sexo = "Masculino"
         OR A.nm_aluno = 'G%'
        AND B.qtd_faltas BETWEEN 5 AND 8
        ORDER BY A.nm_aluno ASC;
        
-- Consulta personalizada 4


SELECT     A.nr_chamada,
           D.nm_disciplina,
           D.nm_professor,
           B.vl_nota,
           B.qtd_faltas
      FROM  tb_boletim 		as  B
INNER JOIN	tb_aluno 		as  A
 	  JOIN	tb_disciplina	as  D
		ON B.id_disciplina = D.id_disciplina
       AND B.id_aluno = A.id_aluno
       WHERE B.qtd_faltas < 6
       AND B.vl_nota IN (5,8,7,10,2)
       AND A.tp_status = "F"
  ORDER BY A.nm_aluno DESC;
  
  -- Consulta personalizada 5
       
       
       SELECT     A.nr_chamada,
           D.nm_disciplina,
           D.nm_professor,
           B.vl_nota,
           B.qtd_faltas,
           A.tp_status
      FROM  tb_boletim 		as  B
INNER JOIN	tb_aluno 		as  A
 	  JOIN	tb_disciplina	as  D
		ON B.id_disciplina = D.id_disciplina
       AND B.id_aluno = A.id_aluno
       WHERE B.qtd_faltas> 6
       AND A.tp_status = "F"
  ORDER BY A.nm_aluno DESC;