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. Additionally only include those employees who were hired in 2012. 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

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

1009 2012-05-24 Current Employee

1047 2012-08-22 Current Employee

1055 2012-08-22 Current Employee

6. Create a query that will show those employees that left Auntie B's in 2014. In your output show the people id, hire date, term date and years employeed. 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 HireDate TermDate Years Emplolyed

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

1016 2010-06-22 2014-01-15 4

1029 2010-02-10 2014-02-05 4

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

itemid size Max Price Min Price

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

1007 M 5.00 3.75

1030 M 55.55 44.44

1039 M 10.00 7.50

1040 M 7.00 4.00

1006 S 10.00 8.95

8. Create a query that shows the item type id, name and description for all bedroom furniture. My output looked like the following:

itemtypeid name description

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

1001 Furniture Adult Adult Bedroom Furniture

1002 Furniture Child Child Bedroom Furniture

1003 Furniture Baby Baby Bedroom Furniture

9. Auntie B's has decided to hold a sale on large clothing. Large clothing includes both womens and mens and sizes that are large, extra large, extra extra large and extra extra extra large. Create a query that will determine a 30% discount on the max price for each piece of large clothing. Since we do not know which item type id's are for the large 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 size Price Discount Discounted Price

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

1061 L 2.50 0.7500 1.7500

1084 L 10.00 3.0000 7.0000

1085 XL 30.00 9.0000 21.0000

1073 XL 45.00 13.5000 31.5000

1005 XL 45.00 13.5000 31.5000

1043 XL 5.00 1.5000 3.5000

1056 xL 2.99 0.8970 2.0900

1031 XXL 25.99 7.7970 18.1900

2002 XXXL 150.00 45.0000 105.0000

10. Create a query that will show the donation date and the charity ids for those charities that have a charity id of 1001 or 1002. Additionally make sure the donation occurred in 2013. Order your output by charity id. Make sure you date looks like the dates you see below. My output looked like the following:

Donation Date Charity Date

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

May 01, 13 1001

May 02, 13 1001

Jan 01, 13 1001

Jun 15, 13 1002

Jun 30, 13 1002