Homework #4 ~ Single Table Queries 1) Create a query that will show the name of
ID: 3597720 • Letter: H
Question
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
Explanation / Answer
Hi,
Answering the 1st 4 sub parts as per chegg policy, please post others as separate question.
1. select CONCAT(FirstName, LastName ) as 'Customer Name',Phone from people where address like '%shelby%';
2. select a,itemId,a.price from ORDER_ITEM a,ITEM b where a.ItemID=b.ItemID and a.price>= 10 and a.price<=20 order by a.price
3.select a.OrderIDa,itemId,a.price,16.66*(a.price) as 'sales tax',(a.price+16.66*(a.price)) as 'sub total' from ORDER_ITEM a,ITEM b where a.ItemID=b.ItemID and a.price>=20;
4. select a.OrderIDa,itemId,CONCAT($,round(a.price)),CONCAT($,round(16.66*(a.price))) as 'sales tax',CONCAT($,a.price+round(16.66*(a.price)))) as 'sub total' from ORDER_ITEM a,ITEM b where a.ItemID=b.ItemID and a.price>=20;
Thumbs up if this was helpful, otherwise let me know in comments
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.