librarysystem.sql

-- CREATE DATABASE LibTrackDB;
-- USE LibTrackDB;

-- CREATE TABLE Members (
--     MemberID INT PRIMARY KEY AUTO_INCREMENT,
--     FullName VARCHAR(100) NOT NULL,
--     Email VARCHAR(100) UNIQUE,
--     Phone VARCHAR(15),
--     DateJoined DATE
-- );

-- CREATE TABLE Books (
--     BookID INT PRIMARY KEY AUTO_INCREMENT,
--     Title VARCHAR(150) NOT NULL,
--     Author VARCHAR(100),
--     ISBN VARCHAR(20) UNIQUE,
--     Genre VARCHAR(50),
--     Status VARCHAR(20) DEFAULT 'Available'
-- );

-- CREATE TABLE Loans (
--     LoanID INT PRIMARY KEY AUTO_INCREMENT,
--     BookID INT,
--     MemberID INT,
--     BorrowDate DATE,
--     ReturnDate DATE,
--     FOREIGN KEY (BookID) REFERENCES Books(BookID) ON DELETE CASCADE,
--     FOREIGN KEY (MemberID) REFERENCES Members(MemberID) ON DELETE CASCADE
-- );

-- INSERT INTO Members (FullName, Email, Phone, DateJoined) VALUES
-- ('John Doe', 'john@gmail.com', '0711004551', '2026-01-10'),
-- ('Jane Smith', 'jane@gmail.com', '0711356002', '2026-01-12'),
-- ('Alice Wambui', 'alice@gmail.com', '0711546783', '2026-01-15'),
-- ('Bob Otieno', 'bob@gmail.com', '0711034764', '2026-01-20'),
-- ('Charlie Mwangi', 'charlie@gmail.com', '0711567005', '2026-02-01'),
-- ('Diana Rose', 'diana@gmail.com', '071100786', '2026-02-05'),
-- ('Evans Kip', 'evans@gmail.com', '0711006007', '2026-02-10');

-- INSERT INTO Books (Title, Author, ISBN, Genre) VALUES
-- ('Database Systems', 'C.J. Date', '978-01', 'Education'),
-- ('Java Programming', 'Herbert Schildt', '978-02', 'Programming'),
-- ('The Great Gatsby', 'F. Scott Fitzgerald', '978-03', 'Fiction'),
-- ('Clean Code', 'Robert Martin', '978-04', 'Programming'),
-- ('Digital Logic', 'Morris Mano', '978-05', 'Engineering'),
-- ('Introduction to AI', 'Stuart Russell', '978-06', 'Technology'),
-- ('Data Structures', 'Mark Weiss', '978-07', 'Programming');

-- INSERT INTO Loans (BookID, MemberID, BorrowDate, ReturnDate) VALUES
-- (1, 1, '2026-03-01', '2026-03-10'),
-- (2, 2, '2026-03-02', '2026-03-11'),
-- (3, 3, '2026-03-03', '2026-03-12'),
-- (4, 4, '2026-03-04', '2026-03-13'),
-- (5, 5, '2026-03-05', '2026-03-14'),
-- (6, 6, '2026-03-06', '2026-03-15'),
-- (7, 7, '2026-03-07', '2026-03-16');

-- SELECT Title, Author, Genre FROM Books WHERE Genre = 'Programming';


-- SELECT * FROM Members ORDER BY DateJoined DESC;

-- UPDATE Books SET Status = 'Borrowed' WHERE BookID = 1;


-- DELETE FROM Loans WHERE LoanID = 7;

-- SELECT Genre, COUNT(*) AS TotalBooks 
-- FROM Books 
-- GROUP BY Genre;

-- SELECT 
--     m.FullName AS Member_Name, 
--     b.Title AS Book_Borrowed, 
--     l.BorrowDate 
-- FROM Loans l
-- JOIN Members m ON l.MemberID = m.MemberID
-- JOIN Books b ON l.BookID = b.BookID;