Assignment Information Schema used for assignment: OE Use the script below to explore the schema's tables. Ensure maximu

[answerhappygod]

Assignment Information Schema Used For Assignment Oe Use The Script Below To Explore The Schema S Tables Ensure Maximu 1 (49.24 KiB) Viewed 38 times

Assignment Information Schema Used For Assignment Oe Use The Script Below To Explore The Schema S Tables Ensure Maximu 2 (68.44 KiB) Viewed 38 times

Assignment Information Schema Used For Assignment Oe Use The Script Below To Explore The Schema S Tables Ensure Maximu 3 (67.48 KiB) Viewed 38 times

Assignment Information Schema Used For Assignment Oe Use The Script Below To Explore The Schema S Tables Ensure Maximu 4 (37.27 KiB) Viewed 38 times

Assignment Information Schema used for assignment: OE Use the script below to explore the schema's tables. Ensure maximum row preference is set to 5000 to obtain a fuller understanding of each table. SELECT FROM oe.categories_tab; SELECT . FROM ce.customers; SELECT FROM oe.orders; SELECT . FROM oe.order_items; SELECT FROM oe.product_information: SELECT all cons_columns.owner AS schema_name, all cons_columns.table_name, all cons columns.column_name, all constraints.constraint_type, all cons_columns.position, all constraints.status FROM all constraints, all cons_columns WHERE all constraints.constraint _type IN AND all cons_columns.owner = 'OE' AND all constraints.constraint_name all cons columns.constraint_name AND all constraints.owner all cons_columns.owner ORDER BY all cons_columns.owner, all cons_columns.table_name, all cons_columns.position: For each question, write a query that solves for the specific task. The columns in the output generated by your query should be ordered in the same order observed in the screenshot accompanying each prompt. The screenshots are of result sets generated by correct, proposed solutions to each problem. They may or may not contain the entire result set. Problems 1-4 should be solved using a subquery in a WHERE clause. Problem 5 should be solved using a subquery in the HAVING clause. Problems 6-7 should be solved using a subquery in the FROM clause. Problems 8-12 should be solved using analytical functions. 1. Retrieve all orders placed with an order date equal to the most recent day a customer placed an order. Include the order ID, order date, customer ID, and order total.
6. For each order, retrieve the customer ID, order ID, order total, minimum order total for the customer, and maximum order total for the customer. Alias the minimum and maximum order total as "min_order_total" and "max_order_total," respectively. Only include customers that have placed more than 1 order. Sort the result set by customer ID (ascending) and order total (descending). CUSTOMER_ID ORDER_ID ORDER TOTAL MIN_ORDER_TOTAL MAX_ORDER_TOTAL 78279.6 48552 33893.6 29669.9 101 101 101 101 101 201 2450 CUSTOMER CUST FIRST NAME CUST LAST NAVEDE 2413 2447 2430 7. For each order, include the customer ID, customer first name, customer last name, order ID, order date, order total, line-item ID(s), product ID(s), unit price(s), and quantity. In each row, include the line-item count for the order. Line- item count is defined as the number of line-items in the order. Alias this derived column as "line_item_count." Sort the result set by customer ID (ascending), order date (ascending), order ID (ascending), and line-item ID (ascending). 101 101 CUSTOMER_ID ORDER ID 29669.9 29669.9 29669.9 29669.9 2458 2430 ORDER DATE 24-46-47 1.34.12.1949 78279.6 78279.6 78279.6 78279.6 779.4 ORDER TOTAL LINE TEPLINE_T 8. For each order, retrieve the customer ID, order ID, order date, and order total. In addition to this data, create a derived column that assigns an integer value based on the order date for each customer's orders. The value of 1 should be assigned to the customer's first order placed, 2 to the second order placed, and so on. Alias this column as "order_date_sequence." Assign the same integer value to ties in order date. Use consecutive integers (e.g., 1, 1, 2, 3, 4, 4, 5). Sort the result set by customer ID (ascending) and order date (ascending). PRODUCT UNIT PRICE QUANTITY 79 2 ORDER DATE ORDER TOTAL ORDER_DATE_SEQUENCE 16-AUG-07 03.34.12.234359 PM 78279.6 02-OCT-07 06.18.36.663332 AM 29669.9 9. Building onto your solution from problem 8, only include each customer's first order. 340 111
CUSTOMER ID ORDER_ID 101 102 101 CUSTOMER_ID ORDER_ID ORDER TOTAL ORDER_TOTAL_SEQUENCE 29669.9 33893.6 40552 78279.6 5610.6 101 10. For each customer, rank the customers' order totals among all other order totals for the customer. Include customer ID, order ID, order total, and the derived column used to rank the order totals for each customer. Alias the ranking column as "order_total_sequence." Assign the same integer value to ties in order total. Use consecutive integers (e.g., 1, 1, 2, 3, 4, 4, 5). Sort the result set by customer ID (ascending) and order total (ascending). 101 101 102 2458 2431 PRODUCT_ID 2870 3097 2430 ORDER DATE ORDER TOTAL ORDER DATE SEQUENCE 16-AUG-07 03.34.12.234359 PM 78279.6 14-SEP-06 07.03.84.763452 AM 5610.6 2447 2413 2458 2431 PRODUCT_NAME 1 Pencil - Mech Cable Connector - 32R 2 3 11. Retrieve the product ID, product name, list price, and minimum price for each product. In addition to this data, create a derived column that ranks the markup (i.e., list price - minimum price) for each product among all other products. Alias this column as "product_markup_sequence." Assign the same integer value to ties in markup values. Use consecutive integers (e.g., 1, 1, 2, 3, 4, 4, 5). Only include products where both, list price and minimum price, are available. Sort/order the result set by markup (list price - min price), ascending. 1 4 1 LIST PRICE MIN PRICE PRODUCT MARKUP_SEQUENCE 12. Building on to your solution to the problem above, retrieve only those product IDS with a rank value of 1. Include the product ID, product name, list price, minimum price, category name, and product markup sequence in the output. In addition, ensure that the ranking methodology is adjusted to retrieve the product(s) with the largest markup. This requires a slight adjustment to the solution for problem 11.
12. Building on to your solution to the problem above, retrieve only those product IDs with a rank value of 1. Include the product ID, product name, list price, minimum price, category name, and product markup sequence in the output. In addition, ensure that the ranking methodology is adjusted to retrieve the product(s) with the largest markup. This requires a slight adjustment to the solution for problem 11. ITSS 4300 Assignment 3 PRODUCT_ID PRODUCT_NAME LIST_PRICE MIN_PRICE CATEGORY_NAME PRODUCT_MARKUP_SEQUENCE 2779 Desk OS/O/F 3980 3347 office1 1