'You are a databas developer at adventure works, inc. the human resource department needs to revise the payment details of the employess. you need to create a procedure that obtains the percentage value by which you ned to increase the pay reat. in addition you need to ensure that the pay is revised for only those employees whose pay rate was not revised in the six months.
Ans:
To solve the preceding problem, you need to perform the following tasks:
1 Create a stored procedure.
2 Execute the stored procedure.
3 verify the result
1.
To  cereate a stored procedure you need to perform the following steps:'
Create PROC PayRateIncrease @EmpID int, @percent float
AS
BEGIN
DECLARE @maxRate float
DECLARE @RevisedRate float
DECLARE @PayFre int
IF Exists (Select * From Human Resources. EmployeePayHistory
where Datediff(mm,RateChangeDate,getdate())> 6 AND EmployeeID = EmpID)
BEGIN
SELECT @maxRate = Rate
FROM HumanResources.EmployeePayHistory where EmployeeID = @EmpID
IF (@maxRate *@percent > 200.00)
BEGIN
PRINT 'Rate of an employee cannot be greater than 200.00'
END
ELSE
BEGIN
SELECT @RevisedRate = Rate, @PayFre = PayFrequency
from HumanResources.EmployeePayHistory where EmployeeID = @EmpID
SET @RevisedRate =@RevisedRate + (@RevisedRate*@percent/100)
INSERT into HumanResources.EmployeePayHistory values (@EmpID,GETDATE(),@RevisedRate,@PayFre,GETDATE())
END
END
END
'Press the F5 key to compile the stored procedure.'

'2: Executing the stored procedure
Execte the following statement to run the stored procedure:'

Exec PayRateIncrese 6,2

'3: Verifying the result
Exectute the following query to verify the result of the stored procedure:'
Select * from HumanResources.EmployeePayHistory where EmployeeID =6
order by ModifiedDate desc





'Exercise 1'

'Create a batch that finds the average pay rate of the employeeds and then lists the
details of the employees who have a pay rate less than the average pay rate'
DECLARE @avg_rate int
SELECT @avg_rate =AVG(rate) from HumanResources.EmployeePayHistory
select * from HumanResources.EmployeePayHistory where Rate<@avg_rate
go
press F5

'Exercise 2'
'Create a function that returns the shipment date of a particular order'

Create function sales.calshipDate(@SalesOrderID int) Returns Date
As
BEGIN
DECLARE @shipDate Datetime
Select @shipDate = ShipDate
From sales.salesOrderHeader where salesOrederID =@salesOrdreID
IF(@shipDate is null)
SET @shipDAte =0
RETURN @shipDate
END

Press F5
Select SalesOrderID, Sales.calShipDate(SalesOrderID)
AS
ShippingDate
From Sales.SalesOrderHeader

Prss F5
Select salesOrderID, Sales.calShipDate(SalesOrderID)
as shippingDate from Sales.SalesOrderHeader

'EXERCISE 3'
'Create a function that returns the credit card number for a
particular order'
Create function Sales.DisplayCardNumber(@SalesOrderID int) RETURNS nvarchar(25)
AS
BEGIN
DECLARE @ret nvarchar(25)
select @ret = CardNumber FROM sales.salesOrderHeard s join sales.creditCard
c on s.CreditCardID = c.CreditCardID where
SalesOrederID = @salesOrederID
IF(@ret is null)
set @ret =0
RETURN @ret
END

select SalesOrderID, 'Credit card number'=
Sales.DisplayCardNumber(SalesOrderID) from Sales.SalesOrderHeader

'Exercise 4'
'create a function theat returns a table containing the Id and the name of the customers who are categrized as individual customers (
CustomersType = 'I'). The function should take one parameter. the parameter
value can be eithter shortname or Longname. if the parameter value is shortname,
only the last name of the customer will be retrived. if the parameter values is Longname, then
the full name will be retrived. '
CREATE FUNCTION Sales.IndividualDetails(@format nvarchar(9))
RETURNS @tbl_Individual Table
(CustomerId int Primary Key, Name nvarchar(100))
as BEGIN IF(@format = 'LONGNAME')
INSERT @tbl_Individual
SELECT Cu.CustomerID, FirstName+''+LastName From Person.Contact AS C
Join Sales.Individual AS I on C.ContactID = Cu.CustomerID
where Cu.CustomerType ='I'
ORDER By LastName,FirstName
ELSE IF (@format = 'SHORTNAME')
INSERT @tbl_Individual
Select Cu.CustomerID,LastName from Perrson.Contact AS C
Join Sales.Individual AS I ON C.ContactID = I.ContactID
Join Sales.Customer AS Cu ON I.CustomerID = Cu.CustomerID
where Cu.CustomerType ='I'
ORDER BY LastName
RETURN
END

Select * From Sales.IndiviualDetails('LONGNAME')
Select * from Sales.IndividualDetails('SHORTNAME')

'Exercise -5'
'Create a user-defined function that accepts the account number of a customer and return the customer's' name from the Depositor tbale.
further, ensure that after creating the function, user is not able to alter or drop
the Depositor table'
Create Function fx_Disp_AccDet (@AccNum int)
RETURNS TABLE WITH SCHEMABINDING
AS
RETURN
(
Select Customer_name, Acc_num
from dbo.Depositor where Acc_num =@AccNum
)

Select * from fx_Disp_AccDet(101)

'Ecercise 6'
'Create a batch to check the availability of the product, chaining Bolts, in the
stock. If the produc is available, display a message, the Stock is not available'
DECLARE @MakeFlag bit
Select @MakeFlag = MakeFlag from Production.Product
where Name = 'Chaining Bolts'
If @MakeFlag =1
Print 'The stock is not available'
ELSE
Print 'The stock is not available'

'Exercise 7'
'Create a stored procedure the accepts the name of a product and display
its ID, number, and availablitly'

Create PROC productList @name varchar(50)
AS
BEGIN
Print'Product details'
Select ProductID,ProductNumber,MakeFlag as Availability
from Production.Product
where Name = @name
END

EXECUTE productList 'Keyed Washer'

'Exercise 8'
'Create a stored procedure that returns the standard cost of a given product.'
Create Procedure prcGetCostDetail2 @productId int,
@StandardCost money OUTPUT
AS
BEGIN
IF EXISTS (Select * from Production.ProductCostHistory
where ProductID =@productId
begin
 select @StandardCost = standardCost from Production.ProductCostHistory
 return 0
 end
 else
 RETURN 1
 END

'Exercise 9'
'Create a stored procedure that accpets two numbers, num1 and num2 and displays
the result after dividing these two numbers. In addition, num1 should always be
greater than num2. If  num1 is less than num2, generate a user-defined error
message, You have entered your numbers in the wrong way'.''
create PROCEDURE vx_DIV @Num1 int, @Num2 int
AS
BEGIN
Declare @Div int
if @Num1 < @Num2
RAISEREOR ('You have wntered your numberss in the wrong way',16,1)
else
set @Div =@Num1/@Num2
Print @Div
end