SQL Database Assignment

SQL Database Assignment

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 10

Part a

Suppose 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.1

Table P10.1 The Database for Problem 1

Table name: PRODUCT Table name: PART

PROD_CODE	PROD_QOH		PART_CODE	PART_QOH
ABC	1,205		A	567
			B	498
			C	549

Given 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 b

Based on the following query:

SELECT P_CODE, P_QOH*P_PRICE

FROM PRODUCT

WHERE 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 SQL

Accomplish 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)

"You need a similar assignment done from scratch? Our qualified writers will help you with a guaranteed AI-free & plagiarism-free A+ quality paper, Confidentiality, Timely delivery & Livechat/phone Support.

Discount Code: CIPD30

Click ORDER NOW..