Question

For this exercise you will use the Order Entry (oe) schema that is one of the sample schemas in Oracle.

Table 4-2 Order Entry (OE) Schema Scripts

Script Name	Description
oc_comnt.sql	Adds comments to the online catalog (OC) subschema wherever possible.
oc_cre.sql	Creates the OC subschema.
oc_drop.sql	Drops the OC subschema.
oc_main.sql	Main script for the OC subschema.
oc_popul.sql a	Populates the object tables.
oe_analz.sql	Gathers statistics on the OE objects.
oe_comnt.sql	Creates comments for the objects in the schema.
oe_cre.sql	Creates the OE objects.
oe_drop.sql	Drops the OE schema and all its objects.
oe_idx.sql	Creates indexes on the OE tables.
oe_main.sql	Main script for the OE schema; calls other scripts.
oe_views.sql	Creates the OE schema views.

List of OE Objects

FUNCTION 
 GET_PHONE_NUMBER_F

INDEX 
 CUSTOMERS_PK
 CUST_ACCOUNT_MANAGER_IX
 CUST_EMAIL_IX
 CUST_LNAME_IX
 CUST_UPPER_NAME_IX
 INVENTORY_IX
 INV_PRODUCT_IX
 ITEM_ORDER_IX
 ITEM_PRODUCT_IX
 ORDER_ITEMS_PK
 ORDER_ITEMS_UK
 ORDER_PK
 ORD_CUSTOMER_IX
 ORD_ORDER_DATE_IX
 ORD_SALES_REP_IX
 PRD_DESC_PK 
 PRODUCT_INFORMATION_PK
 PROD_NAME_IX
 PROD_SUPPLIER_IX
 PROMO_ID_PK
 REFERENCE_IS_UNIQUE
 SYS_C003584
 SYS_C003587
 SYS_C003588
 SYS_C003589
 SYS_C003590
 WAREHOUSES_PK
 WHS_LOCATION_IX
 
LOB 
 SYS_LOB0000045843C00022$$
 SYS_LOB0000045843C00023$$
 SYS_LOB0000045852C00003$$
 SYS_LOB0000045852C00012$$
 SYS_LOB0000045852C00013$$ 
 SYS_LOB0000046019C00004$$ 
 SYS_LOB0000046019C00005$$ 
 SYS_LOB0000046019C00007$$ 
 SYS_LOB0000046019C00011$$ 
 SYS_LOB0000046019C00012$$ 
 SYS_LOB0000046019C00015$$ 
 SYS_LOB0000046019C00024$$ 
 SYS_LOB0000046019C00031$$ 
 SYS_LOB0000046019C00032$$ 
 SYS_LOB0000046044C00003$$ 
 
SEQUENCE 
 ORDERS_SEQ 
 
SYNONYM 
 COUNTRIES 
 DEPARTMENTS 
 EMPLOYEES 
 JOBS 
 JOB_HISTORY 
 LOCATIONS 
 
TABLE 
 CATEGORIES_TAB 
 CUSTOMERS 
 INVENTORIES 
 ORDERS 
 ORDER_ITEMS 
 PRODUCT_DESCRIPTIONS 
 PRODUCT_INFORMATION 
 PRODUCT_REF_LIST_NESTEDTAB 
 PROMOTIONS 
 PURCHASEORDERS 
 STYLESHEET_TAB 
 SUBCATEGORY_REF_LIST_NESTEDTAB 
 WAREHOUSES 
 
TRIGGER 
 INSERT_ORD_LINE 
 ORDERS_ITEMS_TRG 
 ORDERS_TRG 
 PURCHASEORDERS$xd 
 
TYPE 
 CATALOG_TYP 
 CATALOG_TYP 
 CATEGORY_TYP 
 CATEGORY_TYP 
 COMPOSITE_CATEGORY_TYP 
 COMPOSITE_CATEGORY_TYP 
 CORPORATE_CUSTOMER_TYP 
 CUSTOMER_TYP 
 CUST_ADDRESS_TYP 
 INVENTORY_LIST_TYP 
 INVENTORY_TYP 
 LEAF_CATEGORY_TYP 
 LEAF_CATEGORY_TYP 
 ORDER_ITEM_LIST_TYP 
 ORDER_ITEM_TYP 
 ORDER_LIST_TYP 
 ORDER_TYP 
 PHONE_LIST_TYP 
 PRODUCT_INFORMATION_TYP 
 PRODUCT_REF_LIST_TYP 
 SUBCATEGORY_REF_LIST_TYP 
 SYS_YOID0000046073$ 
 SYS_YOID0000046075$ 
 SYS_YOID0000046077$ 
 SYS_YOID0000046079$ 
 SYS_YOID0000046081$ 
 WAREHOUSE_TYP 
 XDBPO_ACTIONS_TYPE 
 XDBPO_ACTION_COLLECTION 
 XDBPO_ACTION_TYPE 
 XDBPO_LINEITEMS_TYPE 
 XDBPO_LINEITEM_COLLECTION 
 XDBPO_LINEITEM_TYPE 
 XDBPO_PART_TYPE 
 XDBPO_REJECTION_TYPE 
 XDBPO_SHIPINSTRUCTIONS_TYPE 
 XDBPO_TYPE 
 
TYPE BODY 
 CATALOG_TYP 
 COMPOSITE_CATEGORY_TYP 
 LEAF_CATEGORY_TYP 
 
VIEW 
 ACCOUNT_MANAGERS 
 BOMBAY_INVENTORY 
 CUSTOMERS_VIEW 
 DEPTVIEW 
 OC_CORPORATE_CUSTOMERS 
 OC_CUSTOMERS 
 OC_INVENTORIES 
 OC_ORDERS 
 OC_PRODUCT_INFORMATION 
 ORDERS_VIEW 
 PRODUCTS 
 PRODUCT_PRICES 
 SYDNEY_INVENTORY 
 TORONTO_INVENTORY 
 

OE Table Descriptions

Table CATEGORIES_TAB 
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 CATEGORY_NAME                                      VARCHAR2(50)
 CATEGORY_DESCRIPTION                               VARCHAR2(1000)
 CATEGORY_ID                               NOT NULL NUMBER(2)
 PARENT_CATEGORY_ID                                 NUMBER(2)
 
Table CUSTOMERS
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 CUSTOMER_ID                               NOT NULL NUMBER(6)
 CUST_FIRST_NAME                           NOT NULL VARCHAR2(20)
 CUST_LAST_NAME                            NOT NULL VARCHAR2(20)
 CUST_ADDRESS                                       CUST_ADDRESS_TYP
 PHONE_NUMBERS                                      PHONE_LIST_TYP
 NLS_LANGUAGE                                       VARCHAR2(3)
 NLS_TERRITORY                                      VARCHAR2(30)
 CREDIT_LIMIT                                       NUMBER(9,2)
 CUST_EMAIL                                         VARCHAR2(30)
 ACCOUNT_MGR_ID                                     NUMBER(6)
 CUST_GEO_LOCATION                                  MDSYS.SDO_GEOMETRY
 DATE_OF_BIRTH                                      DATE
 MARITAL_STATUS                                     VARCHAR2(20)
 GENDER                                             VARCHAR2(1)
 INCOME_LEVEL                                       VARCHAR2(20)
 
Table INVENTORIES
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 PRODUCT_ID                                NOT NULL NUMBER(6)
 WAREHOUSE_ID                              NOT NULL NUMBER(3)
 QUANTITY_ON_HAND                          NOT NULL NUMBER(8)
 
Table ORDERS
 Name                                 Null?    Type
 ------------------------------------ -------- ----------------------------
 ORDER_ID                             NOT NULL NUMBER(12)
 ORDER_DATE                           NOT NULL TIMESTAMP(6) WITH LOCAL TIME ZONE
 ORDER_MODE                                    VARCHAR2(8)
 CUSTOMER_ID                          NOT NULL NUMBER(6)
 ORDER_STATUS                                  NUMBER(2)
 ORDER_TOTAL                                   NUMBER(8,2)
 SALES_REP_ID                                  NUMBER(6)
 PROMOTION_ID                                  NUMBER(6)
 
Table ORDER_ITEMS
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ORDER_ID                                  NOT NULL NUMBER(12)
 LINE_ITEM_ID                              NOT NULL NUMBER(3)
 PRODUCT_ID                                NOT NULL NUMBER(6)
 UNIT_PRICE                                         NUMBER(8,2)
 QUANTITY                                           NUMBER(8)
 
Table PRODUCT_DESCRIPTIONS 
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 PRODUCT_ID                                NOT NULL NUMBER(6)
 LANGUAGE_ID                               NOT NULL VARCHAR2(3)
 TRANSLATED_NAME                           NOT NULL NVARCHAR2(50)
 TRANSLATED_DESCRIPTION                    NOT NULL NVARCHAR2(2000)
 
Table PRODUCT_INFORMATION
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 PRODUCT_ID                                NOT NULL NUMBER(6)
 PRODUCT_NAME                                       VARCHAR2(50)
 PRODUCT_DESCRIPTION                                VARCHAR2(2000)
 CATEGORY_ID                                        NUMBER(2)
 WEIGHT_CLASS                                       NUMBER(1)
 WARRANTY_PERIOD                                    INTERVAL YEAR(2) TO MONTH
 SUPPLIER_ID                                        NUMBER(6)
 PRODUCT_STATUS                                     VARCHAR2(20)
 LIST_PRICE                                         NUMBER(8,2)
 MIN_PRICE                                          NUMBER(8,2)
 CATALOG_URL                                        VARCHAR2(50)
 
PRODUCT_REF_LIST_NESTEDTAB 
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 COLUMN_VALUE                                       NUMBER(6)
 
Table PROMOTIONS
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 PROMO_ID                                  NOT NULL NUMBER(6)
 PROMO_NAME                                         VARCHAR2(20)
 
Table PURCHASEORDERS
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
TABLE of SYS.XMLTYPE(XMLSchema 
"http://www.oracle.com/xdb/ord.xsd" 
Element "PurchaseOrder") 
STORAGE Object-relational
TYPE "XDBPO_TYPE" 
 
Table STYLESHEET_TAB
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                                                 NUMBER
 STYLESHEET                                         XMLTYPE
 
Table SUBCATEGORY_REF_LIST_NESTEDTAB
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 COLUMN_VALUE                                       REF OF CATEGORY_TYP
 
Table WAREHOUSES
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 WAREHOUSE_ID                              NOT NULL NUMBER(3)
 WAREHOUSE_SPEC                                     SYS.XMLTYPE
 WAREHOUSE_NAME                                     VARCHAR2(35)
 LOCATION_ID                                        NUMBER(4)
 WH_GEO_LOCATION                                    MDSYS.SDO_GEOMETRY

1. Write a SQL statement to show all non-married customers’ full name, gender, income_level and total amount spent on the product ‘Video Card /32’ AND whose credit limit is less than 700.

2. Write a SQL statement to show the total amount spent on products summarized by Category and each month of each year (YYYY-MM); Order the results in descending order of YYYY-MM

3. Write a SQL statement to show the total amount spent on products summarized by product category and Year, along with RANK within each year (order by increasing RANK of amount spent)

4. Write a SQL statement to show the product category names of the products that have the smallest percentage discount (that is non-zero) off of the Product_information.list_price. (Display the top 10).

5. Who is the “best” customer? Justify your rationale and back it up with a query showing the data. You may also wish to graph your data to support your justification.

6. Write a query that shows each month/year total sales by state along with a column that shows the difference from the prior month/year sales. (Hint: Look at the LAG function)

7. Create a VIEW in your own schema that joins together all of the columns in the Customers, Orders, Order_items, Product_information and categories_tab. Be sure to “flatten” the CUST_ADDRESS, phone numbers and CUST_GEO_LOCATION. (See below Note on ‘flattening’)

8. Import all of the data from your VIEW into Microsoft Excel. Create a pivot table from the resulting data set and then summarize the data according to total sales by category_name and customer credit limit.

9. Import all of the data from your view into Tableau. Create an appropriate visualization from the resulting data set that summarizes the data according to total sales by category_name and customer income level over time.

Answer 1

1. Write a SQL statement to show all non-married customers’ full name, gender, income_level and total amount spent on the product ‘Video Card /32’ AND whose credit limit is less than 700.

Below sql statement would help for the above query:

SELECT
A.CUST_FIRST_NAME + ' '+A.CUST_LAST_NAME AS CUST_FULL_NAME,
A.GENDER,
A.INCOME_LEVEL,
B.TOTAL_AMOUNT
FROM
(SELECT CUSTOMER_ID,CUST_FIRST_NAME ,CUST_LAST_NAME, GENDER,INCOME_LEVEL
FROM
CUSTOMERS
WHERE MARITAL_STATUS ='non-married' AND CREDIT_LIMIT < 700
) A
JOIN
(
SELECT
ORDERS.CUSTOMER_ID,
ORDERS.ORDER_ID,
ORDER_ITEMS.PRODUCT_ID,
ORDER_ITEMS.UNIT_PRICE * ORDER_ITEMS.QUANTITY AS TOTAL_AMOUNT
FROM ORDERS
JOIN (ORDER_ITEMS ON ORDERS.ORDER_ID = ORDER_ITEMS.ORDER_ID)
JOIN PRODUCT_INFORMATION ON (PRODUCT_INFORMATION.PRODUCT_ID = ORDER_ITEMS.PRODUCT_ID)
WHERE PRODUCT_NAME = 'Video Card /32'
) B
ON (A.CUSTOMER_ID = B.CUSTOMER_ID);

As we can see, the query result requires Customer full name, gender, income level. Hence, the very first step would be to get these field values from customer table with the condition applied as Marital status should be non-married and credit limit should be less than 700. The part A of our SQL does the same. Next would be to find total amount spend on the said product. This will get joining 3 tables i.e. Orders, Order_items and Product_information(assuming the product name given 'Video Card /32' needs to be compared with Product_name column). The part B of above query does the same. Part A and Part B of the query is then joined on customer_id to get the common records which in turn would result with records of non-married customers with total amount spent on the product ‘Video Card /32’ AND whose credit limit is less than 700. Hope this helps.

2. Write a SQL statement to show the total amount spent on products summarized by Category and each month of each year (YYYY-MM); Order the results in descending order of YYYY-MM

Below sql statement would provide an idea to solve the said query:

SELECT A.CATEGORY_NAME,
A.ORDER_DATE AS 'MONTH OF EACH YEAR',
SUM(A.TOTAL_AMOUNT) AS TOTAL_AMOUNT
FROM
(
SELECT
ORDERS.CUSTOMER_ID,
ORDERS.ORDER_ID,
ORDER_ITEMS.PRODUCT_ID,
ORDER_ITEMS.UNIT_PRICE * ORDER_ITEMS.QUANTITY AS TOTAL_AMOUNT ,
PRODUCT_INFORMATION.CATEGORY_ID,
CATEGORIES_TAB.CATEGORY_NAME,
TO_DATE(ORDERS.ORDER_DATE,'YYYY-MM') ORDER_DATE
FROM ORDERS
JOIN (ORDER_ITEMS ON ORDERS.ORDER_ID = ORDER_ITEMS.ORDER_ID)
JOIN PRODUCT_INFORMATION ON (PRODUCT_INFORMATION.PRODUCT_ID = ORDER_ITEMS.PRODUCT_ID)
JOIN CATEGORIES_TAB ON (CATEGORIES_TAB.CATEGORY_ID =PRODUCT_INFORMATION.CATEGORY_ID)
) A
GROUP BY A.CATEGORY_NAME, A.ORDER_DATE
ORDER BY A.ORDER_DATE DESC

3. Write a SQL statement to show the total amount spent on products summarized by product category and Year, along with RANK within each year (order by increasing RANK of amount spent)

Below sql statement would provide an idea to solve the said query:

SELECT B.CATEGORY_NAME,
B.ORDER_DATE AS 'YEAR',
RANK OVER (ORDER BY B.TOTAL_AMOUNT ASC) PRODUCT_RANK
FROM
(
SELECT CATEGORY_NAME,
ORDER_DATE AS 'YEAR',
SUM(TOTAL_AMOUNT) AS TOTAL_AMOUNT
FROM
(
SELECT
ORDERS.CUSTOMER_ID,
ORDERS.ORDER_ID,
ORDER_ITEMS.PRODUCT_ID,
ORDER_ITEMS.UNIT_PRICE * ORDER_ITEMS.QUANTITY AS TOTAL_AMOUNT ,
PRODUCT_INFORMATION.CATEGORY_ID,
CATEGORIES_TAB.CATEGORY_NAME,
EXTRACT(YEAR FROM ORDERS.ORDER_DATE) ORDER_DATE
FROM ORDERS
JOIN (ORDER_ITEMS ON ORDERS.ORDER_ID = ORDER_ITEMS.ORDER_ID)
JOIN PRODUCT_INFORMATION ON (PRODUCT_INFORMATION.PRODUCT_ID = ORDER_ITEMS.PRODUCT_ID)
JOIN CATEGORIES_TAB ON (CATEGORIES_TAB.CATEGORY_ID =PRODUCT_INFORMATION.CATEGORY_ID)
) A
GROUP BY CATEGORY_NAME, ORDER_DATE
) B

Here we are following the same method to get the list of product categories as in above queries. Have used Extract function to get the year part from the date and then Rank() function to get the rank included in the result set.

4. Write a SQL statement to show the product category names of the products that have the smallest percentage discount (that is non-zero) off of the Product_information.list_price. (Display the top 10).

Below SQL statement would help to solve the above query :

SELECT TOP 10
A.CATEGORY_NAME, MIN(A.DISCOUNT_PERCENT) SMALLEST_DISCOUNT_PERCENT
FROM
(
SELECT
CATEGORIES_TAB.CATEGORY_NAME,
PRODUCT_INFORMATION.PRODUCT_ID,
(((PRODUCT_INFORMATION.LIST_PRICE - PRODUCT_INFORMATION.MIN_PRICE) *100 )/PRODUCT_INFORMATION.LIST_PRICE) AS DISCOUNT_PERCENT
FROM
PRODUCT_INFORMATION JOIN
CATEGORIES_TAB ON
(CATEGORIES_TAB.CATEGORY_ID =PRODUCT_INFORMATION.CATEGORY_ID)
WHERE (PRODUCT_INFORMATION.LIST_PRICE - PRODUCT_INFORMATION.MIN_PRICE) > 0
) A
GROUP BY A.CATEGORY_NAME

ORDER BY DISCOUNT_PERCENT

The sub query part A would result product category names with their discount percentage those that have non zero discount. We are assuming here discount as (list_price - min_price) value. On this sub-query result set, a group by clause to find the minimum of discount percentage would give us the mentioned final result set i.e. the product category names of the products that have the smallest percentage discount. 'TOP 10' clause would display only top 10 records.

Tried to provide some idea to solve the first four part/queries of the question only as time is less. For other part need some more time and details . Regarding, who is best customer?, would require the parameter details needed to find out best customer.