# Session Two
autor: Guilherme Oliveira Verissimo
turma: InfoC
número: 21

[toc]

## Modelagem Filme e Genero

>Create Script

CREATE TABLE TB_FILME (
  ID_FILME      INT PRIMARY KEY AUTO_INCREMENT,
  NM_FILME      VARCHAR(100),
  DT_LANCAMENTO DATE,
  VL_AVALIACAO  DECIMAL(15,2),
  BT_DISPONIVEL BOOLEAN
 );
 
CREATE TABLE TB_GENERO (
  ID_GENERO INT PRIMARY KEY AUTO_INCREMENT,
  DS_GENERO VARCHAR (100),
  BT_ATIVO  BOOLEAN
 );
 
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 Script

 INSERT INTO TB_FILME (NM_FILME, DT_LANCAMENTO, VL_AVALIACAO, BT_DISPONIVEL)
 VALUES ("Por Lugares Incríveis", "2020-02-28", 4.4, TRUE),
        ("Guardiões da Galáxia", "2004-07-31", 4.7, TRUE),
        ("Projeto Almanaque", "2015-02-26", 4.4, TRUE),
        ("Projeto X - Uma Festa Fora de Controle", "2012-03-16", 4.9, TRUE),
        ("John Wick 3: Parabellum", "2019-05-16", 4.4, TRUE),
        ("Divertida Mente", "2015-06-18", 4.6, TRUE);
        
       
 INSERT INTO TB_GENERO (DS_GENERO, BT_ATIVO)
 VALUES	("Romance", TRUE),
        ("Drama", TRUE),
        ("Ação", TRUE),
        ("Ficção Científica", TRUE),
        ("Thriller", TRUE),
        ("Comédia", TRUE),
        ("Infantil", TRUE),
        ("Suspense", TRUE);
        
 INSERT INTO TB_FILME_GENERO (ID_FILME, ID_GENERO)
 VALUES (1,1),
        (1,2),
        (2,3),
        (2,4),
        (3,4),
        (3,5),
        (4,6),
        (5,3),
        (5,8),
        (6,6),
        (6,7);

### Update/Delete Script

 UPDATE TB_FILME
 SET NM_FILME = "Guardiões da Galáxia 2"
 WHERE ID_FILME = 2;
 
 UPDATE TB_GENERO
 SET DS_GENERO = "Fantasia"
 WHERE ID_GENERO = 5;
 
 UPDATE TB_FILME_GENERO
 SET ID_GENERO = 5
 WHERE ID_FILME = 1;
 
 DELETE FROM TB_FILME
 WHERE ID_FILME = 3;
 
 DELETE FROM TB_GENERO
 WHERE ID_GENERO = 7;
 
 DELETE FROM TB_FILME_GENERO
 WHERE ID_FILME_GENERO = 2;

### DML

>Select Script

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

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

--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.ID_FILME, F.VL_AVALIACAO, F.DT_LANCAMENTO
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 = "Ação"
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.ID_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 = "Comédia";

### Consultas personalizadas

--1. Selecionar o filme cujo o genero possui a letra "I".
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 LIKE "%I%";

--2. Selecionar o filme cujo a data de lançamento esta entre 2015 e 2020.
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.DT_LANCAMENTO BETWEEN "2015-01-01" AND "2020-12-31";

--3. Selecionar os filmes e avaliacoes dos filmes que possuam avaliacao entre 4.5 e 5, e que se enquadram nos generos de ação ou drama.
SELECT F.NM_FILME, 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
WHERE F.VL_AVALIACAO BETWEEN 4.5 AND 5
AND G.DS_GENERO = "Ação" 
OR G.DS_GENERO = "Drama";

--4. Selecionar os filmes e datas de lancamento dos filmes que possuam as letras "A" e "P" no nome dos filmes e que foram lançados depois de 2017.
SELECT F.NM_FILME, F.DT_LANCAMENTO
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.NM_FILME LIKE "%G%" 
OR F.NM_FILME LIKE "%P%"
AND F.DT_LANCAMENTO >= "2017-01-01"
ORDER BY F.DT_LANCAMENTO;

--5. Selecionar os filmes de Suspense que possuam avaliação menor que 4.5 e que se encontram disponiveis.
SELECT F.NM_FILME, F.DT_LANCAMENTO
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 = "Suspense"
AND F.VL_AVALIACAO <= 4.5
AND F.BT_DISPONIVEL = TRUE;

## Modelagem Alunos e Disciplina

>Create Script

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

CREATE TABLE TB_DISCIPLINA (
  ID_DISCIPLINA INT PRIMARY KEY AUTO_INCREMENT,
  NM_DISCIPLINA VARCHAR(50),
  NM_PROFESSOR  VARCHAR(50)
 );
 
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, "Guilherme Oliveira Verissimo", 21, "Masculino", "P"),
       ("Info D", 2021, "Pedro Pereira da Silva", 17, "Masculino", "F"),
       ("Info A", 2021, "Amanda Silva dos Santos", 31, "Feminino", "P");
      
INSERT INTO TB_DISCIPLINA (NM_DISCIPLINA, NM_PROFESSOR)
VALUES ("Banco de Dados", "Bruno de Oliveira"),
       ("Montagem e Manutenção", "Roberto Carlos"),
       ("Matemática", "Carlos"),
       ("Moral Cristã", "Conrado"),
       ("Inglês Instrumental", "Clayton");
       
INSERT INTO TB_BOLETIM (ID_ALUNO, ID_DISCIPLINA, VL_NOTA, QTD_FALTAS)
VALUES (1, 1, 8, 0),
       (1, 2, 7, 2),
       (1, 3, 5, 8),
       (1, 4, 10, 0),
       (1, 5, 10, 3),
       (2, 1, 9, 3),
       (2, 2, 7, 0),
       (2, 3, 9, 2),
       (2, 4, 5, 5),
       (2, 5, 7, 6),
       (3, 1, 6, 3),
       (3, 2, 4, 9),
       (3, 3, 7, 4),
       (3, 4, 9, 1),
       (3, 5, 8, 2);

### Update/Delete Script

UPDATE TB_ALUNOS
SET NR_CHAMADA = 22
WHERE ID_ALUNO = 1;

UPDATE TB_DISCIPLINA
SET NM_DISCIPLINA = "Análise de Sistemas"
WHERE ID_DISCIPLINA = 1;

UPDATE TB_BOLETIM
SET VL_NOTA = 9
WHERE ID_BOLETIM = 1;

UPDATE TB_BOLETIM
SET VL_NOTA = 3
WHERE ID_BOLETIM = 3;

DELETE FROM TB_ALUNOS
WHERE ID_ALUNO = 1;

DELETE FROM TB_DISCIPLINA
WHERE ID_DISCIPLINA = 3;

DELETE FROM TB_BOLETIM
WHERE ID_BOLETIM = 5;

### 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, D.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 D
ON D.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, D.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 D
ON D.ID_DISCIPLINA = B.ID_DISCIPLINA
WHERE A.NM_TURMA = "INFO A"
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, D.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 D
ON D.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 reprovados por ano e turma.
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 D
ON D.ID_DISCIPLINA = B.ID_DISCIPLINA
WHERE B.VL_NOTA < 5
AND A.NR_ANO_LETIVO = 2021
AND A.NM_TURMA = "Info A";

### Consultas personalizadas

--Selecionar os alunos que tiraram entre 8 e 10.
SELECT A.NM_ALUNO
FROM TB_BOLETIM      B
INNER JOIN TB_ALUNOS A
ON A.ID_ALUNO      = B.ID_ALUNO
INNER JOIN TB_DISCIPLINA D
ON D.ID_DISCIPLINA = B.ID_DISCIPLINA
WHERE B.VL_NOTA BETWEEN 8 AND 10;

--Selecionar o aluno e turma do aluno que possua a letra "N" no nome e que é presente.
SELECT A.NM_ALUNO, A.NM_TURMA
FROM TB_BOLETIM      B
INNER JOIN TB_ALUNOS A
ON A.ID_ALUNO      = B.ID_ALUNO
INNER JOIN TB_DISCIPLINA D
ON D.ID_DISCIPLINA = B.ID_DISCIPLINA
WHERE A.NM_ALUNO LIKE "%N%"
AND A.TP_STATUS = "P";

--Selecionar o aluno e número da chamada do aluno cujo o número esta entre 20 e 35 e que seja do gênero masculino.
SELECT A.NM_ALUNO, A.NR_CHAMADA
FROM TB_BOLETIM      B
INNER JOIN TB_ALUNOS A
ON A.ID_ALUNO      = B.ID_ALUNO
INNER JOIN TB_DISCIPLINA D
ON D.ID_DISCIPLINA = B.ID_DISCIPLINA
WHERE A.NR_CHAMADA BETWEEN 20 AND 35
AND A.DS_SEXO = "Masculino";

--Selecioanar os alunos faltosos ou os alunos que tiraram menos que 3.
SELECT A.NM_ALUNO
FROM TB_BOLETIM      B
INNER JOIN TB_ALUNOS A
ON A.ID_ALUNO      = B.ID_ALUNO
INNER JOIN TB_DISCIPLINA D
ON D.ID_DISCIPLINA = B.ID_DISCIPLINA
WHERE A.TP_STATUS = "F"
OR B.VL_NOTA < 3;

--Selecionar o aluno e turma do aluno que tenha tirado zero no ano de 2021.
SELECT A.NM_ALUNO, A.NM_TURMA
FROM TB_BOLETIM      B
INNER JOIN TB_ALUNOS A
ON A.ID_ALUNO      = B.ID_ALUNO
INNER JOIN TB_DISCIPLINA D
ON D.ID_DISCIPLINA = B.ID_DISCIPLINA
WHERE B.VL_NOTA = 0
AND A.NR_ANO_LETIVO = 2021;