The language is T-SQL.
Create and use two scalar UDFs. The UDFs should be schema bound and use the return null
on null input options. Use the Northwind database. Sales amounts must take into account
any discounts.
a) The TaxRate UDF provides the tax rate based on a ShipCountry and returns a FLOAT
value.
If the ShipCountry starts with a letter in the range
A-J the tax rate is .04
K-P the tax rate is .03
Q-Z the tax rate is .025
b) The TaxAmount UDF provides the tax due. It takes two values, a SalesAmount and a
TaxRate. The SalesAmount is of the MONEY data type and the TaxRate is of the FLOAT
data type. The UDF returns a MONEY value.
c) Create a query that utilizes the two UDFs to display the total tax on sales by country. I
apply the discount when calculating the sales amount. In fact, I used the
dbo.LineItemTotal function from the Day 20 lecture. So I had a total of three user
defined functions. The results should look like this:
The database diagram is shown below:
Ship To Country Argentina Austria Belgium Brazil Canada Denmark Finland France Germany Ireland Italy Mexico Norway Poland Tax Amount $324.76 $5,120.15 $1,352.99 $4,277.03 $2,007.85 $1,306.44 $752.40 $3,254.33 $9,211.39 $1,999.20 $630.81 $707.46 $172.05 $105.96 Portugal Spain Sweden Switzerland $344.17 $449.58 $1,362.38 $792.32 $1.474.28 $6,139.62 $1,420.27 UK USA Venezuela Condensed Type Allow Nulls Identity int Order Details Column Name Condensed Type Allow Nulls Identity 8 OrderlD int 9 ProductID int UnitPrice money Quantity smallint Discount real nvarchar(40) Categories Column Name Condensed Type Allow Nulls Identity 9 CategoryID int CategoryName nvarchar(15) Description ntext Picture image int 000000 Products Column Name 8 ProductID Product Name SupplierlD CategoryID QuantityPerUnit UnitPrice UnitsinStock UnitsOnOrder ReorderLevel 00000 int nvarchar(20) money smallint smallint smallint 00000000000 8 Identity Discontinued bit Employee Territories Column Name Condensed Type Allow Nulls Identity 9 Employeeld TerritoryID nvarchar(20) int Customers Column Name Condensed Type Allow Nulls 9 CustomerlD nchar(5) CompanyNa... nvarchar(40) Contact Name nvarchar(30) Contact Title nvarchar(30) Address nvarchar(60) M City nvarchar(15) Region nvarchar(15) PostalCode nvarchar(10) Country nvarchar(15) Phone nvarchar(24) Fax nvarchar(24) OOO |TOO Territories Column Name Condensed Type Allow Nulls Allow Nulls Identity 8 TerritoryID nvarchar(20) TerritoryDescription nchar(50) RegionID int Suppliers Column Name Condensed Type Allow Nulls Identity Suppliers int CompanyName nvarchar(40) ContactName nvarchar(30) Contact Title nvarchar(30) M Address nvarchar(60) City nvarchar(15) Region nvarchar(15) PostalCode nvarchar(10) Country nvarchar(15) Phone nvarchar(24) M Fax nvarchar(24) HomePage ntext 000 Region Column Name Condensed Type Allow Nulls Identity 9 RegionID int RegionDescription nchar(50) 000 CustomerCustomerDemo Column Name Condensed Type Allow Nulls Identity 9 CustomerlD nchar(5) CustomerTypeID nchar(10) Employees Column Name Condensed Type Allow Nulls Identity Employeeld int LastName nvarchar(20) FirstName nvarchar(10) Title nvarchar(30) TitleOfCourtesy nvarchar(25) BirthDate datetime HireDate datetime Address nvarchar(60) City nvarchar(15) Region nvarchar(15) PostalCode nvarchar(10) M Country nvarchar(15) HomePhone nvarchar(24) Extension nvarchar(4) Photo image OOOOOOOOOOOOOOOOOOO Orders Column Name Condensed Type Allow Nulls Identity od 9 OrderD int CustomerlD nchar(5) Employeeld int OrderDate datetime RequiredDate datetime ShippedDate datetime ShipVia int Freight money ShipName nvarchar(40) ShipAddress nvarchar(60) Ship City nvarchar(15) Ship Region nvarchar(15) ShipPostalCode nvarchar(10) Ship Country nvarchar(15) D Customer Demographics Column Name Condensed Type Allow Nulls Identity CustomerTypeID nchar(10) CustomerDesc ntext 000 ntext Notes Reports To PhotoPath int nvarchar(255) Shippers Column Name Condensed Type Allow Nulls Identity 8 ShipperID int CompanyName nvarchar(40) Phone nvarchar(24) Ship To Country Argentina Austria Belgium Brazil Canada Denmark Finland France Germany Ireland Italy Mexico Norway Poland Tax Amount $324.76 $5,120.15 $1,352.99 $4,277.03 $2,007.85 $1,306.44 $752.40 $3,254.33 $9,211.39 $1,999.20 $630.81 $707.46 $172.05 $105.96 Portugal Spain Sweden Switzerland $344.17 $449.58 $1,362.38 $792.32 $1.474.28 $6,139.62 $1,420.27 UK USA Venezuela Condensed Type Allow Nulls Identity int Order Details Column Name Condensed Type Allow Nulls Identity 8 OrderlD int 9 ProductID int UnitPrice money Quantity smallint Discount real nvarchar(40) Categories Column Name Condensed Type Allow Nulls Identity 9 CategoryID int CategoryName nvarchar(15) Description ntext Picture image int 000000 Products Column Name 8 ProductID Product Name SupplierlD CategoryID QuantityPerUnit UnitPrice UnitsinStock UnitsOnOrder ReorderLevel 00000 int nvarchar(20) money smallint smallint smallint 00000000000 8 Identity Discontinued bit Employee Territories Column Name Condensed Type Allow Nulls Identity 9 Employeeld TerritoryID nvarchar(20) int Customers Column Name Condensed Type Allow Nulls 9 CustomerlD nchar(5) CompanyNa... nvarchar(40) Contact Name nvarchar(30) Contact Title nvarchar(30) Address nvarchar(60) M City nvarchar(15) Region nvarchar(15) PostalCode nvarchar(10) Country nvarchar(15) Phone nvarchar(24) Fax nvarchar(24) OOO |TOO Territories Column Name Condensed Type Allow Nulls Allow Nulls Identity 8 TerritoryID nvarchar(20) TerritoryDescription nchar(50) RegionID int Suppliers Column Name Condensed Type Allow Nulls Identity Suppliers int CompanyName nvarchar(40) ContactName nvarchar(30) Contact Title nvarchar(30) M Address nvarchar(60) City nvarchar(15) Region nvarchar(15) PostalCode nvarchar(10) Country nvarchar(15) Phone nvarchar(24) M Fax nvarchar(24) HomePage ntext 000 Region Column Name Condensed Type Allow Nulls Identity 9 RegionID int RegionDescription nchar(50) 000 CustomerCustomerDemo Column Name Condensed Type Allow Nulls Identity 9 CustomerlD nchar(5) CustomerTypeID nchar(10) Employees Column Name Condensed Type Allow Nulls Identity Employeeld int LastName nvarchar(20) FirstName nvarchar(10) Title nvarchar(30) TitleOfCourtesy nvarchar(25) BirthDate datetime HireDate datetime Address nvarchar(60) City nvarchar(15) Region nvarchar(15) PostalCode nvarchar(10) M Country nvarchar(15) HomePhone nvarchar(24) Extension nvarchar(4) Photo image OOOOOOOOOOOOOOOOOOO Orders Column Name Condensed Type Allow Nulls Identity od 9 OrderD int CustomerlD nchar(5) Employeeld int OrderDate datetime RequiredDate datetime ShippedDate datetime ShipVia int Freight money ShipName nvarchar(40) ShipAddress nvarchar(60) Ship City nvarchar(15) Ship Region nvarchar(15) ShipPostalCode nvarchar(10) Ship Country nvarchar(15) D Customer Demographics Column Name Condensed Type Allow Nulls Identity CustomerTypeID nchar(10) CustomerDesc ntext 000 ntext Notes Reports To PhotoPath int nvarchar(255) Shippers Column Name Condensed Type Allow Nulls Identity 8 ShipperID int CompanyName nvarchar(40) Phone nvarchar(24)
