# SESSION TWO: GETTING BETTER | BANCO DE DADOS
Author: Igor Lima Charles
N°: 18
Grade: INFO A

# TABELA DE FILMES

## SCHEMA SQL (TABELA DE FILMES)

CREATE TABLE TB_GENERO(
id_genero        int primary key auto_increment,
ds_genero        varchar(100),
bt_ativo         bool not null
);

CREATE TABLE TB_FILME(
id_filme       int primary key auto_increment,
nm_filme  varchar(100),
vl_avaliacao decimal(10,2) default 5,
bt_disponivel bool not null,
dt_lancamento date
);

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 into TB_GENERO(ds_genero, bt_ativo)
values ('Ação', true),
       ('Terror', true),
       ('Ficção', true),
       ('Suspense', true),
       ('Drama', true),
       ('Romance', true);
 
insert into TB_FILME (nm_filme, vl_avaliacao, bt_disponivel, dt_lancamento)

values ('Minha mãe é uma peça', 6.8, true, '2013-06-21'),
       ('Mad Max: Estrada da Fúria', 8.1, true, '2015-05-14'),
       ('Hereditário', 7.3, true, '2018-06-21'),
       ('Midsommar', 7.1, true, '2019-09-19'),
       ('Corra', 6.7, true, '2020-11-20'),
       ('Nasce uma estrela', 7.6, true, '2018-10-11');
       
insert into TB_FILME_GENERO(id_filme, id_genero)
values (1, 1),
       (2, 2),
       (2, 3),
       (3, 2),
       (3, 4),
       (3, 5),
       (4, 2),
       (4, 4),
       (4, 5),
       (5, 5),
       (6, 6);

## QUERY SQL (TABELA DE FILMES)

 /* UPDATE */
update TB_FILME
set vl_avaliacao = 9.0
where id_filme = 6;

/* DELETE*/
delete from TB_FILME_GENERO
where id_filme_genero = 5;

/* 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 '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 G.ds_genero = 'Terror' or 'Drama';

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

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

# TABELA DE ALUNOS

## SQUEMA SQL (TABELA DE 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 A', 2021, 'Igor Lima Charles', 18, 'Masculino', 'F'),
('ADM C', 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, 7.5),
       (3, 3, 4.5),
       (4, 4, 5.5),
       (5, 5, 6.0),
       (6, 6, 9.0);

## QUERY SQL (TABELA DE ALUNOS)

/* 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
order by A.nm_turma, A.nr_ano_letivo and AD.vl_nota ;

/* 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
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
order by A.nm_turma, A.nr_ano_letivo, A.nr_chamada;

/* 5 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,
       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 = 2020;

select A.nm_aluno, A.nm_turma,
       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 = 9.0;

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%';

select A.nm_turma, 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 AD.vl_nota >= 7.0;


/* UPDATE */
update tb_aluno
set tp_status = 'T'
where id_aluno = 5;

/* DELETE */
delete from tb_aluno_disciplina
where id_aluno = 6;