code.sql

-- Create Database
CREATE DATABASE library_db;
USE library_db;

-- ========================
-- TABLE: Branch
-- ========================
CREATE TABLE Branch (
    branch_id VARCHAR(10) PRIMARY KEY,
    manager_id VARCHAR(10),
    address VARCHAR(50),
    phone VARCHAR(15)
);

-- ========================
-- TABLE: Employee
-- ========================
CREATE TABLE Employee (
    emp_id VARCHAR(10) PRIMARY KEY,
    name VARCHAR(40),
    role VARCHAR(30),
    salary DECIMAL(10,2),
    branch_id VARCHAR(10),
    FOREIGN KEY (branch_id) REFERENCES Branch(branch_id)
);

-- ========================
-- TABLE: Customer
-- ========================
CREATE TABLE Customer (
    cust_id VARCHAR(10) PRIMARY KEY,
    name VARCHAR(40),
    address VARCHAR(50),
    registered_on DATE
);

-- ========================
-- TABLE: Books
-- ========================
CREATE TABLE Books (
    isbn VARCHAR(25) PRIMARY KEY,
    title VARCHAR(80),
    category VARCHAR(30),
    price DECIMAL(10,2),
    availability ENUM('Available','Issued'),
    author VARCHAR(40),
    publisher VARCHAR(40)
);

-- ========================
-- TABLE: Issue Records
-- ========================
CREATE TABLE Issue (
    issue_id VARCHAR(10) PRIMARY KEY,
    cust_id VARCHAR(10),
    isbn VARCHAR(25),
    issue_date DATE,
    FOREIGN KEY (cust_id) REFERENCES Customer(cust_id) ON DELETE CASCADE,
    FOREIGN KEY (isbn) REFERENCES Books(isbn) ON DELETE CASCADE
);

-- ========================
-- TABLE: Return Records
-- ========================
CREATE TABLE ReturnLog (
    return_id VARCHAR(10) PRIMARY KEY,
    cust_id VARCHAR(10),
    isbn VARCHAR(25),
    return_date DATE,
    FOREIGN KEY (isbn) REFERENCES Books(isbn) ON DELETE CASCADE
);

-- ========================
-- SAMPLE DATA INSERTION
-- ========================

INSERT INTO Branch VALUES
('B01','M101','Main Street','9990001111'),
('B02','M102','Elm Street','9990001112');

INSERT INTO Employee VALUES
('E01','John Doe','Manager',60000,'B01'),
('E02','Jane Smith','Clerk',45000,'B01'),
('E03','Mike','Librarian',55000,'B02');

INSERT INTO Customer VALUES
('C01','Alice','City A','2021-05-10'),
('C02','Bob','City B','2022-01-15');

INSERT INTO Books VALUES
('ISBN1','Book A','Fiction',500,'Available','Author A','Pub A'),
('ISBN2','Book B','History',600,'Issued','Author B','Pub B');

INSERT INTO Issue VALUES
('I01','C01','ISBN2','2023-05-01');

INSERT INTO ReturnLog VALUES
('R01','C01','ISBN2','2023-06-01');

-- ========================
-- QUERIES
-- ========================

-- 1. Available books
SELECT title, category, price 
FROM Books 
WHERE availability = 'Available';

-- 2. Employees sorted by salary
SELECT name, salary 
FROM Employee 
ORDER BY salary DESC;

-- 3. Issued books with customer names
SELECT b.title, c.name
FROM Issue i
JOIN Books b ON i.isbn = b.isbn
JOIN Customer c ON i.cust_id = c.cust_id;

-- 4. Book count per category
SELECT category, COUNT(*) AS total
FROM Books
GROUP BY category;

-- 5. Employees earning > 50000
SELECT name, role
FROM Employee
WHERE salary > 50000;

-- 6. Customers with no issued books
SELECT name
FROM Customer
WHERE cust_id NOT IN (SELECT cust_id FROM Issue);

-- 7. Employee count per branch
SELECT branch_id, COUNT(*) AS total_staff
FROM Employee
GROUP BY branch_id;

-- 8. Customers who issued books in June 2023
SELECT DISTINCT c.name
FROM Customer c
JOIN Issue i ON c.cust_id = i.cust_id
WHERE i.issue_date BETWEEN '2023-06-01' AND '2023-06-30';

-- 9. Books in History category
SELECT title 
FROM Books 
WHERE category = 'History';

-- 10. Branches with more than 2 employees
SELECT branch_id, COUNT(*) AS total
FROM Employee
GROUP BY branch_id
HAVING COUNT(*) > 2;