randomquiers.sql

use library;

create table reservation(
reservation_id int primary key,
student_id int,
reservation_date date,
book_id int not null,
status varchar(50) default 'pending',

foreign key (student_id) references students(student_id),
foreign key (book_id) references books(book_id));

insert into reservation(reservation_id,student_id,reservation_date,book_id) values (301,101,'2025-04-25',3);

select * from reservation;



delimiter //
create trigger fulfil_reservation_after_return
after insert on returns for each row 

begin
declare return_book_id int;
declare reservation_id_to_fulfil int;

select book_id into return_book_id
from issued_books where issue_id = new.return_issue_id;

select reservation_id into reservation_id_to_fulfil
from reservation
where book_id = return_book_id and status ='pending'
order by reservation_date asc limit 1;

if reservation_id_to_fulfil is not null then

update reservation set status = 'fulfilled'
where reservation_id = reservation_id_to_fulfil;

end if;

end; // 

delimiter ;

select * from books where copies_available<10;

update books set copies_available = 1 where book_id = 13;

select * from books where book_id = 13;

select * from students;

insert into issued_books values(08,108,13,'2025-04-25');

insert into reservation values(302,106,'2025-04-26',13,'pending');

insert into returns(return_id,return_issue_id,return_date) values (208,08,'2025-05-20');

select * from returns;

select * from books;

desc issued_books;
desc students;
desc books;

create view issued_books_students as 
select issued_books.issue_id as issued_books,
students.name as students,
books.title as books_title
from issued_books
inner join students on issued_books.issued_student_id = students.student_id
inner join books on issued_books.book_id = books.book_id;

select * from issued_books_students;

select * from reservation;
select * from issued_books;

select reservation.reservation_id as r_id,
reservation.reservation_date as r_date,
reservation.status as r_status,
students.name AS student_name,
books.title AS book_title
from reservation
INNER join students on reservation.student_id = students.student_id
INNER join books on reservation.book_id = books.book_id;


select returns.return_date,
issued_books.issue_id,
students.name,
books.title
from returns
INNER JOIN issued_books ON returns.return_issue_id = issued_books.issue_id
INNER JOIN students ON issued_books.issued_student_id = students.student_id
INNER JOIN books ON issued_books.book_id = books.book_id;

select students.student_id,
issued_books.issue_id,
issued_books.issue_date, 
students.name,
books.title,
books.book_id
from students
left join issued_books on issued_student_id = students.student_id
left join books on issued_books.book_id = books.book_id;

desc reservation;

select books.book_id,
books.title,
books.authorid,
reservation.reservation_id,
reservation.reservation_date,
reservation.student_id,
reservation.status
from books 
left join reservation on books.book_id = reservation.book_id;

select * from category;

select books.book_id,
books.title,
category.Category_name,
count(books.Book_category_id) as total_book_available
from category
LEFT JOIN books ON category.category_id = books.book_id
group by category.category_id, category.category_name;


SELECT 
  books.book_id,
  books.title,
  issued_books.issue_id,
  issued_books.issue_date
FROM books
LEFT JOIN issued_books ON books.book_id = issued_books.book_id;

select students.name,
reservation.reservation_date,
reservation.reservation_id,
reservation.status
from students
left join reservation on reservation.student_id = students.student_id;

select * from reservation;
desc category;
desc books;

create view issued_books_with_name_booktitle_category as 
select students.name,
books.title,
category.category_name
from issued_books
inner join books on books.book_id = issued_books.book_id
inner join students on issued_books.issued_student_id = students.student_id
inner join category on category.category_id = books.book_category_id;

select * from issued_books_with_name_booktitle_category;

select return_date.returns,
book_id.issued_books,
title.books,
students.student_id,
students.name
from students
inner join returns on return_issue_id = issued_books.issue_id
inner join books on issed_books.book_id = books.book_id
inner join students on issued_books.issued_student_id = students.student_id;