Chapter 8 Homework Problems
This assignment uses the GuitarShop Database. Double check the SQL syntax for each query created.
Each numbered question is worth 5 points if the following is included in your submission.
1) The sql code.
a. Use comments to show your name, assignment, and the query number
2) The first 5 lines of the results tab, with headings.
3) The output in the messages tab
4) No query, results, and messages should span any page
eak.
Please create solutions to the following queries:
1. Write a select statement that returns four columns based on the ItemPrice column of the
OrdersItems table.
a. Use the CAST function to return the first column with 0 digits to the right of the decimal
point
. Use the CAST function to return the second column with a $ in front of the value.
c. Using the CONVERT function to return the third column with the smallest precision in
mind to return the scale of 3.
d. Use the CONVERT function to return the fourth column with a $ in front and back of the
value. There should be no spaces between the symbols and number. i.e $3,813.33$
One row example without the headers:
3813 $ XXXXXXXXXX $3,813.33$
2. Write a SELECT statement that returns two columns based on the Customers and Address
tables.
a. The first column FirstName Last name initial “Bob J.” Label it “Contact”
. For the phone, return the full Number with parens around the area code, “ XXXXXXXXXX-
6356”)
Criteria:
1) Only return rows for those Customers in the 401 area code.
2) Sort the result set by last name.
One row example with headers:
Contact Phone
Thom A XXXXXXXXXX