Notes

B)
***
USE AdventureWorks2012;
GO
Select p.Name AS ProductName,
NonDiscountSales= (Orderqty * UnitPrice),
Discounts=((OrderQty * UnitPrice) * UnitPriceDiscount)
FROM Production.Product AS p
INNER JOIN Sales.SalesOrderDetail AS sod
ON p.ProductID = sod.ProductID
ORDER BY ProductName DESC;
GO
---
USE AdventureWorks2012
GO
SELECT 'Total income is', ((OrderQty * UnitPrice) * (1.0 - UnitPriceDiscount)), 'for',
p.Name AS ProductName
FROM Production.Product AS p
INNER JOIN Sales.SalesOrderDetail AS sod
On p.ProductID= sod.ProductID
ORDER BY ProductName ASC;
GO
*****
I.
USE AdventureWorks2012;
GO
SELECT AVG(OrderQty) AS [Averae Quantity],
NonDiscountSales = (OrderQty* UnitPrice)
FROM Sales.SalesOrderDetail
GROUP BY (OrderQty * UnitPrice)
ORDER BY (OrderQty * UnitPrice) DESC;
GO

J.
USE AdventureWorks2012;
GO
SELECT ProductID, AVG(UnitPrice) AS [Average Price]
FROM Sales.SalesOrderDetail
WHERE OrderQty >10
GROUP BY ProductID
ORDER BY AVG (UnitPrice);
GO
K.
USE AdventureWorks2012;
GO
SELECT ProductID
FROM Sales.SalesOrderDetail
GROUP BY ProductID
HAVING AVG(OrderQty) > 5
ORDER BY ProductID;
GO
---
USE AdventureWorks2012;
GO
SELECT SalesOrderID, CarrierTrackingNumber
FROM Sales.SalesOrderDetail
GROUP BY SalesOrderID,CarrierTrackingNumber
HAVING CarrierTrackingNumber LIKE '4BD%'
GO

S
USE AdventureWorks2012;
GO
IF OBJECT_ID('dbo.EmployeeOne','U') IS NOT NULL
DROP TABLE dbo.EmployeeOne;
GO
IF OBJECT_ID('dbo.EmployeeTwo','U') IS NOT NULL
DROP TABLE dbo.EmployeeTwo;
GO
IF OBJECT_ID('dbo.EmployeeThree','U') IS NOT NULL
DROP TABLE dbo.EmployeeThree;
GO
SELECT pp.LastName, pp.FirstName,e.JobTitle
INTO dbo.EmployeeOne
FROM Person.Person AS pp JOIN HumanResources.Employee AS e
ON e.BusinessEntityID= pp.BusinessEntityID
WHERE LastName = 'Johnson';
GO
SELECT pp.LastName, pp.FirstName,e.JobTitle
INTO dbo.EmployeeTwo
FROM Person.Person AS pp JOIN HumanResources.Employee AS e
ON e.BusinessEntityID= pp.BusinessEntityID
WHERE LastName = 'Johnson';
GO
SELECT pp.LastName, pp.FirstName,e.JobTitle
INTO dbo.EmployeeThree
FROM Person.Person AS pp JOIN HumanResources.Employee AS e
ON e.BusinessEntityID= pp.BusinessEntityID
WHERE LastName = 'Johnson';
GO
-- Union ALL
SELECT LastName,FirstName,JobTitle
FROM dbo.EmployeeOne
UNION ALL
SELECT LastName,FirstName,JobTitle
FROM dbo.EmployeeTwo
UNION ALL
SELECT LastName,FirstName,JobTitle
FROM dbo.EmployeeThree;
GO
SELECT LastName,FirstName,JobTitle
FROM dbo.EmployeeOne
UNION
SELECT LastName,FirstName,JobTitle
FROM dbo.EmployeeTwo
UNION
SELECT LastName,FirstName,JobTitle
FROM dbo.EmployeeThree;
GO
SELECT LastName,FirstName,JobTitle
FROM dbo.EmployeeOne
UNION ALL
(
SELECT LastName,FirstName,JobTitle
FROM dbo.EmployeeTwo
UNION
SELECT LastName,FirstName,JobTitle
FROM dbo.EmployeeThree
);
GO