CREATE TABLE Cursos (
     id_curso INT AUTO_INCREMENT PRIMARY KEY,
     nome_curso VARCHAR(100) NOT NULL,
     descricao VARCHAR(200)
);

CREATE TABLE Professores (
    id_professor INT AUTO_INCREMENT PRIMARY KEY,
    nome VARCHAR(100) NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    especialidade VARCHAR(100)
);

CREATE TABLE Alunos (
    id_aluno INT AUTO_INCREMENT PRIMARY KEY,
    matricula VARCHAR(20) UNIQUE NOT NULL,
    nome VARCHAR(100) NOT NULL,
    idade INT CHECK (idade > 0)
);

CREATE TABLE Matriculas (
    id_matricula INT AUTO_INCREMENT PRIMARY KEY,
    id_aluno INT NOT NULL,
    id_curso INT NOT NULL,
    id_professor INT,
    data_matricula DATE NOT NULL DEFAULT (CURRENT_DATE),

   FOREIGN KEY (id_aluno) REFERENCES Alunos(id_aluno) ON DELETE CASCADE,
   FOREIGN KEY (id_curso) REFERENCES Cursos(id_curso) ON DELETE CASCADE,
   FOREIGN KEY (id_professor) REFERENCES Professores(id_professor) ON DELETE SET NULL
);
   
INSERT INTO Cursos (nome_curso, descricao) VALUES
('Banco de Dados SQL', 'Introdução á modelagem e manipulação de banco de dados relacionais.'),
('Desenvolvimento Web', 'Conceitos basicos de HTML, CSS e Javascript.'),
('Estruturas de dados', 'Conceitos de listas, filas, pilhas e árvores.'),
('Lógicas de Programação', 'Fundamentos de algoritmos e resolução de problemas.'),
('Engenharia de Software', 'Metodologias Àgeis, ciclo de vida e requisitos de software.'),
('Redes de Computadores', 'Arquiteturas de redes, protocolos ICP/IP e segurança.'),
('Sistemas Operacionais', 'Gerenciamento de memória, processos  e sistemas da arquivos'),
('Inteligencia Artificial', 'Conceitos de aprendizado de máquina e redes neurais.'),
('Segurança da Informação', 'Criptografia, boas práticas e defesa cibernética.'),
('Computação em Nuvem', 'Fundamentos da infraestrutura AWS, Azure e GCP.');

INSERT INTO Professores (nome, email, especialidade) VALUES
('Carlos Silva', 'carlos.silva@escola.com', 'Banco de Dados'),
('Ana Moser', 'moser.ana@escola.com', 'Progamação Front End'),
('Roberto Alves', 'alves.roberto@escola.com', 'Algoritmos'),
('Fernando Costa', 'costa.fernando@escola.com', 'Engenharia de Software'),
('Ricardo Lima', 'lima.ricardo@escola.com', 'Redes de Computadores'),
('Patricia Souza', 'souza.patricia@escola.com', 'Sistemas Operacionais'),
('Jorge Ferraz', 'ferraz.jorge@escola.com', 'Inteligencia Artificial'),
('mauricio de Souza', 'souza.mauricio@escola.com', 'Segurança da Informação'),
('Fernanda Mine', 'mine.fernanda@escola.com', 'Computação em nuvem'),
('Lucas Silva', 'silva.lucas@escola.com', 'Lógica da Programação'),
('Maria Pereira', 'pereira.maria@escol.com', 'Programação Front-End'),
('João Freitas', 'freitas.joão@escola.com', 'Segurança d informação'),
('Melissa Mendez', 'mendez.melissa@escola.com', 'Inteligencia Artificial'),
('Andre Terra', 'terra.andre@escola.com', 'Redes de Computadores'),
('Mateus Martins', 'silva.mateus@escola.com', 'Segurança da Informação'),
('Anderson Silva', 'silva.anderson@escola.com', 'Redes de Computadores');

INSERT INTO Alunos (matricula, nome, idade) VALUES
('A2026001', 'john Wayne', 16),
('A2026002', 'Amanda Nunes', 17),
('A2026003', 'Elvis Silva', 18),
('A2026004', 'Sara Cristina', 15),
('A2026005', 'Samantha Pessoa', 15),
('A2026006', 'Lucas Gabriel', 16),
('A2026007', 'Gabriel Santos', 17),
('A2026008', 'Larissa Ferreira', 16),
('A2026009', 'Enzo Coelho', 15),
('A2026010', 'Isabela Marins', 18),
('A2026011', 'Ingrid Guimarães', 17),
('A2026012', 'Anatoly Karpov', 18),
('A2026013', 'magnus Carslen', 16),
('A2026014', 'Robert Fischer', 17),
('A2026015', 'Ivan Lima', 15),
('A2026016', 'Eduardo Cabral', 16),
('A2026017', 'Antonio Souza', 19),
('A2026018', 'Albert Silva', 18),
('A2026019', 'Lavinia Vlasak', 17),
('A2026020', 'Joseph Silva', 18);

INSERT INTO Matriculas (id_aluno, id_curso, id_professor, data_matricula) VALUES
(1, 1, 1, '2026-02-01'),
(2, 2, 2, '2026-02-01'),
(3, 3, 3, '2026-02-02'),
(4, 4, 10, '2026-02-02'),
(5, 5, 4, '2026-02-03'),
(6, 6, 5, '2026-02-03'),
(7, 7, 6, '2026-02-04'),
(8, 8, 7, '2026-02-04'),
(9, 9, 8, '2026-02-05'),
(10, 10, 9, '2026-02-05'),
(11, 7, 10, '2026-03-06'),
(8, 6, 5, '2026-03-07'),
(12, 7, 4, '2026-04-09'),
(6, 10, 9, '2026-05-06'),
(1, 8, 7, '2026-06-08');

/*SELECT * FROM Alunos;
SELECT * FROM Professores;
SELECT * FROM Cursos;*/

/*SELECT Nome, Matricula, Idade
FROM Alunos
WHERE Idade >= 18;*/

/*SELECT Nome, Matricula, Idade
FROM Alunos
WHERE Idade < 18;*/

/*SELECT nome, Matricula
FROM Alunos ORDER BY nome ASC;*/

/*SELECT Nome, Matricula
FROM Alunos ORDER BY Matricula DESC;*/

/*SELECT
a.nome AS Aluno,
c.nome_curso AS Curso,
p.nome AS Professor,
m.data_matricula matricula m
INNER JOIN Alunos a ON m.id_aluno - a.id_aluno
INNER JOIN Curso c ON m.id_curso - c.id_curso
LEFT JOIN Professores p ON m.id_professor - p.id_professor;*/

CREATE VIEW vw_detalhes_matricula AS
SELECT
    m.id_matricula,
    a.matricula AS matricula_aluno,
    a.nome AS nome_aluno,
    c.nome_curso,
    p.nome AS nome_professor,
    m.data_matricula
FROM Matriculas m
INNER JOIN Alunos a ON m.id_aluno = a.id_aluno
INNER JOIN Cursos c ON m.id_curso = c.id_curso
LEFT JOIN Professores p ON m.id_professor = p.id_professor;

/*SELECT * FROM vw_detalhes_matricula
WHERE nome_curso = 'Banco de Dados SQL';*/

CREATE VIEW vw_alunos_por_professor AS
SELECT
     p.nome AS professor,
    p.especialidade,
    COUNT(m.id_aluno) AS quantidade_alunos
FROM professores p
LEFT JOIN matriculas m ON p.id_professor = m.id_professor
GROUP BY p.id_professor, p.nome, p.especialidade;

SELECT * FROM vw_alunos_por_professor
WHERE especialidade = 'lnteligência Artificial';

/*SELECT id_aluno, matricula, nome
FROM Alunos
WHERE nome LIKE 'J%';*/

/*SELECT nome, email, especialidade
FROM Professores
WHERE email LIKE '%@escola.com';*/

/*SELECT nome, idade
FROM  Alunos
ORDER BY idade ASC*/

/*SELECT id_curso, nome_curso, descricao
FROM Cursos
ORDER BY nome_curso ASC;*/

/*SELECT nome, idade
FROM Alunos
WHERE idade IN (16,18);*/

/*SELECT AVG(idade) AS menor_idade,
FROM Alunos;*/

/*SELECT
     MIN(idade) AS menor_idade,
     MAX(idade) AS maior_idade
FROM Alunos;*/

/*SELECT id_matricula, id_aluno, id_curso
FROM Matriculas
WHERE id_professor IS NULL;*/

/*SELECT
     a.nome AS Aluno
     c.nome_curso AS Curso
FROM Alunos a
INNER JOIN Matriculas m ON a.id_aluno = m.id_aluno
INNER JOIN Cursos c ON m.id_curso = c.id_curso;*/

/*SELECT
     a.nome AS Aluno_Sem_Matricula
FROM Alunos a
LEFT JOIN Matriculas m ON a.id_aluno = m.id_aluno
WHERE m.id_matricula is null;*/

/*SELECT
    p.nome AS Professor
    m.id_matricula
FROM Matriculas a
RIGHT JOIN Professores p ON m.id_professor  = p.id_professor;*/

/*SELECT
      a.nome AS Aluno,
      c.nome_curso AS Curso
FROM Aluno a
CROSS JOIN Cursos c;*/