class_work.sql

create database class;
use class;

create table author(
author_id int primary key,
name varchar(20) not null,
nationality varchar(20) not null
);

create table book(
book_id int primary key,
title varchar(20) not null,
isbn varchar(20) not null,
publisher varchar(20) not null,
year_published int not null
);

create table member(
member_id int primary key,
name varchar(20) not null,
email varchar(20) not null,
phone varchar(20) not null,
join_date date not null
);

create table loan(
book_id int,
member_id int,
loan_id int primary key,
barrow_date date not null,
return_date date not null,
foreign key (book_id) references book(book_id),
foreign key (member_id) references member(member_id));

#bridge table of book and author
create table book_author(
book_id int,
author_id int,
foreign key (book_id) references book(book_id),
foreign key (author_id) references author(author_id),
primary key (book_id, author_id)
);

#inserting value
insert 
into author values(1, "trishna", "napal");
insert into book values(2, "idea", "raam", "shayam", "2016");
insert into member values(3, "susma", "susma@gmail.com", "988888", "2025-03-12");
insert into loan values(2, 3, 4, "2025-02-15", "2025-02-15");
insert into book_author values (2, 1);

CREATE VIEW loan_details AS # loan_detail view ko name ho
SELECT 
  loan.loan_id, #aba loan table bata loan_id lai select garem
  member.name, #member table bata name lai select garem
  book.title,  #book table bata title column lai select garem
  author.name as author_name,  # author table bata name lai select garem and hamro loan_detail bhaney view table ma 2 tw name column thiyo so auta lai name nai rakhem
  #ani arko lai chai author_name gardiem aba yo column loan_detail bhaney view table ma as a author_name ley save huncha
  loan.barrow_date,  #loan table bata barrow_date liem
  loan.return_date  #loan table bata return_date liem
FROM loan  #sabai table like member, book, book_author, author lai loan saga merge garnw lako
JOIN member ON loan.member_id = member.member_id  #loan table ko member_id rw member table ko member_id ko help bata merge gara bhaney ko
JOIN book ON loan.book_id = book.book_id  #same as above
JOIN book_author ON book.book_id = book_author.book_id  #same as above
JOIN author ON book_author.author_id = author.author_id;  #same as above
SELECT * FROM loan_details;  #loan_detail bhaney view table ko sabai column lai select garey ko