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
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'
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
No comments:
Post a Comment