Monthly Archives: June 2012

SQL Question 22

Problem Use AP Create a stored procedure names uspBalanceRange that accepts 3 optional paramters. The procedure returns a result set consisting of VendorName, InvoiceNumber, and Balance for each invoice. The balance is calculated as InvoiceTotal - CreditTotal - PaymentTotal Parameters: @VendorVar, @BalanceMax, @BalanceMin If the procedure is called with no parameters or @BalanceMax is 0…
Read more

SQL Question 21

Problem Create a database called YourName_Company. Run the LewsCompany script to populate your database with tables and data Create a scalar UDF that calculates a AVG salary for a department. Parameter(s): @DepartmentName ie ‘Accounting’ Returns: AvgSalary for department Now write a test script that will return all employees whose salary is BELOW avg for their…
Read more

SQL Question 20

Problem Use Northwind Write a multistatement table valued function that returns a product list by Supplier. The function takes a Supplier NAME as a parameter and returns a table with the product id, productname and unitprice. Test whether the Supplier Name is in the supplier table, and if not return an error message in the…
Read more

SQL Question 19

Problem USE ADVENTUREWORKSLT2008R2 Write a mulistatment table valued function that accepts an email address and returns a two column table with the username and domain name Test the function with the emailaddress in the Customers table Code USE ADVENTUREWORKSLT2008R2 GO IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[EmailParts]') AND type in (N'FN', N'IF',…
Read more