SQL LAB @ HOME 10 ~ NIIT POST

sql lab @ home 10

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

---

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

---

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