For the product ordering database prac8tables-null.sql adapted from in Practical 7 (we now allow CUS_AREACODE and P_DESCRIPT to be NULL), do the following database queries in SQL. To replace your existing database, you can first just drop all the relevant tables in the right order, see the first few lines in prac8tables-null.sql, and then just run the script prac8tables-null.sql to reconstruct a slightly modified database. For each query, take a (readable) screenshot for the query result and the screenshot is to include the SQL script/statement window as well.

1. (Database Preparation) On top of those existing records prac8data.sql, enter into the database at least the following additional records additionalRecords.sql before completing the rest of the questions

   INSERT INTO Product VALUES ('T-101', 'Impact Drill', '03-NOV-03',8,5,109.99,0.00,NULL); INSERT INTO Cus_order VALUES (111666, NULL, '25-SEP-05'); INSERT INTO ORDER_DETAIL VALUES (111666, 'LZQ202', 1); INSERT INTO ORDER_DETAIL VALUES (111666, 'T-101', 1); INSERT INTO ORDER_DETAIL VALUES (333111, 'T-101', 1); 

   If you run into problems in the above, you can also just reconstruct the table and reload all the data by merely executing this SQL script prac8tabledata-reload.sql. In fact, before finalising your answers, refresh your database by reloading prac8tabledata-reload.sql, if necessary, so that the results are uniform across the board.
3. List order number (ORDER_CODE), order date (ORDDATETIME) and the customer's last name (CUS_LNAME) for all orders, including the orders which contain no customer names.

--------------------------------------------------------------------------------------------------------------------------------------------------

/* drop table order_detail, product; drop table vendor, cus_order, customer; */

CREATE TABLE VENDOR ( V_CODE NUMERIC(5) NOT NULL, V_NAME VARCHAR(35) NOT NULL, V_CONTACT VARCHAR(15) NOT NULL, V_AREACODE VARCHAR(3) NOT NULL, V_PHONE VARCHAR(8) NOT NULL, V_STATE VARCHAR(3) NOT NULL, V_ORDER VARCHAR(1) NOT NULL, CONSTRAINT VENDOR_PK PRIMARY KEY (V_CODE));

CREATE TABLE PRODUCT ( P_CODE varchar(10) CONSTRAINT PRODUCT_P_CODE_PK PRIMARY KEY, P_DESCRIPT varchar(35), -- NULL now allowed P_INDATETIME DATETIME NOT NULL,

-- total number of products current in stock P_ONHAND NUMERIC(4) NOT NULL,

-- recommended minimum number of products to be sold in each order P_MIN NUMERIC(4) NOT NULL,

P_PRICE NUMERIC(8,2) NOT NULL, P_DISCOUNT NUMERIC(4,2) NOT NULL CHECK (P_DISCOUNT >= 0 AND P_DISCOUNT < 1), V_CODE NUMERIC(5), CONSTRAINT PRODUCT_V_CODE_FK FOREIGN KEY (V_CODE) REFERENCES VENDOR);

CREATE TABLE CUSTOMER ( CUS_CODE NUMERIC(6) CONSTRAINT CUSTOMER_PK PRIMARY KEY, CUS_LNAME varchar(15) NOT NULL, CUS_FNAME varchar(15) NOT NULL, CUS_INITIAL CHAR(1), CUS_AREACODE VARCHAR(3), -- NULL now allowed CUS_PHONE VARCHAR(8) NOT NULL, CUS_BALANCE NUMERIC(9,2) DEFAULT 0.00);

CREATE TABLE CUS_ORDER ( ORDER_CODE NUMERIC(6) CONSTRAINT ORDER_PK PRIMARY KEY, CUS_CODE NUMERIC(6) -- NULL now allowd CONSTRAINT CUS_ORDER_FK FOREIGN KEY(CUS_CODE) REFERENCES CUSTOMER, ORDDATETIME DATETIME);

CREATE TABLE ORDER_DETAIL( ORDER_CODE NUMERIC(6), PRODUCT_CODE VARCHAR(10), QUANTITY NUMERIC(4) DEFAULT 1 CHECK (QUANTITY >=0), CONSTRAINT ORD_DETAIL_PK PRIMARY KEY(ORDER_CODE,PRODUCT_CODE), CONSTRAINT ORD_DETAIL_FK1 FOREIGN KEY (ORDER_CODE) REFERENCES CUS_ORDER(ORDER_CODE), CONSTRAINT ORD_DETAIL_FK2 FOREIGN KEY (PRODUCT_CODE) REFERENCES PRODUCT(P_CODE));