need help writing the codes for this

Question

need help writing the codes for this

sergehermane

SSC Enthusiast

Points: 107
More actions
March 9, 2017 at 7:29 pm

#335615

1 - Write a query witch returns the following columns:
2 - a- OrderID
        b- AverageUnitPrice (per order)
      c- TotalOrderAmount (per order) - Total order amount will be (Price * Quantity * (1 - Discount))
3 - Using a SELECT from task 1 create:
4- a- A view - Name it "Sales.v_OrderInfo"
   b - A table valued function - Name it "Sales.tf_OrderInfo". Function should accept a parameter "OrderID"
5- Using a SELECT from task 1 and the objects you created in task 2, please write a select statement which will return the following columns:
6 a- OrderID    - the id of the order
   b-LastName    - Last name of the Contact name of the Customer
   c- FirstName    - First Name of the Contact name of the Customer
d - ProductName    - The name of the product
   e -Unitprice    - The price of the product
   f- OrderDate    - The date of the order (date only)
   g - AverageUnitPrice    - Average unit price (for all orders) - use subquery
   h - DT_AverageUnitPrice    - Average unit price PER ORDER using derived table
   i - DT_TotalOrderAmount    - Total Order Amount PER ORDER using derived table
   j- CTE_AverageUnitPrice    - Average unit price PER ORDER using Common Table Expression (CTE)
   k- CTE_TotalOrderAmount    - Total Order Amount PER ORDER using Common Table Expression (CTE)
l - V_AverageUnitPrice    - Average unit price PER ORDER using view
   m - V_TotalOrderAmount    - Total Order Amount PER ORDER using view
   n - SUB_AverageUnitPrice    - Average unit price PER ORDER using corelated sub query
   o- SUB_TotalOrderAmount    - Total Order Amount PER ORDER using corelated sub query
   p -TF_AverageUnitPrice    - Average unit price PER ORDER using table valued function
    q - TF_TotalOrderAmount    - Total Order Amount PER ORDER using table valued function
Filter the result set to include only orders name in the year 2006 where the category of the product is "Produce"
To get First and Last name of the contact you will have to "split" the string "ContactName". Use comma (,) as a delimiter
Write as script which will clean up the objects you created!
So, to recap:
Once I run your script:
The view and the function will be created
The select will run and I will get the result set with 17 columns and 26 rows
   Task 1 should not return a result set. It is just a preparation step. Make sure you comment it out
The view and the function will be dropped

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004649 More actions · Answer 1

sergehermane - Thursday, March 9, 2017 7:29 PM
1 - Write a query witch returns the following columns:
2 - a- OrderID
        b- AverageUnitPrice (per order)
      c- TotalOrderAmount (per order) - Total order amount will be (Price * Quantity * (1 - Discount))
3 - Using a SELECT from task 1 create:
4- a- A view - Name it "Sales.v_OrderInfo"
   b - A table valued function - Name it "Sales.tf_OrderInfo". Function should accept a parameter "OrderID"
5- Using a SELECT from task 1 and the objects you created in task 2, please write a select statement which will return the following columns:
6 a- OrderID    - the id of the order
   b-LastName    - Last name of the Contact name of the Customer
   c- FirstName    - First Name of the Contact name of the Customer
d - ProductName    - The name of the product
   e -Unitprice    - The price of the product
   f- OrderDate    - The date of the order (date only)
   g - AverageUnitPrice    - Average unit price (for all orders) - use subquery
   h - DT_AverageUnitPrice    - Average unit price PER ORDER using derived table
   i - DT_TotalOrderAmount    - Total Order Amount PER ORDER using derived table
   j- CTE_AverageUnitPrice    - Average unit price PER ORDER using Common Table Expression (CTE)
   k- CTE_TotalOrderAmount    - Total Order Amount PER ORDER using Common Table Expression (CTE)
l - V_AverageUnitPrice    - Average unit price PER ORDER using view
   m - V_TotalOrderAmount    - Total Order Amount PER ORDER using view
   n - SUB_AverageUnitPrice    - Average unit price PER ORDER using corelated sub query
   o- SUB_TotalOrderAmount    - Total Order Amount PER ORDER using corelated sub query
   p -TF_AverageUnitPrice    - Average unit price PER ORDER using table valued function
    q - TF_TotalOrderAmount    - Total Order Amount PER ORDER using table valued function
Filter the result set to include only orders name in the year 2006 where the category of the product is "Produce"
To get First and Last name of the contact you will have to "split" the string "ContactName". Use comma (,) as a delimiter
Write as script which will clean up the objects you created!
So, to recap:
Once I run your script:
The view and the function will be created
The select will run and I will get the result set with 17 columns and 26 rows
   Task 1 should not return a result set. It is just a preparation step. Make sure you comment it out
The view and the function will be dropped

Please, do your own homework. You're the one working for the grade and maybe a career. At least try.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

sergehermane SSC Enthusiast Points: 107 More actions · Answer 2

I already did this but i got stucked in the middle
please help

SELECT
    o.orderid,
    AVG(d.qty * d.unitprice) AS avgunitpriceperorder,
    SUM(d.qty * d.unitprice * (1-discount)) AS totalordeamountperorder
FROM Sales.Orders AS o
INNER JOIN Sales.OrderDetails AS d ON d.orderid = o.orderid
GROUP BY o.orderid;
SELECT
    o.orderid,
    AVG(d.qty * d.unitprice) AS avgunitpriceperorder,
    SUM(d.qty * d.unitprice * (1-discount)) AS totalordeamountperorder
FROM Sales.Orders AS o
INNER JOIN Sales.OrderDetails AS d ON d.orderid = o.orderid
GROUP BY o.orderid;
GO
CREATE VIEW sales.v_orderinfo AS
SELECT
    o.orderid,
    AVG(d.qty * d.unitprice) AS avgunitpriceperorder,
    SUM(d.qty * d.unitprice * (1-discount)) AS totalordeamountperorder
FROM Sales.Orders AS o
INNER JOIN Sales.OrderDetails AS d ON d.orderid = o.orderid
GROUP BY o.orderid;
GO
CREATE FUNCTION Sales.tf_orderinfo
(@orderID AS INT) RETURNS TABLE
AS
RETURN
SELECT
    o.orderid,
    AVG(d.qty * d.unitprice) AS avgunitpriceperorder,
    SUM(d.qty * d.unitprice * (1-discount)) AS totalordeamountperorder
FROM Sales.Orders AS o
INNER JOIN Sales.OrderDetails AS d ON d.orderid = o.orderid
GROUP BY o.orderid;

Luis Cazares SSC Guru Points: 183706 More actions · Answer 3

sergehermane - Thursday, March 9, 2017 9:10 PM
I already did this but i got stucked in the middle
please help

Why did you got stuck? What don't you understand?

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2