# Session Two: Banco de Dados
**Nome:** Patricia Oliveira Paulino
**Número:** 37
**Turma:** INFO D
#

### Link

https://www.db-fiddle.com/f/o7K9WpByKAG2sMbVtpD2BM/0

### SQL Filme

CREATE TABLE tb_filme
(
  id_filme 		int primary key auto_increment,
  nm_filme 		varchar(255),
  dt_lancamento date,
  vl_avaliacao 	decimal(15,2),
  bt_disponivel bool
);

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

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)   on delete cascade,
  foreign key (id_genero) references tb_genero(id_genero) on delete cascade
);


INSERT INTO tb_filme(nm_filme,dt_lancamento,vl_avaliacao,bt_disponivel)
VALUES
	("A Culpa é das Estrelas","2014-06-05",7.9,true),
    ("De repente 30","2005-05-23",6.4,false),
    ('Escape Room',"2019-02-07",9.1,true),
    ("Monster Hunter","2020-12-03",3.1,true),
    ("Estranho Passageiro","2021-01-21",8.9,true);
    

INSERT INTO tb_genero(ds_genero,bt_ativo)
VALUES ("ação",true),
       ("aventura",true),
       ("comedia",true),
       ("terror",true),
       ("romance",true),
       ("anime",true);


INSERT INTO tb_filme_genero(id_filme,id_genero)
VALUES	(1,5),
        (5,4),
        (2,5),
        (3,4),
        (3,2),
        (3,1),
        (4,1),
        (4,2),
        (2,2),
        (5,3);

### Query Filme

-- Selecionar fime, gênero aplicando o relacionamento nas tabelas, sem filtros, ordenando por filme. 
	
    SELECT f.nm_filme, 
		   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
  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.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 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';




-- criar 5 consultas personalizadas 

	  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 g.ds_genero 	 = 'ficção'  
         AND f.dt_lancamento > '2015-01-01';

	  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;

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


	  SELECT f.nm_filme, 
             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 year(f.dt_lancamento) = 2005;

### link

https://www.db-fiddle.com/f/b99TMnY4wymcGr92v3t7r9/0

### SQL Alunos

CREATE TABLE tb_disciplina (

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





INSERT INTO tb_disciplina(nm_disciplina, nm_professor)
VALUES ('Informática', 'Bruno'),
       ('Inglês', 'Lucas'),
       ('Administração', 'Cosma'),
       ('Secretariado', 'Sônia'),
       ('Eletrotécnica', 'Carlos'),
       ('Comunicação Visual', 'Natan');



INSERT INTO tb_aluno (nm_turma, nr_ano_letivo, nm_aluno, nr_chamada, ds_sexo, tp_status)
VALUES
  ('INFO X', 2021, 'Igor Lima Charles', 18, 'Masculino', 'F'),
  ('INFO X', 2021, 'Júlia Carvalho', 30, 'Feminino', 'F'),   
  ('INGLÊS I', 2021, 'Michelle Fonseca', 35, 'Feminino', 'F'),
  ('SECRETARIADO B', 2021, 'Stephanie Ferreira', 45, 'Feminino', 'T'),
  ('ELETROTÉCNICA A', 2020, 'Arthur Guilherme', 1, 'Masculino', 'F'),
  ('CV C', 2020, 'Nayara Lino', 37, 'Feminino', 'T');



INSERT INTO tb_aluno_disciplina (id_aluno, id_disciplina, vl_nota)
VALUES (1, 1, 7.0),
       (2, 2, 3.5),
       (3, 3, 4.5),
       (4, 4, 5.5),
       (5, 5, 6.0),
       (6, 6, 9.0);

### Query 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,
           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
  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_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 A.nr_ano_letivo = 2020
       and A.nm_turma      = 'CV C'
  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_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;



-- 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
    
    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 length(A.nm_aluno) >= 5
  order by A.nm_turma;



    select A.nm_aluno,
           A.nm_turma,
           A.nr_ano_letivo,
           D.nm_disciplina
      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 A.nm_aluno like 'A%';