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 Shelby 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 Shelby Township. My output looked like the following:

Customer Name phone ------------------------------- ------------ LeMay Joe NULL Fanthon James 586-262-4533 Fanthon Amanda 586-262-4533 Stark Rob 586-555-2237

2) Create a query that will show those items that sold for between $10 and $20. 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 ----------- --------------------------------------- 1049 20.00 1003 15.50 1046 15.00 1067 12.00 1041 12.00 1016 11.95 1013 10.50 1023 10.50 1006 10.00 1085 10.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 for $20 or more. Order your output by order id. Your output should look like the following:

orderid Item ID Price Sales Tax Subtotal ----------- ----------- --------------- --------------- --------------- 1004 1004 32.50 1.9500 34.4500 1016 1020 47.95 2.8770 50.8270 1018 1018 47.50 2.8500 50.3500 1019 1017 80.00 4.8000 84.8000 1020 1019 30.00 1.8000 31.8000 1021 1031 25.99 1.5594 27.5494 1031 1029 82.50 4.9500 87.4500 1032 1037 85.00 5.1000 90.1000 1033 1032 100.00 6.0000 106.0000 1035 1038 145.00 8.7000 153.7000 1036 1049 20.00 1.2000 21.2000 1038 1054 57.50 3.4500 60.9500 2001 2000 175.50 10.5300 186.0300 2002 2002 125.00 7.5000 132.5000 3021 2030 35.00 2.1000 37.1000 3022 2031 145.00 8.7000 153.7000

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 1004 $32.50 $1.95 $34.45 1016 1020 $47.95 $2.88 $50.83 1018 1018 $47.50 $2.85 $50.35 1019 1017 $80.00 $4.80 $84.80 1020 1019 $30.00 $1.80 $31.80 1021 1031 $25.99 $1.56 $27.55 1031 1029 $82.50 $4.95 $87.45 1032 1037 $85.00 $5.10 $90.10 1033 1032 $100.00 $6.00 $106.00 1035 1038 $145.00 $8.70 $153.70 1036 1049 $20.00 $1.20 $21.20 1038 1054 $57.50 $3.45 $60.95 2001 2000 $175.50 $10.53 $186.03 2002 2002 $125.00 $7.50 $132.50 3021 2030 $35.00 $2.10 $37.10 3022 2031 $145.00 $8.70 $153.70

5) Create a query that will replace the null for termdate in the workers table with the text 'Current Employee'. Include in your output the people id, hire date and term date. Rename the term date column to Current. Order your output so that the current employees show at the top. Hint: This query can be solved a number of ways. If you try to use COALESE() only, you will get an error. The function COALESCE(), will look for nulls and replace with text. Since the term date field is not a text field you will need to convert it to a text field before using COALSCE(). My output looked like the following:

PeopleID hiredate Current ----------- ---------- ---------------- 1045 2013-05-01 Current Employee 1047 2012-08-22 Current Employee 1055 2012-08-22 Current Employee 1060 2009-01-25 Current Employee 1066 2010-06-22 Current Employee 1073 2014-03-04 Current Employee 1079 2014-02-04 Current Employee 2010 2010-06-22 Current Employee 2014 2010-04-01 Current Employee 1003 2011-12-10 Current Employee 1007 2011-01-25 Current Employee 1009 2012-05-24 Current Employee 1021 2011-11-04 Current Employee 1029 2010-02-10 2014-02-05 1016 2010-06-22 2014-01-15 1040 2010-01-15 2013-08-20 1035 2011-05-05 2013-08-06 1005 2010-10-15 2012-05-14

6) Create a query that will show those employees that have left Auntie B's and how many years they were employed. In your output include people id and the number of years employed. Title this last column "Years Employed". Your output should look like the following:

PeopleID Years Employed ----------- --------------- 1005 2 1016 4 1029 4 1035 2 1040 3

7) Create a query that will show the item id, size, max and min price for all those items that have a size of Large, Extra Large or Extra Extra Large. Before writing this query make sure you know how the size data is stored. Sort your output by size. My output looked like the following:

itemid size MaxPrice MinPrice ----------- --------------- --------------------------------------- --------------- 1061 L 2.50 1.50 1084 L 10.00 2.00 1085 XL 30.00 5.00 1073 XL 45.00 37.50 1005 XL 45.00 37.50 1043 XL 5.00 2.00 1056 xL 2.99 0.99 1031 XXL 25.99 18.00

8) Create a query that shows those items that have sizes: 8 - 10, 10 - 12, 12 - 14 and 14 - 16 and have a max price greater than or equal to $5. Include in your output, the item id, size and max price. Make sure to order your output by max price so that the higher priced items are at the top. My output looked like the following:

ItemID size MaxPrice ----------- --------------- --------------------------------------- 1016 10 - 12 12.00 1009 12 - 14 5.50 1015 8 - 10 5.00

9) Auntie B's has decided to hold a sale on toddler clothing. Toddler clothing ranges in sizes 2T - 6T and 6X. Create a query that will determine a 20% discount on the max price for each piece of toddler clothing. Since we do not know which item type id's are for toddler clothing yet, filter your query based on the sizes. Rename your columns as you see below and round the price to two decimals. My output looked like the following:

Item ID Price Discount Discounted Price ----------- ------ ---------- --------------------------------------- 1000 5.00 1.0000 4.0000 1001 7.50 1.5000 6.0000 1002 10.50 2.1000 8.4000 1021 3.50 0.7000 2.8000 1048 4.00 0.8000 3.2000 1050 8.00 1.6000 6.4000 1058 2.99 0.5980 2.3900 1060 1.99 0.3980 1.5900 1068 5.00 1.0000 4.0000 1069 7.50 1.5000 6.0000 1070 10.50 2.1000 8.4000

10) Auntie B's wants to know who their loyal customers are. Create a query that will show the people id, customer since date and the number of years someone has been a customer at Auntie B's. Only show those customers that have been with Auntie B's for 5 or more years. Sort your output by the number of years someone has been a customer so that those who have been a customer the longest are on top. My output looked like the following:

peopleid CustomerSince Years Customer ----------- ------------- -------------- 2013 2005-04-01 10 2011 2007-04-01 8 1057 2008-09-27 7 1059 2009-08-09 6 1063 2010-06-01 5 1064 2010-08-04 5 1065 2010-09-10 5 1001 2010-06-01 5 1002 2010-08-04 5 1004 2010-09-10 5 1006 2010-10-24 5 1008 2010-11-05 5 1012 2010-02-14 5 1014 2010-04-01 5 1015 2010-06-24 5 1019 2010-03-18 5 1020 2010-07-05 5 1022 2010-09-20 5 1023 2010-09-20 5 1024 2010-10-21 5 1048 2010-09-11 5 1052 2010-09-11 5

AuntiB sql File: https://drive.google.com/open?id=0BwasM-ovulfxcF9VX2RFbmUyU2M