# Session Two Banco de Dados: Getting Better
**Autor**: Elias de Freitas Cavalcante
**Número**: 12
**Turma**: INFO C
[toc]
## Tabela Filme
> Create script
```sql=
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,1) 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)
);
```
### Inserts
**Inserir 5 registros na tabela.**
```sql=
INSERT INTO tb_genero (nm_genero, bt_ativo)
VALUES ('Comédia', true),
('Ação', true),
('Terror', 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),
('Invocação do Mal 2', '2014-06-09',8.5, true),
('Amor e Monstros', '2021-02-14', 9.5, true),
('No Limite do Amanhã', '2014-05-29', 6.0, true);
INSERT INTO tb_filme_genero (id_filme, id_genero)
VALUES (1, 1),
(2, 1),
(2, 2),
(3, 3),
(4, 1),
(5, 2);
```
### SELECTS
**Selecionar fime, gênero aplicando o relacionamento nas tabelas, sem filtros, ordenando por filme.**
```sql=
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;
```
**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.**
```sql=
SELECT*
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;
```
**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.**
```sql=
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'
ORDER BY f.vl_avaliacao ASC;
```
**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’.**
```sql=
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 = 'Comédia';
```
### UPDATES
**Alterar 1 filme, 1 gênero, 1 vínculo de filmeXgênero das tabelas.**
```sql=
UPDATE tb_filme
SET nm_filme ='Star Wars'
WHERE id_filme = 5;
```
```sql=
UPDATE tb_genero
SET nm_genero ='Avetentura'
WHERE id_genero = 3;
```
```sql=
UPDATE tb_filme_genero
SET id_filme = 1
WHERE id_filme = 2;
```
### DELETS
**Remover 1 filme, 1 gênero, 1 vínculo de filmeXgênero das tabelas.**
```sql=
DELETE FROM tb_filme
WHERE nm_filme = 'Gente Grande';
```
```sql=
DELETE FROM tb_genero
SET id_genero = 1;
```
```sql=
DELETE FROM tb_filme_genero
WHERE id_genero = 3;
```
### Consultas Personalizadas
```sql=
SELECT*
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 = '2014-01-01';
```
```sql=
SELECT*
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 '%S';
```
```sql=
SELECT*
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 = 9;
```
```sql=
SELECT*
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 <= 9
ORDER BY f.nm_filme ASC;
```
```sql=
SELECT*
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 LIKE 'C%';
```
## Tabela Boletim
> Create script
```sql=
CREATE TABLE tb_diciplina (
id_diciplina int primary key auto_increment,
nm_diciplina 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_diciplina int,
vl_nota decimal(15,1),
qtd_faltas int,
FOREIGN KEY (id_aluno) REFERENCES tb_aluno(id_aluno),
FOREIGN KEY (id_diciplina) REFERENCES tb_diciplina(id_diciplina)
);
```
### SELECTS
**Selecionar aluno, turma, ano, disciplina, nota aplicando o relacionamento nas tabelas, sem filtros, ordenando por aluno.**
```sql=
SELECT a.nm_aluno,
a.nm_turma,
a.nr_ano_letivo,
d.nm_diciplina,
ad.vl_nota
FROM tb_boletim ad
INNER JOIN tb_aluno a ON a.id_aluno = ad.id_aluno
INNER JOIN tb_diciplina d ON d.id_diciplina = ad.id_diciplina
ORDER BY a.nm_aluno ASC;
```
**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.**
```sql=
SELECT a.nm_aluno,
a.nm_turma,
a.nr_ano_letivo,
d.nm_diciplina,
ad.vl_nota
FROM tb_boletim ad
INNER JOIN tb_aluno a ON a.id_aluno = ad.id_aluno
INNER JOIN tb_diciplina d ON d.id_diciplina = ad.id_diciplina
ORDER BY ad.vl_nota ASC;
```
**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.**
```sql=
SELECT a.nm_aluno,
a.nm_turma,
a.nr_ano_letivo,
d.nm_diciplina,
ad.vl_nota
FROM tb_boletim ad
INNER JOIN tb_aluno a ON a.id_aluno = ad.id_aluno
INNER JOIN tb_diciplina d ON d.id_diciplina = ad.id_diciplina
WHERE a.tp_status ='A'
ORDER BY a.nr_ano_letivo,
a.nm_turma,
a.nr_chamada ASC;
```
**Selecionar aluno, turma, ano aplicando o relacionamento nas tabelas, filtrando os alunos reprovados por ano e turma.**
```sql=
SELECT a.nm_aluno,
a.nm_turma,
a.nr_ano_letivo,
d.nm_diciplina,
ad.vl_nota
FROM tb_boletim ad
INNER JOIN tb_aluno a ON a.id_aluno = ad.id_aluno
INNER JOIN tb_diciplina d ON d.id_diciplina = ad.id_diciplina
WHERE a.tp_status ='R'
ORDER BY a.nr_ano_letivo,
a.nm_turma ASC;
```
### UPDATES
**Alterar 1 aluno, 1 disciplina, 2 notas.**
```sql=
UPDATE tb_aluno
SET nm_aluno = 'Taylor'
WHERE id_aluno = 1;
```
```sql=
UPDATE tb_diciplina
SET nm_diciplina = 'Portugues'
WHERE id_diciplina = 1;
```
```sql=
UPDATE tb_boletim
SET vl_nota = 0.0
WHERE id_boletim = 2;
```
```sql=
UPDATE tb_boletim
SET vl_nota = 10.0
WHERE id_boletim = 3;
```
### DELETS
**Remover 1 nota, 1 disciplina, 1 aluno.**
```sql=
DELETE FROM tb_boletim
WHERE vl_nota = 3.0;
```
```sql=
DELETE FROM tb_diciplina
WHERE id_diciplina = 2;
```
```sql=
DELETE FROM tb_aluno
WHERE nm_aluno = 'Elias da Silva';
```
### Consultas Personalizadas
```sql=
SELECT a.nm_aluno,
a.nm_turma
FROM tb_boletim ad
INNER JOIN tb_aluno a ON a.id_aluno = ad.id_aluno
INNER JOIN tb_diciplina d ON d.id_diciplina = ad.id_diciplina
WHERE d.nm_diciplina = 'Ingles';
```
```sql=
SELECT a.nm_aluno,
a.nm_turma,
ad.vl_nota
FROM tb_boletim ad
INNER JOIN tb_aluno a ON a.id_aluno = ad.id_aluno
INNER JOIN tb_diciplina d ON d.id_diciplina = ad.id_diciplina
WHERE ad.vl_nota > 5.0
ORDER BY ad.vl_nota ASC;
```
```sql=
SELECT a.nm_turma,
a.nm_aluno
FROM tb_boletim ad
INNER JOIN tb_aluno a ON a.id_aluno = ad.id_aluno
INNER JOIN tb_diciplina d ON d.id_diciplina = ad.id_diciplina
WHERE a.nm_turma LIKE '%B%'
ORDER BY a.nm_turma,
a.nm_aluno ASC;
```
```sql=
SELECT a.nm_aluno
FROM tb_boletim ad
INNER JOIN tb_aluno a ON a.id_aluno = ad.id_aluno
INNER JOIN tb_diciplina d ON d.id_diciplina = ad.id_diciplina
WHERE a.nm_aluno LIKE '%s%';
```
```sql=
SELECT a.nm_aluno,
a.nm_turma
FROM tb_boletim ad
INNER JOIN tb_aluno a ON a.id_aluno = ad.id_aluno
INNER JOIN tb_diciplina d ON d.id_diciplina = ad.id_diciplina
WHERE ad.vl_nota <= 4.0;
```
Google Drive
Gist
Clipboard
Sign in with Wallet
