Question 1
Create a function called fnGetTimeOnly to return the time part (hh:mm) of a DateTime value
ALTER FUNCTION dbo.fnGetTimeOnly(@datefield DATETIME)
RETURNS varchar(5)
AS
BEGIN
RETURN CONVERT(VARCHAR(2),
DATEPART(hh,@datefield)) + ':' + CONVERT(VARCHAR(2),DATEPART(mi,@datefield))
END
----------------------------------------------------
SELECT dbo.fnGetTimeOnly(GETDATE()) AS Expr1
Create a function called fnGetDateOnly to return the date part (dd/mm/yyyy) of a DateTime value
ALTER FUNCTION dbo.fnGetDateOnly
(@datefield DATETIME)
RETURNS VARCHAR(10)
AS
BEGIN
RETURN CONVERT(VARCHAR(2),DATEPART(dd,@datefield))+ '/' +
CONVERT(VARCHAR(2),DATEPART(mm,@datefield))+ '/' +
CONVERT(VARCHAR(4),DATEPART(yyyy,@datefield))
END
---------------------------------------------------------
SELECT dbo.fnGetDateOnly(NOW()) AS Expr1
Create a stored procedure called OrderDateTimeSpecific to return all order dates by splitting the date part and the time part into different columns. Use the functions created above in the stored procedure.
ALTER PROCEDURE dbo.OrderDateTimeSpecific
AS
SELECT dbo.fnGetDateOnly(OrderDate) AS OrderDate,
dbo.fnGetTimeOnly(OrderDate) AS OrderTime
FROM Orders
RETURN
Question 2
Create a function called fnContactInfo that returns a table containing all employees full name and their contact number
ALTER FUNCTION dbo.fnContactInfo()
RETURNS @contactInfoTable TABLE (fullname varchar(30), contactnumber varchar(24))
AS
BEGIN
INSERT INTO @contactInfoTable
SELECT FirstName + ' ' + LastName, HomePhone FROM Employees
RETURN
END
Create a query to return all values from the fnContactInfo function
SELECT fullname, contactnumber
FROM dbo.fnContactInfo() AS fnContactInfo_1
Question 3
Create a function called fnLastDay to get the last day in a month of a given date.
ALTER FUNCTION dbo.fnLastDay(@givendate DATETIME)
RETURNS VARCHAR(30)
AS
BEGIN
DECLARE @dayindex INT
DECLARE @lastday VARCHAR(30)
DECLARE @lastdate DATETIME
SET @lastdate = DATEADD(day, - 1, DATEADD(month, DATEDIFF(month, 0, @givendate) + 1, 0))
SET @dayindex = CONVERT(INT,DATEPART(weekday,@lastdate))
SELECT @lastday =
CASE @dayindex
WHEN 1
THEN 'Family Day Sunday'
WHEN 2
THEN 'Back to work Day Monday'
WHEN 3
THEN 'Movie night Tuesday'
WHEN 4
THEN 'Borrow money day Wednesday'
WHEN 5
THEN 'Shopping spree Thursday'
WHEN 6
THEN 'Binge drinking night Friday'
WHEN 7
THEN 'Activities day Saturday'
END
RETURN @lastday
END
Create a query to return all employees and their pay day last month, this month, and next month. The pay day is always the last day of the month.
SELECT LastName, FirstName,
dbo.fnLastDay(DATEADD(month, - 1, GETDATE())) AS PreviousMonthPayDay,
dbo.fnLastDay(GETDATE()) AS ThisMonthPayDay,
dbo.fnLastDay(DATEADD(month, 1, GETDATE())) AS NextMonthPayDay
FROM Employees
Question 4
Create a function called fnRoundCurrency to format the currency value into the correct monetary format. For e.g. 39.07 should be rounded to 39.10.
ALTER FUNCTION dbo.fnRoundCurrency(@currency MONEY)
RETURNS DECIMAL(10,2)
AS
BEGIN
RETURN CAST ((ROUND((@currency * 2),1) /10) *5 AS DECIMAL(10,2))
END
Modify the stored procedure created last week and call this function to format the new price of the first ten products into the right format.
ALTER PROCEDURE dbo.spShowCorrectPrice
AS
SELECT TOP 10 *,dbo.fnRoundCurrency(UnitPrice) as RoundedPrice FROM Products
RETURN
PART II
Create a table called Books in Northwind database containing BookID, ISBN, Publisher, Author, Year, Country, Category, Description.
CREATE TABLE Books
(
BookID INT,
ISBN VARCHAR(13),
Publisher VARCHAR(30),
Author VARCHAR(30),
Year INT,
Country VARCHAR(20),
Category VARCHAR(30),
Description VARCHAR(30),
CONSTRAINT pk_BookID PRIMARY KEY (BookID)
)
Question 1: FUNCTIONS TO CHECK CONSTRAINTS IN A TABLE DEFINITION
Create a function called fnValidISBN to check the value entered into ISBN field in the Books table. The following requirements are to check for valid ISBN:
ISBN numbers can contain 10 or 13 digits number.
Assuming that the company only accepts English-language publisher books, make sure that the ISBN follows the systematic pattern.
To check if the numbers entered are valid or not, use the formula and pattern in this webpage: http://en.wikipedia.org/wiki/International_Standard_Book_Number
ALTER FUNCTION dbo.fnValidISBN
(@isbn VARCHAR(13))
RETURNS INT
AS
BEGIN
DECLARE @result INT
DECLARE @isbn_ten INT
DECLARE @isbn_thirteen INT
DECLARE @isbn_sum INT
DECLARE @startpos INT
DECLARE @isbnlen INT
SET @isbn_ten = 9
SET @isbn_thirteen = 12
SET @isbn_sum = 0
SET @startpos = 1
SET @isbnlen = LEN(@isbn)
IF (@isbnlen = @isbn_ten)
BEGIN
WHILE (@startpos<= @isbn_ten+1)
BEGIN
SET @isbn_sum = @isbn_sum + (@isbn_ten * SUBSTRING(@isbn, @startpos, 1))
SET @startpos = @startpos + 1
END
IF (11-@isbn_sum%11) = SUBSTRING(@isbn,10,1)
SET @result = 0 --Success
ELSE
SET @result= 1 --Failure
END
ELSE IF (@isbnlen = @isbn_thirteen+1)
BEGIN
WHILE (@startpos <= @isbn_thirteen)
BEGIN
IF @startpos % 2 <> 0
BEGIN
SET @isbn_sum = @isbn_sum + (1 * SUBSTRING(@isbn, @startpos, 1))
SET @startpos = @startpos + 1
END
ELSE
BEGIN
SET @isbn_sum = @isbn_sum + (3 * SUBSTRING(@isbn, @startpos, 1))
SET @startpos = @startpos + 1
END
END
IF (10 - @isbn_sum % 10) = SUBSTRING(@isbn,13,1)
SET @result = 0 --Success
ELSE
SET @result= 1 --Failure
END
ELSE SET @result = 1
RETURN @result
END
---------------------------------------------
SELECT dbo.fnValidISBN(9780306406156) AS Expr1
