CREATE DATABASE Biblioteca;
USE Biblioteca;
CREATE TABLE AUTORES (
autor_id INT AUTO_INCREMENT PRIMARY KEY,
nome_autor VARCHAR(255) NOT NULL,
nacionalidade VARCHAR(255) NOT NULL
);
CREATE TABLE ALUNOS (
aluno_id INT AUTO_INCREMENT PRIMARY KEY,
nome_do_aluno VARCHAR(255) NOT NULL,
matricula VARCHAR(255) NOT NULL UNIQUE,
curso VARCHAR(255) NOT NULL
);
CREATE TABLE LIVROS (
livro_id INT AUTO_INCREMENT PRIMARY KEY,
nome_do_livro VARCHAR(255) NOT NULL,
genero VARCHAR(255),
nacionalidade VARCHAR(255),
autor_id INT,
editora VARCHAR(255),
valor DECIMAL(10, 2),
FOREIGN KEY (autor_id) REFERENCES AUTORES(autor_id)
);
CREATE TABLE EMPRESTIMOS (
id_emprestimo INT AUTO_INCREMENT PRIMARY KEY,
id_livro INT,
id_aluno INT,
data_do_emprestimo DATE NOT NULL,
data_de_devolucao DATE,
FOREIGN KEY (id_livro) REFERENCES LIVROS(livro_id),
FOREIGN KEY (id_aluno) REFERENCES ALUNOS(aluno_id)
);
-- Quais são os livros disponíveis na biblioteca (ou seja, que ainda não
foram emprestados)? --
SELECT L.*
FROM LIVROS L
LEFT JOIN EMPRESTIMOS E ON L.livro_id = E.id_livro
WHERE E.id_livro IS NULL;
-- Qual é a nacionalidade dos autores que têm livros emprestados
atualmente?
SELECT DISTINCT [Link]
FROM AUTORES A
JOIN LIVROS L ON A.autor_id = L.autor_id
JOIN EMPRESTIMOS E ON L.livro_id = E.id_livro;
-- Quais livros foram emprestados pelo aluno de ID 101?
SELECT L.*
FROM LIVROS L
JOIN EMPRESTIMOS E ON L.livro_id = E.id_livro
WHERE E.id_aluno = 101;
-- Qual é o preço médio dos livros emprestados?
SELECT AVG([Link]) AS preco_medio
FROM LIVROS L
JOIN EMPRESTIMOS E ON L.livro_id = E.id_livro;
-- Quais são os livros que foram emprestados entre 2024-09-01 e 2024-
09-10?
SELECT L.*
FROM LIVROS L
JOIN EMPRESTIMOS E ON L.livro_id = E.id_livro
WHERE E.data_do_emprestimo BETWEEN '2024-09-01' AND '2024-09-10';
-- Qual autor tem o livro mais caro emprestado atualmente?
SELECT A.nome_autor
FROM AUTORES A
JOIN LIVROS L ON A.autor_id = L.autor_id
JOIN EMPRESTIMOS E ON L.livro_id = E.id_livro
WHERE [Link] = (SELECT MAX(valor) FROM LIVROS L2 JOIN EMPRESTIMOS
E2 ON L2.livro_id = E2.id_livro);
-- Quantos livros foram emprestados no total?
SELECT COUNT(*) AS total_emprestimos
FROM EMPRESTIMOS;
-- Qual é o número de livros emprestados por cada aluno?
SELECT E.id_aluno, COUNT(*) AS num_livros_emprestados
FROM EMPRESTIMOS E
GROUP BY E.id_aluno;
-- Quais livros foram devolvidos e em qual data?
SELECT L.*, E.data_de_devolucao
FROM LIVROS L
JOIN EMPRESTIMOS E ON L.livro_id = E.id_livro
WHERE E.data_de_devolucao IS NOT NULL;
-- Qual é a média de tempo (em dias) que os livros ficam emprestados?
SELECT AVG(DATEDIFF(E.data_de_devolucao, E.data_do_emprestimo)) AS
media_tempo_emprestimo
FROM EMPRESTIMOS E
WHERE E.data_de_devolucao IS NOT NULL;
-- Quais autores têm mais de um livro emprestado atualmente?
SELECT A.nome_autor
FROM AUTORES A
JOIN LIVROS L ON A.autor_id = L.autor_id
JOIN EMPRESTIMOS E ON L.livro_id = E.id_livro
GROUP BY A.nome_autor
HAVING COUNT(L.livro_id) > 1;
-- Qual é o total arrecadado com o empréstimo de livros (considerando o
preço dos livros emprestados)?
SELECT SUM([Link]) AS total_arrecadado
FROM LIVROS L
JOIN EMPRESTIMOS E ON L.livro_id = E.id_livro;
-- inserções
INSERT INTO AUTORES (nome_autor, nacionalidade) VALUES
('Gabriel García Márquez', 'Colombiano'),
('J.K. Rowling', 'Britânica'),
('George Orwell', 'Britânico'),
('Haruki Murakami', 'Japonês');
INSERT INTO ALUNOS (nome_do_aluno, matricula, curso) VALUES
('João Silva', '123456', 'Literatura'),
('Maria Oliveira', '789012', 'Filosofia'),
('Pedro Santos', '345678', 'História'),
('Ana Souza', '901234', 'Ciência da Computação'),
('Carlos Lima', '101112', 'Matemática');
INSERT INTO LIVROS (nome_do_livro, genero, nacionalidade, autor_id,
editora, valor) VALUES
('Cem Anos de Solidão', 'Romance', 'Colombiana', 1, 'Editora XYZ', 45.00),
('Harry Potter e a Pedra Filosofal', 'Fantasia', 'Britânica', 2, 'Editora ABC',
60.00),
('1984', 'Distopia', 'Britânica', 3, 'Editora DEF', 50.00),
('Kafka à Beira-Mar', 'Ficção', 'Japonês', 4, 'Editora GHI', 55.00);
INSERT INTO ALUNOS (aluno_id, nome_do_aluno, matricula, curso) VALUES
(101, 'Lucas Pereira', '112233', 'Engenharia');
INSERT INTO EMPRESTIMOS (id_livro, id_aluno, data_do_emprestimo,
data_de_devolucao) VALUES
(1, 101, '2024-09-05', NULL),
(2, 101, '2024-09-07', '2024-09-14');
-- imprimir tabelas --
SELECT * FROM AUTORES;
SELECT * FROM ALUNOS;
SELECT * FROM EMPRESTIMOS;
SELECT * FROM LIVROS;
