Question

Ch5p1 - The HR department needs a report to display employee information as shown from the job_history table. The months worked is the number of months from the start date to the end date. Only list those employees whose months worked is an even number. Order by months worked descending. Employment history Employee 102 worked 66 months in department 60 Employee 101 worked 52 months in department 110 Ch5p2 - Write a query that displays the employees whose last name contains the letter 'P' (Upper or lower case) and the date of the first Friday after their hire date. Format as shown. Sort the list by last name. First Friday After Hire Date 1 Fripp******17-APR-15 2 Partners07-JAN-05 3 Pataballa*12-FEB-16 4 Patel*****07-APR-06 5 Perkins*21-DEC-07 6 Philtanker09-FEB-18 7 Popp**14-DEC-07 8 Raphaely14-DEC-12 Ch5p3 - Management wants a list of all employees with their base salary and overtime salary (this is time and a half of their base salary). Only display those employees whose phone number has area code that is entered by the user at run time. Sort by the base salary, and in case of a tie, then by last name. The following run is for area code 590: Employee ID Employee Name Phone Base Salary Overtime Salary 1 107 Lorentz 590.423.5567 4200 6300 2 105 Austin 590.423.4569 4800 7200 3 106 Pataballa 590.423.4560 4800 7200 4 104 Ernst 590.423.4568 6000 9000 5 103 Hunold 590.423.4567 9000 13500 Here is another run with area code 603: Employee ID Employee Name Phone Base Salary Overtime Salary 1 202 Fay 603.123.6666 6000 9000 Ch5p4 - Create a query to display the faculty's full name, formatted phone number, pin, and Multifactor Authentication Code. The MFA Code is calculated by adding up the digits in the pin. Order by the faculty last names. Faculty Name Phone Number Pin Multifactor Authentication Code 1 John R. Blanchard (715) 555-9087 1075 13 2 Philip E. Brown (715) 555-6082 9899 35 3 Kim J. Cox (715) 555-1234 1181 11 4 Laura M. Sheng (715) 555-6409 1690 16 5 Jerry F. Williams (715) 555-5412 8531 17 Ch5p5 – Create a listing of the course sections with their relevant information as shown. Meeting Days Number of class meetings Class Duration Weekly Duration 1 TR 2 75 150 2 TR 2 75 150 3 TR 2 75 150 4 TR 2 75 150 5 MWF 3 50 150 6 MWF 3 50 150 7 MWF 3 50 150 8 MWF 3 50 150 9 MWF 3 50 150 10 MWF 3 50 150 11 MTWRF 5 90 450 12 MTWRF 5 90 450 13 MTWRF 5 90 450

          Ch5p1 - The HR department needs a report to display employee information as shown from the job_history table. The months worked is the number of months from the start date to the end date. Only list those employees whose months worked is an even number. Order by months worked descending.

Employment history
Employee 102 worked 66 months in department 60
Employee 101 worked 52 months in department 110

Ch5p2 - Write a query that displays the employees whose last name contains the letter 'P' (Upper or lower case) and the date of the first Friday after their hire date. Format as shown. Sort the list by last name.

First Friday After Hire Date
1 Fripp**********17-APR-15
2 Partners********07-JAN-05
3 Pataballa*******12-FEB-16
4 Patel***********07-APR-06
5 Perkins*********21-DEC-07
6 Philtanker******09-FEB-18
7 Popp************14-DEC-07
8 Raphaely********14-DEC-12

Ch5p3 - Management wants a list of all employees with their base salary and overtime salary (this is time and a half of their base salary). Only display those employees whose phone number has area code that is entered by the user at run time. Sort by the base salary, and in case of a tie, then by last name. The following run is for area code 590:

Employee ID Employee Name Phone Base Salary Overtime Salary
1 107 Lorentz 590.423.5567 4200 6300
2 105 Austin 590.423.4569 4800 7200
3 106 Pataballa 590.423.4560 4800 7200
4 104 Ernst 590.423.4568 6000 9000
5 103 Hunold 590.423.4567 9000 13500

Here is another run with area code 603:

Employee ID Employee Name Phone Base Salary Overtime Salary
1 202 Fay 603.123.6666 6000 9000

Ch5p4 - Create a query to display the faculty's full name, formatted phone number, pin, and Multifactor Authentication Code. The MFA Code is calculated by adding up the digits in the pin. Order by the faculty last names.

Faculty Name Phone Number Pin Multifactor Authentication Code
1 John R. Blanchard (715) 555-9087 1075 13
2 Philip E. Brown (715) 555-6082 9899 35
3 Kim J. Cox (715) 555-1234 1181 11
4 Laura M. Sheng (715) 555-6409 1690 16
5 Jerry F. Williams (715) 555-5412 8531 17

Ch5p5 – Create a listing of the course sections with their relevant information as shown.

Meeting Days Number of class meetings Class Duration Weekly Duration
1 TR 2 75 150
2 TR 2 75 150
3 TR 2 75 150
4 TR 2 75 150
5 MWF 3 50 150
6 MWF 3 50 150
7 MWF 3 50 150
8 MWF 3 50 150
9 MWF 3 50 150
10 MWF 3 50 150
11 MTWRF 5 90 450
12 MTWRF 5 90 450
13 MTWRF 5 90 450

Ch5p1 - The HR department needs a report to display employee information as shown from the jobhistory table. The months worked is the number of months from the start date to the end date. Only list those employees whose months worked is an even number. Order by months worked descending.

Employment history
Employee 102 worked 66 months in department 60
Employee 101 worked 52 months in department 110

Ch5p2 - Write a query that displays the employees whose last name contains the letter 'P' (Upper or lower case) and the date of the first Friday after their hire date. Format as shown. Sort the list by last name.

First Friday After Hire Date
1 Fripp**********17-APR-15
2 Partners********07-JAN-05
3 Pataballa*******12-FEB-16
4 Patel***********07-APR-06
5 Perkins*********21-DEC-07
6 Philtanker******09-FEB-18
7 Popp************14-DEC-07
8 Raphaely********14-DEC-12

Ch5p3 - Management wants a list of all employees with their base salary and overtime salary (this is time and a half of their base salary). Only display those employees whose phone number has area code that is entered by the user at run time. Sort by the base salary, and in case of a tie, then by last name. The following run is for area code 590:

Employee ID Employee Name Phone Base Salary Overtime Salary
1 107 Lorentz 590.423.5567 4200 6300
2 105 Austin 590.423.4569 4800 7200
3 106 Pataballa 590.423.4560 4800 7200
4 104 Ernst 590.423.4568 6000 9000
5 103 Hunold 590.423.4567 9000 13500

Here is another run with area code 603:

Employee ID Employee Name Phone Base Salary Overtime Salary
1 202 Fay 603.123.6666 6000 9000

Ch5p4 - Create a query to display the faculty's full name, formatted phone number, pin, and Multifactor Authentication Code. The MFA Code is calculated by adding up the digits in the pin. Order by the faculty last names.

Faculty Name Phone Number Pin Multifactor Authentication Code
1 John R. Blanchard (715) 555-9087 1075 13
2 Philip E. Brown (715) 555-6082 9899 35
3 Kim J. Cox (715) 555-1234 1181 11
4 Laura M. Sheng (715) 555-6409 1690 16
5 Jerry F. Williams (715) 555-5412 8531 17

Ch5p5 – Create a listing of the course sections with their relevant information as shown.

Meeting Days Number of class meetings Class Duration Weekly Duration
1 TR 2 75 150
2 TR 2 75 150
3 TR 2 75 150
4 TR 2 75 150
5 MWF 3 50 150
6 MWF 3 50 150
7 MWF 3 50 150
8 MWF 3 50 150
9 MWF 3 50 150
10 MWF 3 50 150
11 MTWRF 5 90 450
12 MTWRF 5 90 450
13 MTWRF 5 90 450

Added by Silvia C.

Question

Please give Ace some feedback