SQL Problem http://imgur.com/v3ViaAx You can use the Adventure_Works 2014 data b

Question

SQL Problem

http://imgur.com/v3ViaAx

You can use the Adventure_Works 2014 data base, get the avg price and sum of yr-to-date sales, grouped by prodID and specialofferID in Sales/SalesOrderDetal table. Submit SQL CODE

2. Pick a table of ur choce in Adventure_Works 2014 data base, write SQL statements using CAST&CONVERT. Desrcibe its difference. Submit SQL Code

3. Make SQL statement that shows shipping methds where annual total due is > 5 million. The data comes from Purchsing.PurchaseOrderHeder and Purchasing.ShipMetod tables in Adventure_Works 2014 data base. Submit SQL code

4. Make and execute stored procedure that retrieve data from Person.Adress table in Adventure_Works 2014 data base. Submit SQL code

5. Make and execute single statement with left join and right join. Use any table inAdventure_Works 2014 data base. Submit SQL code

6. Use the PurchseOrderHeader table Adventure_Works 2014 data base, make a PIVOT statement to get report pic below. Submit SQL code

url : http://imgur.com/ZgYd22s

Explanation / Answer

1)

*** we are using gruop by command and for total subTotal using SUM ***

select Average_Price, SUM(subTotal)
from SalesOrderDetail
group by prodID, SpecialofferID;

-----------------------------------------------------------------------------------------------
2)
-- Use CAST , cast decimal price to int
USE AdventureWorks2012;
GO
SELECT SUBSTRING(Name, 1, 30) AS ProductName, ListPrice
FROM Production.Product
WHERE CAST(ListPrice AS int) LIKE '3%';
GO

-- Use CONVERT. , convert decimal price to int

USE AdventureWorks2012;
GO
SELECT SUBSTRING(Name, 1, 30) AS ProductName, ListPrice
FROM Production.Product
WHERE CONVERT(int, ListPrice) LIKE '3%';
GO

-----------------------------------------------------------------------------------------------------
3)
SELECT SUM(annualTotal) FROM PurchaseOrderHeader;
SELECT SUM(annualTotal) FROM ShipMetod;

------------------------------------------------------------------------------------------------

4)
CREATE PROCEDURE GetAddress
AS
SELECT * FROM AdventureWorks.Person.Address
GO

------------------------------------------------------------------------------------------------
5)
SELECT con.FirstName, con.LastName, emp.Title
FROM Person.Contact con
LEFT JOIN HumanResources.Employee emp
ON con.ContactID = emp.ContactID

SELECT con.FirstName, con.LastName, emp.Title
FROM Person.Contact con
RIGHT JOIN HumanResources.Employee emp
ON con.ContactID = emp.ContactID

-------------------------------------------------------------------------------------------------------------
6)
USE AdventureWorks2008R2;
GO
SELECT VendorID, [250] AS Emp1, [251] AS Emp2, [256] AS Emp3, [257] AS Emp4, [260] AS Emp5
FROM
(SELECT PurchaseOrderID, EmployeeID, VendorID
FROM Purchasing.PurchaseOrderHeader) p
PIVOT
(
COUNT (PurchaseOrderID)
FOR EmployeeID IN
( [250], [251], [256], [257], [260] )
) AS pvt
ORDER BY pvt.VendorID;

----------------------------------------------------------------------------------------------------------

Related Questions

Navigate

• Browse (All)
• Browse S
• Subjects

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