# Session Two: Getting Better
Autor: Jonathan Fernando Costa Galo

[toc]

# Modelagem Filme - Sistema de Banco de Dados

## DDL: Criar as tabelas respeitando os relacionamentos do DER.

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

## DDL: Inserir 5 filmes, 5 gêneros, 10 vínculos de filme X gênero nas tabelas.

INSERT INTO tb_genero (nm_genero, bt_ativo)
     VALUES ('Comédia', true),
            ('Ação', true),
            ('Terror', true),
            ('Romance', true),
            ('Drama', true),
            ('Suspense', true),
            ('Aventura', true);

INSERT INTO tb_filme (nm_filme, dt_lancamento, vl_avaliacao, bt_disponivel)
VALUES ('Gente Grande', '2010-01-02', 9, true),
	   ('As branquelas', '2008-01-02', 8.5, true),
       ('Velozes e Furiosos 9', '2021-07-22', 9.8, true),
       ('Como Eu Era Antes de Você', '2016-06-16', 9.6, true),
       ('A Maldição da Casa Winchester', '2018-03-01', 7.4, true),
       ('As Panteras', '2019-11-14', 6.8, true),
       ('Velozes & Furiosos: Hobbs & Shaw', '2019-08-01', 8, true);


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

## DDL: Alterar 1 filme, 1 gênero, 1 vínculo de filme X gênero das tabelas.

UPDATE tb_filme
SET vl_avaliacao = 6.9
WHERE id_filme = 6;

UPDATE tb_genero
SET nm_genero = 'Ficção'
WHERE id_genero = 6;

UPDATE tb_filme_genero
SET id_genero = 2
WHERE id_genero = 7;

## DDL: Remover 1 filme, 1 gênero, 1 vínculo de filme X gênero das tabelas.

DELETE FROM tb_genero 
WHERE nm_genero = 'Aventura';

DELETE FROM tb_filme_genero
WHERE id_filme = 7;

DELETE FROM tb_filme
WHERE dt_lancamento = '2019-08-01';

## DML: 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 ASC;

# DML: 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.nm_filme,
   		   f.id_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 ASC;

## DML: 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.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.nm_genero = 'Ação' AND f.bt_disponivel = true
  ORDER BY f.nm_filme ASC;

## DML: 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 = 'Comédia';

# Modelagem Aluno - Sistema de Banco de Dados

## DDL: Criar as tabelas respeitando os relacionamentos do DER.

CREATE TABLE tb_disciplina (
  id_disciplina			int primary key auto_increment,
  nm_disciplina 		varchar(255),
  nm_professor			varchar(255)
);

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_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_aluno(id_aluno),
  FOREIGN KEY (id_disciplina) REFERENCES tb_disciplina(id_disciplina)
);

## DDL: Inserir 3 alunos, 5 disciplinas, e as notas de cada aluno nas disciplinas.

INSERT INTO tb_disciplina (nm_disciplina, nm_professor)
VALUES ('Banco de Dados', 'Bruno'),
	   ('Analise de Sistemas', 'Bruno'),
       ('Hardware', 'Robertão'),
       ('Inglês', 'Clayton'),
       ('Matématica', 'Carlos');
       
INSERT INTO tb_aluno (nm_turma, nr_ano_letivo, nm_aluno, nr_chamada, ds_sexo, tp_status)
VALUES ('Avengers', 8, 'Kleber de Andrade', 15, 'Masculino', 'F'),
	   ('Minecraft', 6, 'Maria da Silva', 24, 'Feminino', 'F'),
       ('Pinóquio', 3, 'Carol de Andrade', 14, 'Feminino', 'F');
       
INSERT INTO tb_boletim (id_aluno, id_disciplina, vl_nota, qtd_faltas)
VALUES (1, 1, 9, 2),
	   (1, 2, 7.5, 0),
       (1, 3, 7, 1),
       (1, 4, 8, 0),
       (1, 5, 8.5, 3),
       (2, 1, 5, 6),
       (2, 2, 9, 0),
       (2, 3, 8, 2),
       (2, 4, 7, 2),
       (2, 5, 10, 1),
       (3, 1, 9, 0),
       (3, 2, 8, 4),
       (3, 3, 6, 2),
       (3, 4, 6, 3),
       (3, 5, 8, 0);

## DDL: Alterar 1 aluno, 1 disciplina, 2 notas.

UPDATE tb_aluno
SET nm_aluno = 'Maria Clara do Carmo'
WHERE id_aluno = 2;

UPDATE tb_disciplina
SET nm_disciplina = 'PMT'
WHERE id_disciplina = 4;

UPDATE tb_boletim
SET qtd_faltas = 0
WHERE id_disciplina = 5;

## DDL: Remover 1 nota, 1 disciplina, 1 aluno.

## DML: 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_disciplina	d ON d.id_disciplina  = b.id_disciplina
INNER JOIN tb_aluno			a ON a.id_aluno = b.id_aluno
ORDER BY nm_aluno ASC;

## DML: 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_disciplina	d ON d.id_disciplina  = b.id_disciplina
INNER JOIN tb_aluno			a ON a.id_aluno = b.id_aluno
WHERE a.nm_turma = 'Avengers' AND a.nr_ano_letivo = 8
ORDER BY vl_nota ASC;

## DML: 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_disciplina	d ON d.id_disciplina  = b.id_disciplina
INNER JOIN tb_aluno			a ON a.id_aluno = b.id_aluno
WHERE b.vl_nota > 5 AND a.nm_turma = 'Minecraft'
ORDER BY nr_ano_letivo AND nm_turma AND nr_chamada ASC;

## DML: 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_disciplina	d ON d.id_disciplina  = b.id_disciplina
INNER JOIN tb_aluno			a ON a.id_aluno = b.id_aluno
WHERE b.vl_nota <= 5 AND a.nm_turma = 'Minecraft' AND a.nr_ano_letivo = 6;