USA: +1(669)289-8683 
Sign in
All instructions are in the attached file ..
Assignment 3
Total : 60 marks.
The results are based on the script that is provided on the blackboard. You can do the Assignment in a group of 3 max members.
Q. Use queries to solve following according to expected output given to you:
a. Create a report that displays all employees, and indicate with the words yes or no whether they receive a commission. Use the DECODE expression in your query. (5 marks)
LAST_NAME
SALARY
COMMISSION
Ayesha
120000
no
Manzer
12000
no
King
24000
no
Kochhar
17000
no
De Haan
17000
no
Hunold
9000
no
Ernst
6000
no
Lorentz
4200
no
Mourgos
5800
no
Rajs
3500
no
Davies
3100
no
Matos
2600
no
Vargas
2500
no
Zlotkey
10500
yes
Abel
11000
yes
Taylor
8600
yes
Grant
7000
yes
Whalen
4400
no
Hartstein
13000
no
Fay
6000
no
Higgins
12000
no
Gietz
8300
no
b. Show all employees who were hired in the first half of the month (before the 16th of the month) (5 marks)
LAST_NAME
HIRE_DATE
Manzer
02-OCT-08
De Haan
13-JAN-93
Hunold
03-JAN-90
Lorentz
07-FEB-99
Matos
15-MAR-98
Vargas
09-JUL-98
Abel
11-MAY-96
Higgins
07-JUN-94
Gietz
07-JUN-94
c. Create a report that displays the employee_number, last_name, salary, department_number, and the average salary in their department for all employees. (5 marks)
EMPLOYEE_ID
LAST_NAME
DEPARTMENT_ID
AVG(S.SALARY)
113
Manzer
70
12000
101
Kochhar
90
19333.3333
103
Hunold
60
6400
174
Abel
80
9600
202
Fay
20
9500
141
Rajs
50
22916.6667
102
De Haan
90
19333.3333
100
King
90
19333.3333
200
Whalen
10
4400
205
Higgins
110
12000
144
Vargas
50
22916.6667
142
Davies
50
22916.6667
124
Mourgos
50
22916.6667
149
Zlotkey
80
9600
77777
Ayesha
50
22916.6667
176
Taylor
80
9600
143
Matos
50
22916.6667
104
Ernst
60
6400
107
Lorentz
60
6400
206
Gietz
80
9600
201
Hartstein
20
9500
d. The HR department wants to determine the names of all employees hired after Davies. Create a query to display the name and hire date of any employee hired after employee Davies. (5 marks)
LAST_NAME
HIRE_DATE
HArris
29-NOV-08
HArris
29-NOV-08
oiu
01-DEC-08
Lorentz
07-FEB-99
Mourgos
16-NOV-99
Matos
15-MAR-98
Vargas
09-JUL-98
Zlotkey
29-JAN-00
Taylor
24-MAR-98
Grant
24-MAY-99
Cores
06-NOV-08
Fay
17-AUG-97
HArris
29-NOV-08
HArris
29-NOV-08
14 rows selected.
e. The HR department needs to find the names and hire dates for all employees who were hired before their managers, along with their manager’s names and hire dates. Label the columns Employee, Emp hired, Manager, and Mgr hired, respectively. (5 marks)
LAST_NAME
HIRE_DATE
LAST_NAME
HIRE_DATE
Hunold
03-JAN-90
De Haan
13-JAN-93
Rajs
17-OCT-95
Mourgos
16-NOV-99
Davies
29-JAN-97
Mourgos
16-NOV-99
Matos
15-MAR-98
Mourgos
16-NOV-99
Vargas
09-JUL-98
Mourgos
16-NOV-99
Abel
11-MAY-96
Zlotkey
29-JAN-00
Taylor
24-MAR-98
Zlotkey
29-JAN-00
Grant
24-MAY-99
Zlotkey
29-JAN-00
8 rows selected.
f. The Accounting department requires an analysis on maximum and minimum salaries by job, manager, and department. They have asked you to do the following: (5 marks)
Write a query to display the following groupings:
· Department_id, job_id
· Job_id, manager_id
JOB_ID
MANAGER_ID
DEPARTMENT_ID
MAX(SALARY)
MIN(SALARY)
AC_MGR
101
12000
12000
ST_MAN
100
5800
5800
IT_Prog
10800
10800
MK_MAN
100
13000
13000
AD_PRES
24000
24000
IT_PROG
102
9000
9000
IT_PROG
103
6000
4200
AC_ACCOUNT
205
8300
8300
SA_REP
100
1000
1000
SA_REP
149
11000
1000
149
5487
5487
AD_VP
100
17000
17000
ST_CLERK
124
5100
4100
SA_MAN
100
10500
10500
MK_REP
201
6000
6000
60
5487
5487
AD_VP
90
17000
17000
ST_CLERK
50
5100
4100
SA_REP
80
11000
8600
IT_Prog
10800
10800
SA_REP
60
1000
1000
ST_MAN
50
5800
5800
SA_MAN
80
10500
10500
AC_MGR
110
12000
12000
g. The HR department wants a list of employees who are up for review in January; so they have requested you to do the following: (5 marks)
Write a query to display the last names, month of the date of hire, and hire date of those employees who have been hired in the month of January, irrespective of the year of hire.
LAST_NAME
EXTRACT(MONTHFROMHIRE_DATE)
HIRE_DATE
De Haan
1
13-JAN-93
Hunold
1
03-JAN-90
Davies
1
29-JAN-97
Zlotkey
1
29-JAN-00
h. The CEO needs a report on the top three earners in the company for profit sharing. He has asked you to provide him with a list. (5 marks)
Write a query to display top three earners in the Employees table. Display their last names and salaries.
LAST_NAME
SALARY
King
24000
Kochhar
17000
De Haan
17000
i. The benefits for the state of California have been changed based on a local ordinance. So the benefits representative has asked you to compile a list of the people who are affected. Write a query to display the employee id and last name of the employees who work in the state of California. Hint: Use Scalar Subqueries. (5 marks)
EMPLOYEE_ID
LAST_NAME
124
Mourgos
141
Rajs
142
Davies
143
Matos
144
Vargas
j.Show those employees who have a last name starting with letters j, K, L or M. (5 marks)
LAST_NAME
King
Kochhar
Lorentz
Matos
Mourgos
k. Find the number of employees who have a last name that ends with the letter n. Create two possible solutions. (5 marks)
COUNT(*)
2
l. Create a report that shows name, location, and number of employees for each department. Make sure that the report also includes departments without employees. (5 marks)
DEPARTMENT_ID
DEPARTMENT_NAME
LOCATION_ID
COUNT(E.EMPLOYEE_ID)
80
Sales
2500
4
110
Accounting
1700
1
120
support
2500
0
60
IT
1400
8
10
Administration
1700
0
200
support
2500
0
90
Executive
1700
3
20
Marketing
1800
2
70
public relations
1700
0
50
Shipping
1500
5
10 rows selected.
