# Session Recomeçar
autor: Guilherme Oliveira Verissimo
turma: InfoC
número: 21
[toc]
## Modelagem Filme
>Create Script
```sql=
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(200),
ID_GENERO INT,
VL_AVALIACAO DECIMAL(15,2),
BT_DISPONIVEL BOOL,
DT_LANCAMENTO DATE,
FOREIGN KEY (ID_GENERO)
REFERENCES TB_GENERO (ID_GENERO)
);
```
>Insert Script
```sql=
INSERT INTO TB_GENERO (DS_GENERO, BT_ATIVO)
VALUES ("Ação", true),
("Ficção", true),
("Comédia", true),
("Infantil", true),
("Aventura", true);
INSERT INTO TB_FILME (NM_FILME, ID_GENERO, VL_AVALIACAO, BT_DISPONIVEL, DT_LANCAMENTO)
VALUES ("V&F 9", 1, 4.9, true, "2021-04-01"),
("The Avengers", 2, 4.7, true, "2012-04-27"),
("Vovó Zona", 3, 4.5, true, "2000-05-31"),
("The Last Naruto: O Filme", 5, 4.8, true, "2015-05-28"),
("Os Incríveis", 4, 4.7, true, "2004-12-10"),
("Mortal Kombat", 1, 4.0, true, "2021-04-15"),
("Rambo IV", 1, 4.6, true, "2011-02-28");
```
>Update/Delete Script
```sql=
UPDATE TB_FILME
SET VL_AVALIACAO = 4.80
WHERE ID_FILME = 2;
UPDATE TB_FILME
SET DT_LANCAMENTO = "2003-12-10"
WHERE ID_FILME = 5;
DELETE FROM TB_FILME
WHERE ID_FILME = 1;
DELETE FROM TB_FILME
WHERE ID_FILME = 3;
```
### Desafios
>Select Script
```sql=
--4. Consultar todos os filmes com gênero igual a ‘Ação’.
SELECT F.NM_FILME
FROM TB_FILME F
INNER JOIN TB_GENERO G
ON F.ID_GENERO = G.ID_GENERO
WHERE DS_GENERO = "Ação";
```
```sql=
--5. Consultar todos os filmes que possuam o nome e gênero contendo a letra ‘a’.
SELECT F.NM_FILME
FROM TB_FILME F
INNER JOIN TB_GENERO G
ON F.ID_GENERO = G.ID_GENERO
WHERE NM_FILME LIKE "%A%"
AND DS_GENERO LIKE "%A%";
```
```sql=
--6. Consultar todos os filmes que estejam disponíveis e com avaliação maior que 8. (minhas avaliações vão de 0 a 5, não 8, por isso mudei um pouco)
SELECT NM_FILME
FROM TB_FILME F
WHERE BT_DISPONIVEL = true
AND VL_AVALIACAO > 4.0;
```
```sql=
--7. Consultar todos os filmes que foram lançados entre 2010 e 2020 com nome de filme menor que 10 caracteres.
SELECT NM_FILME
FROM TB_FILME
WHERE DT_LANCAMENTO BETWEEN "2010-01-01" AND "2020-12-31"
AND LENGTH (NM_FILME) < 10;
```
### Minhas 5 consultas
```sql=
--1. Consultar os filmes que se encontram disponiveis e possuem as letras O e M no nome.
SELECT NM_FILME
FROM TB_FILME F
WHERE NM_FILME LIKE "%O%"
AND NM_FILME LIKE "%M%"
AND BT_DISPONIVEL = true;
```
```sql=
--2. Consultar os filmes que tenham avaliação entre 4.2 e 4.8.
SELECT NM_FILME
FROM TB_FILME F
WHERE VL_AVALIACAO
BETWEEN 4.2 AND 4.8;
```
```sql=
--3. Consultar os filmes que foram lançados depois de 01/01/2017.
SELECT NM_FILME
FROM TB_FILME F
WHERE DT_LANCAMENTO > "2017-01-01";
```
```sql=
--4. Consultar os filmes que possuam a letra A no nome
SELECT NM_FILME
FROM TB_FILME F
INNER JOIN TB_GENERO G
ON F.ID_GENERO = G.ID_GENERO
WHERE DS_GENERO LIKE "%A%"
AND VL_AVALIACAO < 4.6
OR VL_AVALIACAO > 4.8;
```
```sql=
--5. Consultar os filmes lançados depois de 01/01/2011 e que sejam de Ação ou Ficção
SELECT NM_FILME
FROM TB_FILME F
INNER JOIN TB_GENERO G
ON F.ID_GENERO = G.ID_GENERO
WHERE DT_LANCAMENTO > "2011-01-01"
AND DS_GENERO = "Ação"
OR DS_GENERO = "Ficçâo";
```
## Modelagem Alunos
>Create Script
```sql=
CREATE TABLE TB_TURMA (
ID_TURMA INT PRIMARY KEY AUTO_INCREMENT,
NM_TURMA VARCHAR(50),
NR_ANO_LETIVO INT,
NM_CURSO VARCHAR(50)
);
CREATE TABLE TB_ALUNOS (
ID_ALUNO INT PRIMARY KEY AUTO_INCREMENT,
ID_TURMA INT,
NM_ALUNO VARCHAR(50),
NR_CHAMADA INT,
DS_SEXO VARCHAR(50),
TP_STATUS VARCHAR(1),
FOREIGN KEY (ID_TURMA)
REFERENCES TB_TURMA (ID_TURMA)
);
```
>Insert Script
```sql=
INSERT INTO TB_TURMA (NM_TURMA, NR_ANO_LETIVO, NM_CURSO)
VALUES ("Info A", 2021, "Informatica"),
("Info B", 2021, "Informatica"),
("Info C", 2021, "Informatica"),
("Info D", 2021, "Informatica");
INSERT INTO TB_ALUNOS (ID_TURMA, NM_ALUNO, NR_CHAMADA, DS_SEXO, TP_STATUS)
VALUES (1, "Bruno Conceição de Souza", 1, "Masculino", "P"),
(1, "Davi Gabriel Freitas de Lima", 5, "Masculino", "F"),
(1, "Nayara Vitória Bernardo Ehnert", 37, "Feminino", "F"),
(2, "Daniel Aristótelis Assunção da Silva", 7, "Masculino", "P"),
(2, "Felipe Costa Sanches Vale", 11, "Masculino", "P"),
(3, "Heloysa dos Santos Arruda", 22, "Feminino", "P"),
(3, "Karoline Sampaio Silva", 27, "Feminino", "P"),
(4, "Letícia Simões da Silva", 26, "Feminino", "F"),
(4, "Gustavo da Cunha Vicente", 14, "Masculino", "F"),
(4, "Catarina dos Santos Siqueira", 3, "Feminino", "P");
```
>Update/Delete Script
```sql=
UPDATE TB_ALUNOS
SET NR_CHAMADA = 2
WHERE ID_ALUNO = 1;
UPDATE TB_ALUNOS
SET TP_STATUS = "F"
WHERE ID_ALUNO = 4;
DELETE FROM TB_ALUNOS
WHERE ID_ALUNO = 10;
DELETE FROM TB_ALUNOS
WHERE ID_ALUNO = 2;
```
### Desafios
>Select Script
```sql=
--4. Selecionar turma, nome e chamada da tabela alunos filtrando por turma.
SELECT G.NM_TURMA,
F.NM_ALUNO,
F.NR_CHAMADA
FROM TB_ALUNOS F
INNER JOIN TB_TURMA G
ON F.ID_TURMA = G.ID_TURMA
WHERE NM_TURMA = "Info C";
```
```sql=
--5. Selecionar todos campos da tabela alunos filtrando por nome e turma de forma parcial.
SELECT *
FROM TB_ALUNOS F
INNER JOIN TB_TURMA G
ON F.ID_TURMA = G.ID_TURMA
WHERE NM_ALUNO LIKE "%Sil%"
AND NM_TURMA LIKE "%C%";
```
```sql=
--6. Selecione ano letivo, turma, nome, chamada, sexo da tabela alunos filtrando os frequentes por turma e ano letivo.
SELECT G.NR_ANO_LETIVO,
G.NM_TURMA,
F.NM_ALUNO,
F.NR_CHAMADA,
F.DS_SEXO
FROM TB_ALUNOS F
INNER JOIN TB_TURMA G
ON F.ID_TURMA = G.ID_TURMA
WHERE TP_STATUS = "P"
AND NM_TURMA = "Info C"
AND NR_ANO_LETIVO = 2021;
```
```sql=
--7. Selecione nome, sexo da tabela alunos filtrando por ano, turma e sexo.
SELECT F.NM_ALUNO,
F.DS_SEXO
FROM TB_ALUNOS F
INNER JOIN TB_TURMA G
ON F.ID_TURMA = G.ID_TURMA
WHERE NR_ANO_LETIVO = 2021
AND NM_TURMA = "Info B"
AND DS_SEXO = "Masculino";
```
### Minhas 5 consultas
```sql=
--Selecionar o nome dos alunos que tenham o número da chamada entre 20 e 30.
SELECT NM_ALUNO
FROM TB_ALUNOS
WHERE NR_CHAMADA BETWEEN 20 AND 30;
```
```sql=
--Selecionar o nome dos alunos que tenham a letra C no nome e que são presentes.
SELECT NM_ALUNO
FROM TB_ALUNOS
WHERE NM_ALUNO LIKE "%C%"
AND TP_STATUS = "P";
```
```sql=
--Selecionar o nome dos alunos que são da turma Info A e são do sexo masculino.
SELECT NM_ALUNO
FROM TB_ALUNOS
INNER JOIN TB_TURMA
ON TB_ALUNOS.ID_TURMA = TB_TURMA .ID_TURMA
WHERE NM_TURMA = "Info A"
AND DS_SEXO = "Masculino";
```
```sql=
--Selecionar o nome dos alunos que não são presentes e que tenham o número da chamada maior que 20.
SELECT NM_ALUNO
FROM TB_ALUNOS
INNER JOIN TB_TURMA
ON TB_ALUNOS.ID_TURMA = TB_TURMA .ID_TURMA
WHERE TP_STATUS = "F"
AND NR_CHAMADA > 20;
```
```sql=
--Selecionar o nome do alunos que tenha Silva no nome e que seus números são menores que 10 ou maiores que 20.
SELECT NM_ALUNO
FROM TB_ALUNOS
INNER JOIN TB_TURMA
ON TB_ALUNOS.ID_TURMA = TB_TURMA .ID_TURMA
WHERE NM_ALUNO LIKE "%SILVA%"
AND NR_CHAMADA < 10
OR NM_ALUNO LIKE "%SILVA%"
AND NR_CHAMADA > 20;
```
Google Drive
Gist
Clipboard
Sign in with Wallet
