# Session Two
autor: Felipe Oliveira Verissimo
turma: Info C
número: 16

[toc]

## Tabelas Filme e Genero

>Create Script

CREATE TABLE TB_GENERO (
ID_GENERO   INT PRIMARY KEY AUTO_INCREMENT,
NM_GENERO   VARCHAR(255),
BT_ATIVO    BOOL
);

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_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, BT_ATIVO)
VALUES ("COMEDIA", TRUE),
       ("AÇAO", TRUE),
       ("AVENTURA", TRUE),
       ("TERROR", TRUE),
       ("FICÇÃO CIENTIFICA", TRUE),
       ("ROMANCE", TRUE);
       
INSERT INTO TB_FILME (NM_FILME, DT_LANCAMENTO, VL_AVALIACAO, BT_DISPONIVEL)
VALUES ("VELOSES E FURIOSOS 8", "2017-4-13", 7.8, TRUE),
       ("VELOSES E FURIOSOS 5", "2011-4-15", 6.7, TRUE),
       ("JURISSIC WORD", "2015-6-11", 8.8, TRUE),
       ("JURASSIC PARK", "1993-6-25", 7, TRUE),
       ("ANNABELLE", "2014-10-9", 8, TRUE),
       ("A CINCO PASSOS DE VOCÊ", "2019-3-21", 9, TRUE),
       ("AMOR AO ACASO", "2016-4-9", 5.9, TRUE);
          
       

INSERT INTO TB_FILME_GENERO (ID_FILME, ID_GENERO)
VALUES (1, 2),
       (1, 3),
       (2, 3),
       (3, 2),
       (3, 3),
       (4, 5),
       (4, 3),
       (5, 4),
       (6, 6),
       (7, 1);

### Delete e update

 UPDATE TB_FILME 
SET DT_LANCAMENTO = "2011-12-11"
WHERE ID_FILME = 3;
UPDATE TB_GENERO 
SET NM_GENERO = "TERROR"
WHERE ID_GENERO = 4;
UPDATE TB_FILME_GENERO 
SET ID_FILME = 7
WHERE ID_GENERO = 6;


DELETE FROM TB_FILME 
WHERE ID_FILME = 1;
DELETE FROM TB_GENERO 
WHERE NM_GENERO = 1;
DELETE FROM TB_FILME_GENERO 
WHERE ID_FILME = 1

### DML

>Select Script

--1.Selecionar filme, 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;

--2.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;

--3.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;

--4.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.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 G.NM_GENERO = "ROMANCE" 
        OR G.NM_GENERO = "COMEDIA";

### Consultas personalizadas

--1.Seleciona o filme do genero ação que tenha a avaliação maior ou igual que cinco.
    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.NM_GENERO = "AÇAO" 
       AND F.VL_AVALIACAO >= 5;

--2.Seleciona o filme do genero aventura que tenha a avaliação maior ou igual que cinco.
    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.NM_GENERO = "AVENTURA" 
       AND F.VL_AVALIACAO >= 5;

--3.Seleciona o filme do genero ação cujo a data de lançamento seja maior ou igual que 2015.
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.NM_GENERO = "AÇAO" 
       AND F.DT_LANCAMENTO >= 2015-1-1;

--4.Seleciona o filme do genero aventura cujo a data de lançamento seja maior ou igual que 2018
    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.NM_GENERO = "AVENTURA" 
       AND F.DT_LANCAMENTO >= 2018-1-1;.

--5.Seleciona o filme do genero aventura cujo a avaliação seja maior ou igual que 7.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 G.NM_GENERO = "AVENTURA" 
       AND F.VL_AVALIACAO >= 7.0;

## Modelagem Alunos e Disciplina

>Create Script

CREATE TABLE TB_ALUNOS (
  ID_ALUNO      INT PRIMARY KEY AUTO_INCREMENT,
  NM_TURMA		VARCHAR(100),
  NR_ANO_LETIVO	INT,
  NM_ALUNO      VARCHAR(100),
  NR_CHAMADA    INT,
  DS_SEXO       VARCHAR(100),
  TP_STATUS     VARCHAR(100)
 );

CREATE TABLE TB_DISCIPLINA (
  ID_DISCIPLINA INT PRIMARY KEY AUTO_INCREMENT,
  NM_DISCIPLINA VARCHAR(100),
  NM_PROFESSOR  VARCHAR(100)
 );
 
CREATE TABLE TB_BOLETIM (
  ID_BOLETIM INT PRIMARY KEY AUTO_INCREMENT,
  ID_ALUNO INT,
  ID_DISCIPLINA INT,
  VL_NOTA DECIMAL(15,2),
  QTD_FALTAS INT,
  FOREIGN KEY (ID_ALUNO) REFERENCES TB_ALUNOS (ID_ALUNO) ON DELETE CASCADE,
  FOREIGN KEY (ID_DISCIPLINA) REFERENCES TB_DISCIPLINA (ID_DISCIPLINA) ON DELETE CASCADE
 );

>Insert Script

INSERT INTO TB_ALUNOS (NM_TURMA, NR_ANO_LETIVO, NM_ALUNO, NR_CHAMADA, DS_SEXO, TP_STATUS)
VALUES ("INFO C", 2021, "FELIPE OLIVEIRA VERISSIMO", 16, "MASCULINO", "PRESENTE"),
       ("INFO C", 2021, "BEATRIZ GOMES FELICIANO", 7, "FEMININO", "PRESENTE"),
       ("INFO C", 2021, "GUILHERME OLIVEIRA VERISSIMO", 21, "MASCULINO",       "PRESENTE");
       
      
INSERT INTO TB_DISCIPLINA (NM_DISCIPLINA, NM_PROFESSOR)
VALUES ("LINGUAGEM DE PROGRAMAÇÃO", "BRUNO OLIVEIRA"),
       ("PREPARAÇÃO PARA O MERCADO DE TRABALHO", "CLAYTON"),
       ("MATEMÁTICA", "CARLOS"),
       ("INGLÊS INSTRUMENTAL", "CLAYTON"),
       ("OPERAÇÃO E SUPORTE A APLICATIVOS", "ROBERTO CARLOS");
       
INSERT INTO TB_BOLETIM (ID_ALUNO, ID_DISCIPLINA, VL_NOTA, QTD_FALTAS)
VALUES (1, 1, 8.0, 0),
       (1, 2, 7.0, 0),
       (1, 3, 7.5, 0),
       (1, 4 , 9.0, 0),
       (1, 5, 8.0, 0),
       (2, 1, 9.0, 0),
       (2, 2, 8.0, 0),
       (2, 3, 7.0, 0),
       (2, 4, 8.0, 0),
       (2, 5, 8.0, 0),
       (3, 1, 7.0, 0),
       (3, 2, 4.0, 0),
       (3, 3, 9.0, 0),
       (3, 4, 8.0, 0),
       (3, 5, 10.0, 0);

### Update/Delete Script

UPDATE TB_ALUNOS
SET NR_CHAMADA = 33
WHERE ID_ALUNO = 3;
UPDATE TB_DISCIPLINA
SET NM_DISCIPLINA = "LINGUAGEM DE PROGRAMAÇÃO"
WHERE ID_DISCIPLINA = 5;
UPDATE TB_BOLETIM
SET VL_NOTA = 10
WHERE ID_BOLETIM = 3;
UPDATE TB_BOLETIM
SET VL_NOTA = 10
WHERE ID_BOLETIM = 1;

DELETE FROM TB_ALUNOS
WHERE ID_ALUNO = 3;
DELETE FROM TB_DISCIPLINA
WHERE ID_DISCIPLINA = 4;
DELETE FROM TB_BOLETIM
WHERE ID_BOLETIM = 1;

### DML

>Select Script

--1. 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,
           AB.NM_DISCIPLINA,
           B.VL_NOTA
      FROM TB_BOLETIM        B
INNER JOIN TB_ALUNOS         A
        ON A.ID_ALUNO      = B.ID_ALUNO
INNER JOIN TB_DISCIPLINA AB
        ON AB.ID_DISCIPLINA = B.ID_DISCIPLINA
  ORDER BY A.NM_ALUNO;

--2. 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,
           AB.NM_DISCIPLINA,
           B.VL_NOTA
      FROM TB_BOLETIM        B
INNER JOIN TB_ALUNOS         A
        ON A.ID_ALUNO      = B.ID_ALUNO
INNER JOIN TB_DISCIPLINA AB
        ON AB.ID_DISCIPLINA = B.ID_DISCIPLINA
     WHERE A.NM_TURMA = "INFO C"
       AND A.NR_ANO_LETIVO = 2021
  ORDER BY B.VL_NOTA DESC;

--3. 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,
           AB.NM_DISCIPLINA,
           B.VL_NOTA
      FROM TB_BOLETIM        B
INNER JOIN TB_ALUNOS         A
        ON A.ID_ALUNO      = B.ID_ALUNO
INNER JOIN TB_DISCIPLINA AB
        ON AB.ID_DISCIPLINA = B.ID_DISCIPLINA
     WHERE B.VL_NOTA >= 5
       AND A.NR_ANO_LETIVO = 2021
       AND A.NM_TURMA = "INFO C"
  ORDER BY A.NR_ANO_LETIVO, A.NM_TURMA, A.NR_CHAMADA;

--4. Selecionar aluno, turma, ano aplicando o relacionamento nas tabelas, filtrando os alunos 
SELECT   A.NM_ALUNO,
            A.NM_TURMA,
            A.NR_ANO_LETIVO
       FROM TB_BOLETIM        B
 INNER JOIN TB_ALUNOS         A
         ON A.ID_ALUNO      = B.ID_ALUNO
 INNER JOIN TB_DISCIPLINA AB
         ON AB.ID_DISCIPLINA = B.ID_DISCIPLINA
      WHERE B.VL_NOTA < 5
        AND A.NR_ANO_LETIVO = 2021
        AND A.NM_TURMA = "INFO C";

### Consultas personalizadas

--1.Selecionar os alunos que tiraram notas acima de sete.
   SELECT   A.NM_ALUNO
       FROM TB_BOLETIM        B
 INNER JOIN TB_ALUNOS         A
         ON A.ID_ALUNO      = B.ID_ALUNO
 INNER JOIN TB_DISCIPLINA AB
         ON AB.ID_DISCIPLINA = B.ID_DISCIPLINA
      WHERE B.VL_NOTA > 7;

--2.Selecionar os alunos que tiraram notas abaixo de sete.
  SELECT   A.NM_ALUNO
       FROM TB_BOLETIM        B
 INNER JOIN TB_ALUNOS         A
         ON A.ID_ALUNO      = B.ID_ALUNO
 INNER JOIN TB_DISCIPLINA AB
         ON AB.ID_DISCIPLINA = B.ID_DISCIPLINA
      WHERE B.VL_NOTA < 7;

--3.Selecionar os alunos que tem o número da chamada entre dez e vinte e cinco.
  SELECT   A.NM_ALUNO
       FROM TB_BOLETIM        B
 INNER JOIN TB_ALUNOS         A
         ON A.ID_ALUNO      = B.ID_ALUNO
 INNER JOIN TB_DISCIPLINA AB
         ON AB.ID_DISCIPLINA = B.ID_DISCIPLINA
      WHERE A.NR_CHAMADA BETWEEN 10 AND 25; 

--4.Selecioanar os alunos que o nome contem a letra e.
   SELECT   A.NM_ALUNO
       FROM TB_BOLETIM        B
 INNER JOIN TB_ALUNOS         A
         ON A.ID_ALUNO      = B.ID_ALUNO
 INNER JOIN TB_DISCIPLINA AB
         ON AB.ID_DISCIPLINA = B.ID_DISCIPLINA
      WHERE A.NM_ALUNO LIKE "%E%";

--5.Selecioanar os alunos que o nome contem a letra t.
   SELECT   A.NM_ALUNO
       FROM TB_BOLETIM        B
 INNER JOIN TB_ALUNOS         A
         ON A.ID_ALUNO      = B.ID_ALUNO
 INNER JOIN TB_DISCIPLINA AB
         ON AB.ID_DISCIPLINA = B.ID_DISCIPLINA
      WHERE A.NM_ALUNO BETWEEN "%T%";