Notes

CREATE TABLE A2_Book
(
BookID INT PRIMARY KEY,
Title VARCHAR(50),
PublisherName VARCHAR(25)
);

CREATE TABLE A2_Publisher
(
Name VARCHAR(50),
Address VARCHAR(50),
Mobile INT
);


CREATE TABLE A2_BookAuthor
(
BookID INT,
AuthorName VARCHAR(50),
FOREIGN KEY(BookID) REFERENCES A2_Book(BookID)
);

CREATE TABLE A2_LibraryBranch
(
BranchID INT NOT NULL PRIMARY KEY,
BranchName VARCHAR(50),
Address VARCHAR(50)
);

CREATE TABLE A2_BookCopies
(
BookID INT NOT NULL,
BranchID INT NOT NULL,
NoOfCopies INT,
FOREIGN KEY(BookID) REFERENCES A2_Book(BookID),
FOREIGN KEY(BranchID) REFERENCES A2_LibraryBranch(BranchID)
);

CREATE TABLE A2_Borrower
(
CardNo INT NOT NULL PRIMARY KEY,
Name VARCHAR(25) NOT NULL,
Address VARCHAR(25),
Phone INT
);

CREATE TABLE A2_BookLoans
(
BookID INT NOT NULL,
BranchID INT NOT NULL,
CardNo INT NOT NULL,
DateOut DATE,
DueDate DATE,
FOREIGN KEY(BookID) REFERENCES A2_Book(BookID),
FOREIGN KEY(BranchID) REFERENCES A2_LibraryBranch(BranchID),
FOREIGN KEY(CardNo) REFERENCES A2_Borrower(CardNo)
);



INSERT INTO A2_Book VALUES(1,'Data Structures','Dhanwant Rai Publication')
INSERT INTO A2_Book VALUES(2,'Introduction to Algorithms','Virat Publishers')
INSERT INTO A2_Book VALUES(3,'Gone With The Wind','Prestige Publication')

INSERT INTO A2_BookAuthor VALUES(1,'Sumita Arora')
INSERT INTO A2_BookAuthor VALUES(2,'Richard Cormen')
INSERT INTO A2_BookAuthor VALUES(3,'Samuel Jackson')

INSERT INTO A2_LibraryBranch VALUES(1001,'Malviya','Allahabad')
INSERT INTO A2_LibraryBranch VALUES(1002,'Tandon','Allahabad')
INSERT INTO A2_LibraryBranch VALUES(1003,'Tagore','Allahabad')
INSERT INTO A2_LibraryBranch VALUES(1004,'Patel','Allahabad')

INSERT INTO A2_BookCopies VALUES(1,1001,10)
INSERT INTO A2_BookCopies VALUES(1,1002,5)
INSERT INTO A2_BookCopies VALUES(1,1004,0)
INSERT INTO A2_BookCopies VALUES(2,1004,25)
INSERT INTO A2_BookCopies VALUES(2,1003,25)
INSERT INTO A2_BookCopies VALUES(2,1002,15)
INSERT INTO A2_BookCopies VALUES(2,1001,5)
INSERT INTO A2_BookCopies VALUES(3,1002,35)
INSERT INTO A2_BookCopies VALUES(3,1004,18)

INSERT INTO A2_Publisher VALUES('Dhanwant Rai Publication','Civil Lines Allahabad',33854596)
INSERT INTO A2_Publisher VALUES('Virat Publishers','Teliarganj Allahabad',33854587)
INSERT INTO A2_Publisher VALUES('Prestige Publication','Govindpur Allahabad',33854510)

INSERT INTO A2_Borrower VALUES('1101','Akshar','Room 222 Patel',8948114532)
INSERT INTO A2_Borrower VALUES('2101','Prakhar','Room 221 Tilak',8948120452)
INSERT INTO A2_Borrower VALUES('3101','Nikhil','Room 220 Tandon',8948194412)

INSERT INTO A2_BookLoans VALUES(1,1001,1101,'2015-01-12','2015-01-19')
INSERT INTO A2_BookLoans VALUES(3,1003,3101,'2015-01-10','2015-01-29')
INSERT INTO A2_BookLoans VALUES(2,1002,2101,'2015-01-05','2015-01-13')
INSERT INTO A2_BookLoans VALUES(1,1002,2101,'2015-01-11','2015-01-15')



SELECT NoOfCopies FROM A2_BookCopies JOIN A2_Book ON A2_BookCopies.BookID = A2_Book.BookID JOIN A2_LibraryBranch ON A2_BookCopies.BranchID = A2_LibraryBranch.BranchID
WHERE A2_Book.Title = "Data Structures" AND A2_LibraryBranch.BranchID = 1001

SELECT A2_LibraryBranch.BranchName,NoOfCopies FROM A2_BookCopies INNER JOIN A2_Book ON A2_BookCopies.BookID = A2_Book.BookID INNER JOIN A2_LibraryBranch ON A2_BookCopies.BranchID = A2_LibraryBranch.BranchID
WHERE A2_Book.Title = "Data Structures"