SQLize Online / PHPize Online  /  SQLtest Online

-- Create table use test; CREATE TABLE Branch ( BranchID INT PRIMARY KEY AUTO_INCREMENT, Location VARCHAR(255) NOT NULL ); CREATE TABLE Employee ( EmployeeID INT PRIMARY KEY AUTO_INCREMENT, Name VARCHAR(255) NOT NULL, Position VARCHAR(50) NOT NULL, BranchID INT, FOREIGN KEY (BranchID) REFERENCES Branch(BranchID) ); -- Now, add a manager field to the Branch table ALTER TABLE Branch ADD ManagerID INT; -- After adding ManagerID, create a foreign key constraint ALTER TABLE Branch ADD FOREIGN KEY (ManagerID) REFERENCES Employee(EmployeeID); CREATE TABLE Patron ( PatronID INT PRIMARY KEY AUTO_INCREMENT, Name VARCHAR(255) NOT NULL, MembershipStatus ENUM('Active', 'Inactive') NOT NULL ); CREATE TABLE Book ( BookID INT PRIMARY KEY AUTO_INCREMENT, Title VARCHAR(255) NOT NULL, Author VARCHAR(255) NOT NULL, Category VARCHAR(100), ISBN VARCHAR(20) UNIQUE NOT NULL ); CREATE TABLE Checkout ( TransactionID INT PRIMARY KEY AUTO_INCREMENT, BookID INT, PatronID INT, DateCheckedOut DATE NOT NULL, DueDate DATE NOT NULL, FOREIGN KEY (BookID) REFERENCES Book(BookID), FOREIGN KEY (PatronID) REFERENCES Patron(PatronID) ); CREATE TABLE Returns ( ReturnID INT PRIMARY KEY AUTO_INCREMENT, BookID INT, PatronID INT, DateReturned DATE NOT NULL, LateFees DECIMAL(10, 2) DEFAULT 0.00, FOREIGN KEY (BookID) REFERENCES Book(BookID), FOREIGN KEY (PatronID) REFERENCES Patron(PatronID) ); CREATE TABLE Payroll ( EmployeeID INT PRIMARY KEY, HoursWorked DECIMAL(5, 2) NOT NULL, Salary DECIMAL(10, 2) NOT NULL, PayDate DATE NOT NULL, FOREIGN KEY (EmployeeID) REFERENCES Employee(EmployeeID) ); -- populate the data base -- Insert libraries (branches) INSERT INTO Branch (Location, ManagerID) VALUES ('Downtown', NULL), -- Assume managers will be added later ('Eastside', NULL), ('Westside', NULL); -- Insert employees (5 per library) -- Downtown library employees INSERT INTO Employee (Name, Position, BranchID) VALUES ('Alice Smith', 'Librarian', 1), ('Bob Johnson', 'Assistant', 1), ('Cathy Lee', 'Assistant', 1), ('David Brown', 'Librarian', 1), ('Eve Davis', 'Assistant', 1); -- Eastside library employees INSERT INTO Employee (Name, Position, BranchID) VALUES ('Frank Wilson', 'Librarian', 2), ('Grace Taylor', 'Assistant', 2), ('Hannah White', 'Assistant', 2), ('Ian Harris', 'Librarian', 2), ('Julia Clark', 'Assistant', 2); -- Westside library employees INSERT INTO Employee (Name, Position, BranchID) VALUES ('Kevin Lewis', 'Librarian', 3), ('Laura Martin', 'Assistant', 3), ('Mike Walker', 'Assistant', 3), ('Nina Hall', 'Librarian', 3), ('Oscar Young', 'Assistant', 3); -- Insert patrons (10 per library) -- Downtown library patrons INSERT INTO Patron (Name, MembershipStatus) VALUES ('Peter Adams', 'Active'), ('Quinn Baker', 'Active'), ('Rita Collins', 'Inactive'), ('Sam Edwards', 'Active'), ('Tina Foster', 'Inactive'), ('Uma Green', 'Active'), ('Vera Hill', 'Active'), ('Willow Jackson', 'Inactive'), ('Xander King', 'Active'), ('Yara Lewis', 'Inactive'); -- Eastside library patrons INSERT INTO Patron (Name, MembershipStatus) VALUES ('Zane Martinez', 'Active'), ('Amy Nelson', 'Active'), ('Brian Ortiz', 'Inactive'), ('Clara Perez', 'Active'), ('Diana Rodriguez', 'Inactive'), ('Elena Sanchez', 'Active'), ('Felix Torres', 'Active'), ('Gabriel Valdez', 'Inactive'), ('Holly Walker', 'Active'), ('Isla Young', 'Inactive'); -- Westside library patrons INSERT INTO Patron (Name, MembershipStatus) VALUES ('Jack Zimmerman', 'Active'), ('Kelly Anderson', 'Inactive'), ('Laura Bell', 'Active'), ('Mike Carter', 'Active'), ('Nina Diaz', 'Inactive'), ('Owen Edwards', 'Active'), ('Pamela Fields', 'Active'), ('Quinn Garcia', 'Inactive'), ('Rick Hernandez', 'Active'), ('Sophie Johnson', 'Inactive'); -- Insert books (30 books across all libraries) INSERT INTO Book (Title, Author, Category, ISBN) VALUES ('Book A', 'Author A', 'Fiction', '1234567890123'), ('Book B', 'Author B', 'Non-Fiction', '1234567890124'), ('Book C', 'Author C', 'Science', '1234567890125'), ('Book D', 'Author D', 'History', '1234567890126'), ('Book E', 'Author E', 'Biography', '1234567890127'), ('Book F', 'Author F', 'Fiction', '1234567890128'), ('Book G', 'Author G', 'Non-Fiction', '1234567890129'), ('Book H', 'Author H', 'Science', '1234567890130'), ('Book I', 'Author I', 'History', '1234567890131'), ('Book J', 'Author J', 'Biography', '1234567890132'), ('Book K', 'Author K', 'Fiction', '1234567890133'), ('Book L', 'Author L', 'Non-Fiction', '1234567890134'), ('Book M', 'Author M', 'Science', '1234567890135'), ('Book N', 'Author N', 'History', '1234567890136'), ('Book O', 'Author O', 'Biography', '1234567890137'), ('Book P', 'Author P', 'Fiction', '1234567890138'), ('Book Q', 'Author Q', 'Non-Fiction', '1234567890139'), ('Book R', 'Author R', 'Science', '1234567890140'), ('Book S', 'Author S', 'History', '1234567890141'), ('Book T', 'Author T', 'Biography', '1234567890142'), ('Book U', 'Author U', 'Fiction', '1234567890143'), ('Book V', 'Author V', 'Non-Fiction', '1234567890144'), ('Book W', 'Author W', 'Science', '1234567890145'), ('Book X', 'Author X', 'History', '1234567890146'), ('Book Y', 'Author Y', 'Biography', '1234567890147'), ('Book Z', 'Author Z', 'Fiction', '1234567890148'), ('Book AA', 'Author AA', 'Non-Fiction', '1234567890149'), ('Book AB', 'Author AB', 'Science', '1234567890150'), ('Book AC', 'Author AC', 'History', '1234567890151'), ('Book AD', 'Author AD', 'Biography', '1234567890152'); -- Insert checkouts (at least a few per library) INSERT INTO Checkout (BookID, PatronID, DateCheckedOut, DueDate) VALUES (1, 1, '2024-01-01', '2024-01-15'), (2, 2, '2024-01-03', '2024-01-17'), (3, 3, '2024-01-05', '2024-01-19'), (4, 4, '2024-01-07', '2024-01-21'), (5, 5, '2024-01-09', '2024-01-23'); -- Eastside library checkouts INSERT INTO Checkout (BookID, PatronID, DateCheckedOut, DueDate) VALUES (6, 11, '2024-01-02', '2024-01-16'), (7, 12, '2024-01-04', '2024-01-18'), (8, 13, '2024-01-06', '2024-01-20'), (9, 14, '2024-01-08', '2024-01-22'), (10, 15, '2024-01-10', '2024-01-24'); -- Westside library checkouts INSERT INTO Checkout (BookID, PatronID, DateCheckedOut, DueDate) VALUES (11, 21, '2024-01-02', '2024-01-16'), (12, 22, '2024-01-04', '2024-01-18'), (13, 23, '2024-01-06', '2024-01-20'), (14, 24, '2024-01-08', '2024-01-22'), (15, 25, '2024-01-10', '2024-01-24'); -- Insert returns for the checkouts INSERT INTO Returns (BookID, PatronID, DateReturned, LateFees) VALUES (1, 1, '2024-01-14', 0.00), (2, 2, '2024-01-15', 1.50), (3, 3, '2024-01-16', 0.00), (6, 11, '2024-01-15', 0.00), (7, 12, '2024-01-17', 0.00), (11, 21, '2024-01-15', 0.00), (12, 22, '2024-01-18', 2.00); -- Insert payroll data for Downtown library employees INSERT INTO Payroll (EmployeeID, HoursWorked, Salary, PayDate) VALUES (1, 40.00, 3000.00, '2024-01-31'), -- Alice Smith (2, 35.00, 2500.00, '2024-01-31'), -- Bob Johnson (3, 30.00, 2200.00, '2024-01-31'), -- Cathy Lee (4, 40.00, 3200.00, '2024-01-31'), -- David Brown (5, 25.00, 1800.00, '2024-01-31'); -- Eve Davis -- Insert payroll data for Eastside library employees INSERT INTO Payroll (EmployeeID, HoursWorked, Salary, PayDate) VALUES (6, 40.00, 3100.00, '2024-01-31'), -- Frank Wilson (7, 30.00, 2400.00, '2024-01-31'), -- Grace Taylor (8, 35.00, 2500.00, '2024-01-31'), -- Hannah White (9, 40.00, 3300.00, '2024-01-31'), -- Ian Harris (10, 25.00, 1900.00, '2024-01-31'); -- Julia Clark -- Insert payroll data for Westside library employees INSERT INTO Payroll (EmployeeID, HoursWorked, Salary, PayDate) VALUES (11, 40.00, 3000.00, '2024-01-31'), -- Kevin Lewis (12, 30.00, 2300.00, '2024-01-31'), -- Laura Martin (13, 35.00, 2400.00, '2024-01-31'), -- Mike Walker (14, 40.00, 3200.00, '2024-01-31'), -- Nina Hall (15, 25.00, 1800.00, '2024-01-31'); -- Oscar Young -- Develop Essential Queries and SQL Statements -- 1. Register a new patron to the system. INSERT INTO Patron (Name, MembershipStatus) VALUES ('John Doe', 'Active'); -- 2. Update patron information. UPDATE Patron SET Name = 'John Smith', MembershipStatus = 'Inactive' WHERE PatronID = 1; -- 3. Record a new book being added to the inventory. INSERT INTO Book (Title, Author, Category, ISBN) VALUES ('The Great Gatsby', 'F. Scott Fitzgerald', 'Fiction', '9780743273565'); -- 4. Record a book checkout for a patron. INSERT INTO Checkout (BookID, PatronID, DateCheckedOut, DueDate) VALUES (1, 1, '2024-11-01', '2024-11-15'); -- 5. Record the return of a book, including late fees (if applicable). INSERT INTO Returns (BookID, PatronID, DateReturned, LateFees) VALUES (1, 1, '2024-11-10', 0.00); -- 6. Retrieve a list of overdue books. SELECT b.Title, b.Author, p.Name, c.DueDate FROM Checkout c JOIN Book b ON c.BookID = b.BookID JOIN Patron p ON c.PatronID = p.PatronID WHERE c.DueDate < CURDATE(); -- 7. Retrieve a patron's borrowing history. SELECT b.Title, c.DateCheckedOut, r.DateReturned, r.LateFees FROM Checkout c JOIN Book b ON c.BookID = b.BookID LEFT JOIN Returns r ON c.BookID = r.BookID AND c.PatronID = r.PatronID WHERE c.PatronID = 1; -- 8. Update employee hours worked for payroll purposes. UPDATE Payroll SET HoursWorked = 40.00 WHERE EmployeeID = 1; -- 9. Generate a payroll statement for a library branch. SELECT e.Name, p.HoursWorked, p.Salary, p.PayDate FROM Payroll p JOIN Employee e ON p.EmployeeID = e.EmployeeID WHERE e.BranchID = 1; -- Replace with the BranchID -- 10. Retrieve a list of available books by category. SELECT Title, Author FROM Book WHERE BookID NOT IN (SELECT BookID FROM Checkout); -- 11. Transfer a employee from one branch to another. UPDATE Employee SET BranchID = 2 WHERE EmployeeID = 1; -- 12. Retrieve a list of all employees working at a specific branch. SELECT Name, Position FROM Employee WHERE BranchID = 1; -- 13. Generate a list of books borrowed within the last 30 days. SELECT b.Title, b.Author, c.DateCheckedOut FROM Checkout c JOIN Book b ON c.BookID = b.BookID WHERE c.DateCheckedOut >= DATE_SUB(CURDATE(), INTERVAL 30 DAY); -- 14. Remove a patron’s account. DELETE FROM Patron WHERE PatronID = 7; -- 15. Promote an employee to a new position. UPDATE Employee SET Position = 'Senior Librarian' WHERE EmployeeID = 1; -- Write stored procedures where needed to simplify complex transactions, such as multiple -- updates for checkouts and returns. -- Stored Procedure to Handle Checkout -- This procedure records a book checkout for a patron and ensures that the book is available. DELIMITER // CREATE PROCEDURE CheckoutBook ( IN p_BookID INT, IN p_PatronID INT, IN p_DateCheckedOut DATE, IN p_DueDate DATE ) BEGIN DECLARE available_count INT; -- check if the book is available (not checked out) SELECT COUNT(*) INTO available_count FROM Checkout WHERE BookID = p_BookID AND PatronID = p_PatronID; IF available_count = 0 THEN INSERT INTO Checkout (BookID, PatronID, DateCheckedOut, DueDate) VALUES (p_BookID, p_PatronID, p_DateCheckedOut, p_DueDate); ELSE SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Book is already checked out by this patron.'; END IF; END // DELIMITER ; -- test case CALL CheckoutBook(1, 1, '2024-01-20', '2024-02-03'); -- Define User Roles and Permissions -- Create roles for the library system CREATE ROLE Librarian; CREATE ROLE BranchManager; CREATE ROLE Assistant; -- Grant permissions to Librarian GRANT INSERT, UPDATE, DELETE ON Book TO Librarian; -- Manage inventory GRANT INSERT, UPDATE ON Patron TO Librarian; -- Register patrons GRANT INSERT ON Checkout TO Librarian; -- Record checkouts GRANT INSERT ON Returns TO Librarian; -- Record returns GRANT ALL PRIVILEGES ON Employee TO BranchManager; -- Manage employees (update schedules, view payroll) GRANT ALL PRIVILEGES ON Payroll TO BranchManager; -- Manage payroll -- Grant permissions to Assistant GRANT INSERT ON Checkout TO Assistant; -- Record checkouts GRANT INSERT ON Returns TO Assistant; -- Record returns -- Assign roles to individual users GRANT Librarian TO 'librarian_user'@'localhost'; GRANT BranchManager TO 'manager_user'@'localhost'; GRANT Assistant TO 'assistant_user'@'localhost'; SET ROLE Librarian;