# Session Two: Getting Better | Banco de dados
**author** Murillo Tadeu
**Turma** Info - D
**Número**: 33

#
## Tabela Filme

> Create Scipt

create table tb_genero(
	id_genero 				int primary key auto_increment,
    nm_genero 				varchar(100),
    ds_restricao			bool,
    bt_ativo				bool
);

create table tb_filme(
  id_filme				int primary key auto_increment,
  nm_filme 				varchar(100) not null,
  dt_lancamento 		date,
  vl_avaliacao			decimal(15,2) default 5,
  bt_disponivel 		bool not null
);
  
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),
  foreign key (id_genero) references tb_genero (id_genero)
  ); 

>Insert Script

insert into tb_genero (nm_genero, ds_restricao, bt_ativo)
  values	('Ação', true, true),
            ('Romance', true, true),
            ('Comédia', false, true),
            ('Ficção', false, false);
            
  insert into tb_filme (nm_filme, dt_lancamento,  vl_avaliacao, bt_disponivel) 
     values ('Fast&Furious 5', '2011-04-15', 4.3, true),
     		('50 tons de cinza', '2015-02-12', 4.6, true),
            ('Minha mãe é uma peça ', '2013-06-18', 3.8, true),
            ('Vingadores: Ultimato', '2019-04-25', 5, true),
            ('Tropa de elite', '2017-10-05', 3.9, false),
            ('Por lugares incriveis', '2020-02-28', 3.6, true),
            ('Para todos garotos que já amei 2','2021-02-12', 3.6, true),
            ('Bird Box','2018-12-14', 3.7, true),
            ('Interestelar','2014-11-06',4.5, true),
            ('Paul: O Alien Fugitivo','2013-11-13' , 2.9, false);
            
INSERT INTO tb_filme_genero (id_filme, id_genero)
	 VALUES (1, 1),
            (2, 2),
            (3, 3),
            (4, 4),
            (5, 1),
            (6, 2),
            (7, 2),
            (8, 1),
            (9, 4),
            (10, 4);

>Insert Script

Modificação 
-- Removendo um Vínculo de Filme x Gênero

DELETE FROM tb_genero
	  WHERE id_genero = 2;
      
DELETE FROM tb_filme_genero
	  WHERE id_filme_genero = 5;
      

>Select Script

-- Selecionar fime, gênero aplicando o relacionamento nas tabelas, sem filtros, ordenando por filme. --
SELECT	   f.nm_filme, 
		   g.nm_genero
	  FROM tb_filme_genero   fg
INNER JOIN tb_filme          f
        ON f.id_filme        = fg.id_filme
INNER JOIN tb_genero         g
	    ON g.id_genero       = fg.id_genero
  ORDER BY f.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     f.id_filme, 
		   f.nm_filme,
		   g.id_genero,
		   g.nm_genero
	  FROM tb_filme_genero   fg
INNER JOIN tb_filme          f
		ON f.id_filme 		 = fg.id_filme
INNER JOIN tb_genero         g
		ON g.id_genero 		 = fg.id_genero
     WHERE f.vl_avaliacao 	 > 4
  ORDER BY f.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.dt_lancamento,
		   f.vl_avaliacao
	  FROM tb_filme_genero   fg
INNER JOIN tb_filme          f
        ON f.id_filme 		 = fg.id_filme
INNER JOIN tb_genero         g
        ON g.id_genero       = fg.id_genero
  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, 
		   f.bt_disponivel,
		   g.ds_genero
	  FROM tb_filme_genero   fg
INNER JOIN tb_filme          f
		ON f.id_filme 		 = fg.id_filme
INNER JOIN tb_genero         g
        ON g.id_genero       = fg.id_genero
	 WHERE g.ds_genero = 'romance' 
        OR g.ds_genero = 'drama';
-- 5 consultas personalizadas.
1 - 
      SELECT     f.id_filme, 
		   f.nm_filme,
		   g.id_genero,
		   g.nm_genero
	  FROM tb_filme_genero   fg
INNER JOIN tb_filme          f
		ON f.id_filme 		 = fg.id_filme
INNER JOIN tb_genero         g
		ON g.id_genero 		 = fg.id_genero
     WHERE f.dt_lancamento > '2016-01-01'
     
2 - 
      SELECT     f.id_filme, 
		   f.nm_filme,
		   g.id_genero,
		   g.nm_genero
	  FROM tb_filme_genero   fg
INNER JOIN tb_filme          f
		ON f.id_filme 		 = fg.id_filme
INNER JOIN tb_genero         g
		ON g.id_genero 		 = fg.id_genero
ORDER BY   f.vl_avaliacao DESC 

3 - 
      SELECT     f.id_filme, 
		   f.nm_filme,
		   g.id_genero,
		   g.nm_genero
	  FROM tb_filme_genero   fg
INNER JOIN tb_filme          f
		ON f.id_filme 		 = fg.id_filme
INNER JOIN tb_genero         g
		ON g.id_genero 		 = fg.id_genero
ORDER BY   f.vl_avaliacao ASC

4 - 
        SELECT f.nm_filme
		FROM tb_filme_genero 	fg
  INNER JOIN tb_filme	 		f
		  ON f.id_filme 		= fg.id_filme
  INNER JOIN tb_genero 			g
		  ON g.id_genero 		= fg.id_genero
	   WHERE f.vl_avaliacao BETWEEN 8.0 AND 9.0;
      
5 -      
        	  SELECT f.nm_filme
	    FROM tb_filme_genero 	fg
  INNER JOIN tb_filme	 		f
		  ON f.id_filme 		= fg.id_filme
  INNER JOIN tb_genero 			g
		  ON g.id_genero 		= fg.id_genero
	   WHERE length(f.nm_filme) >= 10;

#
## Tabela Alunos
> Create Scipt

CREATE TABLE tb_materia (

  id_materia	      int primary key auto_increment,
  nm_disciplina   varchar(30),
  nm_professor    varchar(30)
);

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_aluno_materia (
  id_turma_aluno  int primary key auto_increment,
  id_aluno        int,
  id_materia      int,
  vl_nota		  decimal,
  FOREIGN KEY (id_materia) REFERENCES tb_materia(id_materia),
  FOREIGN KEY (id_aluno) REFERENCES tb_aluno(id_aluno)
);

>Insert Script

INSERT INTO tb_materia(nm_disciplina, nm_professor)
VALUES      ('Informática', 'Bruno'),
	        ('Informática', 'Roberto'),
            ('Inglês', 'Clayton');

INSERT INTO tb_aluno(nm_turma, nr_ano_letivo, nm_aluno, nr_chamada, ds_sexo, tp_status)
VALUES 
  ('INFO D', 2021, 'Angélica Silva Santos', 3, 'Feminino', 'F'),
  ('INFO B', 2021, 'Júlia Soares', 30, 'Feminino', 'F'),   
  ('INGLÊS I', 2021, 'Bárbara Carvalho', 11, 'Feminino', 'F'),
  ('INFO A', 2021, 'Sophia Fernandes', 45, 'Feminino', 'T'),
  ('INFO C', 2020, 'Gabriel Oliveira', 9, 'Masculino', 'F'),
  ('INGLÊS II', 2020, 'Mariana Monteiro', 37, 'Feminino', 'T');
  
INSERT INTO tb_aluno_materia (id_aluno, id_materia, vl_nota)
VALUES (1, 2, 6.0),
       (2, 1, 2.5),
       (3, 3, 8.5),
       (4, 2, 6.5),
       (5, 1, 9.0),
       (6, 3, 10.0);

>Select Script

-- 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,
           AD.vl_nota
      from tb_aluno_materia  AD
inner join tb_aluno       		A
        on A.id_aluno 			= AD.id_aluno
inner join tb_materia   		D
        on D.id_materia		= AD.id_materia
  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,
           AD.vl_nota
      from tb_aluno_materia  AD
inner join tb_aluno       		A
        on A.id_aluno 			= AD.id_aluno
inner join tb_materia   		D
        on D.id_materia 		= AD.id_materia
     where A.nr_ano_letivo = 2020
       and A.nm_turma      = 'INGLÊS II'
  order by A.nm_turma, 
  		   A.nr_ano_letivo,
           AD.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,
           AD.vl_nota
      from tb_aluno_materia  AD
inner join tb_aluno       		A
        on A.id_aluno 			= AD.id_aluno
inner join tb_materia  		D
        on D.id_materia 		= AD.id_materia
     where AD.vl_nota 	>= 5.0
       and A.nr_ano_letivo = 2021
       and A.nm_turma      = 'INFO D'
  order by A.nm_turma, 
           A.nr_ano_letivo, 
           A.nr_chamada;
-- 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,
           D.nm_disciplina,
           AD.vl_nota
      from tb_aluno_disciplina  AD
inner join tb_aluno       		A
        on A.id_aluno			= AD.id_aluno
inner join tb_disciplina  		D
        on D.id_disciplina 		= AD.id_disciplina
     where AD.vl_nota 		< 5.0
       and A.nr_ano_letivo = 2021
       and A.nm_turma      = 'INFO X'
  order by A.nm_turma, 
           A.nr_ano_letivo, 
           A.nr_chamada;
          
-- consultas personalizadas
1 -     select A.nm_aluno,
           A.nm_turma,
           A.nr_ano_letivo,
           D.nm_disciplina
      from tb_aluno_materia  AD
inner join tb_aluno       		A
        on A.id_aluno 			= AD.id_aluno
inner join tb_materia  		D
        on D.id_materia 		= AD.id_materia
     where A.nm_aluno like 'G%'
     and   A.nm_aluno like 'S%';
   
2 -     select A.nm_aluno, 
           A.nm_turma, 
           A.nr_ano_letivo, 
           D.nm_disciplina, 
           AD.vl_nota
      from tb_aluno_materia  AD
inner join tb_aluno       		A
        on A.id_aluno 			= AD.id_aluno
inner join tb_materia  		D
        on D.id_materia		= AD.id_materia
     where length(A.nm_aluno) >= 3
  order by A.nm_turma;
  
3 -     select A.nm_aluno,
           A.nm_turma,
           A.nr_ano_letivo,
           D.nm_disciplina,
           AD.vl_nota
      from tb_aluno_materia  AD
inner join tb_aluno       		A
        on A.id_aluno 			= AD.id_aluno
inner join tb_materia  		D
        on D.id_materia 		= AD.id_materia
     where A.nr_ano_letivo = 2021
     
4 -     select A.nm_aluno,
           A.nm_turma,
           A.nr_ano_letivo,
           D.nm_disciplina,
           AD.vl_nota
      from tb_aluno_materia  AD
inner join tb_aluno       		A
        on A.id_aluno 			= AD.id_aluno
inner join tb_materia  		D
        on D.id_materia 		= AD.id_materia
     where AD.vl_nota > 4

5 -     select A.nm_aluno,
           A.nm_turma,
           A.nr_ano_letivo,
           D.nm_disciplina,
           AD.vl_nota
      from tb_aluno_materia  AD
inner join tb_aluno       		A
        on A.id_aluno 			= AD.id_aluno
inner join tb_materia  		D
        on D.id_materia 		= AD.id_materia
     where AD.vl_nota < 5