SQLize Online / PHPize Online  /  SQLtest Online

CREATE TABLE Book ( BookId int NOT NULL AUTO_INCREMENT, Title varchar(100) NOT NULL, Publisher varchar(127) NOT NULL, PRIMARY KEY (BookId), KEY Publisher (Publisher) ) ENGINE=InnoDB ; CREATE TABLE BookAuthor ( AuthorName varchar(127) NOT NULL, BookId int NOT NULL, PRIMARY KEY (AuthorName), KEY BookId (BookId) ) ENGINE=InnoDB ; CREATE TABLE Publisher ( Name varchar(127) NOT NULL, Address varchar(255) NOT NULL, Phone varchar(63) NOT NULL, PRIMARY KEY (Name) ) ENGINE=InnoDB ; CREATE TABLE BookCopy ( Id int NOT NULL AUTO_INCREMENT, BookId int NOT NULL, BranchId int NOT NULL, NoOfCopies int NOT NULL, PRIMARY KEY (Id), KEY BookId (BookId), KEY BranchId (BranchId) ) ENGINE=InnoDB ; CREATE TABLE BookLoan ( Id int NOT NULL AUTO_INCREMENT, BookId int NOT NULL, BranchId int NOT NULL, CardNo int NOT NULL, DateOut date NOT NULL, DueDate date NOT NULL, PRIMARY KEY (Id), KEY BookId (BookId), KEY BranchId (BranchId), KEY CardNo (CardNo) ) ENGINE=InnoDB ; CREATE TABLE LibraryBranch ( BranchId int NOT NULL AUTO_INCREMENT, BranchName varchar(127) NOT NULL, Address varchar(255) NOT NULL, PRIMARY KEY (BranchId) ) ENGINE=InnoDB ; CREATE TABLE Borrower ( CardNo int NOT NULL AUTO_INCREMENT, Name varchar(127) NOT NULL, Address varchar(255) NOT NULL, Phone varchar(63) NOT NULL, PRIMARY KEY (CardNo) ) ENGINE=InnoDB ; Alter TABLE Book add CONSTRAINT Book_ibfk_1 FOREIGN KEY (Publisher) REFERENCES Publisher (Name) ON DELETE RESTRICT ON UPDATE CASCADE; Alter TABLE BookAuthor add CONSTRAINT BookAuthor_ibfk_1 FOREIGN KEY (BookId) REFERENCES Book (BookId) ON DELETE CASCADE ON UPDATE CASCADE; Alter TABLE BookCopy add CONSTRAINT BookCopy_ibfk_1 FOREIGN KEY (BranchId) REFERENCES LibraryBranch (BranchId) ON DELETE RESTRICT ON UPDATE CASCADE; Alter TABLE BookCopy add CONSTRAINT BookCopy_ibfk_2 FOREIGN KEY (BookId) REFERENCES Book (BookId) ON DELETE RESTRICT ON UPDATE CASCADE; Alter TABLE BookLoan add CONSTRAINT BookLoan_ibfk_1 FOREIGN KEY (BookId) REFERENCES Book (BookId) ON DELETE RESTRICT ON UPDATE CASCADE; Alter TABLE BookLoan add CONSTRAINT BookLoan_ibfk_2 FOREIGN KEY (CardNo) REFERENCES Borrower (CardNo) ON DELETE RESTRICT ON UPDATE CASCADE; Alter TABLE BookLoan add CONSTRAINT BookLoan_ibfk_3 FOREIGN KEY (BranchId) REFERENCES LibraryBranch (BranchId) ON DELETE RESTRICT ON UPDATE RESTRICT; INSERT INTO `Publisher` (`Name`, `Address`, `Phone`) VALUES ('Publisher2', 'PubAddress2', 'Pubphone2'), ('Publisher3', 'PubAddress3', 'Pubphone3'), ('Publisher4', 'PubAddress4', 'Pubphone4'), ('Publisher5', 'PubAddress4', 'Pubphone3'), ('Rehman Publishers', 'PubAddress1', ''); INSERT INTO `Book` (`BookId`, `Title`, `Publisher`) VALUES (5, 'The Kite Runner', 'Publisher2'), (6, 'AlChemist', 'Rehman Publishers'), (7, 'Book3', 'Publisher3'), (8, 'Book4', 'Rehman Publishers'), (9, 'Book5', 'Publisher4'); INSERT INTO `BookAuthor` (`AuthorName`, `BookId`) VALUES ('Author2', 5), ('Author5', 5), ('Author1', 6), ('Author4', 6), ('Author3', 8); INSERT INTO `LibraryBranch` (`BranchId`, `BranchName`, `Address`) VALUES (1, 'Iqbal Town Branch', 'Iqbal Town Krachi'), (2, 'Jinnah Branch', 'Quaid e azam university Islamabad'), (3, 'Jamia Branch', 'Jamia Karachi'), (4, 'Lahore Branch', '24 A Khayaban-e-Jinnah Lahore, Pakistan'), (5, 'Branch5', 'South Pole, Antarctica'); INSERT INTO `BookCopy` (`Id`, `BookId`, `BranchId`, `NoOfCopies`) VALUES (7, 6, 1, 5), (8, 6, 3, 10), (9, 7, 3, 20), (10, 6, 4, 40), (11, 7, 2, 5); INSERT INTO `Borrower` (`CardNo`, `Name`, `Address`, `Phone`) VALUES (1, 'Borrower1', '24 A Khayaban-e-Jinnah Lahore, Pakistan', '001'), (2, 'The Kite Runner', '10-A, University Town, Swat', '002'), (3, 'Borrower3', '34-A POF-2, Lahore', '003'), (4, 'Ali Ahsan Khan', '1-B WAPDA Town, Lahore', ''), (5, 'Borrower5', '3-C, Gulshan-e-Hadeed Krach', '005'); INSERT INTO `BookLoan` (`Id`, `BookId`, `BranchId`, `CardNo`, `DateOut`, `DueDate`) VALUES (2, 6, 1, 4, '2023-01-01', '2023-09-11'), (3, 6, 4, 2, '2021-05-01', '2022-09-19'), (4, 5, 1, 4, '2023-05-01', '2023-05-26'), (5, 5, 4, 3, '2023-04-03', '2023-09-01'), (6, 6, 3, 1, '2023-04-10', '2023-05-26'); SELECT Name as NameOfBorrowersFromLahore FROM Borrower where phone is not null and address like '%lahore%'; SELECT b.Title as BooksWithLessThan5Copies, bc.NoOfCopies FROM Book b join BookCopy bc on b.BookId=bc.BookId where bc.NoOfCopies<10; SELECT lb.BranchName, b.Title, bc.NoOfCopies FROM Book b join BookCopy bc on b.BookId=bc.BookId join LibraryBranch lb on lb.BranchId=bc.BranchId; SELECT br.Name as BorrwersOfKitRunner from Borrower br join BookLoan bl on bl.CardNo=br.CardNo join Book b on b.BookId=bl.BookId where b.Title='The Kite Runner'; SELECT br.Name BranchesOfKarachi from Borrower br join BookLoan bl on bl.CardNo=br.CardNo join LibraryBranch lb on lb.BranchId=bl.BranchId where lb.Address like '%Karachi%'; SELECT b.Title BooksOfRehmanPublishers, ba.AuthorName from Book b join Publisher p on b.Publisher=p.Name join BookAuthor ba on ba.BookId=b.BookId where p.Name='Rehman Publishers'; SELECT p.Name PublisherOfAlChemist from Book b join Publisher p on b.Publisher=p.Name where b.Title='Alchemist' and p.phone is null or phone=''; SELECT b.Title IqbalTownBranchBooks, br.Name,br.Address from LibraryBranch lb JOIN BookLoan bl on bl.BranchId=lb.BranchId join Borrower br on bl.CardNo=br.CardNo join Book b on bl.BookId=b.BookId WHERE lb.BranchName='Iqbal Town Branch' and bl.DueDate = CURRENT_DATE(); SELECT b.Title BooksToReturnInSeptember from Book b JOIN BookLoan bl on bl.BookId=b.BookId WHERE bl.DueDate = MONTH(bl.DueDate)='September'; SELECT DISTINCT lb.BranchName BranchWithAliAhsanAsBorrower from Borrower br JOIN BookLoan bl on bl.CardNo=br.CardNo join LibraryBranch lb on bl.BranchId=lb.BranchId join Book b on bl.BookId=b.BookId WHERE br.Name='Ali Ahsan Khan';