create database library;

use  library;

CREATE TABLE book (
accno bigint(10)  primary key, 
title varchar(50) not null, 
author varchar(35), 
price bigint(10),
isbn char (13)
);

CREATE TABLE members (
memo varchar(10)  primary key, 
name varchar(35), 
maxbooks int(5)not null,
maxdays int (5)not null
);


CREATE TABLE book_hold (
hold_id bigint(10) primary key auto_increment,
book_accno bigint(10),
FOREIGN KEY (book_accno) REFERENCES book (accno),
members_memo varchar(10),
FOREIGN KEY (members_memo) REFERENCES members (memo),
start_date date,
end_date date
);



INSERT INTO book values (101,"Social Processes","S. Sharma",300,"81-266-2078-1");
INSERT INTO book values (102,"Fundamentals of IT","P. Dubey",600,"71-265-2077-2");
INSERT INTO book (accno, title, author, price, isbn) values (103,"Management Information System","Lauden & Lauden",700,"72-265-2076-3");
INSERT INTO book (accno, title, author, price, isbn) values (104,"Artificial Intelligence","D W Patterson",800,"73-265-2075-4");
INSERT INTO book (accno, title, author, price, isbn) VALUES (105,"Software Enginnering","Roger S Presman",1000,"70-265-2074-5");
INSERT INTO book (accno, title, author, price, isbn) VALUES (106,"Software Enginnering","Roger S Presman",1000,"70-265-2074-5");

INSERT INTO members (memo, name, maxbooks, maxdays) values ("M01","Gian Singh",4,15);
INSERT INTO members (memo, name, maxbooks, maxdays) values ("M02","A S Rao",4,15);
INSERT INTO members (memo, name, maxbooks, maxdays) values ("M03","S Malik",2,15);
INSERT INTO members (memo, name, maxbooks, maxdays) values ("M04","S Dua",2,15);
INSERT INTO members (memo, name, maxbooks, maxdays) values ("M05","R Rai",2,15);

INSERT INTO book_hold (book_accno, members_memo, start_date, end_date) values (101,"M01",'2007/04/05','2007/04/20');
INSERT INTO book_hold (book_accno, members_memo, start_date, end_date) values (104,"M02",'2007/04/10','2007/04/25');
INSERT INTO book_hold (book_accno, members_memo, start_date, end_date) values (101,"M03",'2007/04/15','2007/04/30');
INSERT INTO book_hold (book_accno, members_memo, start_date, end_date) values (102,"M04",'2007/04/25','2007/05/10');

select * from book;
select * from members;
select * from book_hold;


Alter table book add publisher varchar(50);


Alter table book drop publisher;


UPDATE book SET author='P Sharma' WHERE accno=101;


delete from book WHERE accno=101; 


SELECT author, title FROM book;


SELECT * FROM book;


SELECT DISTINCT title FROM book;


SELECT * FROM book WHERE price >600;


SELECT title FROM book WHERE author ="Roger S Presman";


SELECT * FROM members WHERE name LIKE "S%";


SELECT title FROM book WHERE title LIKE "%computer%";
SELECT title FROM book WHERE title LIKE "%engi%";


SELECT title, author FROM book WHERE author IN ("Roger S Presman","D W Patterson" );


SELECT accno,title FROM book WHERE accno BETWEEN 101 AND 105;


SELECT * FROM book WHERE author="Roger S Presman" AND accno=105;


SELECT * FROM book WHERE author="Roger S Presman" OR accno=105;


SELECT members_memo, book_accno, datediff(end_date,start_date) AS no_of_days FROM book_hold;


SELECT sum(price) from book;

SELECT avg(price) from book;

SELECT count(*) from members;

SELECT count(distinct title) from book;

SELECT max(price) from book;


SELECT ucase(title) from book;

SELECT lcase(title) from book;

SELECT substring(title,1,8) from book;

SELECT length(title) from book;

SELECT mid(title,5,10) from book;

SELECT ROUND(4.535,2);


SELECT isbn, count(isbn) AS number_of_copies FROM book GROUP BY isbn;


SELECT isbn,count(isbn) AS number_of_books FROM book GROUP BY isbn HAVING count(isbn)>=2;


SELECT title,accno FROM book ORDER BY title;


SELECT title,accno FROM book ORDER BY title DESC;


SELECT title,COUNT(isbn) AS numer_of_copies FROM book GROUP BY title,isbn HAVING COUNT(isbn)>=2 ORDER BY title;


SELECT book.accno, book.title, members.name 
FROM book,book_hold,members 
WHERE book.accno=book_hold.book_accno AND book_hold.members_memo=members.memo;


SELECT memo,name FROM members WHERE memo NOT IN (SELECT  members_memo FROM book_hold);


SELECT book.accno, book.title, memb.name FROM book INNER JOIN book_hold bkh ON book.accno=bkh.book_accno  INNER JOIN members memb ON memb.memo=bkh.members_memo;


CREATE VIEW view_name AS
SELECT book.accno, book.title, memb.name FROM book INNER JOIN book_hold bkh ON book.accno=bkh.book_accno  INNER JOIN members memb ON memb.memo=bkh.members_memo;


SELECT * FROM view_name;