Notes

A 1
USE AdventureWorks2012;
GO
SELECT *
FROM Production.Product
ORDER By Name ASC;

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

C 1-

Use Adventureworks2012;
GO
SELECT DISTINCT JobTitle
FROM HumanResources.Employee
ORDER BY Jobtitle;
GO

d-1
USE tempdb;
GO
IF OBJECT_ID (N'#Bicycles',N'U') IS NOT NULL
DROP TABLE #Bicycles;
GO
SELECT *
INTO #Bicycles
FROM AdventureWorks2012.Production.Product
WHERE ProductNumber LIKE 'BK%';
GO

d-2
USE AdventureWorks2012;
GO
IF OBJECT_ID('dbo.NewProducts', 'U') IS NOT NULL
DROP TABLE dbo.NewProducts;
GO ALTER DATABASE AdventureWorks2012 SET RECOVERY BULK_LOGGED;
GO

SELECT* INTO dbo.NewProducts
FROM Production.Product
WHERE ListPrice > $25
AND ListPrice < $100;
GO
ALTER DATABASE AdventureWORKS2012 SET RECOVERY FULL;
GO

E-1
USE AdventureWorks2012;
GO
SELECT DISTINCT Name
FROM Production.Product AS p
WHERE EXISTS
(SELECT *
FROM Production.Product AS pm
WHERE p.ProductModelID=pm.ProductModelID
AND pm.Name LIKE 'Long-Sleeve Logo Jersey½');
GO

--OR

USE AdventureWorks2012;
GO
SELECT DISTINCT Name
FROM Production.Product
Where ProductModelID IN
(SELECT PRODUCTMODELID
FROM Production.ProductModel
WHERE Name LIKE 'Long-Sleeve Logo Jersey%');
GO
USE AdventureWorks2012;
GO
SELECT DISTINCT p.LastName,p.FirstName
FROM Person.Person AS p
JOIN HumanResources.Employee AS e
ON e.BusinessEntityID=p.BusinessEntityID where 5000.00 IN
(select Bonus
FROM Sales.SalesPerson AS sp
WHERE e.BusinessEntityID=sp.BusinessEntityID);
GO

use AdventureWorks2012
go
SELECT p1.ProductModelID
from Production.product AS p1
group by p1.ProductModelID
having max(p1.ListPrice)>=ALL
(select avg(p2.ListPrice)
from production.product AS p2
where p1.ProductModelID =p2.ProductModelID);
go

use AdventureWorks2012;
go
SELECT DISTINCT pp.LastName, pp.FirstName
from person.person pp JOIN HumanResources.Employee e
ON e.BusinessEntityID=pp.BusinessEntityID where pp.BusinessEntityID IN
(select SalesPersonID
from Sales.SalesOrderHeader
where SalesOrderID IN
(select SalesOrderID
from Sales.SalesOrderDetail
where ProductID IN
(SELECT ProductID
from Production.Product p
where ProductNumber ='BK-M68B-42')));
GO

F

USE AdventureWorks2012
GO
SELECT SalesOrderID, SUM(LineTotal) AS Subtotal
FROM Sales.SalesOrderDetail
GROUP BY SalesOrderID
ORDER BY SalesOrderID;
GO



G

USE AdventureWorks2012
GO
SELECT ProductID, SpecialOfferID, AVG(UnitPrice) AS [Average Price], SUM(LineTotal) AS SubTotal
FROM Sales.SalesOrderDetail
GROUP BY ProductID, SpecialOfferID
ORDER BY ProductID;
GO


H

USE AdventureWorks2012
GO
SELECT ProductModelID, AVG(ListPrice) AS [Average List Price]
FROM Production.Product
WHERE ListPrice > $1000
GROUP BY ProductModelID
ORDER BY ProductModelID;
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

L

USE AdventureWorks2012
GO
SELECT ProductID
FROM Sales.SalesOrderDetail
WHERE UnitPrice < 25.00
GROUP BY ProductID
HAVING AVG(OrderQty) > 5
ORDER BY ProductID;
GO

M-1
USE AdventureWorks2012;
GO
SELECT ProductID,AVG(OrderQty) AS AverageQuantity, SUM (LineTotal) AS Total
FROM Sales.SalesOrderDetail
GROUP BY ProductID
HAVING SUM(LineTotal) > $2000000.00;
GO

M-2
USE AdventureWorks2012;
GO
SELECT ProductID, SUM(LineTotal)AS Total
FROM Sales.SalesOrderDetail
GROUP BY ProductID
HAVING COUNT(*) > 1500;
GO

M-3
USE AdventureWorks2012;
GO
SELECT ProductID, OrderQty,SUM(LineTotal) AS Total
FROM Sales.SalesOrderDetail
WHERE UnitPrice< $5.00
GROUP BY ProductID,OrderQty
ORDER BY ProductID,OrderQty
OPTION (HASH GROUP, FAST 10);
GO

N

USE AdventureWorks2012
GO
SELECT pp.FirstName, pp.LastName, e.NationalIDNumber
FROM HumanResources.Employee AS e WITH (INDEX(AK_Employee_NationalIDNumber))
JOIN Person.Person AS pp on e.BusinessEntityID = pp.BusinessEntityID
WHERE LastName = 'Johnson';
GO



O

USE AdventureWorks2012
GO
SELECT BusinessEntityID, JobTitle, HireDate, VacationHours, SickLeaveHours
FROM HumanResources.Employee AS e1
UNION
SELECT BusinessEntityID, JobTitle, HireDate, VacationHours, SickLeaveHours
FROM HumanResources.Employee AS e2
OPTION (MERGE UNION);
GO

p1

USE AdventureWorks2012
GO
IF OBJECT_ID ('dbo.Gloves', 'U') IS NOT NULL
DROP TABLE dbo.Gloves;
GO
SELECT ProductModelID, Name
INTO dbo.Gloves
FROM Production.ProductModel
GO

P2

USE AdventureWorks2012
GO
SELECT ProductModelID, Name
FROM Production.ProductModel
WHERE ProductModelID NOT IN (3, 4)
UNION
SELECT ProductModelID, Name
FROM dbo.Gloves
ORDER BY Name;
GO


Q-1
USE AdventureWorks2012;
GO
IF OBJECT_ID('dbo.ProductResult','U') IS NOT NULL
DROP TABLE dbo.ProductResults;
GO
IF OBJECT_ID ('dbo.Gloves','U') IS NOT NULL
DROP TABLE dbo.Gloves;
GO

SELECT ProductModelID,Name
INTO dbo.Gloves
FROM Production.ProductModel
WHERE ProductModelID IN (3,4);
GO

Q-2
USE AdventureWorks2012;
GO
SELECT ProductModelID,Name
INTO dbo.ProductResults
FROM Production.ProductModel
WHERE ProductModelID NOT IN (3,4)
UNION
SELECT ProductModelID,Name
FROM dbo.Gloves;
GO
SELECT ProductModelID,Name
FROM dbo.ProductResult

R1

USE AdventureWorks2012
GO
IF OBJECT_ID ('dbo.Gloves', 'U') IS NOT NULL
DROP TABLE dbo.Gloves;
GO
SELECT ProductModelID, Name
INTO dbo.Gloves
FROM. Production.ProductModel
WHERE ProductModelID IN (3, 4);
GO

R INCORRECT

USE AdventureWorks2012
GO
SELECT ProductModelID, Name
FROM Production.ProductModel
WHERE ProductModelID NOT IN (3, 4)
ORDER BY Name
UNION
SELECT ProductModelID, Name
FROM dbo.Gloves;
GO

R-correct
USE AdventureWorks2012;
GO
SELECT ProductModelID,Name
FROM Production.ProductModel
WHERE ProductModelID NOT IN (3,4)
UNION
SELECT ProductModelID,Name
FROM dbo.Gloves
ORDER BY Name;
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