cotemig.sql

drop database if exists cotemig;
create database if not exists cotemig;
use cotemig;

create table if not exists departamento(
	codigodepartamento int primary key,
    nomedepartamento varchar(100) not null
) engine=innodb;

create table if not exists aluno(
	codigoaluno int primary key,
    nomealuno varchar(100) not null,
    idade int not null
) engine=innodb;

create table if not exists disciplina(
	codigodisciplina int primary key,
    nomedisciplina varchar(100) not null,
    codigodepartamentodisciplina int not null,
    foreign key (codigodepartamentodisciplina) references departamento(codigodepartamento)
		on update cascade
) engine=innodb;

create table if not exists situacao(
	codigosituacao int primary key,
    nomesituacao varchar(100) not null
) engine=innodb;

create table if not exists matricula_aluno(
	codigoalunomatricula int not null,
    codigodisciplinamatricula int not null,
    ano timestamp not null,
    codigosituacaomatricula int not null,
    primary key (codigoalunomatricula, codigodisciplinamatricula, ano),
    foreign key (codigoalunomatricula) references aluno(codigoaluno)
		on update cascade,
	foreign key (codigodisciplinamatricula) references disciplina(codigodisciplina)
		on update cascade,
	foreign key (codigosituacaomatricula) references situacao(codigosituacao)
		on update cascade
) engine=innodb;

create or replace view info_aluno as
select
	nomealuno,
    nomedisciplina,
    ano,
    nomesituacao,
    nomedepartamento
from
	aluno
		inner join matricula_aluno on codigoalunomatricula = codigoaluno
        inner join disciplina on codigodisciplina = codigodisciplinamatricula
        inner join situacao on codigosituacao = codigosituacaomatricula
        inner join departamento on codigodepartamento = codigodepartamentodisciplina;
	
create or replace view qtde_aluno_ano as
select
	count(codigoaluno) as qtde_aluno,
    year(ano) as ano_matricula
from
	aluno
		inner join matricula_aluno on codigoalunomatricula = codigoaluno
group by
	year(ano);

create or replace view qtde_disciplinas_departamento as
select
	codigodepartamento,
    nomedepartamento,
	count(codigodisciplina) as qtde_disciplinas
from
	departamento
        inner join disciplina on codigodepartamentodisciplina = codigodepartamento
group by
	codigodepartamento, nomedepartamento;

create table if not exists recuperacao(
	codigoalunorecuperacao int not null,
    codigodisciplinarecuperacao int not null,
    nota decimal(5,2),
    primary key (codigoalunorecuperacao, codigodisciplinarecuperacao),
	foreign key (codigoalunorecuperacao) references aluno(codigoaluno)
		on update cascade,
	foreign key (codigodisciplinarecuperacao) references disciplina(codigodisciplina)
		on update cascade
) engine=innodb;
	
create table if not exists log_recuperacao(
	idlog int auto_increment primary key,
    descricao text,
    data timestamp not null
) engine=innodb;

delimiter %

create trigger trig_inserir_recuperacao
before insert on recuperacao
for each row
begin
	declare v_texto text;
    declare v_nome_aluno varchar(100);
    declare v_nome_disciplina varchar(100);
    
    select nomealuno into v_nome_aluno from aluno
    where codigoaluno = new.codigoalunorecuperacao;
    
    select nomedisciplina into v_nome_disciplina from disciplina
    where codigodisciplina = new.codigodisciplinarecuperacao;
    
    set v_texto = concat('foi incluído na recuperação: ', v_nome_aluno,' - para a disciplina: ', v_nome_disciplina);
    
	insert into log_recuperacao(descricao, data) values
		(v_texto, current_timestamp());
end%

create trigger trig_alterar_recuperacao
after update on recuperacao
for each row
begin
	declare v_texto text;
    declare v_nome_aluno varchar(100);
    
    select nomealuno into v_nome_aluno from aluno
	where codigoaluno = new.codigoalunorecuperacao;
    
    set v_texto = concat('foi alterada a nota do aluno ', v_nome_aluno,': ', old.nota,' -> ', new.nota);
    
	insert into log_recuperacao(descricao, data) values
		(v_texto, current_timestamp());
end%

create trigger trig_deletar_matricula_aluno
before delete on matricula_aluno
for each row
begin
	declare v_texto text;
    declare v_nome_disciplina varchar(100);
    declare v_nome_aluno varchar(100);
    
    select nomedisciplina into v_nome_disciplina from disciplina 
	where codigodisciplina = old.codigodisciplinamatricula;
    
    select nomealuno into v_nome_aluno from aluno
    where codigoaluno = old.codigoalunomatricula;
    
    set v_texto = concat('a matrícula de: ', v_nome_aluno, ' - na disciplina: ', v_nome_disciplina, ' (ano: ', year(old.ano), ') - foi excluída.');
    
	insert into log_recuperacao(descricao, data) values
		(v_texto, current_timestamp());
end%

create procedure sp_atualizar_recuperacao()
begin
    start transaction;
    
    insert into recuperacao (codigoalunorecuperacao, codigodisciplinarecuperacao, nota)
    select
        m.codigoalunomatricula,
        m.codigodisciplinamatricula,
        null
    from
        matricula_aluno m
        inner join situacao s on m.codigosituacaomatricula = s.codigosituacao
    where
        s.nomesituacao like '%reprovado%'
    on duplicate key update
        nota = null;
        
    commit;
end%

create function fn_status_aluno(notaaluno decimal(5,2)) returns text deterministic
begin
	declare v_retornar text;
    
    select
		case 
			when notaaluno >= 60 then 'aprovado - boas férias'
            else 'ainda há mais uma chance! bora estudar'
		end
	into v_retornar;
    
    return v_retornar;
end%

create procedure sp_excluir_matricula_ano(in param_ano int)
begin
	if (param_ano > 0) then
		delete from matricula_aluno
		where year(ano) = param_ano;
    end if;
end%

delimiter ;

call sp_atualizar_recuperacao();
call sp_excluir_matricula_ano(2023);


-- =============================================================================
-- ATENÇÃO: As consultas abaixo são APENAS para fins de estudo/referência.
-- Elas utilizam o banco de dados "world" (disponível por padrão no MySQL),
-- que contém as tabelas: country, city e countrylanguage.
-- NÃO fazem parte do banco "cotemig" criado acima.
-- Para executá-las, certifique-se de estar conectado ao banco "world":
--     USE world;
-- =============================================================================

-- -----------------------------------------------------------------------------
-- Questão 1: Liste o nome dos países (country.name) e o nome das cidades
-- (city.name) que são capitais desses países.
-- Lógica: a coluna country.capital armazena o ID (city.id) da cidade capital,
-- então fazemos o JOIN entre country e city por essa relação.
-- -----------------------------------------------------------------------------
-- show tables;
-- select * from city;
-- select * from country;
-- select * from countrylanguage;

SELECT
    country.name  AS pais,
    city.name     AS capital
FROM country
INNER JOIN city ON country.capital = city.id;

-- -----------------------------------------------------------------------------
-- Questão 2: Liste o nome das cidades (city.name) e o nome dos países
-- (country.name) que pertencem ao continente 'South America'.
-- Lógica: filtramos pelo campo country.continent e juntamos com city
-- usando a relação capital -> city.id (apenas as capitais).
-- Para listar TODAS as cidades do continente, substituir a condição do JOIN
-- por: city.countrycode = country.code
-- -----------------------------------------------------------------------------

SELECT
    country.name  AS pais,
    city.name     AS capital
FROM country
INNER JOIN city
    ON country.capital = city.id
WHERE country.continent = 'South America';

-- -----------------------------------------------------------------------------
-- Questão 3: Mostre o nome dos países (country.name), o nome das cidades
-- (city.name) e a população das cidades (city.population) para países cuja
-- língua oficial é 'English'.
-- Lógica: fazemos JOIN entre city, country e countrylanguage;
-- filtramos por language = 'English' e isofficial = 'T' (true) para garantir
-- que a língua seja de fato oficial no país.
-- -----------------------------------------------------------------------------

SELECT
    country.name       AS pais,
    city.name          AS cidade,
    city.population    AS populacao
FROM city
JOIN country
    ON city.countrycode = country.code
JOIN countrylanguage
    ON country.code = countrylanguage.countrycode
WHERE countrylanguage.language  = 'English'
  AND countrylanguage.isofficial = 'T';