4. Create a PL/SQL Block that selects customer details (Name & Address), Products ordered, Quantity ordered and total amount for all 'pending' orders of 2015. [Hint;- total amount can be calculated using Quanity*unit_price]

5.Create a PL/SQL block, using table type, list all the Product names which were ordered so far but not available in stock in any warehouse any more. Make sure product names are unique.

 

Transcribed Image Text:

CUSTOMERS CUSTOMER_ID NAME ADDRESS WEBSITE CREDIT_LIMIT ORDERS ORDER ID CUSTOMER_ID STATUS SALESMAN_ID ORDER DATE ORDER ITEMS ORDER_ID ITEM_ID PRODUCT_ID QUANTITY UNIT_PRICE PRODUCTS .PRODUCT_ID PRODUCT_NAME DESCRIPTION STANDARD COST LIST_PRICE CATEGORY_ID ++ CONTACTS CONTACT_ID FIRST NAME LAST NAME EMAIL PHONE CUSTOMER ID EMPLOYEES EMPLOYEE_ID FIRST NAME LAST NAME EMAIL PHONE HIRE_DATE MANAGER ID JOB_TITLE INVENTORIES PRODUCT_ID WAREHOUSE_ID QUANTITY PRODUCT CATEGORIES CATEGORY_ID CATEGORY_NAME +H REGIONS REGION_ID REGION_NAME COUNTRIES COUNTRY_ID COUNTRY_NAME REGION_ID LOCATIONS LOCATION_ID ADDRESS POSTAL CODE CITY STATE COUNTRY_ID WAREHOUSES *WAREHOUSE_ID WAREHOUSE_NAME LOCATION_ID CUSTOMERS CUSTOMER_ID NAME ADDRESS WEBSITE CREDIT_LIMIT ORDERS ORDER ID CUSTOMER_ID STATUS SALESMAN_ID ORDER DATE ORDER ITEMS ORDER_ID ITEM_ID PRODUCT_ID QUANTITY UNIT_PRICE PRODUCTS .PRODUCT_ID PRODUCT_NAME DESCRIPTION STANDARD COST LIST_PRICE CATEGORY_ID ++ CONTACTS CONTACT_ID FIRST NAME LAST NAME EMAIL PHONE CUSTOMER ID EMPLOYEES EMPLOYEE_ID FIRST NAME LAST NAME EMAIL PHONE HIRE_DATE MANAGER ID JOB_TITLE INVENTORIES PRODUCT_ID WAREHOUSE_ID QUANTITY PRODUCT CATEGORIES CATEGORY_ID CATEGORY_NAME +H REGIONS REGION_ID REGION_NAME COUNTRIES COUNTRY_ID COUNTRY_NAME REGION_ID LOCATIONS LOCATION_ID ADDRESS POSTAL CODE CITY STATE COUNTRY_ID WAREHOUSES *WAREHOUSE_ID WAREHOUSE_NAME LOCATION_ID