Notes

--Tables:-
--[HumanResources.Employee]--, --[ HumanResources.Department]--,--[Person.Person ]--,--[Sales.SalesPerson]--,--[Sales.SalesOrderHeader]--, --[Sales.SalesOrderDetail]--,--[Production.Product]--

-- <1> --
-- Write a query using the Sales.SalesOrderDetail table to display a value (“Low order” or “Between 10–19 order” or “Between 20–29 order” or “Between 30–39 order” or
-- “40 and over order”) based on the OrderQty value by using the CASE function.Include the SalesOrderID and OrderQty columns in the results.
--SELECT * FROM [Sales].[SalesOrderDetail]

SELECT SalesOrderID
,OrderQty AS Quantity
,CASE WHEN OrderQty between 10 AND 19 THEN 'Between 10–19 order'
WHEN OrderQty between 20 AND 29 THEN 'Between 20–29 order'
WHEN OrderQty between 30 AND 39 THEN 'Between 30–39 order'
WHEN OrderQty > 40 THEN '40 and over order'
ELSE 'Low order'
END AS OrderType
from [Sales].[SalesOrderDetail]

-- <2> --
-- Write a query that returns all the rows from the Sales.SalesPerson table joined to the Sales.SalesOrderHeader table along with the SalesOrderID column even if no orders
-- match. Include the SalesPersonID and SalesYTD columns, salesperson’s name in the results.
--select * from Sales.SalesPerson
--select * from Sales.SalesOrderHeader

SELECT soh.SalesOrderID AS OrderID
,soh.SalesPersonID AS SalesPersonId
,SP.SalesYTD
,p.FirstName+' '+p.LastName AS SalesPersonName
FROM Sales.SalesOrderHeader soh
RIGHT JOIN Sales.SalesPerson SP ON SP.BusinessEntityID=soh.SalesPersonID
INNER JOIN Person.Person p ON soh.SalesPersonID=p.BusinessEntityID

-- <3> --
-- Write a query that returns a count of detail lines in the Sales.SalesOrderDetail table by SalesOrderID. Include only those sales that have more than five detail lines.
--SELECT * FROM [Sales].[SalesOrderDetail]

SELECT SalesOrderID AS SalesOrderId
,COUNT(SalesOrderDetailID) AS DetailLineCount
FROM Sales.SalesOrderDetail
GROUP BY SalesOrderID
HAVING COUNT(SalesOrderDetailID) > 5

-- <4> --
-- Write a query that displays all customers along with the orders placed in 2001. Use a common table expression to write the query and include the CustomerID, SalesOrderID, and OrderDate columns in the results.

WITH CustomerDetail
AS
(
SELECT soh.CustomerID
,soh.SalesOrderID
,soh.OrderDate
FROM [Sales].[SalesOrderHeader] AS soh
WHERE YEAR(soh.OrderDate) = 2001
)

SELECT * FROM CustomerDetail

-- <5> --
-- Write a query using the Sales.SalesOrderHeader, Sales.SalesOrderDetail, and Production.Product tables to display the total sum of products by ProductID and OrderDate.
--SELECT * FROM [Sales].[SalesOrderDetail]
--SELECT * FROM [Sales].[SalesOrderHeader]
--SELECT * FROM [Production].[Product]

SELECT p.ProductID AS ProductId
,soh.OrderDate AS OrderDate
,SUM(sod.OrderQty) As SumOfProduct
FROM Sales.SalesOrderHeader soh
INNER JOIN Sales.SalesOrderDetail sod ON sod.SalesOrderID = soh.SalesOrderID
INNER JOIN Production.Product p ON sod.ProductID = p.ProductID
GROUP BY p.ProductID,soh.OrderDate ORDER BY p.ProductID;

-- <6> --
-- Write a query that joins the HumanResources.Employee table to the Person.Person table so that you can display the FirstName, LastName, and HireDate columns for each employee.
-- Display the JobTitle ealong with a count of employees for the title. (use CTE)
--SELECT * FROM [HumanResources].[Employee]
--SELECT * FROM [Person].[Person]

WITH HumanResourcesToPerson
AS
(
SELECT pp.FirstName
,pp.LastName
,he.HireDate
,he.JobTitle
,COUNT(he.JobTitle) As JobTitleCount FROM [HumanResources].[Employee] AS he
INNER JOIN [Person].[Person] AS pp ON he.BusinessEntityID = pp.BusinessEntityID
GROUP BY pp.FirstName,pp.LastName,he.HireDate,he.JobTitle
)
SELECT * FROM HumanResourcesToPerson
-- <7> --
-- Write a script that declares two integer variables called @MaxID and @MinID. Use the variables to print the highest and lowest SalesOrderID values from the Sales.SalesOrderHeader table.
DECLARE @MaxID INT,@MinID INT
SELECT @MinID=MIN(SalesOrderID),@MaxID=MAX(SalesOrderID) FROM Sales.SalesOrderHeader
SELECT @MinID AS MinId,@MaxID AS MaxId

-- <8> --
-- Display the CustomerID, SalesOrderID, and OrderDate for each Sales.SalesOrderHeader row as long as the customer has placed at least two orders. (Use subquery or derived table)
SELECT SalesOrderID
,CustomerID
,OrderDate
FROM Sales.SalesOrderHeader WHERE CustomerID IN
(
SELECT CustomerID FROM Sales.SalesOrderHeader
GROUP BY CustomerID
HAVING COUNT(SalesOrderID) >= 2
)


-- <9> --
-- Write a query that attempts to insert a duplicate row into the HumanResources.Department table. Use the @@ERROR function to display the error.
--SELECT * FROM [HumanResources].[Department]
DECLARE @ErrorVar INT;
INSERT INTO HumanResources.Department(DepartmentID,[Name],GroupName,ModifiedDate)
VALUES (1,'Engineering','Research and Development',GETDATE());
SET @ErrorVar = @@ERROR
IF (@ErrorVar <> 0)
BEGIN
PRINT @ErrorVar
END

-- <10> --
-- Write above query with use TRY…CATCH. Display the error number and error message
BEGIN TRY
INSERT INTO HumanResources.Department(DepartmentID,[Name],GroupName,ModifiedDate)
Values (1,'Engineering','Research and Development',GETDATE());
END TRY

BEGIN CATCH
Select ERROR_NUMBER() AS ErrorNumber,ERROR_MESSAGE() AS ErrorMessage
END CATCH;
-- <11> --
-- Write a query to Create a temp table called #CustomerInformation_YourName that contains CustomerID, FirstName+ LastName columns. Include CountOfSales and SumOfTotalDue columns.
-- Populate the table with a query using the Sales.Customer, Person.Person, and Sales.SalesOrderHeader tables
--SELECT * FROM [Sales].[Customer]
--SELECT * FROM [Sales].[SalesOrderHeader]
--SELECT * FROM [Person].[Person]

IF OBJECT_ID('#CustomerInformation_Deepak') IS NOT NULL
DROP TABLE #CustomerInformation_Deepak

CREATE TABLE #CustomerInformation_Deepak
(
CustomerID int,
FullName varchar(100),
CountOfSales int,
SumOfTotalDue int
)

--SELECT * FROM #CustomerInformation_Deepak
INSERT INTO #CustomerInformation_Deepak
SELECT soh.CustomerID
,p.FirstName+' '+p.LastName AS FullName
,COUNT(soh.SalesOrderID) AS CountOfSales
,SUM(soh.TotalDue) AS SumOfTotalDue
FROM Sales.SalesOrderHeader soh
Inner JOIN Sales.Customer c on soh.CustomerID=c.CustomerID
Inner JOIN Person.Person p on p.BusinessEntityID=c.PersonID
Group BY soh.CustomerID,p.FirstName,p.LastName;

-- <12> --
-- Write a query to create a view called dbo.vw_CustomerTotals_YourName that displays the total sales from the TotalDue column per year and month for each customer.
-- Test the view by creating a query that retrieves data from the view.
IF OBJECT_ID('dbo.vw_CustomerTotals_Deepak') IS NOT NULL
DROP VIEW dbo.vw_CustomerTotals_Deepak

CREATE VIEW dbo.vw_CustomerTotals_Deepak
AS
SELECT sc.CustomerID
,sc.AccountNumber
,YEAR(ss.OrderDate) AS TotalDuePerYear
,MONTH(ss.OrderDate) AS TotalDuePerMonth
,SUM(ss.TotalDue) AS TotalDue
FROM [Sales].[Customer] as sc
INNER JOIN [Sales].[SalesOrderHeader] as ss ON sc.CustomerID = ss.CustomerID
GROUP BY sc.CustomerID,sc.AccountNumber,YEAR(ss.OrderDate),MONTH(ss.OrderDate)

--Execution
--SELECT * FROM dbo.vw_CustomerTotals_Deepak

-- <13> --
-- Create a function called dbo.fn_GetStringDetails_YourName (check if function exist than drop and create) that removes any numeric characters from a VARHCHAR(250) string. Write query to test the function
IF OBJECT_ID('dbo.fn_GetStringDetails_Deepak') IS NOT NULL
DROP FUNCTION dbo.fn_GetStringDetails_Deepak
GO

CREATE FUNCTION dbo.fn_GetStringDetails_Deepak(@ReqString VARCHAR(250))
RETURNS VARCHAR(250)
AS
BEGIN
WHILE PATINDEX('%[0-9]%', @ReqString) > 0
SET @ReqString = STUFF(@ReqString,patindex('%[0-9]%', @ReqString), 1, '')
RETURN @ReqString
END

--Execution
--SELECT dbo.fn_GetStringDetails_Deepak('dee8767kljkfjd8678')

-- <14> --
-- Write a query to Create a stored procedure called dbo.usp_ProductSales_Details (check if procedure exist than drop and create) that accepts a ProductID
-- for a parameter and has an OUTPUT parameter that returns the number sold for the product. Test the stored procedure.

IF OBJECT_ID('dbo.usp_ProductSales_Details_Deepak') Is Not null
DROP PROC dbo.usp_ProductSales_Details_Deepak
GO

CREATE PROC dbo.usp_ProductSales_Details_Deepak
(
@ProductId INT,
@NoOfProductSold INT = null OUT
)
AS
BEGIN
SELECT @NoOfProductSold = SUM(OrderQty) FROM [Sales].[SalesOrderDetail] WHERE ProductID = @ProductId
SELECT @NoOfProductSold
END
GO

--Execution

--EXEC dbo.usp_ProductSales_Details_Deepak 776

-- <15> --
-- View the execution plans, and explain why one query performs better than the other.
--select * from Sales.SalesOrderHeader
--select * from Sales.SalesOrderDetail
SELECT o.SalesOrderID,d.SalesOrderDetailID FROM Sales.SalesOrderHeader AS o INNER JOIN Sales.SalesOrderDetail AS d ON o.SalesOrderID = d.SalesOrderID;
SELECT o.SalesOrderID,d.SalesOrderDetailID FROM Sales.SalesOrderHeader AS o INNER JOIN Sales.SalesOrderDetail AS d ON o.SalesOrderID = d.OrderID;