queries.sql

USE readease_db;

-- SELECT: view all students.
SELECT student_id, name, email, department FROM Student;

-- SELECT with WHERE: find books that contain a search word.
SELECT * FROM Book WHERE title LIKE '%Code%';

-- INNER JOIN: show student names with borrowed books.
SELECT
  s.name AS student_name,
  b.title AS book_title,
  br.borrow_date,
  br.due_date,
  br.return_date
FROM BorrowRecord br
INNER JOIN Student s ON br.student_id = s.student_id
INNER JOIN BookCopy bc ON br.copy_id = bc.copy_id
INNER JOIN Book b ON bc.book_id = b.book_id;

-- LEFT JOIN: show all books, even books that do not yet have authors.
SELECT
  b.title,
  COALESCE(GROUP_CONCAT(a.author_name SEPARATOR ', '), 'No author added') AS authors
FROM Book b
LEFT JOIN BookAuthor ba ON b.book_id = ba.book_id
LEFT JOIN Author a ON ba.author_id = a.author_id
GROUP BY b.book_id, b.title;

-- Aggregate function: show total and available copies per book.
SELECT
  b.title,
  COUNT(bc.copy_id) AS total_copies,
  SUM(CASE WHEN bc.status = 'Available' THEN 1 ELSE 0 END) AS available_copies
FROM Book b
LEFT JOIN BookCopy bc ON b.book_id = bc.book_id
GROUP BY b.book_id, b.title;

-- Show overdue books.
SELECT
  s.name,
  b.title,
  br.due_date,
  DATEDIFF(CURDATE(), br.due_date) AS overdue_days
FROM BorrowRecord br
INNER JOIN Student s ON br.student_id = s.student_id
INNER JOIN BookCopy bc ON br.copy_id = bc.copy_id
INNER JOIN Book b ON bc.book_id = b.book_id
WHERE br.return_date IS NULL AND br.due_date < CURDATE();

-- Show books by category.
SELECT
  c.category_name,
  b.title
FROM Category c
INNER JOIN BookCategory bcat ON c.category_id = bcat.category_id
INNER JOIN Book b ON bcat.book_id = b.book_id
ORDER BY c.category_name, b.title;

-- UPDATE: change a student department.
UPDATE Student
SET department = 'Artificial Intelligence'
WHERE student_id = 1;

-- UPDATE: mark a copy as borrowed.
UPDATE BookCopy
SET status = 'Borrowed'
WHERE copy_id = 2;

-- DELETE: remove a category relationship from one book.
DELETE FROM BookCategory
WHERE book_id = 5 AND category_id = 5;

-- DELETE: remove a book copy that is not currently borrowed.
DELETE FROM BookCopy
WHERE copy_id = 8 AND status = 'Available';