CSC 352 / 452: DATABASE PROGRAMMING
ASSIGNMENT #1 (60 POINTS)

Due on Tuesday, 1/12/2016 at 11:59PM

Unless prior arrangements are made, homework turned in late but within 24 hours of the due time will be graded at 75% credit, homework turned in between 24 and 48 hours will be graded at 50% credit, and homework turned in later than 48 hours will not be accepted.

Please note that only TEXT files will be accepted. All other file types (e.g., DOC, DOCX, RTF, PDF, JPG, or ZIP) will be rejected. In D2L, only the most recent submission is kept.

Part I (No need to submit)

1) Download and install Oracle SQL Developer on your machine.
2) Create a connection from your SQL Developer to our database server.
3) Create table DEPARTMENT as described below.

CREATE TABLE department
( DEPARTMENT_ID NUMBER(4) PRIMARY KEY,
DEPARTMENT_NAME VARCHAR2(20) NOT NULL UNIQUE,
ADDRESS VARCHAR2(20) NOT NULL);

4) Populate the DEPARTMENT table.

INSERT INTO department VALUES(10, ‘ACCOUNTING’, ‘NEW YORK’);
INSERT INTO department VALUES(20, ‘RESEARCH’, ‘DALLAS’);
INSERT INTO department VALUES(30, ‘SALES’, ‘CHICAGO’);
INSERT INTO department VALUES(40, ‘IT’, ‘DALLAS’);
INSERT INTO department VALUES(50, ‘EXECUTIVE’, ‘NEW YORK’);
INSERT INTO department VALUES(60, ‘MARKETING’, ‘CHICAGO’);
COMMIT;

5) Create table EMPLOYEE as described below.

CREATE TABLE employee
( EMPLOYEE_ID NUMBER(4) PRIMARY KEY,
EMPLOYEE_NAME VARCHAR2(20) NOT NULL,
JOB VARCHAR2(50) NOT NULL,
MANAGER_ID NUMBER(4),
HIRE_DATE DATE NOT NULL,
SALARY NUMBER(9, 2) NOT NULL,
COMMISSION NUMBER(9, 2),
DEPARTMENT_ID NUMBER(4) REFERENCES department(DEPARTMENT_ID));

6) Populate the EMPLOYEE table.

INSERT INTO employee
VALUES(7839, ‘KING’, ‘PRESIDENT’, NULL, ’20-NOV-01′, 5000, NULL, 50);
INSERT INTO employee
VALUES(7596, ‘JOST’, ‘VICE PRESIDENT’, 7839, ’04-MAY-01′, 4500, NULL, 50);
INSERT INTO employee
VALUES(7603, ‘CLARK’, ‘VICE PRESIDENT’, 7839, ’12-JUN-01′, 4000, NULL, 50);
INSERT INTO employee
VALUES(7566, ‘JONES’, ‘PUBLIC ACCOUNTANT’, 7596, ’05-APR-01′, 3000, NULL, 10);
INSERT INTO employee
VALUES(7886, ‘STEEL’, ‘PUBLIC ACCOUNTANT’, 7566, ’08-MAR-03′, 2500, NULL, 10);
INSERT INTO employee
VALUES(7610, ‘WILSON’, ‘ANALYST’, 7596, ’03-DEC-01′, 3000, NULL, 20);
INSERT INTO employee
VALUES(7999, ‘WOLFE’, ‘ANALYST’, 7610, ’15-FEB-02′, 2500, NULL, 20);
INSERT INTO employee
VALUES(7944, ‘LEE’, ‘ANALYST’, 7610, ’04-SEP-06′, 2400, NULL, 20);
INSERT INTO employee
VALUES(7900, ‘FISHER’, ‘SALESMAN’, 7603, ’06-DEC-01′, 3000, 500, 30);
INSERT INTO employee
VALUES(7921, ‘JACKSON’, ‘SALESMAN’, 7900, ’25-FEB-05′, 2500, 400, 30);
INSERT INTO employee
VALUES(7952, ‘LANCASTER’, ‘SALESMAN’, 7900, ’06-DEC-06′, 2000, 150, 30);
INSERT INTO employee
VALUES(7910, ‘SMITH’, ‘DATABASE ADMINISTRATOR’, 7596, ’20-DEC-01′, 2900, NULL, 40);
INSERT INTO employee
VALUES(7788, ‘SCOTT’, ‘PROGRAMMER’, 7910, ’15-JAN-03′, 2500, NULL, 40);
INSERT INTO employee
VALUES(7876, ‘ADAMS’, ‘PROGRAMMER’, 7910, ’15-JAN-03′, 2000, NULL, 40);
INSERT INTO employee
VALUES(7934, ‘MILLER’,’PROGRAMMER’, 7876, ’25-JAN-02′, 1000, NULL, 40);
INSERT INTO employee
VALUES(8000, ‘BREWSTER’, ‘TBA’, NULL, ’22-AUG-13′, 2500, NULL, NULL);
COMMIT;

Part II(60 points)

Your SQL statements can only reference the DEPARTMENTtable and/or EMPLOYEE table. You are not allowed to create/access other tables/views.

1) (CSC 352 – 20 points | CSC 452 – 10 points)

Write a SQL SELECT statement to display all jobs,the maximum salary for each job, the maximumtotal pay (salary + commission) for each job, and the total number of employees in each job. You must display the maximumsalary and total pay with a dollar ($) sign, a comma, and two decimal places (e.g., $1,234.56).(You will lose 5 points if you fail to do so.) Sort your outputin ascending orderby job. (Submitting more than one SQL statement will receive 0 points.)

Your statement’s output should match the following format:

JOB MAXIMUM SALARY MAXIMUM TOTAL PAY TOTAL NUMBER OF EMPLOYEES
————————————————– ————– —————– ————————-
ANALYST $3,000.00 $3,000.00 3
……

2) (CSC 352 – 20 points | CSC 452 – 10 points)

Write a SQL SELECT statement to displayemployee ID, name, job, and hire date for all employees along with their managers’ namesand 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 “——” and the manager’s hire date is shown as 01-JAN-3000in your output. The hire date must be displayed in the DD-MON-YYYY format (4-digit year). Sort your outputin ascending order by employee name.(Submitting more than one SQL statement will receive 0 points.)

Hints: 1) You may need to use an OUTER JOINand aSELF-JOIN.
2) NVL(TO_CHAR(column_x, ‘DD-MON-YYYY’), ’01-JAN-3000′)

You cannot use hard-coded employee names (e.g., WHERE employee_name = ‘KING’) in your programs.

Your statement’s output should match the following format:

EMPLOYEE ID EMPLOYEE NAME EMPLOYEE JOB EMPLOYEE HIRE DATE MANAGER NAME MANAGER HIRE DATE
———– ——————– ————————————————– —————— ——————– —————–
7876 ADAMS PROGRAMMER 15-JAN-2003 SMITH 20-DEC-2001
8000 BREWSTER TBA 22-AUG-2013 —— 01-JAN-3000
……

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

Write aSQL SELECT statement to find out the most recently hired employees in each department.YourSELECT statement must display the department ID, department name, employee ID, employee name, job, and hire date.Any employeewho does not belong to any department is excluded from your output.Sort your outputin ascending order by department name and then employee name. (Submitting more than one SQL statement will receive 0 points.)

Hint: A subquery may be needed in your SELECT statement.

Your statement’s output should match the following format:

DEPARTMENT ID DEPARTMENT NAME EMPLOYEE ID EMPLOYEE NAME JOB HIRE_DATE
————- ——————– ———– ——————– ————————————————– ———
10 ACCOUNTING 7886 STEEL PUBLIC ACCOUNTANT 08-MAR-03
……

4) (CSC 452 only – 20 points)

Write a SQL SELECT statement to displaythe name and address of all departments(except the departments in Dallas) having maximum number of employees.Sort your output in ascending order by department name. (Submitting more than one SQL statement will receive 0 points.)

Hard coding, except the string ‘DALLAS’, is not allowed in your program.

Hints: Subqueries should be used. No join operation is needed.

DEPARTMENT NAME ADDRESS NUMBER OF EMPLOYEES
ACCOUNTING NEW YORK 2
EXECUTIVE NEW YORK 3
IT DALLAS 4
MARKETING CHICAGO 0
RESEARCH DALLAS 3
SALES CHICAGO 3

Your statement’s output should match the following format:

DEPARTMENT_NAME ADDRESS
——————– ——————–
EXECUTIVE NEW YORK
……

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

Example: your_name_hw1.txt
================
Part II
================
1)
SELECT … FROM…;

2)
SELECT … FROM …;
……

Optional Question

Just for fun (no credit, no extra credit, no need to submit, just for if you are a curious person and like database programming).

CREATE TABLE t1
(
ENAME VARCHAR2(10),
SALARY NUMBER(7, 2)
);
/
INSERT INTO t1VALUES(‘BLAKE’, 2850.90);
INSERT INTO t1VALUES(‘CLARK’, 2499.00);
INSERT INTO t1VALUES(‘KING’, 5049.25);
INSERT INTO t1VALUES(‘MILLER’, 0.0);
INSERT INTO t1VALUES(‘SMITH’, NULL);
COMMIT;

Writea SQL SELECT statement to convert numbers to their words equivalent.

SELECT …
FROM t1

ORDER BY ename;

The output of your statementmust match the following: