assignment #1 SOLUTION

Part II (60 points)




Your SQL statements can only reference the DEPARTMENT table and/or EMPLOYEE table. You are not allowed to create/access other tables/views. You will receive 0 points if you access different tables or columns (e.g., dept, emp, job, sal, deptid …).




1) (CSC 352 - 20 points | CSC 452 – 15 points)




Write a SQL SELECT statement to display the employee ID, name, job title, and hire date for all employees along with their managers’ names, job titles, and hire dates. Make sure that employees without managers are included as well.




If an employee does not have a manager, the manager’s name is shown as “------”, the manager’s job title is shown as “------”, and the manager’s hire date is shown as 31-DEC-9999 in your output.
The hire date must be displayed in the DD-MON-YYYY format (4-digit year).
You cannot use hard-coded employee names (e.g., WHERE employee_name = 'KING') in your SELECT statement.
Sort your output in ascending order by employee ID.
You will receive 0 points if you submit more than one SELECT statement.



Hints: 1) You may need to use an OUTER JOIN and a SELF-JOIN.

2) NVL(TO_CHAR(column_x, 'DD-MON-YYYY'), '31-DEC-9999')




Your statement’s output should match the following format:




EMPLOYEE ID EMPLOYEE NAME EMPLOYEE JOB TITLE EMPLOYEE HIRE DATE MANAGER NAME MANAGER JOB TITLE MANAGER HIRE DATE

----------- -------------------- -------------------------------------------------- ------------------ -------------------- -------------------------------------------------- -----------------

7566 JONES CHIEF ACCOUNTANT 05-APR-2001 JOST VICE PRESIDENT 04-MAY-2001

7596 JOST VICE PRESIDENT 04-MAY-2001 KING PRESIDENT 20-NOV-2001

7603 CLARK VICE PRESIDENT 12-JUN-2001 KING PRESIDENT 20-NOV-2001

7610 WILSON BUSINESS ANALYST 03-DEC-2001 JOST VICE PRESIDENT 04-MAY-2001

7788 SCOTT PROGRAMMER 15-JAN-2003 SMITH DATABASE ADMINISTRATOR 20-DEC-2001

7839 KING PRESIDENT 20-NOV-2001 ------ ------ 31-DEC-9999

……




(This question should take less than 20 minutes to solve.)

2) (CSC 352 - 20 points | CSC 452 – 15 points)




Write a SQL SELECT statement to display 1) all department names, 2) the maximum total pay (salary + commission) for each department, 3) the minimum hire date for each department, and 4) the total number of employees in each department.

Any employee who does not belong to any department is excluded from your output.
You must display the maximum total pay with a dollar ($) sign, a comma, and two decimal places (e.g., $1,234.56). If a department does not have any employee, the maximum total pay is shown as $0.00.
The minimum hire date must be displayed in the DD-MON-YYYY format (4-digit year). If a department does not have any employee, the minimum hire date is shown as 31-DEC-9999.
Sort your output in ascending order by department name.
You will receive 0 points if you submit more than one SELECT statement.



Hint: You may need to use an OUTER JOIN.




Your statement’s output should match the following format:




DEPARTMENT NAME MAXIMUM TOTAL PAY MINIMUM HIRE DATE TOTAL NUMBER OF EMPLOYEES

-------------------- ----------------- ----------------- -------------------------

ACCOUNTING $3,000.00 05-APR-2001 2

EXECUTIVE $5,000.00 04-MAY-2001 3

IT $2,900.00 20-DEC-2001 4

MARKETING $0.00 31-DEC-9999 0

RESEARCH $3,000.00 03-DEC-2001 3

SALES $3,500.00 06-DEC-2001 3




(This question should take less than 20 minutes to solve.)

3) (CSC 352 - 20 points | CSC 452 – 15 points)




Write a SQL SELECT statement to find out the most recently hired employees in each department. Your SELECT statement must display the department ID, department name, employee ID, employee name, job title, and hire date. Any employee who does not belong to any department is excluded from your output.




The hire date must be displayed in the DD-MON-YYYY format (4-digit year).
You cannot use hard-coded values (e.g., WHERE department_id = 10) in your SELECT statement.
Sort your output in ascending order by department name and then employee name.
You will receive 0 points if you submit more than one SELECT statement.



Hint: A subquery may be needed in your SELECT statement (The examples on pages 27-28 may help

you).

 

Your statement’s output should match the following format:




DEPARTMENT ID DEPARTMENT NAME EMPLOYEE ID EMPLOYEE NAME JOB TITLE HIRE DATE

------------- -------------------- ----------- -------------------- -------------------------------------------------- -----------

10 ACCOUNTING 7886 STEEL PUBLIC ACCOUNTANT 08-MAR-2003

……




(This question should take less than 20 minutes to solve.)

4) (CSC 452 only – 15 points)




Write a SQL SELECT statement to display the name and location of all departments (except the departments located in Dallas) with the highest number of employees.




You cannot use join operations in your SELECT statement.
Hard coding, except the string 'DALLAS', is not allowed in your SELECT statement.
Sort your output in ascending order by department name.
You will receive 0 points if you submit more than one SELECT statement.



Hint:

 

Department Name
Location
# of Employees
Meet the search criteria?
ACCOUNTING
NEW YORK
2
No
EXECUTIVE
NEW YORK
3
Yes
IT
DALLAS
4
No
MARKETING
CHICAGO
0
No
RESEARCH
DALLAS
3
No
SALES
CHICAGO
3
Yes



Your statement’s output should match the following format:







DEPARTMENT NAME LOCATION

-------------------- --------------------

EXECUTIVE NEW YORK

……




(This question should take less than 30 minutes to solve.)

Please submit a text file (your_name_hw1.txt) containing all the source codes (Part II) to D2L by the due date.




Example: your_name_hw1.txt

================

Part II

================

1)

SELECT … FROM …;




2)

SELECT … FROM …;

……