Question DetailsNormal
$ 18.00
CSC 352 / 452 ASSIGNMENT #2 | Complete Solution
Question posted by

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

Due on Tuesday, 1/19/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.

•    If you modified the DEPARTMENT and EMPLOYEE tables created in Assignment #1, you need to delete and re-populate them.
•    Do not try to use complicated queries (e.g., joins) to get the results.  You can use multiple PL/SQL statements to get the results easily.
•    You cannot use hard-coded values (e.g., IF employee_id = 7839 THEN ……) in your programs.
•    You are not allowed to create temporary tables, views, functions, or procedures.
•    Explicit cursors are NOT allowed in your programs.
•    The EXCEPTION Section is NOT allowed in your programs.
•    Please review your assignment file before submitting it to make sure you have the correct one. It is your responsibility to ensure that you upload the correct assignment file.
1) (CSC 352 - 30 points | CSC 452 – 20 points)

Based on the DEPARTMENT and EMPLOYEE tables created in Assignment #1, write a PL/SQL anonymous block that accepts an employee ID from the user input and displays 1) the employee’s name, hire date, and total pay (salary + commission), 2) his/her manager’s name, hire date, and total pay (salary + commission), 3) the name of the department where the employee works, and 4) the maximum total pay (salary + commission) for the department where the employee works.

•    Submitting more than one PL/SQL program will receive 0 points.
•    If the employee ID from the user input is not in the EMPLOYEE table (EMPLOYEE_ID), you display a message telling the user that the employee ID is not in the table.
•    You must display the total pay with a dollar ($) sign, a comma, and two decimal places (e.g., $1,234.56).
•    The hire date must be displayed in the mm/dd/yyyy format (4-digit year).
•    If the employee does not have a manager, the manager’s name, hire date and total pay must be shown as “N/A”.
•    If the employee does not belong to any department, the department name must be shown as “N/A”, and the maximum total pay (salary + commission) for the department must be shown as “0”.
•    You will lose 5 points if your output does not have “User Input:”, “My Output:”, “----- Employee -----”, “----- Manager -----”, or “----- Department -----”.
•    To avoid complicating issues, you can assume that the user always enters input from keyboard that consists only of the digits 0 through 9 and Enter.

Example:

ACCEPT p_1 PROMPT 'Please enter the employee ID:'
DECLARE
 -- Your statements

BEGIN
     -- Your statements
END;

Please test your program. The output of your program must match the following:

Case 1)

 

Output:

 

Case 2)

 

Output:

 

Case 3)

 

Output:

 

2) (CSC 352 - 30 points | CSC 452 – 25 points)

Based on the DEPARTMENT and EMPLOYEE tables created in Assignment #1, write a PL/SQL anonymous block that accepts a department name from the user input and displays a) the department ID of that department, b) the address of that department, c) the number of employees working in that department, d) the average salary for that department, e) the number of employees in that department whose salary is higher than the average salary for that department, and f) the number of employees in that department whose salary is higher than the average salary of all employees in the company.

•    Submitting more than one PL/SQL program will receive 0 points.
•    If the department name from the user input is not in the DEPARTMENT table (DEPARTMENT_NAME), you display a message telling the user that the department name is not in the table.
•    Department name is not case sensitive (e.g., SALES = Sales).  You will lose 5 points if you do not use the UPPER (or LOWER) function in your program.
•    You will lose 5 points if your output does not have “User Input:” or “My Output:”.

Please test your program. The output of your program must match the following:

Case 1)

 


Output:

 

Case 2)

 

Output:

 

Case 3)

 

Output:

 

 

3) (CSC 452 only - 15 points)

Based on the EMPLOYEE table created in Assignment #1, write a PL/SQL anonymous block that displays the number of employees earned salaries in each of the following ranges: $0.00 - $499.99, $500.00 - $999.99, …, $4500.00 - $4999.99, $5000.00 - $5499.99. You can only use ONE SELECT-INTO statement in your program.

•    Submitting more than one PL/SQL program will receive 0 points.
•    You will lose 10 points if more than one SELECT-INTO statement is used.

Hint:     FOR idx IN 0..10 LOOP … SELECT … INTO … FROM …; … END LOOP;

Creating cleanly formatted output is a common programming requirement. The format of your output must match mine EXACTLY. If your output does not match mine EXACTLY, you will lose some points.

Please test your program. The output of your program must match the following:

 

Please submit a text file containing all the source codes to D2L before or on due date.

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).

In a bug tracking database, there is a table called BUGS. The table has several columns: BUG_ID, REPORTED_DATE, DESCRIPTION, PRIORITY, ASSIGNED_TO, CLOSED_DATE, and NOTE.

Create and populate the BUGS table.

CREATE TABLE bugs
(
    BUG_ID          NUMBER PRIMARY KEY,
    REPORTED_DATE        DATE NOT NULL,
    DESCRIPTION        VARCHAR2(20),
    PRIORITY           NUMBER(2),
    ASSIGNED_TO        VARCHAR2(10),
    CLOSED_DATE        DATE,
       NOTE            VARCHAR2(20)
);

INSERT INTO BUGS VALUES (1230, '25-APR-14', NULL, 3, 'Team 3', '28-APR-14', NULL);
INSERT INTO BUGS VALUES (1231, '29-APR-14', NULL, 1, 'Team 1', '29-APR-14', NULL);
INSERT INTO BUGS VALUES (1232, '03-MAY-14', NULL, 1, 'Team 1', '03-MAY-14', NULL);
INSERT INTO BUGS VALUES (1233, '03-MAY-14', NULL, 1, 'Team 3', '08-MAY-14', NULL);
INSERT INTO BUGS VALUES (1234, '04-MAY-14', NULL, 2, 'Team 5', '15-MAY-14', NULL);
INSERT INTO BUGS VALUES (1235, '04-MAY-14', NULL, 2, 'Team 1',  NULL,       NULL);
INSERT INTO BUGS VALUES (1236, '05-MAY-14', NULL, 1, 'Team 2', '06-MAY-14', NULL);
INSERT INTO BUGS VALUES (1237, '05-MAY-14', NULL, 3, 'Team 3', '10-MAY-14', NULL);
INSERT INTO BUGS VALUES (1238, '09-MAY-14', NULL, 4, 'Team 5', '16-MAY-14', NULL);
INSERT INTO BUGS VALUES (1239, '09-MAY-14', NULL, 5, 'Team 6',  NULL,       NULL);
INSERT INTO BUGS VALUES (1240, '12-MAY-14', NULL, 5, 'Team 2', '30-MAY-14', NULL);
INSERT INTO BUGS VALUES (1241, '12-MAY-14', NULL, 1, 'Team 1', '20-MAY-14', NULL);
INSERT INTO BUGS VALUES (1242, '13-MAY-14', NULL, 4, 'Team 4', '25-MAY-14', NULL);
INSERT INTO BUGS VALUES (1243, '14-MAY-14', NULL, 4, 'Team 3', '01-JUN-14', NULL);
INSERT INTO BUGS VALUES (1244, '14-MAY-14', NULL, 2, 'Team 4', '25-MAY-14', NULL);
INSERT INTO BUGS VALUES (1245, '20-MAY-14', NULL, 2, 'Team 4',  NULL,       NULL);
INSERT INTO BUGS VALUES (1246, '22-MAY-14', NULL, 2, 'Team 4', '25-MAY-14', NULL);
INSERT INTO BUGS VALUES (1247, '25-MAY-14', NULL, 2, 'Team 1', '29-MAY-14', NULL);
INSERT INTO BUGS VALUES (1248, '30-MAY-14', NULL, 1, 'Team 1', '01-JUN-14', NULL);
INSERT INTO BUGS VALUES (1249, '05-JUN-14', NULL, 1, 'Team 2', '07-JUN-14', NULL);
COMMIT;

 “Open Bugs” - A bug is considered open on a given day if (1) its “REPORTED_DATE” is on or before that day, and (2) its “CLOSED_DATE” is on or after that day (or is unknown (NULL)). For example, we have 5 open bugs on 5/5/2014.

Write a PL/SQL anonymous block that generates a report to show the number of open bugs from 5/1/2014 through 5/31/2014. At the end of the report, the maximum number of open bugs on a single day is displayed. Assume that there were no open bugs on 4/30/2014.

The output of your program should match the following:

  Date      Number of Open Bugs
01-MAY-14            0
02-MAY-14            0
03-MAY-14            2
04-MAY-14            3
05-MAY-14            5
06-MAY-14            5
07-MAY-14            4
08-MAY-14            4
09-MAY-14            5
10-MAY-14            5
11-MAY-14            4
12-MAY-14            6
13-MAY-14            7
14-MAY-14            9
15-MAY-14            9
16-MAY-14            8
17-MAY-14            7
18-MAY-14            7
19-MAY-14            7
20-MAY-14            8
21-MAY-14            7
22-MAY-14            8
23-MAY-14            8
24-MAY-14            8
25-MAY-14            9
26-MAY-14            6
27-MAY-14            6
28-MAY-14            6
29-MAY-14            6
30-MAY-14            6
31-MAY-14            5
---------------------------------------
The maximum number of open bugs on a single day is 9.
There were 9 open bugs on 14-MAY-14.
There were 9 open bugs on 15-MAY-14.
There were 9 open bugs on 25-MAY-14.

Available Solution
$ 18.00
CSC 352 / 452 ASSIGNMENT #2 | Complete Solution
  • This Solution has been Purchased 4 time
  • Average Rating for this solution is A+
  • Submitted On 24 Jan, 2016 05:31:12
Solution posted by
DECLARE EmpName varchar2(30); hire varchar2(40); pay...
Buy now to view full solution.


Other Related Solutions

$ 629.35