COMP 3321 atabase Systems
Chapter 7 atabase Construction and Application Processing
Assignment 7
Name: _________ __________
Due Date: November 29th, 2022,
Grade: _______
Consider the following model:
1. Check the Assignment 6 solution and compare it with your work. Make necessary changes to
your work, if needed.
2. Use the attached SQL script named HSD-Insert-Data.sql to insert the data for the HSD
database. Just run the file.
3. Create a SQL script named HSD-Data.sql and add the following SQL statements:
a. Write an SQL statement to modify PRODUCT UnitPrice for ProductNumber VK004 to
$34.95 instead of the current UnitPrice of $24.95.
b. Add a column called åminarDescription4o the SEMINAR table. The new column should
be maximum of 50 characters and it is not null.
c. Write a statement to drop the column åminarDescriptionŠd. Imagine you have a table in the schema called IMPORTED_PRODUCT that has the same
structure as the PRODUCT table. Write a statement to insert all the data from the
IMPROTED_PRODUCT to the PRODUCT table.
Note: The statement may not run in old versions of MySQL.
4. Create a SQL script named HSD-MoreData.sql and add the following SQL statements:
a. Write an SQL statement to create a view called InvoiceSummaryView that contains
INVOICE.InvoiceNumber, INVOICE.InvoiceDate, LINE_ITEM.LineNumber,
LINE_ITEM.ProductNumber, PRODUCT.ProductDescription, and LINE_ITEM.UnitPrice.
b. Create and test a user-defined function named LastNameFirst that combines two parameters
named FirstName and LastName into a concatenated name field formatted LastName,
FirstName (including the comma and space).
c. Write an SQL statement to create a view called CustomerInvoiceSummaryView that contains
INVOICE.InvoiceNumber, INVOICE.InvoiceDate, the concatenated customer name using
the LastNameFirst function, CUSTOMER.EmailAddress, and INVOICE.Total.
d. Create a stored procedure that selects Customers&irst name and last name from a certain
City with a certain ZipCode. **May not be run, just write a procedure**
e. Create a stored procedure out of the INVOICE table that selects a field as the sum of Total
and Shipping if Subtotal is less than 100 and selects a field as the Total only if Subtotal is
equal or greater than 100. **May not be run, just write a procedure**
Compress to ZIP file, the 2 files HSD-MoreData.sql and HSD-MoreData.sq, and upload it to
the portal.
End of Assignment #7. Good Luck

Purchase answer to see full
attachment