docx
keyboard_arrow_up
School
Grand Canyon University *
*We aren’t endorsed by this school
Course
605
Subject
Information Systems
Date
Jun 13, 2024
Type
docx
Pages
3
Uploaded by ChancellorPencilIbis60
Part1
:
1.
Using what you have learned, replicate the data found in #MySalesOrderDetailbyDate
and #MyProductPriceHistory using a single SELECT statement for each query. Refer to
the Topic 5 assignment, "Creating an Entity Relationship Diagram (ERD)" for this
information.
Select *
From Production.Product as p
Inner Join Production.ProductListPriceHistory as l on p.ProductID = l.ProductID
Inner Join Sales.SalesOrderDetail as s on s.ProductID = l.ProductID
InnerJoin Sales.SalesOrderHeader as h on h.SalesOrderID = s.SalesOrderID
Where p.ProductID = ‘723’
2.
Modify the <MyProductPriceHistory and #MySalesOrderDetailbyDate> queries
completed in the Topic 6 assignment and present it in an easy-to-read format. To do this,
write the query in a single SELECT statement without temporary tables, using
abbreviations and line spacing.
Select p.ProductID,
p.Name,
l.EndDate,
l.ListPrice,
h.OrderDate,
Sum(s.OrderQty) as SumofQuantity
From Production.Product as p
Inner Join Production.ProductListPriceHistory as l on p.ProductID = l.ProductID
Inner Join Sales.SalesOrderDetail as s on s.ProductID = l.ProductID
InnerJoin Sales.SalesOrderHeader as h on h.SalesOrderID = s.SalesOrderID
Where p.ProductID = ‘723’
Group By p.ProductID, p.Name, l.EndDate, l.ListPrice, h.OrderDate
Part 2:
Karen Berge, a document control assistant at the company, comes to you with a request. Karen
wants the titles and file names of all of the documents she has produced and has asked you to
generate this information for her. Using what you have learned, produce a query in one statement
to give her the information she needs. Note that constructing the query will require some
thinking outside the box since the relationships are not well-documented.
Select
p.BusinessEntityID
p.LastName
p.FirstName
hr.JobTitle,
d.Title,
d.FileName
From Person.Person as p
left join HumanResources.Employee hr on p.BusinessEntityID = hr.BusinessEntityID
left join Production.Document d on p.BusinessEntityID = d.Owner
Where p.FirstName= 'Karen' and p.LastName = Berge'
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
- Access to all documents
- Unlimited textbook solutions
- 24/7 expert homework help