# Session Two: Banco de dados

Erik Alexandre da Silva - InfoB - 10

[toc]

## *TABELA FILME*
### Exercício 1

CREATE TABLE tb_Filme (
  id_Filme int PRIMARY KEY AUTO_INCREMENT,
  nm_Filme varchar(100),
  dt_Lancamento date,
  vl_Avaliacao decimal(15,2),
  bt_Disponivel bool
);

CREATE TABLE tb_Genero (
  id_Genero int PRIMARY KEY AUTO_INCREMENT,
  ds_Genero varchar(100),
  bt_Ativo bool
);

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)ON DELETE CASCADE,
  FOREIGN KEY (id_Genero) REFERENCES tb_Genero(id_Genero)ON DELETE CASCADE
);

INSERT INTO tb_Filme(nm_Filme, dt_Lancamento, vl_Avaliacao, bt_Disponivel)
VALUES	("Invocação do Mal 3", "2021-06-03", 8.8, true),
		("Estou passando", "2010-09-24", 9.6, false),
        ("As Apimentadas", "2002-05-17", 9.1, false),
        ("Dênis o Pestinha", "2008-03-04", 9.4, true),
        ("Caindo em tentação", "2005-05-24", 8.9, true);
    

INSERT INTO tb_Genero(ds_Genero, bt_Ativo)
VALUES	("Ação",true),
		("Aventura",true),
    	("Comedia",true),
    	("Terror",true),
    	("Romance",true);


INSERT INTO tb_Filme_Genero(id_Filme, id_Genero)
VALUES	(1, 5),
		(1, 3),
		(5, 4),
		(2, 5),
		(3, 4),
		(4, 1),
		(3, 2),
		(4, 2),
		(2, 2),
		(5, 3);
      
SELECT F.nm_Filme,
	   G.ds_Genero
FROM tb_Filme_Genero   FG
INNER JOIN tb_Genero   G ON FG.id_Genero = G.id_Genero
INNER JOIN  tb_Filme   F ON FG.id_Filme = F.id_Filme;

### Exercício 2

/* DDL 3 */
UPDATE tb_Filme
SET nm_Filme ='Caindo em tentação'
WHERE id_Filme = 2;

UPDATE tb_Genero
SET ds_Genero='Suspense'
WHERE id_Genero = 3;

UPDATE tb_Filme_Genero
SET id_Filme = 2
WHERE id_Filme_Genero = 2;

### Exercício 3

/* DDL 4 */
DELETE FROM tb_Filme_Genero 
WHERE id_Filme_Genero = 2;

DELETE FROM tb_Genero
WHERE id_Genero = 4;

DELETE FROM tb_Filme
WHERE id_Filme = 1;

### Exercício 4

/* DML 1 */
SELECT F.nm_Filme,
	   G.ds_Genero
FROM tb_Filme_Genero   FG
INNER JOIN tb_Genero   G ON FG.id_Genero = G.id_Genero
INNER JOIN  tb_Filme   F ON FG.id_Filme = F.id_Filme
ORDER BY F.nm_Filme;

### Exercício 5

/* DML 2 */
SELECT F.id_Filme,
	   F.nm_Filme,
	   G.id_Genero,
	   G.ds_Genero
FROM tb_Filme_Genero   FG
INNER JOIN tb_Genero   G ON FG.id_Genero = G.id_Genero
INNER JOIN  tb_Filme   F ON FG.id_Filme = F.id_Filme
WHERE F.vl_Avaliacao > 4
ORDER BY F.nm_Filme;

### Exercício 6

/* DML 3 */
SELECT F.nm_Filme,
	   F.bt_Disponivel,
       F.vl_Avaliacao
FROM tb_Filme F
INNER JOIN tb_Genero G ON F.id_Genero = G.id_Genero
WHERE F.bt_Disponivel LIKE true
AND F.vl_Avaliacao > 8;

### Exercício 7

/* DML 4 */
SELECT F.nm_Filme,
	   F.vl_Avaliacao,
       F.dt_Lancamento,
       F.bt_Disponivel,
       G.ds_Genero
FROM tb_Filme_Genero   FG
INNER JOIN tb_Genero   G ON FG.id_Genero = G.id_Genero
INNER JOIN  tb_Filme   F ON FG.id_Filme = F.id_Filme
WHERE G.ds_Genero = 'Romance' OR G.ds_Genero ='Comédia';

### Exercício 8

/* DML 5 */
SELECT F.nm_Filme,
	   F.vl_Avaliacao,
       F.dt_Lancamento,
       F.bt_Disponivel,
       G.ds_Genero
FROM tb_Filme_Genero   FG
INNER JOIN tb_Genero   G ON FG.id_Genero = G.id_Genero
INNER JOIN  tb_Filme   F ON FG.id_Filme = F.id_Filme
WHERE F.bt_Disponivel = true AND G.ds_Genero = 'Terror';

SELECT G.ds_Genero,
	   F.nm_Filme
FROM tb_Filme_Genero   FG
INNER JOIN tb_Genero   G ON FG.id_Genero = G.id_Genero
INNER JOIN  tb_Filme   F ON FG.id_Filme = F.id_Filme
WHERE LENGTH(F.nm_Filme) > 16;

SELECT F.nm_Filme
FROM tb_Filme_Genero   FG
INNER JOIN tb_Genero   G ON FG.id_Genero = G.id_Genero
INNER JOIN  tb_Filme   F ON FG.id_Filme = F.id_Filme
WHERE F.nm_Filme NOT LIKE 'e%';

SELECT F.nm_Filme,	
	   F.dt_Lancamento
FROM tb_Filme_Genero   FG
INNER JOIN tb_Genero   G ON FG.id_Genero = G.id_Genero
INNER JOIN  tb_Filme   F ON FG.id_Filme = F.id_Filme
WHERE DAY(F.dt_Lancamento) > MONTH(F.dt_Lancamento);

SELECT F.nm_Filme,	
	   F.dt_Lancamento
FROM tb_Filme_Genero   FG
INNER JOIN tb_Genero   G ON FG.id_Genero = G.id_Genero
INNER JOIN  tb_Filme   F ON FG.id_Filme = F.id_Filme
WHERE MONTH(F.dt_Lancamento) IN (03,04,05) AND F.bt_Disponivel = false;

## *TABELA ALUNO*
### Exercício 1

CREATE TABLE tb_Aluno(
  id_Aluno int PRIMARY KEY AUTO_INCREMENT,
  nm_Turma varchar(100),
  nr_Ano_letivo int,
  nm_Aluno varchar(100),
  nr_Chamada int,
  ds_Sexo varchar(100),
  tp_Status varchar(1)
);


CREATE TABLE tb_Disciplina(
  id_Disciplina int PRIMARY KEY AUTO_INCREMENT,
  nm_Disciplina varchar(100),
  nm_Professor varchar(100)
);

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) ON DELETE CASCADE,
  FOREIGN KEY (id_Disciplina) REFERENCES tb_Disciplina (id_Disciplina) ON DELETE CASCADE
);

INSERT INTO tb_Aluno (nm_Turma, nr_Ano_letivo, nm_Aluno, nr_Chamada, ds_Sexo, tp_Status)
VALUES("A", 2021, "Igor Lima Meiado", 24, "Masculino", "T"),
	  ("B", 2015, "Arianae Porcon Grande", 19, "Feminino", "F"),
      ("C", 2013, "Erik Alexandre da Silva", 7, "Masculino", "F");
      
INSERT INTO tb_Disciplina (nm_Disciplina, nm_Professor)
VALUES	("Matemática", "Claúdio"),
		("Português", "Célio"),
		("Biologia", "Marta"),
		("Historia", "Rafael"),
		("Inglês", "Rosana");

INSERT INTO tb_Boletim(id_Aluno, id_Disciplina, vl_Nota, qtd_Faltas)
VALUES	(1, 1, 9.5, 0),
		(1, 2, 7.5, 1),
		(1, 3, 5, 0),
		(1, 4, 6.5, 2),
		(1, 5, 9, 0),
		(2, 1, 4.5, 1),
		(2, 2, 7, 3),
		(2, 3, 3.5, 4),
		(2, 4, 5, 7),
		(2, 5, 7, 0),
        (3, 1, 6, 2),
		(3, 2, 8, 3),
		(3, 3, 7.2, 5),
		(3, 4, 9, 4),
		(3, 5, 7, 3);
/* DDL 2 */
SELECT A.nm_Aluno,
	   A.nm_Turma,
       D.nm_Disciplina,
       D.nm_Professor,
       B.vl_Nota
FROM tb_Boletim  B
INNER JOIN tb_Aluno A ON B.id_Aluno = A.id_Aluno
INNER JOIN tb_Disciplina D ON B.id_Disciplina = D.id_Disciplina;

### Exercício 2

/* DDL 3 */
UPDATE tb_Aluno
SET nm_Aluno = 'Jailson Augusto das Costas Duidas'
WHERE id_Aluno = 3;

UPDATE tb_Disciplina
SET nm_Disciplina = "Geografia"
WHERE nm_Professor = "Eduardo";

UPDATE tb_Boletim
SET vl_Nota = 10
WHERE id_Boletim = 4;

UPDATE tb_Boletim
SET vl_Nota = 4.5
WHERE id_Boletim = 7;

### Exercício 3

/* DDL 4 */
DELETE FROM tb_Aluno
WHERE nm_Aluno = "Alex Araujo Correia";

DELETE FROM tb_Disciplina
WHERE nm_Disciplina ="Biologia";

### Exercício 4

/* DML 1 */
SELECT A.nm_Aluno,
	   A.nm_Turma,
       D.nm_Disciplina,
       B.vl_Nota
FROM tb_Boletim  B
INNER JOIN tb_Aluno A ON B.id_Aluno = A.id_Aluno
INNER JOIN tb_Disciplina D ON B.id_Disciplina = D.id_Disciplina
ORDER BY A.nm_Aluno;

### Exercício 5

/* DML 2 */
SELECT A.nm_Aluno,
	   A.nm_Turma,
       D.nm_Disciplina,
       B.vl_Nota
FROM tb_Boletim  B
INNER JOIN tb_Aluno A ON B.id_Aluno = A.id_Aluno
INNER JOIN tb_Disciplina D ON B.id_Disciplina = D.id_Disciplina
WHERE A.nm_Turma = "A" AND A.nr_Ano_letivo = 2021
ORDER BY B.vl_Nota DESC;

### Exercício 6

/* DML 3 */
SELECT A.nm_Aluno,
	   A.nm_Turma,
       D.nm_Disciplina,
       B.vl_Nota
FROM tb_Boletim  B
INNER JOIN tb_Aluno A ON B.id_Aluno = A.id_Aluno
INNER JOIN tb_Disciplina D ON B.id_Disciplina = D.id_Disciplina
WHERE B.vl_Nota >= 5 AND A.nm_Aluno = "Marina Fernandes Castro" AND A.nm_Turma = "B"
ORDER BY A.nr_Ano_letivo, A.nm_Turma, A.nr_Chamada;
..

### Exercício 7

/* DML 4 */
SELECT A.nm_Aluno,
	   A.nm_Turma,
       A.nr_Ano_letivo
FROM tb_Boletim  B
INNER JOIN tb_Aluno A ON B.id_Aluno = A.id_Aluno
INNER JOIN tb_Disciplina D ON B.id_Disciplina = D.id_Disciplina
WHERE B.vl_Nota < 5 AND A.nm_Aluno = "Julian Barros Ribeiro" AND A.nm_Turma = "A";

### Exercício 8

/* DML 5 */
SELECT A.nm_Aluno,
       A.tp_status     
FROM tb_Boletim  B
INNER JOIN tb_Aluno  A ON B.id_Aluno = A.id_Aluno
INNER JOIN tb_Disciplina  D ON B.id_Disciplina = D.id_Disciplina
WHERE A.tp_status = "T"

SELECT UPPER (A.nm_Aluno),
       B.vl_Nota     
FROM tb_Boletim  B
INNER JOIN tb_Aluno  A ON B.id_Aluno = A.id_Aluno
INNER JOIN tb_Disciplina  D ON B.id_Disciplina = D.id_Disciplina
WHERE B.vl_Nota BETWEEN 6 AND 9.5;

SELECT A.nm_Aluno    
FROM tb_Boletim  B
INNER JOIN tb_Aluno  A ON B.id_Aluno = A.id_Aluno
INNER JOIN tb_Disciplina  D ON B.id_Disciplina = D.id_Disciplina
WHERE B.qtd_Faltas = 0;

SELECT A.nm_Aluno,
	   A.nr_ano_letivo
FROM tb_Boletim  B
INNER JOIN tb_Aluno  A ON B.id_Aluno = A.id_Aluno
INNER JOIN tb_Disciplina  D ON B.id_Disciplina = D.id_Disciplina
WHERE A.ds_Sexo = "Feminino" AND A.nr_Ano_letivo < YEAR(NOW());

SELECT A.nm_Aluno
FROM tb_Boletim  B
INNER JOIN tb_Aluno  A ON B.id_Aluno = A.id_Aluno
INNER JOIN tb_Disciplina  D ON B.id_Disciplina = D.id_Disciplina
WHERE A.nm_Aluno LIKE "%os%";