-- Question #1

/* Create a scalar UDF that calculates a AVG salary for a department.

Parameters: @DepartmentName ie 'Accounting'
Returns: AvgSalary for department
*/
USE Stanton_Company
GO
IF OBJECT_ID('[dbo].[udf_AvgSalaryDepartment]', 'FN') IS NOT NULL
DROP Function [dbo].[udf_AvgSalaryDepartment];
GO
CREATE FUNCTION udf_AvgSalaryDepartment(@DepartmentName VARCHAR(max))
RETURNS MONEY
AS
BEGIN
DECLARE @deptsalaryavg AS MONEY;
SET @deptsalaryavg = '';
SELECT @deptsalaryavg = AVG(e.salary)
FROM EMPLOYEE e JOIN DEPARTMENT d ON (e.deptno = d.deptno)
WHERE d.name = @DepartmentName
GROUP BY d.name
RETURN @deptsalaryavg
END
GO
--SELECT dbo.udf_AvgSalaryDepartment('Accounting') AS 'AvgSalary for Department'
--GO
--SELECT dbo.udf_AvgSalaryDepartment('Research') AS 'AvgSalary for Department'
--GO
SELECT dbo.udf_AvgSalaryDepartment('Sales') AS 'AvgSalary for Department'
GO
--SELECT dbo.udf_AvgSalaryDepartment('Operations') AS 'AvgSalary for Department'
SELECT e.name
FROM EMPLOYEE e JOIN DEPARTMENT d ON (e.deptno = d.deptno)
WHERE e.salary < dbo.udf_AvgSalaryDepartment('Research')
-- Test script returns 8 names with average below Research avg salary
SELECT e.name
FROM EMPLOYEE e JOIN DEPARTMENT d ON (e.deptno = d.deptno)
WHERE e.salary < dbo.udf_AvgSalaryDepartment('Sales')
-- Test script returns 8 names with average below Sales salary avg
-- *****************************************************************************
***********************************************
-- Question #2
/* Create a stored procedure named uspBalanceRange that accepts 3 optional param
eters. 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 then the proce
dure
should return all invoices where there is a balance due
Otherwise return invoices whose total is between @BalanceMin and @BalanceMax
@VendorVar is used in a LIKE clause so if a user passes a value of 'E%', the pro
gram will find all vendors that start with 'E'
*/
USE AP
GO
IF OBJECT_ID('usp_BalanceRange', 'P') IS NOT NULL
DROP Procedure usp_BalanceRange
GO
CREATE PROC usp_BalanceRange
(
@VendorVar AS VARCHAR(max) = NULL
, @BalanceMax AS MONEY = NULL
, @BalanceMin AS MONEY = NULL
)
AS
IF @BalanceMax IS NULL OR @BalanceMax = 0 OR @BalanceMin IS NULL OR @Ven
dorVar IS NULL
SELECT v.VendorName, i.InvoiceNumber, SUM(i.InvoiceTotal - i.Cre
ditTotal - i.PaymentTotal) AS 'Balance'
FROM Vendors v JOIN Invoices i ON (v.VendorID = i.VendorID)
WHERE (i.InvoiceTotal - i.CreditTotal - i.PaymentTotal) > 0
GROUP BY v.VendorName, i.InvoiceNumber, (i.InvoiceTotal - i.Cred
itTotal - i.PaymentTotal)
--HAVING (i.InvoiceTotal - i.CreditTotal - i.PaymentTotal
) > 0
ELSE
SELECT v.VendorName, i.InvoiceNumber, SUM(i.InvoiceTotal - i.Cre
ditTotal - i.PaymentTotal) AS 'Balance'
FROM Vendors v JOIN Invoices i ON (v.VendorID = i.VendorID)
WHERE (i.InvoiceTotal - i.CreditTotal - i.PaymentTotal) BETWEEN
@BalanceMin AND @BalanceMax
GROUP BY v.VendorName, i.InvoiceNumber, (i.InvoiceTotal - i.Cred
itTotal - i.PaymentTotal)
HAVING v.VendorName LIKE @VendorVar
EXEC usp_BalanceRange
@VendorVar = 'B%'
,@BalanceMax = 500
, @BalanceMin = 20
-- Question #3
EXEC usp_BalanceRange 'Z%' -- Should return first IF statement because not all p
arameters are there
EXEC usp_BalanceRange @BalanceMax = 1000.00, @BalanceMin = 200.00 -- Should ret
urn first IF statement because not all parameters are there
EXEC usp_BalanceRange '[CF]%', 200 -- Should return first IF statement because
not all parameters are there
-- Question #4
/* Create a stored procedure named spDateRange that accepts two parameters. If t
he parameters are valid, return a result set that
includes the invoiceNumber, InvoiceDate, InvoiceTotal, and Balance for each invo
ice that is written within the date range.
Parameters: @DateMin varchar(50) @DateMax varchar(50) default value NULL
IF called with no parameters or with null values raise an error that describes t
he problem. If called with non-null values, validate
the parameters. Is it a real date? Is maxdate earlier than mindate? */
IF OBJECT_ID('spDateRange', 'P') IS NOT NULL
DROP Procedure spDateRange
GO
CREATE PROC spDateRange
(
@DateMin AS VARCHAR(50) = NULL
, @DateMax AS VARCHAR(50) = NULL
)
AS
BEGIN
BEGIN
IF ISDATE(@DateMin) = 1
PRINT 'VALID Minimum Date'
ELSE
RAISERROR('INVALID Minimum Date; could be null value', 1
6, 1)
END
BEGIN
IF ISDATE(@DateMax) = 1
PRINT 'VALID Maximum Date'
ELSE
RAISERROR('INVALID Maximum Date; could be null value', 1
6, 1)
END
BEGIN
IF @DateMin < @DateMax
PRINT 'VALID dates'
ELSE
RAISERROR('Minimum date is after maximum date', 16, 1)
END
SELECT i.InvoiceNumber, i.InvoiceDate, i.InvoiceTotal, (i.InvoiceTotal -
i.CreditTotal - i.PaymentTotal) AS 'Balance'
FROM Invoices i
WHERE InvoiceDate BETWEEN @DateMin AND @DateMax
END
EXEC spDateRange '04/10/208', '04/13/2008' -- error in dates
EXEC spDateRange '2008-05-28', '2008-07-31' -- valid dates
-- Question #5
IF OBJECT_ID('spDateRange', 'P') IS NOT NULL
DROP Procedure spDateRange
GO
CREATE PROC spDateRange
(
@DateMin AS VARCHAR(50) = NULL
, @DateMax AS VARCHAR(50) = NULL
)
AS
DECLARE @errnum INT
DECLARE @severity INT
DECLARE @errstate INT
DECLARE @proc VARCHAR(126)
DECLARE @line INT
DECLARE @message VARCHAR(max)
BEGIN TRY
BEGIN
BEGIN
IF ISDATE(@DateMin) = 1
PRINT 'VALID Minimum Date'
ELSE
RAISERROR('INVALID Minimum Date; could be null v
alue', 16, 1)
END
BEGIN
IF ISDATE(@DateMax) = 1
PRINT 'VALID Maximum Date'
ELSE
RAISERROR('INVALID Maximum Date; could be null v
alue', 16, 1)
END
BEGIN
IF @DateMin < @DateMax
PRINT 'VALID dates'
ELSE
RAISERROR('Minimum date is after maximum date',
16, 1)
END
SELECT i.InvoiceNumber, i.InvoiceDate, i.InvoiceTotal, (i.Invoic
eTotal - i.CreditTotal - i.PaymentTotal) AS 'Balance'
FROM Invoices i
WHERE InvoiceDate BETWEEN @DateMin AND @DateMax
END
END TRY
BEGIN CATCH
SELECT @errnum = ERROR_NUMBER()
, @severity = ERROR_SEVERITY()
, @errstate = ERROR_STATE()
, @proc = ERROR_PROCEDURE()
, @line = ERROR_LINE()
, @message = ERROR_MESSAGE()
Raiserror('Failed to generate results because this is no
t a valid date:
Error: %d, State: %d, in proc %s at line %d,
Message: %s', 16,1, @errnum, @errstate, @proc, @
line, @message)
END CATCH
EXEC spDateRange '2008-04-04', '2008-04-20'
EXEC spDateRange '2008-04-40', '2008-04-20'
EXEC spDateRange '2009-04-04', '2008-04-20'
-- Question #6
USE Stanton_Company
GO
IF OBJECT_ID('spAvgSalaryEmployee', 'P') IS NOT NULL
DROP Procedure spAvgSalaryEmployee;
GO
CREATE PROC spAvgSalaryEmployee(
@projectNo VARCHAR(max)
, @startDate AS DATE
, @endDate AS DATE
)
AS
BEGIN
DECLARE @projsalaryavg AS MONEY;
SET @projsalaryavg = '';
DECLARE @projsalarytot AS MONEY;
SET @projsalarytot = '';
SELECT @projsalaryavg = AVG(e.salary), @projsalarytot = SUM(e.sa
lary)
FROM EMPLOYEE e JOIN PROJECT_PARTICIPATION pp ON (e.empno = pp.e
mpno)
--WHERE pp.projectno = @projectNo AND pp.start_date = @startDate

--AND pp.end_date = @endDate AND @projectNo
--BETWEEN @startDate AND @endDate
RETURN @projsalaryavg
END
EXEC spAvgSalaryEmployee
1001
, '2007-08-13'
, '2006-01-01'
BEGIN CATCH
SELECT @errnum = ERROR_NUMBER()
, @severity = ERROR_SEVERITY()
, @errstate = ERROR_STATE()
, @proc = ERROR_PROCEDURE()
, @line = ERROR_LINE()
, @message = ERROR_MESSAGE()
Raiserror('You have an error:
Error: %d, State: %d, in proc %s at line %d,
Message: %s', 16,1, @errnum, @errstate, @proc, @
line, @message)
END CATCH