- Access QBE query exercises Employee database 1. Suppose there is a 10% raise in wage rate for those non-salaried employees. Write a QBE query to display the SSN, current and new wage rates for those non-salaried employees. query1 : Select Query SSN Wage Rate 099-11-3344 $8.00 114-11-2333 $10.00 148-90-1234 $12.00 New Rate $8.80 $11.00 $13.20 2. Write a QBE query to display the SSN, last name, wage rate, salary status and citizenship for only U.S. citizens and hourly workers (non-salaried). ME US Citizen? Salaried query2: Select Query Last Name SSN Brady 099-11-3344 Howard 114-11-2333 Smith 148-90-1234 Wage Rate $8.00 $10.00 $12.00 3. Suppose we want to see the wages owed to hourly employees for week 2. Show the last name, wage owed, salary status, number of hours worked and SSN. Salaried Hours 9 query3: Select Query Last Name SSN Brady 099-11-3344 Howard 114-11-2333 Smith 148-90-1234 Pay $440.00 $500.00 $480.00 55 50 40 6. For non- salaried employees, show their SSN and elapsed time (number of hours) at work for Wednesday, March 31st, 1999. Note that the time unit in Access is per day; and there are 24 hours per day; you need to multiply 24 to the decimal value.- 9 query6: Select Query SSN Salaried 099-11-3344 114-11-2333 148-90-1234 Elapsed Time 8 6 10 8. Show the last name, SSN, week number and the wage owed to those hourly (non- salaried) workers for week 1. 4 Week # 4 query8: Select Query Last Name SSN Brady 099-11-3344 Howard 114-11-2333 Smith 148-90-1234 & 1 1 1 Total Owed $480.00 $400.00 $456.00 ME HLED 9. Show the SSN, last name, state, and date hired for those employees living in state DE and who were hired after 12/31/1992. Sort (ascending) by SSN. query9: Select Query SSN Last Name State Date Hired 114-11-2333 Howard DE Monday, August 01, 1994 123-45-6789 Smith DE Saturday, June 01, 1996 10. Show the last name, first name, date hired and state for those employees living in state DE, or who were hired after 12/31/1992. Sort (ascending) on last name. ADE 9 query 10: Select Query Last Name First Name Brady Joseph Howard Jane Jones Sue Ruth Billy Smith Albert Smith John Date Hired State Wednesday, March 31, 1999 MN Monday, August 01, 1994 DE Wednesday, July 15, 1992 DE Tuesday, August 15, 1995 MD Wednesday, July 15, 1987 DE Saturday, June 01, 1996 DE 2 - Access QBE query exercises Employee database 1. Suppose there is a 10% raise in wage rate for those non-salaried employees. Write a QBE query to display the SSN, current and new wage rates for those non-salaried employees. query1 : Select Query SSN Wage Rate 099-11-3344 $8.00 114-11-2333 $10.00 148-90-1234 $12.00 New Rate $8.80 $11.00 $13.20 2. Write a QBE query to display the SSN, last name, wage rate, salary status and citizenship for only U.S. citizens and hourly workers (non-salaried). ME US Citizen? Salaried query2: Select Query Last Name SSN Brady 099-11-3344 Howard 114-11-2333 Smith 148-90-1234 Wage Rate $8.00 $10.00 $12.00 3. Suppose we want to see the wages owed to hourly employees for week 2. Show the last name, wage owed, salary status, number of hours worked and SSN. Salaried Hours 9 query3: Select Query Last Name SSN Brady 099-11-3344 Howard 114-11-2333 Smith 148-90-1234 Pay $440.00 $500.00 $480.00 55 50 40 6. For non- salaried employees, show their SSN and elapsed time (number of hours) at work for Wednesday, March 31st, 1999. Note that the time unit in Access is per day; and there are 24 hours per day; you need to multiply 24 to the decimal value.- 9 query6: Select Query SSN Salaried 099-11-3344 114-11-2333 148-90-1234 Elapsed Time 8 6 10 8. Show the last name, SSN, week number and the wage owed to those hourly (non- salaried) workers for week 1. 4 Week # 4 query8: Select Query Last Name SSN Brady 099-11-3344 Howard 114-11-2333 Smith 148-90-1234 & 1 1 1 Total Owed $480.00 $400.00 $456.00 ME HLED 9. Show the SSN, last name, state, and date hired for those employees living in state DE and who were hired after 12/31/1992. Sort (ascending) by SSN. query9: Select Query SSN Last Name State Date Hired 114-11-2333 Howard DE Monday, August 01, 1994 123-45-6789 Smith DE Saturday, June 01, 1996 10. Show the last name, first name, date hired and state for those employees living in state DE, or who were hired after 12/31/1992. Sort (ascending) on last name. ADE 9 query 10: Select Query Last Name First Name Brady Joseph Howard Jane Jones Sue Ruth Billy Smith Albert Smith John Date Hired State Wednesday, March 31, 1999 MN Monday, August 01, 1994 DE Wednesday, July 15, 1992 DE Tuesday, August 15, 1995 MD Wednesday, July 15, 1987 DE Saturday, June 01, 1996 DE 2