A)Write a SQL query statement to list the details of all orders (using the OrderDetail view) in the descending order of the order date and the ascending order of customer id in the same order date.

B)Write a SQL query statement to list the number of order items and the total order price per each order using the OrderDetail view.

C) Write a SQL statement to drop the OrderDetail view.

GIVEN ORDERDETAIL:

CREATE VIEW orderdetail AS SELECT o.orderid, o.orderdate, c.customerid, c.customername, p.productid, productdescription, productstandardprice, orderedquantity, productstandardprice * orderedquantity AS orderprice FROM order_t o, customer_t c, product_t p, orderline_t ot WHERE o.customerid = c.customerid AND o.orderid = ot.orderid AND p.productid = ot.productid;

GIVEN TABLES:

CREATE TABLE customer_t( customerid NUMBER(11, 0) NOT NULL, customername VARCHAR2(25) NOT NULL, customeraddress VARCHAR2(30), customercity VARCHAR2(20), customerstate CHAR(2), customerpostalcode VARCHAR2(10), CONSTRAINT customer_pk PRIMARY KEY (customerid) );

CREATE TABLE employee_t( employeeid VARCHAR2(10) NOT NULL, employeename VARCHAR2(25), employeeaddress VARCHAR2(30), employeebirthdate DATE, employeecity VARCHAR2(20), employeestate CHAR(2), employeezipcode VARCHAR2(10), employeedatehired DATE, employeesupervisor VARCHAR2(10), CONSTRAINT employee_pk PRIMARY KEY (employeeid) );

CREATE TABLE order_t( orderid NUMBER(11, 0) NOT NULL, customerid NUMBER(11, 0), orderdate DATE DEFAULT SYSDATE, CONSTRAINT order_pk PRIMARY KEY (orderid), CONSTRAINT order_fk1 FOREIGN KEY (customerid) REFERENCES customer_t( customerid) );

CREATE TABLE product_t( productid NUMBER(11, 0) NOT NULL, productlineid NUMBER(11, 0), productdescription VARCHAR2(50), productfinish VARCHAR2(20), productstandardprice DECIMAL(6, 2), CONSTRAINT product_pk PRIMARY KEY (productid) );

CREATE TABLE orderline_t( orderid NUMBER(11, 0) NOT NULL, productid NUMBER(11, 0) NOT NULL, orderedquantity NUMBER(11, 0), CONSTRAINT orderline_pk PRIMARY KEY (orderid, productid), CONSTRAINT orderline_fk1 FOREIGN KEY (orderid) REFERENCES order_t(orderid), CONSTRAINT orderline_fk2 FOREIGN KEY (productid) REFERENCES product_t( productid) );