Write the primary key constraint for the Customer table. What happens if this

statement is subsequently executed? $(4\%)$

Add a Postal Code field to the Customer table. $(3\%)$

Display all the records in the Customer table. $(2\%)$

Change the Phone number of CUSTID $=1002$ from $3655558736$ to $9055551635$ ;

and POSTAL$\_$CODE $=\text{'}$M$4$H$3$F$9\text{'}$ in the customer table. $(4\%)$

Create a NAMED constraint $($for example: CHK$\_$PROVINCE$)$ that will only allow

Alberta $($AB$),$ British Columbia $(BC),$ and Ontario $($ON$)$ values in the PROVINCE

column of the CUSTOMER table. $)$

Which customers $($CUSTID$,$ LNAME, FNAME$)$ have the earliest and most recent

orders? $(4\%)$

List all the customers $($CUSTID$,$ LNAME$)$ which have an $\text{'}$R$\text{'}$ as the third character

in their last names. $(5\%)$

Display all the fields from customer table by descending order of their LNAME

column. $(4\%)$

Write a select statement to display all customers $($CUSTID$,$ LNAME, FNAME$)$ who

have ordered ITEMID $=774($WOMEN$\text{'}$S FLEECE PULLOVER$).(4\%)$

Display all the INVID, ITEMID, ITEMDESC and CURR$\_$PRICE from the inventory

table, where QUANTITY$\_$ON$\_$HAND is less than $100.(4\%)$Display all the fields from customer table by descending order of their LNAME

column. $(4\%)$

Write a select statement to display all customers $($CUSTID$,$ LNAME, FNAME$)$ who

have ordered ITEMID $=774($WOMEN$\text{'}$S FLEECE PULLOVER$).(4\%)$

Display all the INVID, ITEMID, ITEMDESC and CURR$\_$PRICE from the inventory

table, where QUANTITY$\_$ON$\_$HAND is less than $100.(4\%)$

Write a select statement which should display the ITEM$\_$SIZE, COLOR,

CURR$\_$PRICE of ORDERID $=1058.(5\%)$

Display each province and the total of all orders for each province with the heading

'Total $/$ Prov'. $(5\%)$

Display Customer First Name, Last Name, and total of orders for each customer

who has ordered items. $(6\%)$

How many customers have not ordered anything? $(10\%)$

For each customer in the Customer table $($CUSTID$,$ LNAME, FNAME, SPENT$),$

write a select statement that will display what they spent. 'Spent' is defined as

ORDER$\_$PRICE $$ QUANTITY. Arrange the list by 'Spent' in descending order.

$(10\%)$

DROP TABLE IF EXISTS ORDERS;DROP TABLE IF EXISTS INVENTORY;DROP TABLE IF EXISTS ITEM;DROP TABLE IF EXISTS CUSTOMER;DROP SEQUENCE IF EXISTS ITEM$\_$SEQ;CREATE TABLE customer $($custid INT NOT NULL PRIMARY KEY,lname VARCHAR$(20),$fname VARCHAR$(20),$city VARCHAR$(20),$province VARCHAR$(2),$phone BIGINT$)$;CREATE TABLE inventory $($invid INT NOT NULL PRIMARY KEY,itemid INT NOT NULL,itemsize VARCHAR$(3),$color VARCHAR$(15),$curr$\_$price DECIMAL$(10,2),$quantity$\_$on$\_$hand INT$)$;CREATE TABLE item $($itemid INT NOT NULL PRIMARY KEY,itemdesc VARCHAR$(50),$category VARCHAR$(30))$;CREATE TABLE orders $($orderid INT NOT NULL PRIMARY KEY,invid INT NOT NULL,orderdate DATE,custid INT NOT NULL,order$\_$price DECIMAL$(10,2),$quantity INT$)$;ALTER TABLE inventory ADD CONSTRAINT inventory$\_$fk$\_$itemid FOREIGN KEY $($itemid$)$ REFERENCES item $($itemid$)$;ALTER TABLE orders ADD CONSTRAINT orders$\_$fk$\_$invid FOREIGN KEY $($invid$)$ REFERENCES inventory $($invid$)$;ALTER TABLE orders ADD CONSTRAINT orders$\_$fk$\_$custid FOREIGN KEY $($custid$)$ REFERENCES customer $($custid$)$;INSERT INTO CUSTOMER $($custid$,$ lname$,$ fname, city, province, phone$)$VALUES $(1001,$ 'HARRIS', 'PAULA', 'TORONTO', $\text{'}$ON$\text{'},4165552378),(1002,$ 'EDWARDS', 'MICHAEL', 'HAMILTON', $\text{'}$ON$\text{'},3655558736),(1003,$ 'GORDON', 'MARY', 'LONDON', $\text{'}$ON$\text{'},5195553865),(1004,$ 'MILLER', 'CHRISTOPHER', 'CALGARY', $\text{'}$AB$\text{'},8255556294),(1005,$ 'CHAN', 'LISA', 'EDMONTON', $\text{'}$AB$\text{'},7805558665),(1006,$ 'SMITH', 'PAULINE', 'SUDBURY', $\text{'}$ON$\text{'},7075556388),(1007,$ 'PEREZ', 'JORGE', 'CALGARY', $\text{'}$AB$\text{'},8255558888),(1008,$ 'NELSON', 'JANICE', 'VANCOUVER', $\text{'}$BC$\text{'},7785551111),(1009,$ 'CRUZ', 'TED', 'HAMILTON', $\text{'}$ON$\text{'},3655551245),(1010,$ 'TRUDEAU', 'WILLIAM', 'EDMONTON', $\text{'}$AB$\text{'},7805558366),(1011,$ 'MCGOVERN', 'PAUL', 'CALGARY', $\text{'}$AB$\text{'},8255559292),(1012,$ 'THOMPSON', 'JENNIFER', 'TORONTO', $\text{'}$ON$\text{'},6475558775),(1013,$ 'SMITH', 'JAMES', 'HAMILTON', $\text{'}$ON$\text{'},3655551234)$;CREATE SEQUENCE ITEM$\_$SEQSTART WITH $771$INCREMENT BY $1$NO CACHE;INSERT INTO ITEM $($itemid$,$ itemdesc, category$)$VALUES $($NEXT VALUE FOR ITEM$\_$SEQ, 'MENS WINTER BOMBER JACKET', 'MENS CLOTHING'$),($NEXT VALUE FOR ITEM$\_$SEQ, 'MENS BROAD RIMMED HATS', 'MENS CLOTHING'$),($NEXT VALUE FOR ITEM$\_$SEQ, 'WOMENS RUNNING SHORTS', 'WOMENS CLOTHING'$),($NEXT VALUE FOR ITEM$\_$SEQ, 'WOMENS FLEECE PULLOVER', 'WOMENS CLOTHING'$),($NEXT VALUE FOR ITEM$\_$SEQ, 'WOMENS LEATHER JACKET', 'WOMENS CLOTHING'$)$;INSERT INTO INVENTORY $($invid$,$ itemid, itemsize, color, curr$\_$price, quantity$\_$on$\_$hand$)$VALUES $(11655,771,\text{'}$S$\text{'},$ 'BLACK', $165\mathrm{.}00,120),(11657,771,\text{'}$M$\text{'},$ 'BROWN', $165\mathrm{.}00,200),(11660,771,\text{'}$L$\text{'},$ 'BLUE', $165\mathrm{.}00,60),(11662,771,\text{'}$XL$\text{'},$ 'WHITE', $165\mathrm{.}00,46),(11663,772,\text{'}$S$\text{'},$ 'WHITE', $75\mathrm{.}00,21),(11664,773,\text{'}$S$\text{'},$ 'BLACK', $25\mathrm{.}00,104),(11668,773,\text{'}$M$\text{'},$ 'BLUE', $25\mathrm{.}00,149),(11771,773,\text{'}$L$\text{'},$ 'RED', $25\mathrm{.}00,47),(11772,774,\text{'}$