CREATE TABLE cursos(
    id INT AUTO_INCREMENT PRIMARY KEY,
    nome_curso VARCHAR(150) NOT NULL,
    duracao_meses INT
);

CREATE TABLE alunos( 
    id INT AUTO_INCREMENT PRIMARY KEY,
    nome VARCHAR(100) NOT NULL,
    idade INT,
    curso_id INT,
    nota_final DECIMAL(5,2),
    FOREIGN KEY (curso_id) REFERENCES cursos(id)
);

CREATE TABLE livros(
    id INT PRIMARY KEY AUTO_INCREMENT,
    titulo VARCHAR(150) NOT NULL,
    autor VARCHAR(150) NOT NULL,
    ano_publicacao INT,
    preco DECIMAL(10,2)
);

CREATE TABLE emprestimos(
    id INT PRIMARY KEY AUTO_INCREMENT,
    livro_id INT,
    aluno_id INT,
    data_emprestimo DATE,
    data_devolucao DATE,
    FOREIGN KEY (livro_id) REFERENCES livros(id),
    FOREIGN KEY (aluno_id) REFERENCES alunos(id)
);

INSERT INTO cursos(id, nome_curso, duracao_meses) VALUES
(1, 'Ciência da Computação', 48),
(2, 'Administração', 36),
(3, 'Engenharia Civil', 60);
    
INSERT INTO alunos(id, nome, idade, curso_id, nota_final) VALUES
(1, 'Ana Paula Rodrigues', 20, 1, 8.5),
(2, 'Bruno Tavares', 22, 2, 4.0),
(3, 'Carlos Santiago', 25, 1, 9.0),
(4, 'Amanda Silva', 23, 3, 6.5),
(5, 'Diego da Silva', 25, 2, 10.0);

INSERT INTO livros(id, titulo, autor, ano_publicacao, preco) VALUES
(1, 'Dom Quixote', 'Miguel de Cervantes', 1605, 50.0),
(2, '1984', 'George Orwell', 1949, 35.0),
(3, 'O Senhor dos Anéis', 'J.R.R. Tolkien', 1954, 70.0),
(4, 'Orgulho e Preconceito', 'Jane Austen', 1813, 40.0),
(5, 'Cem Anos de Solidão', 'Gabriel García Márquez', 1967, 60.0);

INSERT INTO emprestimos(id, livro_id, aluno_id, data_emprestimo, data_devolucao) VALUES
(1, 1, 1, '2023-01-10', '2023-01-20'),
(2, 2, 2, '2023-02-15', '2023-02-25'),
(3, 3, 3, '2023-03-20', NULL),
(4, 4, 4, '2023-04-05', '2023-04-15'),
(5, 5, 5, '2023-05-12', NULL);

-- Atividade 1: Consulta com JOIN e Funções de Agregação
SELECT c.nome_curso, AVG(a.nota_final) AS media_notas
FROM cursos c
INNER JOIN alunos a ON c.id = a.curso_id
GROUP BY c.nome_curso
HAVING AVG(a.nota_final) > 7.0;

-- Atividade 2: Consulta com LEFT JOIN e IS NULL
SELECT l.titulo, l.autor
FROM livros l
LEFT JOIN emprestimos e ON l.id = e.livro_id
WHERE e.id IS NULL;

-- Atividade 3: Consulta com BETWEEN e ORDER BY
SELECT 
    l.titulo,
    l.autor,
    l.ano_publicacao
FROM livros l
WHERE ano_publicacao BETWEEN 1900 AND 2000
ORDER BY ano_publicacao ASC;

-- Atividade 4: Consulta com CASE e Funções de Agregação
SELECT
    a.nome,
    a.nota_final,
    CASE
        WHEN a.nota_final >= 7.0 THEN 'Aprovado'
        WHEN a.nota_final BETWEEN 5.0 AND 6.9 THEN 'Recuperação'
        ELSE 'Reprovado'
    END AS classificacao
FROM alunos a;

-- Atividade 5: Consulta com UNION e DISTINCT
SELECT DISTINCT a.nome
FROM alunos a
INNER JOIN emprestimos e ON a.id = e.aluno_id
UNION
SELECT DISTINCT a.nome
FROM alunos a
WHERE a.nota_final > 8.0;

-- Atividade 6: Consulta com EXISTS e Subconsulta
SELECT c.nome_curso
FROM cursos c
WHERE EXISTS (
    SELECT 1
    FROM alunos a
    WHERE a.curso_id = c.id AND a.nota_final > 9.0
);

-- Atividade 7: Consulta com RIGHT JOIN e COUNT:
SELECT c.nome_curso, COUNT(a.id) AS quantidade_alunos
FROM alunos a
RIGHT JOIN cursos c ON a.curso_id = c.id
GROUP BY c.nome_curso;