sql assignment.txt

-- Exercise 1
use AdventureWorks2008R2

-- Query-1
select Count(*) as 'Total Records' from Sales.SalesPerson;

-- Query 2
Select FirstName+' '+LastName as 'Full Name' from Person.Person where FirstName Like 'B%';

-- Query 3
Select FirstName,LastName,JobTitle
From Person.Person 
INNER JOIN HumanResources.Employee
ON Person.Person.BusinessEntityID=HumanResources.Employee.BusinessEntityID
AND  JobTitle LIKE '%Design Engineer%' OR JobTitle LIKE '%Tool Designer %' OR JobTitle LIKE'%Marketing Assistant%';
-- We Take % % Because Some Employee is Senior Design Engineer,Senior Tool Designer and Senior Marketing Assistant

-- Query 4
Select Name,Color,Weight From Production.Product 
where Weight=(Select MAX(Weight) from Production.Product);

--Query 5
Select SpecialOffer.Description,ISNULL(SpecialOffer.MaxQty,0.00) From Sales.SpecialOffer;

--Query 6
Select CurrencyRateDate,FromCurrencyCode,ToCurrencyCode,AverageRate
From Sales.CurrencyRate
Where datepart(year,CurrencyRateDate)=2007 and ToCurrencyCode='GBP'; 

--Query 7
SELECT ROW_NUMBER() over (order by FirstName asc) As RowNumber,FirstName,LastName
FROM Person.Person
where FirstName like '%ss%';

--Query 8
SELECT BusinessEntityID,CommissionPct ,'Commission Band'=
CASE
	WHEN CommissionPct = 0 then 'Band 0'
    WHEN CommissionPct > 0 and CommissionPct <= 0.01 THEN 'Band 1'  
    WHEN CommissionPct > 0.01 and CommissionPct <= 0.015 then 'Band 2'
	WHEN CommissionPct > 0.015 then 'Band 3'
     
END FROM Sales.SalesPerson
Order by CommissionPct;

--Query 9
Declare @id int;
Select @id = BusinessEntityID
From Person.Person
where FirstName='Ruth' and LastName ='Ellerbrock' and PersonType='EM'
Exec dbo.uspGetEmployeeManagers @BusinessEntityID=@ID;

--Query 10
Select Max(dbo.ufnGetStock(ProductID)) from Production.Product;

////////////////////////////////////////////////////////////////////////////////////////

use AdventureWorks2008R2;
-- Using Join
Select C.CustomerID from Sales.SalesOrderHeader SOH
Right Join Sales.Customer C
on C.CustomerID=SOH.CustomerID
Where SalesOrderID is NULL;

-- Using Subquery
Select CustomerID from Sales.Customer C  
Where C.CustomerID  
Not in (Select CustomerID from Sales.SalesOrderHeader);

-- EXIST
Select C.CustomerID From Sales.Customer C
Where NOT EXISTS (Select s.CustomerID from Sales.SalesOrderHeader as s where s.CustomerID=C.CustomerID); 

-- CTE(Common Table Expression)
With CustomerWithoutOrder(CustomerID)
As

(Select C.CustomerID from Sales.SalesOrderHeader SOH
Right Join Sales.Customer C
on C.CustomerID=SOH.CustomerID
Where SalesOrderID is NULL)

Select * from CustomerWithoutOrder;

///////////////////////////////////////////////////////////////////////////////////////////////////////////////

use AdventureWorks2008R2;
 
Select Top 5 SalesOrderID,AccountNumber,OrderDate 
from Sales.SalesOrderHeader
where AccountNumber IN
	(Select AccountNumber from Sales.SalesOrderHeader -- Show Account No Which Has Subtotal is > 70000  
	group by AccountNumber Having SUM(SubTotal)>70000)
Order By OrderDate;

////////////////////////////////////////////////////////////////////////////////////////////////////////////////
Use AdventureWorks2008R2;
GO
CREATE FUNCTION Sales.uf_getProducts(@SalesOrderID int, @CurrencyCode nchar(3) ,@CurrencyRateDate datetime)
	RETURNS TABLE 
AS
	RETURN
	WITH Products
	AS (
		SELECT *
		FROM Sales.SalesOrderDetail AS sod 
		WHERE sod.SalesOrderID = @SalesOrderID
	)
	SELECT p.ProductID, p.OrderQty, p.UnitPrice, p.UnitPrice*scr.EndOfDayRate AS 'Converted Price'
	FROM Products AS p, Sales.CurrencyRate AS scr
	WHERE scr.ToCurrencyCode = @CurrencyCode
		AND scr.CurrencyRateDate = @CurrencyRateDate
GO

SELECT * FROM Sales.uf_getProducts(43659, 'CNY', '2011-06-01 00:00:00.000');

////////////////////////////////////////////////////////////////////////////////////////////////////////////////

use AdventureWorks2008R2;
GO
Create Proc Person.up_getNameByType @type nchar(5)='IN'
AS
Select FirstName From Person.Person Where PersonType=@type;
GO
 
Execute Person.up_getNameByType;
Execute Person.up_getNameByType @type='EM';
 ///////////////////////////////////////////////////////////////////////////////////////////////////////////////
use AdventureWorks2008R2;
GO
CREATE TRIGGER CheckPriceRise
ON Production.Product
AFTER UPDATE
AS
BEGIN
	SET NOCOUNT ON;
	IF UPDATE(ListPrice)
		BEGIN
			UPDATE Production.Product
			SET ListPrice = IIF((ins.ListPrice - del.ListPrice) > del.ListPrice*0.15, del.ListPrice, ins.ListPrice)
			FROM deleted AS d, Production.Product AS p
			INNER JOIN inserted AS ins ON ins.ProductID = p.ProductID
		END
END
GO
SELECT * FROM Production.Product;
UPDATE Production.Product
SET ListPrice = 14
WHERE ProductID = 1;