Script Banco Biblitoeca_Resolvido.txt

CREATE DATABASE BIBLIOTECA;
USE BIBLIOTECA;

-- Tabela de Tipos de Usuários
CREATE TABLE TiposDeUsuarios (
    TipoUsuarioID INT PRIMARY KEY,
    Nome VARCHAR(50) NOT NULL
);

-- Tabela de Usuários
CREATE TABLE Usuarios (
    UsuarioID INT PRIMARY KEY,
    Nome VARCHAR(100) NOT NULL,
    TipoUsuarioID INT,
    FOREIGN KEY (TipoUsuarioID) REFERENCES TiposDeUsuarios(TipoUsuarioID)
);

-- Tabela de Livros
CREATE TABLE Livros (
    LivroID INT PRIMARY KEY,
    Titulo VARCHAR(255) NOT NULL,
    Autor VARCHAR(100) NOT NULL,
    AnoPublicacao INT,
    Disponivel BOOLEAN NOT NULL
);

-- Tabela de Empréstimos
CREATE TABLE Emprestimos (
    EmprestimoID INT PRIMARY KEY,
    UsuarioID INT,
    LivroID INT,
    DataEmprestimo DATE NOT NULL,
    DataDevolucaoPrevista DATE NOT NULL,
    FOREIGN KEY (UsuarioID) REFERENCES Usuarios(UsuarioID),
    FOREIGN KEY (LivroID) REFERENCES Livros(LivroID)
);

-- Tabela de Devoluções
CREATE TABLE Devolucoes (
    DevolucaoID INT PRIMARY KEY,
    EmprestimoID INT,
    DataDevolucao DATE NOT NULL,
    Penalidade DECIMAL(10, 2),
    FOREIGN KEY (EmprestimoID) REFERENCES Emprestimos(EmprestimoID)
);

-- Inserindo dados
-- Inserir registros na tabela TiposDeUsuarios
INSERT INTO TiposDeUsuarios (TipoUsuarioID, Nome)
VALUES
    (1, 'Estudante'),
    (2, 'Professor'),
    (3, 'Funcionário'),
    (4, 'Visitante'),
    (5, 'Bibliotecário');

-- Inserir registros na tabela Usuarios
INSERT INTO Usuarios (UsuarioID, Nome, TipoUsuarioID)
VALUES
    (1, 'João Silva', 1),
    (2, 'Maria Santos', 2),
    (3, 'Pedro Alves', 1),
    (4, 'Ana Pereira', 3),
    (5, 'Lucas Oliveira', 4);

-- Inserir registros na tabela Livros
INSERT INTO Livros (LivroID, Titulo, Autor, AnoPublicacao, Disponivel)
VALUES
    (1, 'Dom Casmurro', 'Machado de Assis', 1899, true),
    (2, 'A Revolução dos Bichos', 'George Orwell', 1945, true),
    (3, 'Cem Anos de Solidão', 'Gabriel García Márquez', 1967, true),
    (4, 'O Senhor dos Anéis', 'J.R.R. Tolkien', 1954, true),
    (5, 'Harry Potter e a Pedra Filosofal', 'J.K. Rowling', 1997, true);

-- Inserir registros na tabela Emprestimos
INSERT INTO Emprestimos (EmprestimoID, UsuarioID, LivroID, DataEmprestimo, DataDevolucaoPrevista)
VALUES
    (1, 1, 2, '2023-08-15', '2023-09-15'),
    (2, 3, 1, '2023-08-20', '2023-09-20'),
    (3, 2, 4, '2023-08-25', '2023-09-25'),
    (4, 4, 3, '2023-08-30', '2023-09-30'),
    (5, 5, 5, '2023-09-01', '2023-10-01');

-- Inserir registros na tabela Devolucoes
INSERT INTO Devolucoes (DevolucaoID, EmprestimoID, DataDevolucao, Penalidade)
VALUES
    (1, 1, '2023-09-16', 0.00),
    (2, 2, '2023-09-21', 0.00),
    (3, 3, '2023-09-28', 0.00),
    (4, 4, '2023-10-05', 0.00),
    (5, 5, '2023-10-02', 0.00);
-- Retornando dados

-- ------------------------------------------------------------------------------------------------------

-- 1 - Selecione todos os livros emprestados atualmente com os nomes dos usuários:
SELECT L.Titulo, U.Nome AS NomeUsuario
FROM Emprestimos AS E
INNER JOIN Livros AS L ON E.LivroID = L.LivroID
INNER JOIN Usuarios AS U ON E.UsuarioID = U.UsuarioID;

-- 2 - Listar todos os livros cujos títulos começam com a letra 'A':
SELECT *
FROM Livros
WHERE Titulo LIKE 'A%';

-- 3 - Encontre todos os empréstimos com uma data de devolução prevista após 2023-09-15:
SELECT *
FROM Emprestimos
WHERE DataDevolucaoPrevista > '2023-09-15';

-- 4 - Liste todos os usuários que não são do tipo 'Funcionário':
SELECT *
FROM Usuarios
WHERE TipoUsuarioID != (SELECT TipoUsuarioID FROM TiposDeUsuarios WHERE Nome = 'Funcionário');

-- 5 - Listar todos os empréstimos de livros feitos por usuários do tipo 'Estudante', incluindo os detalhes do livro emprestado:
SELECT U.Nome AS NomeUsuario, L.*
FROM Emprestimos AS E
INNER JOIN Usuarios AS U ON E.UsuarioID = U.UsuarioID
INNER JOIN Livros AS L ON E.LivroID = L.LivroID
WHERE U.TipoUsuarioID = (SELECT TipoUsuarioID FROM TiposDeUsuarios WHERE Nome = 'Estudante');

-- 6 - Listar os empréstimos de livros com suas devoluções correspondentes, incluindo os nomes dos usuários:
SELECT U.Nome AS NomeUsuario, E.*, D.*
FROM Emprestimos AS E
INNER JOIN Usuarios AS U ON E.UsuarioID = U.UsuarioID
LEFT JOIN Devolucoes AS D ON E.EmprestimoID = D.EmprestimoID;

-- 7 - Encontre o total de empréstimos feitos por cada usuário e ordene os resultados pelo número de empréstimos em ordem decrescente:
SELECT U.Nome AS NomeUsuario, COUNT(E.EmprestimoID) AS TotalEmprestimos
FROM Usuarios AS U
LEFT JOIN Emprestimos AS E ON U.UsuarioID = E.UsuarioID
GROUP BY U.Nome
ORDER BY TotalEmprestimos DESC;

-- 8 - Encontre o número total de empréstimos por ano de publicação dos livros e ordene os resultados pelo ano em ordem crescente:
SELECT L.AnoPublicacao, COUNT(E.EmprestimoID) AS TotalEmprestimos
FROM Livros AS L
LEFT JOIN Emprestimos AS E ON L.LivroID = E.LivroID
GROUP BY L.AnoPublicacao
ORDER BY L.AnoPublicacao;

-- 9 - Liste todos os livros que não foram emprestados ainda:
SELECT *
FROM Livros
WHERE LivroID NOT IN (SELECT LivroID FROM Emprestimos);

-- 10 - Mostre o número total de empréstimos para cada usuário:
SELECT U.Nome AS NomeUsuario, COUNT(E.EmprestimoID) AS TotalEmprestimos
FROM Usuarios AS U
LEFT JOIN Emprestimos AS E ON U.UsuarioID = E.UsuarioID
GROUP BY U.Nome;

-- 11 - Encontre o autor com a maior quantidade de livros emprestados:
SELECT L.Autor, COUNT(E.LivroID) AS TotalLivrosEmprestados
FROM Livros AS L
INNER JOIN Emprestimos AS E ON L.LivroID = E.LivroID
GROUP BY L.Autor
ORDER BY TotalLivrosEmprestados DESC
LIMIT 1;

-- 12 - Liste todos os livros emprestados por usuários do tipo "Estudante":
SELECT L.*
FROM Livros AS L
INNER JOIN Emprestimos AS E ON L.LivroID = E.LivroID
INNER JOIN Usuarios AS U ON E.UsuarioID = U.UsuarioID
WHERE U.TipoUsuarioID = (SELECT TipoUsuarioID FROM TiposDeUsuarios WHERE Nome = 'Estudante');

-- 13 - Mostre a média das penalidades de devolução:
SELECT AVG(Penalidade) AS MediaPenalidades
FROM Devolucoes;

-- 14 - Liste os livros emprestados que foram publicados entre 1990 e 2000:
SELECT L.*
FROM Livros AS L
INNER JOIN Emprestimos AS E ON L.LivroID = E.LivroID
WHERE L.AnoPublicacao BETWEEN 1990 AND 2000;

-- 15 - Encontre os usuários que não fizeram nenhum empréstimo:
SELECT U.*
FROM Usuarios AS U
LEFT JOIN Emprestimos AS E ON U.UsuarioID = E.UsuarioID
WHERE E.UsuarioID IS NULL;

-- 16 - Liste todos os livros emprestados por usuários do tipo "Estudante" OU "Professor":
SELECT L.*
FROM Livros AS L
INNER JOIN Emprestimos AS E ON L.LivroID = E.LivroID
INNER JOIN Usuarios AS U ON E.UsuarioID = U.UsuarioID
WHERE U.TipoUsuarioID = (SELECT TipoUsuarioID FROM TiposDeUsuarios WHERE Nome = 'Estudante')
OR U.TipoUsuarioID = (SELECT TipoUsuarioID FROM TiposDeUsuarios WHERE Nome = 'Professor');

-- 17 - Mostre o total de empréstimos por usuário e filtre apenas aqueles com mais de 3 empréstimos:
SELECT U.Nome AS NomeUsuario, COUNT(E.EmprestimoID) AS TotalEmprestimos
FROM Usuarios AS U
LEFT JOIN Emprestimos AS E ON U.UsuarioID = E.UsuarioID
GROUP BY U.Nome
HAVING COUNT(E.EmprestimoID) > 3;

-- 18 - Listar todos os livros emprestados por usuários do tipo "Estudante" ou "Professor", agrupados por autor.
SELECT L.Autor, L.Titulo, U.Nome AS NomeUsuario
FROM Livros AS L
INNER JOIN Emprestimos AS E ON L.LivroID = E.LivroID
INNER JOIN Usuarios AS U ON E.UsuarioID = U.UsuarioID
WHERE U.TipoUsuarioID = (SELECT TipoUsuarioID FROM TiposDeUsuarios WHERE Nome = 'Estudante')
OR U.TipoUsuarioID = (SELECT TipoUsuarioID FROM TiposDeUsuarios WHERE Nome = 'Professor')
GROUP BY L.Autor, L.Titulo, U.Nome;