See the Handout provided to you with the database (DB) queries and sample examples on how to set up criteria. The company wants to increase the salary of its employee using the following rules in the following EMPLOYEE database:
For New Salary of an employee: A female employee working as a Manager will get a 10.5% increase, while a male employee in the same position will get 9% increase (the company wants to take care of previous inequities in salaries!). All other employees get 5% increase if they have been with the company for more than 13 years, irrespective of their gender, otherwise their salary does not change. The salary column refers to the current salary of the employee; the Previous Salary column refers the salaried employee was hired at. A new Salary Column would be the new salary of the employee using the rules to provide more salary equity.
1 See the Handout provided to you with the database (DB) queries and sample examples on how to set up criteria. 2 3 Company wants to increase the salary of its employee using following rules in the following EMPLOYEE database: 4 For New Salary of an employee: 5 A female employee working as a Manager will get a 10.5% increase, while 6 a male employee in the same position will get 9% increase (company wants to take care of previous inequities in salaries!). All other 7 employees get 5% increase if they have been with company for more than 13 years, irrespective of their gender, 8 otherwise their salary does not change. 9 Salary column refers to the current salary of the employee; Previous Salary column refers the salary employee was hired at. 10 New Salary Column would be the new salary of the employee using the rules to provide more salary equity. 11 Previous 12 Name Location Title Gender Hire Date Service Salary Salary New Salary 13 Rubin Boston Account Rep F F 3/16/14 8.1 $45,000 $40,000 $ 45,000.00 14 Smith Atlanta Account Rep 1/14/20 2.2 $65,000 $0 $ 65,000.00 15 Johnson Chicago Account Rep 1/1/18 4.3 $47,500 $40,000 $ 47,500.00 16 Marder Chicago Account Rep 6/15/07 14.8 $38,500 $35,000 $ 40,425.00 17 Manin Boston Account Rep F 3/16/99 23.1 $49,500 $45,000 $ 51,975.00 18 James Chicago Account Rep 10/31/17 4.4 $42,500 $41,000 $ 42,500.00 19 Gillenson Miami Account Rep M 10/31/16 5.4 $55,000 $52,500 $ 55,000.00 20 Charles Boston Account Rep 3/16/20 2.1 $40,000 $0 $ 40,000.00 21 Elofson Miami Account Rep 4/23/95 27.0 $47,500 $45,000 $ 49,875.00 22 Tucci Miami Manager F 1/25/07 15.2 $65,000 $57,550 $ 75,075.00 23 Milgrom Boston Manager M 3/16/13 9.1 $57,500 $55,000 $ 62,675.00 24 Adamson Chicago Manager 3/16/12 10.1 $52,000 $45,000 $ 60,060.00 25 Coulter Atlanta Manager M 11/24/99 22.4 $100,000 $90,000 $ 109,000.00 26 Adams Atlanta Trainee M M 1/9/10 12.3 $19,500 $0 $ 19,500.00 27 Brown Atlanta Trainee F 1/10/10 12.3 $18,500 $0 $ 18,500.00 28 3 3 3 3 T33 3 TI Employee Queries LookUp info + Ready 2 Queries for Employee Worksheet OWN- 1 How many employees are there in this database. 5 6 7 15 10 11 2 How many Managers work in Atlanta? Previous Salary New Salary Name Gender Hire Date Service Salary Location Atlanta Title Manager 1 12 13 14 15 16 17 18 19 3 What percentage of employees are female employees hired after 1/7/2005? Previous Name Location Title Gender Hire Date Service Salary Salary New Salary 20 21 22 23 24 25 26 27 4 What is the average salary of managers earning between $40000 and $75000 (using New Salary)? Previous New New Name Location Title Gender Hire Date Service Salary Salary Salary Salary Manager >=40000 = = 28 29 30 31 32 33 34 35 $61,367.50 5 What is the maximum salary being earned by a female employee hired between 1/1/2010 and 12/31/2020 (both dates included)? Previous New Name Location Title Gender Hire Date Hire Date Service Salary Salary Salary 36 37 Employee Queries LookUp info + Ready A A o 35 5 B E F G H J KL M N What is the maximum salary being earned by a female employee hired between 1/1/2010 and 12/31/2020 (both dates included)? Previous New Name Location Title Gender Hire Date Hire Date Service Salary Salary Salary 36 37 38 39 40 41 42 43 44 6 How many male employees are either trainees or managers? Previous Salary New Salary 45 Name Location Salary Title Manager Trainee Gender Hire Date Service M M M 3 46 47 48 49 50 51 52 7 What is the average salary of employees hired after 1/1/2010 in Miami? Previous Name Location Title Gender Hire Date Service Salary Salary New Salary 53 54 55 56 57 58 59 8 How many employees with name starting with letter M in Boston? 28 9 8 8 8 8 8 8 $988 98 Previous Salary New Salary 60 Name Title Gender Hire Date Service Salary Location Boston M 63 64 65 66 RE 2 9 9 What is the percent rank of the new salary of female employee hired on 3/16/2012? Previous Name Location Title Gender Hire Date Service Salary Salary F 3/16/12 600 New Salary 67 68 Employee Queries LookUp info + Ready R Salary A A B E E F G . J J L . N 0 P 66 9 What is the percent rank of the new salary of female employee hired on 3/16/2012? Previous New 67 Name Location Title Gender Hire Date Service Salary Salary 68 F 3/16/12 69 60060 70 71.40% 71 72 73 74 75 10 Sort the database (on employee worksheet) by hire date (most recent hire showing first) within each title. Previous New 76 Name Location Title Gender Hire Date Service Salary Salary Salary 77 78 79 80 81 82 83 Question using Wildcard ? 84 85 HINT: Use of Wildcard * and ? 86 ????? (Question mark) is used as wildcard for only a single character letter. Example, "BUS?08" to search for all BUS courses in catalog ending with numbers 08. 87 (Asterisk) is used as wildcard for one or more letters. Example, "BUS*" to search for all BUS courses in the catalog. 88 Extra question #1 89 What is the net new salary of employees with name starting with 2nd letter as D? Previous New 90 Name Location Title Gender Hire Date Service Salary Salary Salary 91 92 93 94 95 96 97 98 Extra question #2 99 100 Find the average service time of account reps or trainees hired between 1/1/1997 and 12/31/2013? Previous New 101 Name Location Title Gender Hire Date Service Salary Salary Salary Employee Queries LookUp info + Ready 1 See the Handout provided to you with the database (DB) queries and sample examples on how to set up criteria. 2 3 Company wants to increase the salary of its employee using following rules in the following EMPLOYEE database: 4 For New Salary of an employee: 5 A female employee working as a Manager will get a 10.5% increase, while 6 a male employee in the same position will get 9% increase (company wants to take care of previous inequities in salaries!). All other 7 employees get 5% increase if they have been with company for more than 13 years, irrespective of their gender, 8 otherwise their salary does not change. 9 Salary column refers to the current salary of the employee; Previous Salary column refers the salary employee was hired at. 10 New Salary Column would be the new salary of the employee using the rules to provide more salary equity. 11 Previous 12 Name Location Title Gender Hire Date Service Salary Salary New Salary 13 Rubin Boston Account Rep F F 3/16/14 8.1 $45,000 $40,000 $ 45,000.00 14 Smith Atlanta Account Rep 1/14/20 2.2 $65,000 $0 $ 65,000.00 15 Johnson Chicago Account Rep 1/1/18 4.3 $47,500 $40,000 $ 47,500.00 16 Marder Chicago Account Rep 6/15/07 14.8 $38,500 $35,000 $ 40,425.00 17 Manin Boston Account Rep F 3/16/99 23.1 $49,500 $45,000 $ 51,975.00 18 James Chicago Account Rep 10/31/17 4.4 $42,500 $41,000 $ 42,500.00 19 Gillenson Miami Account Rep M 10/31/16 5.4 $55,000 $52,500 $ 55,000.00 20 Charles Boston Account Rep 3/16/20 2.1 $40,000 $0 $ 40,000.00 21 Elofson Miami Account Rep 4/23/95 27.0 $47,500 $45,000 $ 49,875.00 22 Tucci Miami Manager F 1/25/07 15.2 $65,000 $57,550 $ 75,075.00 23 Milgrom Boston Manager M 3/16/13 9.1 $57,500 $55,000 $ 62,675.00 24 Adamson Chicago Manager 3/16/12 10.1 $52,000 $45,000 $ 60,060.00 25 Coulter Atlanta Manager M 11/24/99 22.4 $100,000 $90,000 $ 109,000.00 26 Adams Atlanta Trainee M M 1/9/10 12.3 $19,500 $0 $ 19,500.00 27 Brown Atlanta Trainee F 1/10/10 12.3 $18,500 $0 $ 18,500.00 28 3 3 3 3 T33 3 TI Employee Queries LookUp info + Ready 2 Queries for Employee Worksheet OWN- 1 How many employees are there in this database. 5 6 7 15 10 11 2 How many Managers work in Atlanta? Previous Salary New Salary Name Gender Hire Date Service Salary Location Atlanta Title Manager 1 12 13 14 15 16 17 18 19 3 What percentage of employees are female employees hired after 1/7/2005? Previous Name Location Title Gender Hire Date Service Salary Salary New Salary 20 21 22 23 24 25 26 27 4 What is the average salary of managers earning between $40000 and $75000 (using New Salary)? Previous New New Name Location Title Gender Hire Date Service Salary Salary Salary Salary Manager >=40000 = = 28 29 30 31 32 33 34 35 $61,367.50 5 What is the maximum salary being earned by a female employee hired between 1/1/2010 and 12/31/2020 (both dates included)? Previous New Name Location Title Gender Hire Date Hire Date Service Salary Salary Salary 36 37 Employee Queries LookUp info + Ready A A o 35 5 B E F G H J KL M N What is the maximum salary being earned by a female employee hired between 1/1/2010 and 12/31/2020 (both dates included)? Previous New Name Location Title Gender Hire Date Hire Date Service Salary Salary Salary 36 37 38 39 40 41 42 43 44 6 How many male employees are either trainees or managers? Previous Salary New Salary 45 Name Location Salary Title Manager Trainee Gender Hire Date Service M M M 3 46 47 48 49 50 51 52 7 What is the average salary of employees hired after 1/1/2010 in Miami? Previous Name Location Title Gender Hire Date Service Salary Salary New Salary 53 54 55 56 57 58 59 8 How many employees with name starting with letter M in Boston? 28 9 8 8 8 8 8 8 $988 98 Previous Salary New Salary 60 Name Title Gender Hire Date Service Salary Location Boston M 63 64 65 66 RE 2 9 9 What is the percent rank of the new salary of female employee hired on 3/16/2012? Previous Name Location Title Gender Hire Date Service Salary Salary F 3/16/12 600 New Salary 67 68 Employee Queries LookUp info + Ready R Salary A A B E E F G . J J L . N 0 P 66 9 What is the percent rank of the new salary of female employee hired on 3/16/2012? Previous New 67 Name Location Title Gender Hire Date Service Salary Salary 68 F 3/16/12 69 60060 70 71.40% 71 72 73 74 75 10 Sort the database (on employee worksheet) by hire date (most recent hire showing first) within each title. Previous New 76 Name Location Title Gender Hire Date Service Salary Salary Salary 77 78 79 80 81 82 83 Question using Wildcard ? 84 85 HINT: Use of Wildcard * and ? 86 ????? (Question mark) is used as wildcard for only a single character letter. Example, "BUS?08" to search for all BUS courses in catalog ending with numbers 08. 87 (Asterisk) is used as wildcard for one or more letters. Example, "BUS*" to search for all BUS courses in the catalog. 88 Extra question #1 89 What is the net new salary of employees with name starting with 2nd letter as D? Previous New 90 Name Location Title Gender Hire Date Service Salary Salary Salary 91 92 93 94 95 96 97 98 Extra question #2 99 100 Find the average service time of account reps or trainees hired between 1/1/1997 and 12/31/2013? Previous New 101 Name Location Title Gender Hire Date Service Salary Salary Salary Employee Queries LookUp info + Ready
