CSC 352 / 452: DATABASE PROGRAMMING ASSIGNMENT #1

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: