I have changed the emails from @ to - as the system won't let me post email. dbconn.php bookstore.sql CREATE TABLE AUTHOR ( authorID CHAR(7)

I have changed the emails from @ to - as the system won't let me post email.

dbconn.php

$dbConn = new mysqli('localhost', 'root', '', 'bookstore'); if($dbConn->connect_error) { exit("Failed to connect to database " . $dbConn->connect_error); } ?>

bookstore.sql

CREATE TABLE AUTHOR ( authorID CHAR(7) NOT NULL PRIMARY KEY, firstname CHAR(15), lastname CHAR(15), birthdate DATE, phone CHAR(10), email CHAR(20), address CHAR(36), country CHAR(16));

CREATE TABLE PUBLISHER ( publishID CHAR(7) NOT NULL PRIMARY KEY, name CHAR(15), postaddress CHAR(35), phone CHAR(10), email CHAR(20), region CHAR(16));

CREATE TABLE BOOK ( bookID CHAR(6) NOT NULL PRIMARY KEY, booktitle CHAR(30), ISBN CHAR(30), price DECIMAL(8,2), genre CHAR(15), edition INT(2), lang CHAR(15), quantity INT(4), publishID CHAR(7), foreign key (publishID) references publisher(publishID) );

CREATE TABLE WRITES ( bookID CHAR(6) NOT NULL, authorID CHAR(7) NOT NULL, constraint writepk primary key (bookID,authorID), foreign key (bookID) references BOOK(bookID), foreign key (authorID) references AUTHOR(authorID) );

INSERT INTO AUTHOR VALUES('A001','ROBIN','GAO','01-01-1990','0202020202','ROBIN-KJ.COM','1 VICTORIA RD,CITY','AUSTRALIA'); INSERT INTO AUTHOR VALUES('A002','FRANCES','LOUISE','02-02-2001','0123456789','FRANCES-KL.COM','1 PARRAMATTA RD,CITY','AUSTRALIA'); INSERT INTO AUTHOR VALUES('A003','ED','JONES','12-12-1999','1234654321','EJONES-KL.COM','3 SYDNEY PLACE,PARRAMATTA','AUSTRALIA'); INSERT INTO AUTHOR VALUES('A004','JACK','LOD','01-11-1996','4356890135','JLOD-KL.COM','3/4 VICTORIA RD,CITY','AUSTRALIA'); INSERT INTO AUTHOR VALUES('A005','TIM','DAGHER','05-06-1993','3859603813','DAGHER-KL.COM','67 FIRST AVENUE,CITY','AUSTRALIA'); INSERT INTO AUTHOR VALUES('A006','TOM','MANHON','10-09-1990','9586970432','TMAN-KL.COM','56 SECOND AVENUE,CITY','AUSTRALIA'); INSERT INTO AUTHOR VALUES('A007','ROSS','LEE','13-10-1970','3758697049','RLEE-KLCOM','34/102 AUSSIE ST,CITY','AUSTRALIA'); INSERT INTO AUTHOR VALUES('A008','JENNY','BENNY','20-10-1999','1290364758','JENNY-KL.COM','100 THIRD AVENUE,PARRAMATTA','AUSTRALIA');

INSERT INTO PUBLISHER VALUES ('P001','SPRINGER','3 METRO ST,NEW YORK','1122334455','PUB-SPRINGER.COM','NA'); INSERT INTO PUBLISHER VALUES ('P002','OXFORD','4 ROYAL PL,LONDON','0077123456','PUB-OXFORD.COM','EU'); INSERT INTO PUBLISHER VALUES ('P003','CAMBRIDGE','5 MARCH ST, SYDNEY','2958795049','PUB-CAMBRIDGE.COM','OA'); INSERT INTO PUBLISHER VALUES ('P004','SCIENCE','13 CENTURY AVENUE, NEW YORK','2843957684','SCIENCE-SCIENCE.COM','SC');

INSERT INTO BOOK VALUES ('B001','THE LORD OF RINGS','192837',22.95,'MYSTERY',2,'ENGLISH',100,'P001'); INSERT INTO BOOK VALUES ('B002','HARRY POTTER','291837',32.95,'WESTERN',5,'ENGLISH',500,'P002'); INSERT INTO BOOK VALUES ('B003','TOMORROW NEVER COMES','234586',19.95,'THRILLER',4,'ENGLISH',200,'P001'); INSERT INTO BOOK VALUES ('B004','DARK','235223',12.55,'ROMANCE',3,'ENGLISH',100,'P003'); INSERT INTO BOOK VALUES ('B005','WORLD OF RELIGIONS','346575',19.95,'EDUCATION',6,'ENGLISH',3000,'P003'); INSERT INTO BOOK VALUES ('B006','SOME GIRLS','345654',10.95,'KIDS',2,'ENGLISH',100,'P001'); INSERT INTO BOOK VALUES ('B007','ONLY FOR ME','345672',9.95,'FANTASY',3,'SPANISH',200,'P003'); INSERT INTO BOOK VALUES ('B008','SHERLOCK HOLMES','395867',12.95,'DETECTIVE',6,'ENGLISH',20000,'P001'); INSERT INTO BOOK VALUES ('B009','THE PARAGON HOTEL','496879',10.95,'FICTION',7,'GERMAN',2340,'P002'); INSERT INTO BOOK VALUES ('B010','FILM WRITING','798058',20.95,'FICTION',3,'JAPANESE',20000,'P001'); INSERT INTO BOOK VALUES ('B011','COOKING FOR STRANGERS','464535',15.55,'DETECTIVE',2,'ENGLISH',1000,'P003'); INSERT INTO BOOK VALUES ('B012','LULLABY','958393',10.95,'ROMANCE',1,'ENGLISH',20000,'P001'); INSERT INTO BOOK VALUES ('B013','FEEDING FAMILIES','367543',5.95,'MYSTERY',2,'GERMAN',2000,'P002'); INSERT INTO BOOK VALUES ('B014','FLYING FISH','958383',10.95,'FANTASY',4,'JAPANESE',2500,'P003'); INSERT INTO BOOK VALUES ('B015','AUTUMN LEAVES','326583',24.95,'THRILLER',3,'ENGLISH',1340,'P002'); INSERT INTO BOOK VALUES ('B016','DERBY BOYS','112233',20.95,'FICTION',2,'ENGLISH',450,'P003'); INSERT INTO BOOK VALUES ('B017','MAMAMIA','485832',29.95,'ENTERTAINMENT',2,'ENGLISH',550,'P002'); INSERT INTO BOOK VALUES ('B018','NATURE','485833',99.95,'FACTS',2,'ENGLISH',1550,'P004'); INSERT INTO BOOK VALUES ('B019','TIGERS & LIONS','495802',29.95,'NATURE',2,'ENGLISH',550,'P004'); INSERT INTO BOOK VALUES ('B020','ESCAPE FROM SUBMARINE','805832',19.95,'FACTS',2,'ENGLISH',599,'P004'); INSERT INTO BOOK VALUES ('B021','GEORGRAPHY2021','801832',59.95,'NATURE',4,'ENGLISH',199,'P003');

INSERT INTO WRITES VALUES ('B001','A001'); INSERT INTO WRITES VALUES ('B002','A002'); INSERT INTO WRITES VALUES ('B003','A002'); INSERT INTO WRITES VALUES ('B004','A003'); INSERT INTO WRITES VALUES ('B005','A004'); INSERT INTO WRITES VALUES ('B006','A007'); INSERT INTO WRITES VALUES ('B007','A001'); INSERT INTO WRITES VALUES ('B008','A007'); INSERT INTO WRITES VALUES ('B009','A004'); INSERT INTO WRITES VALUES ('B010','A002'); INSERT INTO WRITES VALUES ('B015','A001'); INSERT INTO WRITES VALUES ('B011','A005'); INSERT INTO WRITES VALUES ('B012','A006'); INSERT INTO WRITES VALUES ('B013','A003'); INSERT INTO WRITES VALUES ('B013','A004'); INSERT INTO WRITES VALUES ('B014','A006'); INSERT INTO WRITES VALUES ('B016','A003'); INSERT INTO WRITES VALUES ('B017','A008'); INSERT INTO WRITES VALUES ('B018','A002'); INSERT INTO WRITES VALUES ('B019','A006'); INSERT INTO WRITES VALUES ('B020','A008'); INSERT INTO WRITES VALUES ('B021','A003'); INSERT INTO WRITES VALUES ('B017','A001');

Table Name: BOOK Field Name bookID booktitle ISBN price genre edition language quantity publishID Data Type CHAR(6) CHAR(30) CHAR(30) DECIMAL(8,2) CHAR(15) INT(2) CHAR(15) INT(4) CHAR(7) Description unique identifier for a book title of a book ISBN of a book Selling price of a book Category of a book Current edition of a book Written language of a book Stock level for a book PublisherID for a book a Table Name: AUTHOR Field Name authorID firstname lastname birthdate Iphone email address country Data type CHAR(7) CHAR(15) CHAR(15) DATE CHAR(10) CHAR(20) CHAR(36) CHAR(16) Description Unique identifier for an author First name for an author Last name for an author DOB for an author Contact phone Email address Postal address Country Table Name: WRITES Field Name Data type Description authorld bookID CHAR(7) CHAR(6) Unique identifier for an author unique identifier for a book Table Name: PUBLISHER Field Name lpublishID name Ipostaddress Iphone email region Data type CHAR(7) CHAR(15) CHAR(35) CHAR(10) CHAR(20) CHAR(16) Description Unique identifier for a publisher Name for a publisher Postal address Phone contact Email address Region for a publisher Exercise 3 Create exercise3.php which uses an include statement to establish the connection object via dbConn.php in the week9 tasks folder. In exercise3.php, write an SQL statement that extracts the booktitle, price and quantity from the BOOK table for the books that have more than 80 copies in stock and sort the results in ascending order of price. Display the results in an HTML table in exercise3.php. Validate the script under the C:/XAMPP/htdocs and Save exercise3.php and upload it to C:/TWA/Practicals/PracSet2/week9Tasks folder