SQLize Online / PHPize Online  /  SQLtest Online

A A A
Share      Blog   Popular
Copy Format Clear
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');
Stuck with a problem? Got Error? Ask ChatGPT!
Copy Clear