Can anyine show me how to do these joins? Please? Im having a problem with the c

Question

Can anyine show me how to do these joins? Please? Im having a problem with the correct syntax from start to finish.... Please help

continue to assume the role of a data analyst at Adventure Works Cycling Company. As you work to address the business problem surrounding sales of the "LL Road Frame-Black 60," you must continue to refine the data by condensing it into two tables. This requires you to join the table data sets together. While this is an important process, you must be aware of the data integrity issues that can occur as a result of completing joins.

Complete the steps below to practice joins.

Combine MyProduct and MyPriceHistory displaying the fields into a new temporary table called MyProductPriceHistory. Display ProductID, Name, EndDate, and List Price.

Combine Product and SalesOrderDetail displaying the fields into a new temporary table called MySalesOrderDetail byName. Display ProductID, SalesOrderID, and Quantity.

Combine SalesOrderDetail by Name and all SalesOrderHeader OrderDates displaying the fields into a new temporary table called MySalesHistory. Display ProductID, Name, OrderDate, and SumOfQuantity. This will require you using the SUM and GROUP BY statements.

Create a Word document that includes the SQL query code used for each of the joins listed. Additionally, identify and analyze the data integrity issues you encountered in SQL. Discuss bad queries and bad table data in your analysis. Explain the impact of the data integrity issues, what needs to be rewritten to in the code, and specifically what needs to be done to fix the data integrity issues created by the join. Please note that as part of your final project for the course you will be adding these new tables to the ERD you created in Topic 5.

Explanation / Answer

Answer: For the given cases, the join to be performed on the given tables is "INNER JOIN". In INNER JOIN, all matching records from the concerned tables (two or more) are returned. For the given cases, these joins can be performed as given below:

Combine MyProduct and MyPriceHistory displaying the fields into a new temporary table
called MyProductPriceHistory.

------------------------------
SELECT *
INTO MyProductPriceHistory
FROM MyProduct
INNER JOIN MyPriceHistory
ON MyProduct.ProductID=MyPriceHistory.ProductID;
------------------------------

Display ProductID, Name, EndDate, and List Price.

---------------------------------
SELECT ProductID, Name, EndDate, List Price FROM MyProductPriceHistory;
---------------------------------

Combine Product and SalesOrderDetail displaying the fields into a new temporary table
called MySalesOrderDetail by Name.

-----------------------
SELECT *
INTO MySalesOrderDetail
FROM Product
INNER JOIN SalesOrderDetail
ON Product.Name=SalesOrderDetail.Name;
--------------------------

Display ProductID, SalesOrderID, and Quantity.

-------------------------------
SELECT ProductID, SalesOrderID, Quantity FROM MySalesOrderDetail;
-------------------------------

Combine SalesOrderDetail by Name and all SalesOrderHeader OrderDates displaying the fields
into a new temporary table called MySalesHistory.

-------------------------------
SELECT *
INTO MySalesHistory
FROM SalesOrderDetail
INNER JOIN SalesOrderHeader
ON SalesOrderDetail.Name=SalesOrderHeader.Name;
-------------------------------

Display ProductID, Name, OrderDate, and SumOfQuantity.

--------------------------------
SELECT ProductID, Name, OrderDate, Sum(Quantity) SumOfQuantity FROM MySalesHistory GROUP BY ProductID;
--------------------------------

Note: It would have been better if you had provided the structure of concerned tables.

Related Questions

Navigate

• Browse (All)
• Browse C
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.