CS444 - MP 3

1. Form a query that shows employee names, employee numbers, phone numbers, hiredates and salaries who were born after December 1, 1948.
2. Form a query that shows project numbers, project name, department and the number of months the project was active (prendate prstdate).
3. Form a query that shows employee numbers (from emp_act) and the amount of time (in total, field emptime from emp_act) each employee has spent on each project number.
4. Form a query that shows employee names (from employee) and the amount of time (in total, field emptime from emp_act) each employee has spent on each project number.
5. Form a query that shows employee names (from employee) and the amount of time (field emptime from emp_act) each employee has spent on each project showing project name.
6. Form a query that shows account numbers (actno from emp_act) and list the names of the employees charging time to each account.
7. Form a query that shows account numbers (actno from emp_act) and list the total time charged to each account.
8. Form a query that shows a list of employee names, phone numbers and the name of the department they work in.
9. Using the UPDATE statement, find all employees without a middle initial and add $100 to their bonus. Produce a table showing employee names and their total bonus values.
10. Form a query that shows all employee names and the names of their managers.

Hints

1. Note that all dates are not necessarily in Oracle date format. You may need to use the to_date function.
2. Use the on-line Oracle documentation (http://storage.cs.emporia.edu/doc/index.htm.) to find out how the update command works for question 9.
3. For question 10, you may have to join the table to itself.
4. Table descriptions are given below.

---

Table Field Descriptions

TABLE DEPART (The department table)
DEPTNO - The department number
DEPTNAME - The name of the department
MGRNO - The employee number of the manager of the department
ADMRDEPT - department administrative grouping

TABLE EMP (The employee table)
EMPNO - The employee number
FIRSTNME - Employee first name
MIDINIT - Employee middle initial
LASTNAME - Employww last name
WORKDEPT - the department the employee is assigned to
PHONENO - Employees phone extension
HIREDATE - the date the employee was hired on
JOB - Employee job title
EDLEVEL - Employee education level (in years)
SEX - Employees sex
BIRTHDATE - Employees birthdate
SALARY - The employees annual salary
BONUS - Employees most recent bonus payment
COMM - Employees most recent commision payment

TABLE PROJ (The project table)
PROJNO - Project number
PROJNAME - The project name
DEPTNO - Department in which project is performed
RESPEMP - Emplouee number for employee responsible for the project
PRSTAFF - Project staffing level
PRSTDATE - Project start date
PRENDATE - Project end date
MAJPROJ - Part of what other project

TABLE EMP_ACT (The employee activity table)
EMPNO - Employee number
PROJNO - Project number
ACTNO - Account number
EMPTIME - Employee time billed to project
EMSTDATE - Employee project start date
EMENDATE - Employee project end date