Advertisement

SQL @LAB 10

Rahul maurya December 17, 2015


1.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. (Use the AdventureWorks database)

ANS.

DECLARE @avg_rate INT
SELECT @avg_rate = AVG(rate)
FROM HumanResources.EmployeePayHistory
SELECT * FROM HumanResources.EmployeePayHistory
WHERE Rate < @avg_rate
GO

2.Create a function that returns the shipment date of a particular order.
 (Use the AdventureWorks database)

ANS.

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 ShippingDate 
FROM Sales.SalesOrderHeader

3.Create a stored procedure that accepts the name of a product and display its ID, number,
 and availability. (Use the AdventureWorks database)

ANS.

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'

4.Create a function that returns the credit card number for a particular order.
 (Use the AdventureWorks database)

ANS.

CREATE FUNCTION Sales.DisplayCardNumber(@SalesOrderID INT)
RETURNS NVARCHAR(25)
AS 
BEGIN
DECLARE @ret NVARCHAR(25)
SELECT @ret = CardNumber
FROM Sales.SalesOrderHeader S JOIN Sales.CreditCard C
ON S.CreditCardID = C.CreditCardID
WHERE SalesOrderID = @SalesOrderID
IF (@ret IS NULL)
SET @ret = 0
RETURN @ret
END


5.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. (Use the AdventureWorks database)

ANS.

CREATE FUNCTION Sales.IndividualDetails(@format NVARCHAR(9))
RETURN @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 = I.ContactID JOIN Sales.Customer AS Cu
ON I.CustomerID = Cu.CustomerID
WHERE Cu.CustomerType = 'I'
ORDER BY LastName,FirstName
ELSE IF (@format = 'SHORTNAME')
INSERT @tbl_Individual
SELECT Cu.CustomerID,LastName FROM Person.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


6.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. (Use the AdventureWorks database)

6.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. (Use the AdventureWorks database)

ANS.

CREATE FUNCTION fx_Disp_AccDet ( @AccNum INT)
RETURNS TABLE WITH SCHEMABINDING
AS
RETURN (
SELECT Customer_name, Acc_num 
FROM dbo.Depositer
WHERE Acc_num = @AccNum
)
SELECT * FROM fx_Disp_AccDet(101)


7.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'. (Use the AdventureWorks database)

ANS.

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'


8.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'.

ANS.

CREATE PROCEDURE vx_DIV @Num1 INT, @Num2 INT
AS 
BEGIN
DECLARE @Div INT
    IF @Num1 < @Num2
RAISERROR ('You have entered your numbers
in the wrong way' ,16,1)
ELSE
SET @Div=@Num1/@Num2
PRINT @Div
END


9.Create a stored procedure that returns the standard cost of a given product. (Use the AdventureWorks database)

ANS.

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

Rahul maurya

Posted by Rahul maurya

Post a Comment

2 Comments

  1. Unknown16 January 2018 at 02:34

    • Nice and good article. It is very useful for me to learn and understand easily. Thanks for sharing your valuable information and time. Please keep updating. Power Bi Online course

    ReplyDelete
  2. mnpark9 December 2020 at 23:54

    MN PARK Offers leasing opportunities for fully fitted wet/dry laboratory spaces, pilot plants, clean manufacturing spaces and specialized warehousing facilities for lease to non-polluting, Life Sciences, Chemical and Nutraceutical companies. Knoe More - Click Here

    ReplyDelete

USE ANONYMOUS PROFILE IF YOU WANT D'NT SHARE EMAIL