# Session 2 Banco de Dados

## Filmes

create table tb_genero
(
  id_genero int primary key auto_increment,
  ds_genero varchar(200),
  bt_ativo bool  
 );
 
 create table tb_filme
 (
    id_filme int primary key auto_increment,
    nm_filme varchar(100),
    vl_avaliacao decimal(15,2),
    dt_lancamento date,
    bt_disponivel bool
 );
 
 create table tb_fg
 (
  id_fg 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 into tb_genero (ds_genero, bt_ativo)
 values ('Comedia', 1),
  ('Ação', 1),
        ('Aventura', 1),
        ('Drama', 1),
        ('Suspense', 1);
   
   
 insert into tb_filme (nm_filme, vl_avaliacao, dt_lancamento, bt_disponivel)
 values ('Superbad - É hoje', 10.0, '2007-10-19', true),
        ('Kingsman: Serviço Secreto', 9.0, '2015-03-05', true),
        ('Hobbit - Uma jornada inesperada', 6.0, '2012-12-14', true),
        ('1917', 5.0, '2020-01-16', true),
        ('O resgate do soldado Ryan', 8.0, '1999-03-05', true),
        ('Como se tornar o pior aluno da escola', 10.0, '2017-10-12', true);

insert into tb_fg (id_filme, id_genero)
values (1, 1),
(2, 2),
        (3, 3),
(4, 2),
        (5, 2),
        (6, 1);
       
update tb_filme
set vl_avaliacao = 10
where vl_avaliacao < 6;

update tb_genero
set ds_genero = 'Resgate'
where ds_genero = 'Drama';

update tb_fg
set id_genero = 2
where id_filme = 3;

delete from tb_filme
where id_filme = 5;

delete from tb_genero
where id_genero = 1;

delete from tb_fg
where id_fg = 1;

### Query Filmes

-- 1 

select f.nm_filme, g.ds_genero
from tb_fg	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 

select f.id_filme, f.nm_filme, g.id_genero, g.ds_genero
from tb_fg	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

select f.nm_filme, f.vl_avaliacao, f.dt_lancamento
from tb_fg	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 

select f.nm_filme, f.vl_avaliacao, f.dt_lancamento, f.bt_disponivel, g.ds_genero
from tb_fg	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 = 'Drama'
and   g.ds_genero = 'Suspense';

-- 5

select f.nm_filme, f.vl_avaliacao, f.dt_lancamento, f.bt_disponivel, g.ds_genero
from tb_fg	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 = 'Comedia';


select f.nm_filme, f.vl_avaliacao
from tb_fg	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 > 9;



select f.nm_filme, f.vl_avaliacao, f.dt_lancamento, f.bt_disponivel, g.ds_genero
from tb_fg	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 '%x%';



select f.nm_filme
from tb_fg	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 'H%';



select f.nm_filme, f.vl_avaliacao
from tb_fg	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;

## Alunos

create table tb_disciplina
(
  id_disciplina int primary key auto_increment,
  nm_disciplina varchar(200),
  nm_professor	varchar(200)  
);

create table tb_aluno
(
  id_aluno       int primary key auto_increment,
  nm_turma       varchar(200),
  nr_ano_letivo  int,
  nm_aluno       varchar(200),
  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)
);


insert into tb_disciplina(nm_disciplina, nm_professor)
values ('Software', 'Bruno'),
       ('Inglês', 'Clayton'),
       ('Matemática', 'Carlos'),
       ('Empreendedorismo', 'Natan'),
       ('Hardware', 'Robertoa');
 
 
insert into tb_aluno (nm_turma, nr_ano_letivo, nm_aluno, nr_chamada, ds_sexo, tp_status)
values  ('INFO C', 2021, 'Luis Paulo de Oliveira', 33, 'Masculino', 'F'),
  		('INFO D', 2021, 'Bruna Marquezine', 30, 'Feminino', 'F'),   
  		('INFO C', 2021, 'Dwane Jhonson', 35, 'Masculino', 'F');
  
  
insert into tb_boletim (id_aluno, id_disciplina, vl_nota, qtd_faltas)
values (1, 1, 10, 0),
	   (1, 2, 8, 1),
       (1, 3, 9, 1),
	   (1, 4, 10, 2),
       (1, 5, 6, 2),
	   (2, 1, 5, 3),
       (2, 2, 8, 4),
	   (2, 3, 5, 0),
       (2, 4, 9, 0),
	   (2, 5, 4, 1),
	   (3, 1, 10, 2),
	   (3, 2, 10, 4),
	   (3, 3, 9, 1),
       (3, 4, 7, 1),
	   (3, 5, 6, 1);
	      

### Query alunos

-- 1

select a.nm_aluno, a.nm_turma, a.nr_ano_letivo, d.nm_disciplina, b.vl_nota
from tb_boletim	b 
inner join tb_aluno 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 

select a.nm_aluno, a.nm_turma, a.nr_ano_letivo, d.nm_disciplina, b.vl_nota
from tb_boletim	b 
inner join tb_aluno 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 C'
and   a.nr_ano_letivo = 2021
order by b.vl_nota desc;

-- 3

select a.nm_aluno, a.nm_turma, a.nr_ano_letivo, d.nm_disciplina, b.vl_nota
from tb_boletim	b 
inner join tb_aluno 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 

select a.nm_aluno, a.nm_turma, a.nr_ano_letivo
from tb_boletim	b 
inner join tb_aluno 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 D';

-- 5

select a.nm_aluno
from tb_boletim	b 
inner join tb_aluno 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;


select a.nm_aluno, a.nm_turma, a.nr_ano_letivo
from tb_boletim	b 
inner join tb_aluno 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.nm_turma = 'INFO C'
order by a.nr_ano_letivo;

select a.nm_aluno, a.nm_turma, a.nr_ano_letivo, d.nm_disciplina, b.vl_nota
from tb_boletim	b 
inner join tb_aluno 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; 

select a.nm_aluno, a.nm_turma
from tb_boletim	b 
inner join tb_aluno a on a.id_aluno = b.id_aluno
inner join tb_disciplina d on d.id_disciplina = b.id_disciplina
order by a.nr_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_aluno a on a.id_aluno = b.id_aluno
inner join tb_disciplina d on d.id_disciplina = b.id_disciplina
order by a.nr_ano_letivo;