RDBMS Lab@Home 10

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

Ans.

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

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

Ans. 

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

Ans.

5. 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. https://drive.google.com/open?id=0B08RKBSts8D8RVlkR2N4cVpRZGc

6. 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. https://drive.google.com/open?id=0B08RKBSts8D8b0s5Q2lCZnltNVk

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. https://drive.google.com/open?id=0B08RKBSts8D8dWNNa0dZNjQyNWc

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, https://drive.google.com/open?id=0B08RKBSts8D8TmdyQzFFaVVwRTA

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

Ans.