# Session Two: Getting Better | SQL
autor: Isabela Silva Sousa

[toc]

## Modelagem 1: Filme | Gênero

>[color=hotpink]DER

>[color=hotpink]Create script

CREATE TABLE TB_Generos (
  ID_Genero			int primary key auto_increment,
  DS_Genero			varchar(100),

  BT_Ativo			bool
);


CREATE TABLE TB_Filmes (
  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_Filmes_Generos (
  ID_Filme_Genero		int primary key auto_increment,
  ID_Filme				int,
  ID_Genero				int,
  FOREIGN KEY (ID_Filme) REFERENCES TB_Filmes(ID_Filme) ON DELETE CASCADE,
  FOREIGN KEY (ID_Genero) REFERENCES TB_Generos(ID_Genero) ON DELETE CASCADE
);

>[color=hotpink] Insert script

INSERT INTO TB_Generos (DS_Genero, BT_Ativo)
     VALUES ('Ação', true),
            ('Comedia', true),
            ('Musical', true),
            ('Terror', true),
            ('Romance', true);

INSERT INTO TB_Filmes (NM_Filme, DT_Lancamento, VL_Avaliacao, BT_Disponivel)
VALUES ('Como Eu Era Antes de Você', '2016-06-16', 9.9, true),
	   ('As branquelas', '2008-01-02', 8.5, true),
       ('Vingadores: Ultimato', '2019-04-25', 9.8, true),
       ('Annabelle 3: De Volta Para Casa', '2019-06-27', 8.6, true),
       ('Grease — Nos Tempos da Brilhantina', '1978-06-16', 9.8, true);


INSERT INTO TB_Filmes_Generos (ID_Filme, ID_Genero)
	 VALUES (1, 5),
            (2, 2),
            (3, 1),
            (4, 4),
            (5, 3);

>[color=hotpink] Update script

UPDATE TB_Filmes
SET NM_Filme = 'O Pequenino'
WHERE ID_Filme = 2;

UPDATE TB_Generos
SET DS_Genero = 'Ficção Cientifica'
WHERE ID_Genero = 3;

DELETE FROM TB_Generos
WHERE ID_Genero = 2;

DELETE FROM TB_Filmes
WHERE ID_Filme = 2;

DELETE FROM TB_Filmes_Generos
WHERE ID_Filme_Genero = 2;

>[color=hotpink] Select script

-- Selecionar fime, gênero aplicando o relacionamento nas tabelas, sem filtros, ordenando por filme.
SELECT F.NM_Filme,
       G.DS_Genero
FROM TB_Filmes_Generos FG
INNER JOIN TB_Filmes F 
ON F.ID_Filme  = FG.ID_Filme
INNER JOIN TB_Generos G 
ON G.ID_Genero = FG.ID_Genero;


-- Selecionar id do filme, filme, id do gênero e gênero aplicando o relacionamento nas tabelas, filtrando os que
SELECT F.ID_Filme,
       F.NM_Filme,
       G.ID_Genero,
       G.DS_Genero
FROM TB_Filmes_Generos FG
INNER JOIN TB_Filmes F 
ON F.ID_Filme = FG.ID_Filme
INNER JOIN TB_Generos G 
ON G.ID_Genero = FG.ID_Genero;


-- 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_Filmes_Generos FG
INNER JOIN TB_Filmes F
ON F.ID_Filme = FG.ID_Filme
INNER JOIN TB_Generos 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_Filmes_Generos FG
INNER JOIN TB_Filmes F
ON F.ID_Filme = FG.ID_Filme
INNER JOIN TB_Generos G
ON G.ID_Genero = FG.ID_Genero
WHERE G.DS_Genero = 'Romance' 
OR G.DS_Genero = 'Drama';


-- Selecionar filme e avaliação, aplicando o relacionamento nas tabelas filtrando a avaliação que esteja entre 8 e 9. 
SELECT F.NM_Filme, 
	   F.VL_Avaliacao
FROM TB_Filmes_Generos FG
INNER JOIN TB_Filmes F
ON F.ID_Filme = FG.ID_Filme
INNER JOIN TB_Generos G
ON G.ID_Genero = FG.ID_Genero
WHERE F.VL_Avaliacao BETWEEN "8.0"
AND "9.0";


-- Selecionar filme e lançamento, aplicando o relacionamento nas tabelas filtrando data de lançamento maior igual que 2016.
SELECT F.NM_Filme, 
	   F.DT_Lancamento
FROM TB_Filmes_Generos FG
INNER JOIN TB_Filmes F
ON F.ID_Filme = FG.ID_Filme
INNER JOIN TB_Generos G
ON G.ID_Genero = FG.ID_Genero
WHERE F.DT_Lancamento >='2016-06-16';


-- Selecinar filme, lançamento e gênero aplicando o relacionamento nas tabelas, filtrando o gênero que não possuí a letra "e" ou que possua a letra "o".
SELECT F.NM_Filme, 
	   F.DT_Lancamento,
       G.DS_Genero
FROM TB_Filmes_Generos FG
INNER JOIN TB_Filmes F
ON F.ID_Filme = FG.ID_Filme
INNER JOIN TB_Generos G
ON G.ID_Genero = FG.ID_Genero
WHERE G.DS_Genero NOT LIKE '%e%'
OR F.NM_Filme LIKE '%o%';


-- Selecionar filme, lançamento e avaliação aplicando o relacionamento nas tabelas, filtrando avaliação menor que 8.5.
SELECT F.NM_Filme, 
	   F.DT_Lancamento,
       F.VL_Avaliacao
FROM TB_Filmes_Generos FG
INNER JOIN TB_Filmes F
ON F.ID_Filme = FG.ID_Filme
INNER JOIN TB_Generos G
ON G.ID_Genero = FG.ID_Genero
WHERE F.VL_Avaliacao < 8.5;


-- Selecionar filme, lançamento e avaliação filtrando filme que tenha menis de 10 letras no nome.
SELECT F.NM_Filme, 
	   F.DT_Lancamento,
       F.VL_Avaliacao
FROM TB_Filmes_Generos FG
INNER JOIN TB_Filmes F
ON F.ID_Filme = FG.ID_Filme
INNER JOIN TB_Generos G
ON G.ID_Genero = FG.ID_Genero
WHERE character_length(F.NM_Filme) < 10;

## Modelagem 2: Aluno | Disciplina

>[color=hotpink]DER

>[color=yellow]Create script

CREATE TABLE TB_Disciplinas (

  ID_Disciplina   int primary key auto_increment,
  NM_Disciplina   varchar(100),
  NM_Professor    varchar(100)
);

CREATE TABLE TB_Alunos (
  ID_Aluno        int primary key auto_increment,
  NM_Aluno        varchar(100),
  NR_Ano_Letivo   int,
  NM_Turma        varchar(100),
  NR_Chamada      int,
  DS_Sexo         varchar(100),
  TP_Status       varchar(1)
);

CREATE TABLE TB_Alunos_Disciplinas (
  ID_Turma_Aluno  int primary key auto_increment,
  ID_Aluno        int,
  ID_Disciplina   int,
  VL_Nota	      decimal,
  FOREIGN KEY (ID_Disciplina) REFERENCES TB_Disciplinas(ID_Disciplina) ON DELETE CASCADE,
  FOREIGN KEY (ID_Aluno) REFERENCES TB_Alunos(ID_Aluno) ON DELETE CASCADE
);

>[color=yellow] Insert script

INSERT INTO TB_Disciplinas(NM_Disciplina, NM_Professor)
VALUES ('Língua Prtuguesa', 'Marcone'),
       ('Língua Inglês', 'Lucas'),
       ('Matemática', 'Mustapha'),
       ('Biologia', 'Joyce'),
       ('Sociologia', 'Bruna'),
       ('Química', 'Linaeth');



INSERT INTO TB_Alunos (NM_Aluno, NR_Ano_Letivo, NM_Turma, NR_Chamada, DS_Sexo, TP_Status)
VALUES ('Ana Luisa Partenazi Lopez', 2020, 'Líng.Port A', 1, 'Feminino', 'F'),
       ('Maria Luísa Ribeiro de Souza', 2020, 'Mat.A', 34, 'Feminino', 'F'),   
       ('Leonardo da Silva Oliveira', 2020, 'Mat.A', 25, 'Masculino', 'F');
       

INSERT INTO TB_Alunos (NM_Aluno, NR_Ano_Letivo, NM_Turma, NR_Chamada, DS_Sexo, TP_Status)
VALUES ('Leonardo da Silva Oliveira', 2020, 'Líng.Port A', 26, 'Masculino', 'F'),
       ('Maria luísa Ribeiro de Souza', 2020, 'Sociologia B', 30, 'Feminino', 'F'),
       ('Ana Luisa Partenazi Lopez', 2020, 'Mat.A', 2, 'Feminino', 'F');
              
              
INSERT INTO TB_Alunos (NM_Aluno, NR_Ano_Letivo, NM_Turma, NR_Chamada, DS_Sexo, TP_Status)
VALUES ('Maria Luísa Ribeiro de Souza', 2020, 'Biologia A', 29, 'Feminino', 'F'),
       ('Leonardo da Silva Oliveira', 2020, 'Química A', 25, 'Masculino', 'F'),
       ('Ana Luisa Partenazia Lopez', 2020, 'Líng.Ingl B', 1, 'Feminino', 'F');
              

INSERT INTO TB_Alunos_Disciplinas (ID_Aluno, ID_Disciplina, VL_Nota)
VALUES (1, 1, 9.0),
       (2, 2, 5.5),
       (3, 2, 8.5);
       
INSERT INTO TB_Alunos_Disciplinas (ID_Aluno, ID_Disciplina, VL_Nota)
VALUES (3, 1, 9.5),
       (2, 5, 6.0),
       (1, 6, 9.0);
       
INSERT INTO TB_Alunos_Disciplinas (ID_Aluno, ID_Disciplina, VL_Nota)
VALUES (2, 4, 3.0),
       (3, 5, 7.6),
       (1, 3, 10.0);

>[color=yellow] Update script

UPDATE TB_Disciplinas
 SET NM_Professor = 'Mércia'
 WHERE ID_Disciplina = 3;
 
 UPDATE TB_Alunos
 SET NR_Ano_Letivo = 2019
 WHERE ID_Aluno = 2;
 
 UPDATE TB_Alunos_Disciplinas
 SET VL_Nota = 4.5
 WHERE ID_Turma_Aluno = 3;
 
 DELETE FROM TB_Disciplinas
 WHERE ID_Disciplina = 2;
 
 DELETE FROM TB_Alunos
 WHERE ID_Aluno = 2;
 
 DELETE FROM TB_Alunos_Disciplinas
 WHERE ID_Turma_Aluno = 2;

>[color=yellow] 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_Alunos_Disciplinas  AD
INNER JOIN TB_Alunos A
ON A.ID_Aluno = AD.ID_Aluno
INNER JOIN TB_Disciplinas 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_Alunos_Disciplinas  AD
INNER JOIN TB_Alunos A
ON A.ID_Aluno = AD.ID_Aluno
INNER JOIN TB_Disciplinas D
ON D.ID_Disciplina = AD.id_disciplina
WHERE A.NR_Ano_Letivo = 2020
AND A.NM_Turma = 'Mat.A'
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_Alunos_Disciplinas  AD
INNER JOIN TB_Alunos A
ON A.ID_Aluno = AD.ID_Aluno
INNER JOIN TB_Disciplinas D
ON D.ID_Disciplina = AD.id_disciplina
WHERE AD.VL_Nota < 5.0
AND A.NR_Ano_Letivo = 2020
AND A.NM_Turma = 'Biologia A'
ORDER BY  A.NR_Ano_Letivo,
          A.NM_Turma,
          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
FROM TB_Alunos_Disciplinas  AD
INNER JOIN TB_Alunos A
ON A.ID_Aluno = AD.ID_Aluno
INNER JOIN TB_Disciplinas D
ON D.ID_Disciplina = AD.id_disciplina
WHERE AD.vl_nota < 5.0
AND A.NR_Ano_Letivo = 2020
AND A.NM_Turma = 'Biologia A';


-- Selecionar aluno e turma aplicando o relacionamento nas tabelas, filtrando aluno que não possuí a letra "L" no nome.
SELECT A.NM_Aluno,
       A.NM_Turma
FROM TB_Alunos_Disciplinas  AD
INNER JOIN TB_Alunos A
ON A.ID_Aluno = AD.ID_Aluno
INNER JOIN TB_Disciplinas D
ON D.ID_Disciplina = AD.id_disciplina
WHERE A.NM_Aluno NOT LIKE '%L%';


-- Selecionar aluno e turma aplicando relacionamento nas tabelas, filtrando turma que possuí a letra "i" no nome.
SELECT A.NM_Aluno,
       A.NM_Turma
FROM TB_Alunos_Disciplinas  AD
INNER JOIN TB_Alunos A
ON A.ID_Aluno = AD.ID_Aluno
INNER JOIN TB_Disciplinas D
ON D.ID_Disciplina = AD.id_disciplina
WHERE A.NM_Turma LIKE '%i%';


-- Selecionar aluno, turma e nota aplicando relacionamento nas tabelas, filtrando nota que for maior igual que 7.6.
SELECT A.NM_Aluno,
       A.NM_Turma,
       AD.VL_Nota
FROM TB_Alunos_Disciplinas  AD
INNER JOIN TB_Alunos A
ON A.ID_Aluno = AD.ID_Aluno
INNER JOIN TB_Disciplinas D
ON D.ID_Disciplina = AD.id_disciplina
WHERE AD.VL_Nota >= 7.6;


-- Selecionar aluno e turma aplicando relacionamento nas tabelas, filtrando aluno que possua menos ou igual 20 letras no nome.
SELECT A.NM_Aluno,
       A.NM_Turma
FROM TB_Alunos_Disciplinas  AD
INNER JOIN TB_Alunos A
ON A.ID_Aluno = AD.ID_Aluno
INNER JOIN TB_Disciplinas D
ON D.ID_Disciplina = AD.id_disciplina
WHERE  character_length(A.NM_Aluno) <=20 ;


-- Select aluno, turma e nota aplicando relacionamento nas tabelas, filtrando nota que esteja entre 7 e 8.
SELECT A.NM_Aluno,
       A.NM_Turma,
       AD.VL_Nota
FROM TB_Alunos_Disciplinas  AD
INNER JOIN TB_Alunos A
ON A.ID_Aluno = AD.ID_Aluno
INNER JOIN TB_Disciplinas D
ON D.ID_Disciplina = AD.id_disciplina
WHERE AD.VL_Nota BETWEEN "7.0"
AND "8.0";