*Please help. SQL Server management studio.*

Homework #4 ~ Single Table Queries

1. Create a query that will show the name of each customer and their phone number for all customers who live in Clinton Township. Make sure the first and last name of the customer appears as one column. Additionally make sure your query accommodates for the multiple spellings of Clinton Township. My output looked like the following:

Customer Name phone

------------------------------- ------------

Theodore Turner 586-555-9876

Patricia Jones 586-555-9876

Paul Jones 586-555-9876

Simon Tara NULL

Tara Roswell NULL

Haylee Higgins NULL

2. Create a query that will show those items that sold for between $75 and $100. Include in your query the item id and price column. Order your output by the highest priced item. Your output should look like the following:

itemid price

----------- ---------------------------------------

1032 100.00

1037 85.00

1029 82.50

1017 80.00

3. Create a query that will show the order id, item id, price, sales tax and subtotal for all items in the order item table. Create column aliases to describe the sales tax and subtotal columns. Only include in your output those items that sold between $15 and $20. Order your output by order id. Your output should look like the following:

orderid itemid price Sales Tax Subtotal

----------- ----------- ---------- ---------- ----------

1004 1003 15.50 0.9300 16.4300

1036 1046 15.00 0.9000 15.9000

1036 1049 20.00 1.2000 21.2000

4. Using #3 as a starting point, format the price, Sales Tax and Subtotal columns to include the dollar sign. Also shorten these columns. Finally round the Sales Tax and Subtotal columns to 2 decimal places. Hint: There are a few ways you can solve this. One way is to use the ROUND function. Another is to convert the price, sales tax and subtotal to the money data type. Either way involves nesting multiple functions. Remember to work in iterations here. My output looked like the following:

orderid itemid price Sales Tax Subtotal

----------- ----------- ----------- ----------- -----------

1004 1003 $15.50 $0.93 $16.43

1036 1046 $15.00 $0.90 $15.90

1036 1049 $20.00 $1.20 $21.20