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

[answerhappygod]

Assessment Information Schema Used For Assessment Oe Use The Script Below To Explore The Schema S Tables Ensure Maximu 1 (41.94 KiB) Viewed 42 times

Assessment Information Schema Used For Assessment Oe Use The Script Below To Explore The Schema S Tables Ensure Maximu 2 (35.64 KiB) Viewed 42 times

Assessment Information Schema used for assessment: 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.categorins_tab SELECT FROM on.customers; SELECT . FROM De orders: SELECT . FROM oe.order_items/ SELECT . FROM oe.product_information: SELECT all cons columns.owner AS schema nane all cons columna.table name, all cons columna.column name, all constraints.constraint type, all_com_columna.position. all constraints.status all constraints, all cons columna P FROM WHERE all constraints.constraint_type IN ('58) AND 11 con columns.owner="0" 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. 1. Write a query that returns a result set containing the customer ID, customer first name, customer last name, order ID, order date, and order total for all orders with an order total greater than or equal to 1.5 times the average order total. Sort the result set by order total, descending. ITSS 4300 Assessment 3A CUSTOMER ID CUST_FIRST NAME CUST LAST_ORDER_ID Ishary doldle 150 sleter ORDER DATE ORDER TOTAL 8-06-07 11.34.11.2 84-305-87 4.43.12.554435 P 252694.3 2. Write a query that returns a result set containing the details of the first order placed based on the order date. The result set should include order ID, order date, order total, line-item ID, product ID, product name, unit price, and quantity. There should be one row for each line-item. Sort the result-set by line-item ID.
ITSS 4300 Assessment 3A CAST FIRST CLA 347 2. Write a query that returns a result set containing the details of the first order placed based on the order date. The result set should include order ID, order date, order total, line-item ID. product ID, product name, unit price, and quantity There should be one row for each line-item. Sort the result-set by line-temID. ascending 3. Write a query that returns a result set containing details of each sales representative's largest order based on order total. The result set should include the sales representative's ID, the customer ID the representative made the sale to, order ID, and order total. Sort the result set by order total, descending SALES RIP CUSTOMER ORDERER AL ORDER P 4. Write a query that returns a result set containing the order ID, order date, and order total for each order. Instead of using the already existing order total column in the orders table, create a derived column that takes the sum of unit price multiplied by quantity for each line item in each order. If done properly, the derived column and the original order total column will contain the same value. Alias the derived column as "manual_order_total"Sort the result set by the derived column, descending zi ITSS 4300 Assessment 3A THE IN 5. Write a query that retums a result set containing the customer ID, order ID, and order date for each customer's first and last order. If a customer has only made one order, then there should only be one row for the customer, otherwise two rows Assign the same integer value to ties in order date. Use consecutive integers (eg. 1. 1. 2. 3, 4, 4, 5). Alias any derived columns as you see fit. Sort the result set by customer ID, ascending DASS 4. 24 23 674.34 ORDER TOTAL ITAL