-- ATIVIDADE 05/11/2026 - PROF. CARLOS VALLIM
-- Use este arquivo no MySQL Online: https://[Log in to view URL]
-- Os dados de criacao e cadastro foram mantidos conforme "20261105_Exercício 3.txt".
-- Execute todo o arquivo para exibir os resultados na ordem das atividades.

-- 1) CRIACAO DAS TABELAS E INSERCAO DOS REGISTROS
-- A ordem de exclusao permite executar o arquivo novamente se o ambiente for persistente.
DROP TABLE IF EXISTS Alunos;
DROP TABLE IF EXISTS Cursos;

CREATE TABLE Cursos (
  Categoria CHAR(1) PRIMARY KEY,
  Descricao VARCHAR(100),
  Coordenador VARCHAR(100)
);

CREATE TABLE Alunos (
  ID INT PRIMARY KEY,
  Nome VARCHAR(100) NOT NULL,
  Categoria CHAR(1),
  Rate INT,
  eMail VARCHAR(100),
  Cidade VARCHAR(50),
  Data_Cadastro DATE,
  CONSTRAINT fk_Categoria
    FOREIGN KEY (Categoria) REFERENCES Cursos(Categoria)
);

INSERT INTO Cursos (Categoria, Descricao, Coordenador) VALUES
('A', 'Informatica', 'Graziella'),
('B', 'Quimica', 'Pedro'),
('C', 'Enfermagem', 'Thais'),
('D', 'Alimentos', 'Antonio'),
('E', 'Seguranca', 'Augusto'),
('F', 'Recursos Humanos', 'Jose');

INSERT INTO Alunos (ID, Nome, Categoria, Rate, eMail, Cidade, Data_Cadastro) VALUES
(1, 'Carlos', 'A', 60, 'carlos@gmail.com', 'Mogi Guacu', '2026-10-08'),
(2, 'Jose', 'C', 70, 'jose@gmail.com', 'Itapira', '2026-10-08'),
(3, 'Joaquim', 'C', 80, 'joaquim@gmail.com', 'Mogi Mirim', '2026-10-08'),
(4, 'Ana', 'D', 80, 'ana@hotmail.com', 'Itapira', '2026-10-08'),
(5, 'Paulo', 'E', 70, 'paulo@gmail.com', 'Itapira', '2026-10-08'),
(6, 'Marisa', 'E', 50, 'marisa@hotmail.com', 'Itapira', '2026-10-08'),
(7, 'Pedro', 'E', 60, 'pedro@hotmail.com', 'Lindoia', '2026-10-08');

SELECT 'EXERCICIO 1: 7 alunos inseridos' AS Etapa;
SELECT * FROM Alunos ORDER BY ID;
SELECT 'EXERCICIO 1: 6 cursos inseridos' AS Etapa;
SELECT * FROM Cursos ORDER BY Categoria;

-- 2) CONSULTA DE DADOS (DQL)
SELECT 'EXERCICIO 2A: todos os alunos' AS Etapa;
SELECT * FROM Alunos ORDER BY ID;

SELECT 'EXERCICIO 2B: nome e email' AS Etapa;
SELECT Nome, eMail FROM Alunos ORDER BY ID;

SELECT 'EXERCICIO 2C: Rate maior que 70' AS Etapa;
SELECT * FROM Alunos WHERE Rate > 70 ORDER BY ID;

SELECT 'EXERCICIO 2D: alunos que utilizam Gmail' AS Etapa;
SELECT * FROM Alunos WHERE eMail LIKE '%gmail.com' ORDER BY ID;

SELECT 'EXERCICIO 2E: aluno e descricao do curso' AS Etapa;
SELECT Alunos.Nome, Cursos.Descricao
FROM Alunos INNER JOIN Cursos
ON Alunos.Categoria = Cursos.Categoria
ORDER BY Alunos.ID;

-- 3) ATUALIZACAO DE DADOS (DML)
UPDATE Alunos SET Rate = 65 WHERE Nome = 'Carlos';
UPDATE Alunos SET Rate = 60 WHERE Nome = 'Marisa';
SELECT 'EXERCICIO 3: Carlos 65 e Marisa 60' AS Etapa;
SELECT ID, Nome, Rate FROM Alunos WHERE Nome IN ('Carlos', 'Marisa') ORDER BY ID;

-- 4) EXCLUSAO DE DADOS
DELETE FROM Alunos WHERE Nome = 'Joaquim';
SELECT 'EXERCICIO 4: Joaquim excluido (restam 6 alunos)' AS Etapa;
SELECT * FROM Alunos ORDER BY ID;

-- 5) COMMIT E ROLLBACK
-- O PDF pede que Carlos comece com Rate 60 nos dois casos.
-- Como o exercicio 3 mudou Carlos para 65, restauramos 60 primeiro.
UPDATE Alunos SET Rate = 60 WHERE Nome = 'Carlos';
SELECT 'EXERCICIO 5: Carlos antes das transacoes' AS Etapa;
SELECT Nome, Rate FROM Alunos WHERE Nome = 'Carlos';

-- Caso 1: COMMIT torna permanente a mudanca de 60 para 70.
START TRANSACTION;
UPDATE Alunos SET Rate = Rate + 10 WHERE Nome = 'Carlos';
COMMIT;
SELECT 'EXERCICIO 5 - COMMIT: Carlos fica com 70' AS Etapa;
SELECT Nome, Rate FROM Alunos WHERE Nome = 'Carlos';

-- Caso 2: restauramos o valor inicial de 60 para demonstrar o ROLLBACK.
UPDATE Alunos SET Rate = 60 WHERE Nome = 'Carlos';
START TRANSACTION;
UPDATE Alunos SET Rate = Rate + 10 WHERE Nome = 'Carlos';
ROLLBACK;
SELECT 'EXERCICIO 5 - ROLLBACK: Carlos continua com 60' AS Etapa;
SELECT Nome, Rate FROM Alunos WHERE Nome = 'Carlos';

-- 6) PROPRIEDADES ACID - RESPOSTAS TEORICAS
-- Atomicidade: tudo ou nada.
-- Consistencia: mantem regras do banco.
-- Isolamento: transacoes nao interferem entre si.
-- Durabilidade: dados permanecem apos falhas.