I need help with my homework. Create a Payroll table for the ORACOO Company that
ID: 3711561 • Letter: I
Question
I need help with my homework.
Create a Payroll table for the ORACOO Company that will contain the following field schema as of 10/31/2011, and populate the table You may name the table payroll.
Name
Emp_id
Dep
Hourly_Rate
BiWeekly_Cur_Hours
Hire_ Date
Manager
John Doe
007
X
54.56
135
2/3/09
Betty
Davis
005
Y
14.56
80
1/21/99
John Doe
Joe Smith
003
Z
14.56
80
1/11/05
John Doe
Frank
Coole
001
Z
13.30
82
3/14/05
Joe Smith
Thomas
Crown
143
S
12.40
84
09/25/07
Frank
Coole
Sam
Honan
730
R
11.11
80
4/5/88
Frank
Coole
Mary
Nam
006
R
43.43
46
1/6/11
Betty
Davis
Diane
Baker
004
R3
44.50
110
1/2/94
Sam
Honan
Your Name
111
S
15.00
80
Today’s date
John Doe
Run the following reports for Oraco Company. Include titles, Today’s date, page number, column headings/formats where applicable on the report. Check the "Designing a Report in Oracle" document in the Lecture Folder to help you with the code.
1>Creating a self join or a subquery (your choice):
Show which employees work for John Doe and Frank Coole.
2>Creating the bi-weekly Gross Pay payroll report for period ending 04/30/2016:
Show the following calculations on your report:
Gross Pay per Person-
Note-Gross Pay = (Hourly_Rate * Hours+OT) where OT is for hours over 40 and at 1.5 the normal rate [note: 80 hours is considered regular time for two weeks]
OT is to be shown as a separate column
Further note that Hourly_Rate column should be understood to have an implied decimal (of two decimal places to the left) of the field value put in for calculation purposes only!!!)
Format the Gross Pay column and OT column as currency style.
Further assume all employees are paid on a “by-weekly” basis, that is every 2 weeks.
3>Creating a Report of New Hires:
Include a report of all new hires (include any dates for the year 2005 and after)
Entitle the Report as “New Hires”
4>Create a report using Dates:
Find employees hired between 1995 January 1, and December 5th, 1999.
5>Create a report on departments:
Show the minimum salary per Department in descending Department order.
6>Create a report on departments:
Show a report that displays employees by Department. Break on the department column. Also display the total gross pay for ALL departments.
7>Create a report using Dates:
Find employees hired the last 500 days
8>Create a report off the payroll table
Show the employees who annual salaries are in the ranges of $20000 to $100,000. If the salary is for a manager display a message in a column stating “Management”.
9> Write the select statement to show the current day of the week given the date you finish the homework.
10> Create a view for Frank Coole and a view for John Doe. The view is to contain all data related to people who work for both managers.
Name
Emp_id
Dep
Hourly_Rate
BiWeekly_Cur_Hours
Hire_ Date
Manager
John Doe
007
X
54.56
135
2/3/09
Betty
Davis
005
Y
14.56
80
1/21/99
John Doe
Joe Smith
003
Z
14.56
80
1/11/05
John Doe
Frank
Coole
001
Z
13.30
82
3/14/05
Joe Smith
Thomas
Crown
143
S
12.40
84
09/25/07
Frank
Coole
Sam
Honan
730
R
11.11
80
4/5/88
Frank
Coole
Mary
Nam
006
R
43.43
46
1/6/11
Betty
Davis
Diane
Baker
004
R3
44.50
110
1/2/94
Sam
Honan
Your Name
111
S
15.00
80
Today’s date
John Doe
Explanation / Answer
1.Self JOIN:-
SELECT e.name, m.name as manager
FROM
Payroll e, Payroll m
WHERE e.managerId = m.empId
SELECT p1.Name AS EMPNAME, p2.name AS ManagerName
FROM Payroll p1
INNER JOIN Payroll p2
ON P1.ManagerId= P2.EmpID AND (p2.MANAGER_NAME='John Doe' OR p2.MANAGER_NAME='Frank Coole'
---------------------------------------------------------------------------------------------------------------------------------------------------------
3. Report of New Hires for the year 2005 and after
(SELECT Name,Emp_id,Dep "Depatment", Hourly_Rate,BiWeekly_Cur_Hours,Hire_ Date "NEW_HIRE",Manager
FROM payroll where Hire_ Date >= '01/01/05' order by Hire_Date) // here we can also use TO_DATE() function to set the date format
---------------------------------------------------------------------------------------------------------------------------------------------------------
4.employees hired between 1995 January 1, and December 5th, 1999:-
SELECT Name,Emp_id,Dep "Depatment", Hourly_Rate,BiWeekly_Cur_Hours,Hire_ Date,Manager
FROM payroll where hire_date between to_date('01/01/95', 'mm/dd/yy') AND to_date('12/05/99', 'mm/dd/yy');
---------------------------------------------------------------------------------------------------------------------------------------------------------
5.minimum salary per Department in descending Department order
SELECT Dep, MIN((Hourly_Rate * BiWeekly_Cur_Hours)) "Salary" from Payroll group by Dep OREDR BY Dep DESC;
---------------------------------------------------------------------------------------------------------------------------------------------------------
6.report that displays employees by Department total gross pay for ALL departments
SELECT count(NAME) "No Of EMP" FORM Payroll group by Dep;
SELECT SUM(Hourly_Rate * BiWeekly_Cur_Hours) "Total Gross Pay" FROM Payroll ;
----------------------------------------------------------------------------------------------------------------------
employees hired the last 500 days
SELECT NAME "empname" from Payroll where (to_date(sysdate,'mm/dd/yy')-Hire_date)<500;
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.