Library_DB_Analysis.sql

create database library_database;

use library_database;

select *from publisher;
select *from books;
select *from authors;

alter table authors add book_authors_AuthorID INT PRIMARY KEY AUTO_INCREMENT;

select *from `library branch`;

rename table `library branch` to library_branch;

select *from library_branch;

alter table library_branch add  library_branch_BranchID INT PRIMARY KEY AUTO_INCREMENT;

select * from `book copies` ;

rename table `book copies` to book_copies;

select *from book_copies;

alter table book_copies add book_copies_CopiesID INT PRIMARY KEY AUTO_INCREMENT;

select *from borrower;

select *from `book loans`;

rename table `book loans` to book_loans;

select *from book_loans;

alter table book_loans add book_loans_LoansID INT PRIMARY KEY AUTO_INCREMENT;

alter table book_copies rename column book_copies_BookID to book_copies_BookID;

alter table books rename column book_BookID to book_BookID;

alter table authors rename column book_authors_BookID to book_authors_BookID;

alter table book_loans rename column book_loans_BookID to book_loans_BookID;

-- task qoestions

-- 1.How many copies of the book titled "The Lost Tribe" are owned by the library branch whose name is "Sharpstown"?
select bc.book_copies_No_Of_Copies from  books as b
join book_copies as bc on b.book_BookID = bc.book_copies_BookID 
join library_branch as lb on lb.library_branch_BranchID=bc.book_copies_BranchID 
where b.book_title="The Lost Tribe" and lb.library_branch_BranchName="Sharpstown" ;

-- 2.How many copies of the book titled "The Lost Tribe" are owned by each library branch?
select lb.library_branch_BranchID,lb.library_branch_BranchName, sum(bc.book_copies_No_Of_Copies) 
as total_copies from  books as b join book_copies as bc on b.book_BookID = bc.book_copies_BookID 
join library_branch as lb on lb.library_branch_BranchID=bc.book_copies_BranchID 
where b.book_title="The Lost Tribe" group by lb.library_branch_BranchName,lb.library_branch_BranchID ;

 
-- 3.Retrieve the names of all borrowers who do not have any books checked out.
select borrower_CardNo,borrower_BorrowerName from borrower 
 as b where not exists(select *from book_loans as bl 
 where b.borrower_CardNo=bl.book_loans_CardNo);


-- 4.For each book that is loaned out from the "Sharpstown" branch and whose DueDate is 2/3/18, retrieve the book title, the borrower's name, and the borrower's address. 
select b.book_Title,br.borrower_BorrowerName,br.borrower_BorrowerAddress from books as b 
join book_loans as bl on b.book_BookID=bl.book_loans_BookID 
join library_branch as lb on lb.library_branch_BranchID=bl.book_loans_BranchID 
join borrower as br on br.borrower_CardNo=bl.book_loans_CardNo 
where lb.library_branch_BranchName="Sharpstown" and bl.book_loans_DueDate='2/3/18'; 


-- 5.For each library branch, retrieve the branch name and the total number of books loaned out from that branch.
select lb.library_branch_BranchName,count(bl.book_loans_BranchID) 
as total_books_loaned from library_branch as lb 
join book_loans as bl on bl.book_loans_branchID=lb.library_branch_BranchID
group by lb.library_branch_BranchName ;





-- 6.Retrieve the names, addresses, and number of books checked out for all borrowers who have more than five books checked out.
select br.borrower_BorrowerName,br.borrower_BorrowerAddress,
count(bl.book_loans_CardNo)as total_checked from borrower as br 
join book_loans as bl on br.borrower_CardNo=bl.book_loans_CardNo
group by br.borrower_BorrowerName,br.borrower_BorrowerAddress having total_checked>5;


-- 7.For each book authored by "Stephen King", retrieve the title and the number of copies owned by the library branch whose name is "Central".
select b.book_title,bc.book_copies_No_Of_Copies from books  as b 
join authors as a on b.book_BookID=a.book_authors_BookID join book_copies as bc on b.book_BookID=bc.book_copies_BookID 
join library_branch as lb on lb.library_branch_BranchID=bc.book_copies_BranchID 
where a.book_authors_AuthorName="Stephen King" and lb.library_branch_BranchName="Central" ;