Create SQL statements for the following queries based on Use the Purchase schema:

$1.$ List full details of all products.

$2.$ List full details of all products with $$red$$ PRODTYPE.

$3.$ List the names and addresses of all suppliers whose SUPSTATUS is not NULL,

alphabetically ordered by name.

$4.$ List all products with a quantity below $50,$ in ascending order of quantity.

$5.$ List the supplies for which no purchase price has been specified.

$6.$ How many products each supplier supplies?

$7.$ What is the average price of all products each supplier supplies?

$8.$ What is the maximum delivery period for all products?

$9.$ List the details of suppliers and products of which more than one suppliers

supply the same product.

$10.$ How many different SUPPLIERS have made purchase in APRIL?

$11.$ List the price and type of all products supplied by $$Deliwines$.$

$12.$ How many products each supplier supplies?

$13.$ List the details of all suppliers supplying $$Chateau Petrus$.$

$14.$ How many $$Chateau Cheval Blanc$$ has been purchased in May?

$15.$ How many $$Chateau Cheval Blanc$$ has been purchased each month?

Tips: use EXTRACT$()$ functions to group records in a table by month.

$16.$ List the suppliers that currently didn$$t supply any products.

$17.$ List the products that are currently unsupplied by any supplier.

$18.$ List the number of products of different product types.

$19.$ What is the maximum and minimum quantity for each purchase order in April?

$20.$ What is the most commonly purchased product $($product with the highest

quantity in PO$\_$Line$)$ for each month?

For each question, list the SQL statements and corresponding screenshots.

Using this information for the SQL database

CREATE TABLE SUPPLIER

$($SUPNR CHAR$(4)$ NOT NULL PRIMARY KEY,

SUPNAME VARCHAR$(40)$ NOT NULL,

SUPADDRESS VARCHAR$(50),$

SUPCITY VARCHAR$(20),$

SUPSTATUS SMALLINT$)$;

CREATE TABLE PRODUCT

$($PRODNR CHAR$(6)$ NOT NULL PRIMARY KEY,

PRODNAME VARCHAR$(60)$ NOT NULL,

CONSTRAINT UC$1$ UNIQUE$($PRODNAME$),$

PRODTYPE VARCHAR$(10),$

CONSTRAINT CC$1$ CHECK$($PRODTYPE IN $(\text{'}$white$\text{'},$ 'red', 'rose','sparkling'$)),$

AVAILABLE$\_$QUANTITY INTEGER$)$;

CREATE TABLE SUPPLIES

$($SUPNR CHAR$(4)$ NOT NULL,

PRODNR CHAR$(6)$ NOT NULL,

PURCHASE$\_$PRICE NUMBER$(8,2),$

DELIV$\_$PERIOD INT,

PRIMARY KEY $($SUPNR$,$ PRODNR$),$

FOREIGN KEY $($SUPNR$)$ REFERENCES SUPPLIER $($SUPNR$)$

ON DELETE CASCADE,

FOREIGN KEY $($PRODNR$)$ REFERENCES PRODUCT $($PRODNR$)$

ON DELETE CASCADE$)$;

comment on column SUPPLIES.PURCHASE$\_$PRICE IS 'PURCHASE$\_$PRICE IN EUR';

comment on column SUPPLIES.DELIV$\_$PERIOD IS 'DELIV$\_$PERIOD IN DAYS';

CREATE TABLE PURCHASE$\_$ORDER

$($PONR CHAR$(7)$ NOT NULL PRIMARY KEY,

PODATE DATE,

SUPNR CHAR$(4)$ NOT NULL,

FOREIGN KEY $($SUPNR$)$ REFERENCES SUPPLIER $($SUPNR$)$

ON DELETE CASCADE$)$;

CREATE TABLE PO$\_$LINE

$($PONR CHAR$(7)$ NOT NULL,

PRODNR CHAR$(6)$ NOT NULL,

QUANTITY INTEGER,

PRIMARY KEY $($PONR$,$ PRODNR$),$

FOREIGN KEY $($PONR$)$ REFERENCES PURCHASE$\_$ORDER $($PONR$)$

ON DELETE CASCADE,

FOREIGN KEY $($PRODNR$)$ REFERENCES PRODUCT $($PRODNR$)$

ON DELETE CASCADE$)$;

INSERT INTO SUPPLIER$($SUPNR$,$ SUPNAME, SUPADDRESS, SUPCITY, SUPSTATUS$)$ VALUES$(\text{'}21\text{'},$ 'Deliwines', $\text{'}240,$ Avenue of the Americas', 'New York', $\text{'}20\text{'})$;

INSERT INTO SUPPLIER$($SUPNR$,$ SUPNAME, SUPADDRESS, SUPCITY, SUPSTATUS$)$ VALUES$(\text{'}32\text{'},$ 'Best Wines', $\text{'}660,$ Market Street', 'San Francisco', $\text{'}90\text{'})$;

INSERT INTO SUPPLIER$($SUPNR$,$ SUPNAME, SUPADDRESS, SUPCITY, SUPSTATUS$)$ VALUES$(\text{'}37\text{'},\text{'}$Ad Fundum', $\text{'}82,$ Wacker Drive', 'Chicago', $\text{'}95\text{'})$;

INSERT INTO SUPPLIER$($SUPNR$,$ SUPNAME, SUPADDRESS, SUPCITY, SUPSTATUS$)$ VALUES$(\text{'}52\text{'},$ 'Spirits co$.\text{'},\text{'}928,$ Strip', 'Las Vegas', $\text{'}\text{'})$;

INSERT INTO SUPPLIER$($SUPNR$,$ SUPNAME, SUPADDRESS, SUPCITY, SUPSTATUS$)$ VALUES$(\text{'}68\text{'},$ 'The Wine Depot', $\text{'}132,$ Montgomery Street', 'San Francisco', $\text{'}10\text{'})$;

INSERT INTO SUPPLIER$($SUPNR$,$ SUPNAME, SUPADDRESS, SUPCITY, SUPSTATUS$)$ VALUES$(\text{'}69\text{'},$ 'Vinos del Mundo', $\text{'}4,$ Collins Avenue', 'Miami', $\text{'}92\text{'})$;

INSERT INTO SUPPLIER$($SUPNR$,$ SUPNAME, SUPADDRESS, SUPCITY, SUPSTATUS$)$ VALUES$(\text{'}84\text{'},$ 'Wine Trade Logistics', $\text{'}1002,$ Rhode Island Avenue', 'Washington', $\text{'}92\text{'})$;

INSERT INTO SUPPLIER$($SUPNR$,$ SUPNAME, SUPADDRESS, SUPCITY, SUPSTATUS$)$ VALUES$(\text{'}94\text{'},$ 'The Wine Crate', $\text{'}330,$ McKinney Avenue', 'Dallas', $\text{'}75\text{'})$;

INSERT INTO PRODUCT$($PRODNR$,$PRODNAME,PRODTYPE,AVAILABLE$\_$QUANTITY$)$ VALUES$(\text{'}0119\text{'},$ 'Chateau Miraval, Cotes de Provence Rose, $2015\text{'},$ 'rose', $\text{'}126\text{'})$;

INSERT INTO PRODUCT$($PRODNR$,$PRODNAME,PRODTYPE,AVAILABLE$\_$QUANTITY$)$ VALUES$(\text{'}0154\text{'},$ 'Chateau Haut Brion, $2008\text{'},$ 'red', $\text{'}111\text{'})$;

INSERT INTO PRODUCT$($PRODNR$,$PRODNAME,PRODTYPE,AVAILABLE$\_$QUANTITY$)$ VALUES$(\text{'}0178\text{'},$ 'Meerdael, Methode Traditionnelle Chardonnay, $2014\text{'},$ 'sparkling', $\text{'}136\text{'})$;

INSERT INTO PRODUCT$($PRODNR$,$PRODNAME,PRODTYPE,AVAILABLE$\_$QUANTITY$)$ VALUES$(\text{'}0185\text{'},$ 'Chateau Petrus, $1975\text{'},$ 'red', $\text{'}5\text{'})$;

INSERT INTO PRODUCT$($PRODNR$,$PRODNAME,PRODTYPE,AVAILABLE$\_$QUANTITY$)$ VALUES$(\text{'}0199\text{'},$ 'Jacques Selosse, Brut Initial, $2012\text{'},$ 'sparkling', $\text{'}96\text{'})$;

INSERT INTO PRODUCT$($PRODNR$,$PRODNAME,PRODTYPE,AVAILABLE$\_$QUANTITY$)$ VALUES$(\text{'}0212\text{'},$ 'Billecart$-$Salmon, Brut R$$serve$,2014\text{'},$ 'sparkling', $\text{'}141\text{'})$;

INSERT INTO PRODUCT$($PRODNR$,$PRODNAME,PRODTYPE,AVAILABLE$\_$QUANTITY$)$ VALUES$(\text{'}0219\text{'},$ 'Marques de Caceres, Rioja Crianza, $2010\text{'},$ 'red', $\text{'}0\text{'})$;

IN