Assignment 5
Part 1: Subtotal Operators
Part 1 of Assignment 5 provides experience writing SELECT statements using the subtotal operators (CUBE, ROLLUP, and GROUPING SETS). For the subtotal operator problems, you should follow the examples given in the notes and textbook.
Your SELECT statements will reference the tables of the Inventory Data Warehouse, described in another document. You can use my Inventory Data Warehouse tables by prefixing the table name with my schema name (mmannino). Alternatively, you can create synonyms for my tables so that you can just use the table names. If you create synonyms, you cannot have a table name in your own schema with the same name as the synonym name. Here is an example of a CREATE SYNONYM statement for a table in my schema.
CREATE SYNONYM cust_vendor_dim FOR mmannino.cust_vendor_dim;
Subtotal Operators
Write Oracle SELECT statements for the following problem statements. You should use the CUBE, ROLLUP, or GROUPING SETS operators as indicated by problem requirements except in problems 6 and 7. Problems 6 and 7 involve SELECT statements with UNION operations rather than subtotal operators.
Query 1: Sales Order Shipments by Month and Category Code1
Write a SELECT statement to display the sum of the extended cost and the sum of the quantity. The results should include data for shipments (transaction type 5) in calendar year 2011. Summarize the result by calendar month and Address Category Code 1. The result should include the grouped columns and the full totals for every combination of grouped columns. Do not use the GROUPING SETS and UNION operators.
Query 2: Sales Order Shipments by Name, Zip, and Quarter
Write a SELECT statement to display the sum of the extended cost and the number of inventory transactions. The results should include data for shipments (transaction type 5) in calendar years 2011 and 2012. Summarize the result by calendar quarter, customer zip code, and customer name. The result should include the grouped columns and full set of subtotals for every combination of grouped columns. Do not use the CUBE or UNION operators.
Query 3: Transfers by Company and Branch Plant
Write a SELECT statement to display the sum of the extended cost and the sum of the quantity. The results should include data for transfers (transaction type 2). Summarize the result by company name and branch plant name. The result should include the grouped columns and a partial set of subtotals in order of the grouped columns (company name and branch plant name). Transfer quantities by design should sum to zero across all companies so that the grand total should be 0 for the sum of quantity and extended cost. Do not use the GROUPING SETS or UNION operators.
Query 4: Inventory Transactions by Transaction Description, Company, and Branch Plant
Write a SELECT statement to display the sum of the extended cost and the number of inventory transactions. The results should include data for all transaction types. Summarize the result by transaction description, company name, and branch plant name. The result should include the grouped columns and partial totals in order of the grouped columns (transaction description, company name, and branch plant name). Do not use the ROLLUP or UNION operators.
Query 5: Shipments in Two Years
Write a SELECT statement to display the sum of the extended cost and the number of inventory transactions. The results should include data for shipments (transaction type 5) in calendar years 2011 and 2012. Summarize the result by calendar year, calendar quarter, and customer name. The result should show the grouped columns and the normal set of group by results plus partial subtotals for year and quarter concatenated with customer name. Do not use the GROUPING SETS or UNION operators. (Hint: see the partial ROLLUP example in Part 3 of the Unit 8 lecture notes on Advanced Subtotal Operators).
Query 6: Rewrite Query 1 without CUBE, ROLLUP, or GROUPING SETS
Rewrite query 1 without the usage of the CUBE, ROLLUP, or GROUPING SETS operators. In rewriting the query, you should use NULL as the default value for placeholder columns.
Query 7: Rewrite Query 3 without CUBE, ROLLUP, or GROUPING SETS
Rewrite query 3 without the usage of the CUBE, ROLLUP, or GROUPING SETS operators. In rewriting the query, you should use NULL as the default value for placeholder columns.
Query 8: Sales Order Shipments by Name and Combination of Year and Quarter
Write a SELECT statement to display the sum of the extended cost and the number of inventory transactions. The results should include data for shipments (transaction type 5) in calendar years 2011 and 2012. Summarize the result by calendar year, calendar quarter, and customer name. The result should include the grouped columns and the full set of subtotals for customer name and the combination of year and quarter. Do not use the GROUPING SETS or UNION operators. (Hint: see the composite column example in Part 3 of the Unit 8 lecture notes on Advanced Subtotal Operators).
Query 9: Sales Order Shipments by Month and Category Code1 with Group Number
Write a SELECT statement to display the sum of the extended cost and the sum of the quantity. The results should include data for shipments (transaction type 5) in calendar year 2011. Summarize the result by calendar month and Address Category Code 1. The result should include the grouped columns and the full set of subtotals for every combination of grouped columns along with the hierarchical group number for both grouping columns. Do not use the GROUPING SETS or UNION operators. (Hint: see the group functions slide in Part 3 of the Unit 8 lecture notes on Advanced Subtotal Operators).
Query 10: Sales Order Shipments with Subtotals by Name and Partial Subtotals by Year and Quarter
Write statement to display the sum of the extended cost and the number of inventory transactions. a SELECT The results should include data for shipments (transaction type 5) in calendar years 2011 and 2012. Summarize the result by calendar year, calendar quarter, and customer name. The result should include the grouped columns and subtotals for customer name along with partial subtotals for year and quarter. Do not include the normal GROUP BY totals in the result. Do not use the UNION operator. (Hint: see the nested rollup example in Part 3 of the Unit 8 lecture notes on Advanced Subtotal Operators).
Grading
Grading is based on error severity as indicated in Table 1. The most serious error is a syntax error. The next most serious error is a missing join condition resulting in incorrect rows in the result and excessive resource consumption. Other errors and severity are shown in Table 1.
Table 1: Errors and Point Deduction
Error
Point Deduction
Syntax error
5
Missing join condition
4
Missing table or subtotal operator
3
Incorrect specification of subtotal operator
2
Missing or extra SELECT clause in a UNION query
2
Extra table, missing column, incorrect data type for a constant in a condition, poorly formatted statement
1
Completion
You should upload a file containing your Oracle SQL statements and execution results to the grade book item for part 1 of assignment 5. For the execution results, you should take a snapshot of the script output window in SQL Developer showing the execution results. You only need to show the first 10 rows or so of the result. You should paste the execution results after the associated SELECT statement. Use the following naming scheme for your assignment file: LastNameFirstNameA5P1. As part of good coding practice, you should format your statements neatly. If you are using Microsoft Word, you need to use straight quotes in your statements, not smart quotes. Microsoft Word defaults to smart quotes.
DATE \l 3/17/20 Assignment 5 Part 1 (Subtotal Operators) Page PAGE 4