SQL LAB at Home 10

SQL LAB at Home 10

01. Create a batch that finds the average pay rate of the employees 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



02. 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 salesOrderID=@salesOrderID
If (@shipdate is Null)
set @shipdate=0
Return @shipdate
end


select salesOrderID,Sales.calshipdate(salesOrderID)
as Shipdate
from Sales.SalesOrderHeader


03. Create a stored procedure that accepts the name of a product and display its ID, number, and availability.

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'



04. 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.salesOrderHeader s join sales.CraditCard c
on s.CreditCardID=c.CreditCardID
Where salesOrderID=@salesOrderID
If (@ret is Null)
set @ret=0
Return @ret
end


select SalesOrderID,'Credit Card Number'=Sales.DisplayCardNumber
(SalesOrderID) from Sales.SalesOrderHeader





05. Create a function that returns a table containing the ID and the name of the customers who are categorized as individual customers (CustomerType = 'I'). The function should take one parameter. The parameter value can be either Shortname or Longname. If the parameter value is Shortname, only the last name of the customer will be retrieved. If the parameter value is Longname, then the full name will be retrieved.

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 l
on c.ContactID=l.ContactID join sales.Customer as Cu
on l.CustomerID=cu.CustomerID
Where cu.CustomerType='l'Order by LastName,FirstName
Else
If (@Format='ShortName')
insert @tbl_individual
select cu.customerID,LastName from Person.Contact as c join
sales.Individual as l on c.ContactID=l.ContactID join sales.Customer as Cu
on l.CustomerID=cu.CustomerID
Where cu.CustomerType='l'
Order by lastName
return
end


select * from Sales.IndividualDetails('LongName')
select * from Sales.IndividualDetails('ShortName')





06. Create a user-defined function that accepts the account number of a customer and returns the customer's name from the Depositor table. 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)




07. Create a batch to check the availability of the product, Chaining Bolts, in the stock. If the product is available, display a message, 'The Stock is available'. If the product is not 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 available'
else
Print 'The stock is not available'




08. 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




09. Create a stored procedure that accepts 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
Raiserror('You have entered your number in the wrong way',16,1)
else
set @div=@Num1/@num2
print @div
end