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:

 

 







Calculate Your Essay Price
(550 words)

Approximate price: $22

Calculate the price of your order

550 words
We'll send you the first draft for approval by September 11, 2018 at 10:52 AM
Total price:
$26
The price is based on these factors:
Academic level
Number of pages
Urgency
Basic features
  • Free title page and bibliography
  • Unlimited revisions
  • Plagiarism-free guarantee
  • Money-back guarantee
  • 24/7 support
On-demand options
  • Writer’s samples
  • Part-by-part delivery
  • Overnight delivery
  • Copies of used sources
  • Expert Proofreading
Paper format
  • 275 words per page
  • 12 pt Arial/Times New Roman
  • Double line spacing
  • Any citation style (APA, MLA, Chicago/Turabian, Harvard)

Our guarantees

Delivering a high-quality product at a reasonable price is not enough anymore.
That’s why we have developed 5 beneficial guarantees that will make your experience with our service enjoyable, easy, and safe.

Money-back guarantee

You have to be 100% sure of the quality of your product to give a money-back guarantee. This describes us perfectly. Make sure that this guarantee is totally transparent.

Read more

Zero-plagiarism guarantee

Each paper is composed from scratch, according to your instructions. It is then checked by our plagiarism-detection software. There is no gap where plagiarism could squeeze in.

Read more

Free-revision policy

Thanks to our free revisions, there is no way for you to be unsatisfied. We will work on your paper until you are completely happy with the result.

Read more

Privacy policy

Your email is safe, as we store it according to international data protection rules. Your bank details are secure, as we use only reliable payment systems.

Read more

Fair-cooperation guarantee

By sending us your money, you buy the service we provide. Check out our terms and conditions if you prefer business talks to be laid out in official language.

Read more

Order your essay today and save 10% with the coupon code: best10