Assignment E: Tables, Views, and Functions must be created in your individual database on the Yorktown server!

Download Assignment E-skeleton.sql from Canvas and rename the downloaded file to MIS-421- ASSIGNMENT-E-LastName-FirstName.sql. Open this SQL file and make it associated with your individual database for the following tasks.

1. The User-Defined Function question:

Make sure you have completed the assignment Function example/exercise (Tutorial) before you work on this assignment.

Create the function ufnGetOrderGrandTotalWithoutDiscount

The MIS-421-ASSIGNMENT-E-LastName-FirstName.sql has a brief description of why we need the function and test script to get results using this function. This function is similar to the function ufnGetOrderGrandTotalWithDiscount in the assignment Function example/exercise (Tutorial). You may want to reference the code for ufnGetOrderGrandTotalWithDiscount.

The skeleton of the code is provided in the file. Some statements have been provided. Complete coding the function ufnGetOrderGrandTotalWithoutDiscount and execute it to create the function in your individual database (make sure to highlight the entire function code before clicking the Execute button)

Use the SELECT test script in the file, which uses the function ufnGetOrderGrandTotalWithoutDiscount and produces the result as shown to the right (first 20 records)

Create the function ufnGetFirstName

Remember that the values of the FullName field in tblPersonNames should be in one of the two formats as discussed in the video

Firstname Lastname

Lastname, Firstname

The skeleton of the code for the function ufnGetFirstName is in the MIS-421-ASSIGNMENT-E-LastName-FirstName.sql file,

Complete the function ufnGetFirstName to extract the first name from the FullName field and returns the extracted first name. Reference the ufnGetLastName function from the tutorial Function example/exercise (Tutorial) for how to write the function ufnGetFirstName.

After you code your functions and execute the codes to create the functions in your database, you can test it using this script in a Query Editor window:

SELECT FullName, dbo.ufnGetFirstName (FullName) as FirstName, dbo.ufnGetLastName (FullName) as LastName FROM tblPersonNames;

You should get the results as shown to the right. Compare the values for the FirstName and LastName columns with the values in the FullName to make sure that the functions return the right values.

2. DML and View (all scripts need to be written in the SQL file MIS-421-ASSIGNMENT-E-LastName-FirstName.sql)

Write an UPDATE statement to change the values of tblItem.ItemDescription from Desk Lamp to Desk Lamps

Make sure the foreign keys have been created in tblSaleItem correctly. They should have been created in Assignment D.

Insert new records:

Write an INSERT statement to insert one new record of data to record a sale in tblSale. Choose a customer ID value from tblCustomer and a employee ID value from tblEmployee for this new record. You can make up other values for the other columns of the new record. Remember SaleID is an identity primary key in tblSale.

Look up the new SaleID value for the new record in tblSale, then write two insert statements to insert into tblSaleItem two new records of sale items with the new SaleID value. The first record should have 1 and the second record should have 2 for the SaleItemNum column. Choose two sets of ItemID and ItemPrice values from the tblItem table for the ItemID and ItemPrice column for the insert statements.

Write a DELETE statement or DELETE statements to delete the new Sale record from tblSale and the two sale Items on that Sale from tblSaleItem you just created. Can you use just one DELETE statement to delete the records in tblSale and related records in tblSaleItem? Why?

Write an SQL statement to create a view called vueSaleSummary that contains tblSale.SaleID, tblSale.SaleDate, tblSaleItem.SaleItemNum, tblSaleItem.ItemID, tblItem.ItemDescription, and tblItem.ItemPrice. Run the statement to create the view and write an SQL statement to show all columns from this view.

Assignment E-skeleton.sql

\begin{tabular}{|l|l|l|} \hline & OrderlD & Grand Total w/o Discourit \\ \hline 1 & 10248 & 440 \\ \hline 2 & 10249 & 1863.4 \\ \hline 3 & 10250 & 1813 \\ \hline 4 & 10251 & 670.8 \\ \hline 5 & 10252 & 3730 \\ \hline 6 & 10253 & 1444.8 \\ \hline 7 & 10254 & 625.2 \\ \hline 8 & 10255 & 2490.5 \\ \hline 9 & 10256 & 517.8 \\ \hline 10 & 10257 & 1119.9 \\ \hline 11 & 10258 & 2018.6 \\ \hline 12 & 10259 & 80 \\ \hline 13 & 10260 & 1746.2 \\ \hline 14 & 10261 & 448 \\ \hline 15 & 10262 & 624.8 \\ \hline 16 & 10263 & 2464.8 \\ \hline 17 & 10264 & 724.5 \\ \hline 18 & 10265 & 1176 \\ \hline 19 & 10266 & 364.8 \\ \hline 20 & 10267 & 4031 \\ \hline \end{tabular} The tblPSMOrder table does not have a column for the grand total of all products ordered without discount. Create a function that calculates the grand total without discount of all products purchased in an order a. Name the function as ufnGetorderGrandTotalWithoutDiscount b. The function takes this parameter: @0rderID int c. Note this function use both tblPSMOrder and tblPSMOrderDetail tables. d. This function uses a cursor to process each product in an order If you need to modify the function after you create it in your database, change CREATE to ALTER in the header of the function, then execute the modified function to update the function in your database. Test script: select OrderID, dbo.ufnGetorderGrandTotalWithoutDiscount(OrderID) as 'Grand Total w/o Discount' from tblpsmorder; */ CREATE FUNCTION dbo.ufnGetOrderGrandTotalWithoutDiscount (@orderID int) returns float AS BEGIN --Declare local variables: --@grandTotal float, @unitPrice float, @quantity int --Initiate the local variable values to be zero --Declare a cursor to get UnitPrice and Quantity for all products from tblPSMOrderDetail where OrderID=@orderID --Open the cursor OPEN cs; --Fetch UnitPrice and Quantity into @unitPrice, @quantity FETCH next from cs INTO @unitPrice, @quantity --Use @@FETCH_STATUS to check if there are more records in the cursor --@@FETCH_STATUS=0 - -> it successfully fetched a row; @@FETCH_STATUS=-1 - is no more WHILE @@FETCH_STATUS=0 BEGIN --calculate the grand total without discount --fetch next row from the cursor FETCH next from cs INTO @unitPrice, @quantity END --close the cursor CLOSE cs; --deallocate the cursor in memory DEALLOCATE cs; --return @grandTotal END / Reference the ufngetLastName function from the assignment "Function example/exercise (Tutorial)" for how to write the function ufnGetFirstName. You should have completed the function ufnGetLastName in the assignment "Function example/exercise (Tutorial)". If you haven't, please complete the assignment to create the ufngetlastName in your database. If you need to modify the function after you create it in your database, change CREATE to ALTER in the header of the function, then execute the modified function to update the function in your database. Test script: Select FullName, dbo. ufnGetFirstName (FullName) AS First from tblPersonNames; / CREATE FUNCTION dbo, ufnGetFirstName (@fullname varchar (100)) returns varchar (50) AS BEGIN --declare the local variable: @firstName --declare the index variable to find the index of the separator --that separates first name from last name --get the separator index value: first check if the default separator (,) exists --if it does, use the substring function to find the first name --if it does not, let's assume the space is the separator and --the full name format is FirstName LastName --find the index for the space, then find the first name --return the first name variable @firstName END