You Are Eligible For 15% Discount This Month!
This is broken into three sections: Part A, is question based, whereas Part B relies on a query, and questions based on the query. The third part is based on using a database, writing code, and explaining parts of the code. Please, no plagiarism, it must be at a university standard. See below, attached is the script to create the database for the Assignment 10 SQL portion of the assignment.Assignment Week 10Part aSuppose you are a manufacturer of product ABC, which is composed of parts A, B, and C. Each time a new product is created, it must be added to the product inventory, using the PROD_QOH in a table named PRODUCT. And each time the product ABC is created, the parts inventory, using PART_QOH in a table named PART, must be reduced by one each of parts A, B, and C. The sample database contents are shown in Table P10.1Table P10.1 The Database for Problem 1Table name: PRODUCT Table name: PARTPROD_CODEPROD_QOH PART_CODEPART_QOHABC1,205 A567 B498 C549Given that information, answer the following questions.1. How many database requests can you identify for an inventory update for both PRODUCT and PART?2. Using SQL, write each database request you identified in problem 1.3. Write the complete transaction(s) all of the above in a batch transaction.Part bBased on the following query:SELECT P_CODE, P_QOH*P_PRICEFROM PRODUCTWHERE P_QOH*P_PRICE > (SELECT AVG(P_QOH*P_PRICE) FROM PRODUCT)1. What is the likely data sparsity of the P_QOH and P_PRICE columns?2. Should you create an index, what would the index column(s) be, and why should you create that index?(50 Points)Assignment Week 10 SQLAccomplish the following exercise and copy/paste the results into your week 10 assignment submission Word document. Using ch10_abc_markets database, write the SQL code to select the invoice year from inv_date with the column name invoiceYear, the invoice month from inv_date with the column name invoiceMonth, the invoice day from inv_date with the column name invoiceDay, and the sum of the invoice total with the column name invoiceTotal from the invoice table. Group the results by year, month, and day using the ROLLUP subclause. QUESTION: What does the rollup subclause provide in terms of grouping and how does it differ from the CUBE subclause? Using ch10_abc_markets database and line table, write the SQL code to select the invoice number, line number and the sum of the line price as sumLP. Group the results using grouping sets of invoice number and line number to show the sumLP by invoice number order. QUESTION: What is the purpose of the GROUPING SETS subclause and what is its primary use?Once you have accomplished that action, paste the script into your assignment submission document for this part of your assignment. (25 Points)