'Exercise 1'
'The management of adventure works incorporation has decided that no user
should be able to change the prices of products. in addition, the management
wants that all the attempts to change the price should be saved in a
temporary table, Temp.John, the Database Developer, has been asked to make
significant changes in the datebas to implement this policy. what should john
do to achieve the same?'
create table Temp
(
ProductID int,
AttChangeCost money,
AttTime datetime)

PRess F5
create TRIGGER updTrigger ON
Production.ProductionCostHistory instead of UPDATE
as
BEGIN
DECLARE @PID AS int
DECLARE @COST AS  money
select @pid = ProductID,@cost = STandarCost From
Deleted INSERT INTO Temp values(@pid, @cost, getdate())
Select 'Sorry you can not change the price of a product'
END

replay

UPDATE Production.ProductCostHistory
SET StandarCost = 55
where ProductID =707

press F5

'Exercise 2'
'The management of  adventure works incorporation wants that whenever
the pay rate of and employee is modified, its effect on the monthly salary
of that employee should be displayed John, the database Developer at
Adventure works, has been asked to resolve this problem. Help John
to find an appropriate solution.'
create TRIGGER updTrigger
on HumanResources.EmployeePayHistory For UPDATE
AS
BEGIN
DECLARE @rate AS money
DECLARE @frq AS int
SELECT @rate = Rate,
@frq =PayFRequency From Inserted
Select @rate * @frq * 30 AS 'Monthly Salary'
END

Update HumanResources.EmployeePayHistory
SET Rate = Rate + 5 where EmployeeID =160

'Exercise 3'
'Create a tigger to ensure that the average of the values in the tate columna o f
employeepay hisory table should not be more than 20 when the value of the
rate is increased'
create TRIGGER updatetriggerEPayHistory
ON HumanResources.EmployeePayHistory
For UPDATE
AS
IF UPDATE (Rate)
BEGIN
DECLARE @AvgRate flat
Select @AvgRate = AVG(Rate)
From HumanResource.EmployeePayHistory
IF(@AvgRate > 20)
BEGIN
PRINT 'The averge value of rate cannot be more than 20'
ROLLBACk TRANSACTION
END
END

'Exercise 4'
'Create a tigger on the product table to ensure that if the value for
the sagety stock level column becomes less than or equal to the specified
recorder point column for a product because of an update operation, a
message information  that the recorder level has been attained should
be displayed'

Create TRIGGER orderStock
ON Production.Product
For UPDATE
AS
DECLARE @PID varchar
Select @PID =ProductID from inserted
IF((Select safetyStockLeve from inserted)
<=(Select ReorderPoint from inserted))
BEGIN
PRINT ' The stock of '+@PID + 'is underr the reorder point'
END

'Exercise 5'
'Create a tigger named Emp_Update on the EmployeeDetail table.
This trigger should restrict a user from performing any  kind of DMl
operating on the table before 9 A.M and after 5P.M.'

create TRIGGER Emp_Update
ON EmployeeDetails after INSERT, UPDATE, DELETE
AS
BEGIN
DECLARE @Time INT
SET @Time = DATENAME (HH,GETDATE())
IF @Time not between 9 and 17
Begin
ROLLBACK
Print
('The operation cannot be perrformed before 9 A.M and after 5P.M.')
END
END

'Exercise 6'
'The management of adventure works wnats that whenever the account number
of a user is modified, the old account number, as wll as the new account
number, should be instaneously displayed to the user. how can you accomplish the
desired taks? '
Create trigger vendoraccountnum
on purchasing.vendor
for update AS
Begin
declare @oldvalue nvarchar (15)
declare @newvalue nvarchar (15)
Select  @oldvalue = AccountNumber from deleted
Select  @newvalue = AccountNumber from inserted
Print 'the old account number is' +@oldvalue
Print 'The old account number is' +@newvalue
END

'Exercise 7'
'Create a trigger named Emp_Delete_Trg on the EmployeeDetails table. this
should restrict the deletion of records from the table if the designation
of an employee is Manager.'
create trigger Emp_Delete_Trg
On EmployeeDetails
After delete
as
begin declare @job varchar(50)
select * @job= designation from deleted
if @job ='Manager'
begin
rollback
Print ('Cannot delete manager from the table')
END
END

'Exercise 8'
'Create a tigger named Emp_Update_Try what will restrict the updating of
salary of an employee if the new salary is less than the previous salary in the employee Details table.'
Create trigger Emp_Upage_Trg
on EmployeeDetails AFTER UPDATE
AS
BEGIN
DECLARE @OldSal Money
DECLARE @NewSal Money
Select @OldSal = Salary from DELETED
Select @NewSal =Salary from INSERTED
if @OLDSAL > @NEWSAL
BEGIN
ROLLBACK
Print('New salary cannot be less than the old salary')
END
END

'Exercise 9'
'Create a triggernamed Emp_Nested_Trg on the EmployeeDetails table for
an insert operation this trigger should ensure that a new record beging
inserted into the EmployeeDetails table has a matching record in the
DepDetails tablee. Otherwise, the insert operation is rolled back.'

create Trigger Emp_Nested_Trg
On EmployeeeDetails AFTER Insert
As
BEGIN
DECLARE @DptNo INT
Select @DptNo = DepNo from INSERTED
IF not exists(Select * from DeptDetails
where DeptNo =@DptNo)
Print 'The specified DeptNo does not exist.'
ROLLBACK
END

'Exercise10'
'Consider the following statement:
Create viwe Emp_Dept
As
Select E.EmployeeID, E.EmpName, E.Designation,E.Salary, D.DeptNo,
D.DeptName from EmployeeDetails E
Inner join DeptDetails D On E.DeptNo = D.DeptNo
A view named Emp_Dept ha been created on the base tables, EmployeeDetails and
DePTDetails, by using the proceding statement. Joint is the databsa developer
with adventure works Incorporation . He want that the users should be able to
insert data in the undelying base tables by using the view. He can implementt
the desired functionality if the inserrt operation affects a single table.
Howevver, the same cannot be done if multiple tables are involved. He
wants to be able to inserrt date in both the tables with the help of
a single insert statement. Help john in rsolving the problem.'
Create Trigger view_Inserrt_Trg
on Emp_Dept
INSTEAD of INSERT
AS
Begin
INSERT INTO EmployeeDetails (EmployeeID, EmpName
Designation, Salary, DeptNo)
Select EmployeeID,EmpName,Degination, Salary
DeptNo from INSERTED
INSERT INTO DeptDEtails(DeptNo, DeptName)
Select DeptNo, DeptName from INSERTED
END
'Exercise 11'
'John is a Databas Developer. He is not able to delted the unwanted records
from the base tables by using the view, Emp_Dept.  Help John in
resolving the problem..'
Create Trigger view_Delete_Trg
On Emp_Dept
INSTEAD of DELETE
AS
BEGIN
DECLARE @DeptNo int
DECLARE @Count int
Select @DeptNo = DeptNo from DELETED
SElect @Count = count(*) from EmployeeeDetails where deptNo =@DeptNo
DELETE From EmployeeDetails Where DeptNo =@DeptNo
if @Count = 1
DELETE From DeptDetails where DeptNo =@DeptNo
END

'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