ComputerScienceExpert
$18/per page/
Question 3
2 tables
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) Please make sure that there are 6 rows in your DEPARTMENT table.
SELECT * FROM department;
6) 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));
7) 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;
Please test your program. The output of your program must match the following: $0.00 - $499.99：
CSC 352 / 452: DATABASE PROGRAMMINGASSIGNMENT #2 (60 POINTS)CSC 352/452-501 (Classroom Students):Due on Tuesday, 7/26/2016 at 11:59PMCSC 352/452-510 (Online Students):Due on Wednesday, 7/27/2016 at 11:59PMUnless prior arrangements are made, homework turned in late but within 24 hours of the due time will begraded at 75% credit, homework turned in between 24 and 48 hours will be graded at 50% credit, andhomework 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 theDEPARTMENTandEMPLOYEEtables created in Assignment #1, you need todelete and re-populate them.Do not try to use complicated joins to get the results.You can use multiple PL/SQL statementsto get the results easily.You cannot use hard-coded values (e.g.,IFemployee_id = 7839THEN……) in your programs.You are not allowed to create temporary tables, views, functions, or procedures.Explicit cursors areNOTallowed in your programs.TheEXCEPTIONSection isNOTallowed in your programs.Please review your assignment file before submitting it to make sure you have the correct one. Itis your responsibility to ensure that you upload the correct assignment file.1) (CSC 352 - 30 points | CSC 452 – 20 points)Based on theDEPARTMENTandEMPLOYEEtables created in Assignment #1, write aPL/SQLanonymous blockthat 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) thenameof the department where the employee works, 4) the number of employees inthe department where the employee works, and 5) the sum of total pay (salary + commission) of allemployees in the company.Submitting more thanonePL/SQL program will receive 0 points.If the employee ID from the user input is not in theEMPLOYEEtable (EMPLOYEE_ID), yourprogram displays a message telling the user that the employee ID is not in the table.Your program 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 theDD-MON-YYYYformat (4-digit year).If the employee does not have a manager, the manager’s name, hire date and total pay must beshown as “N/A”.If the employee does not belong to any department, the department name must be shown as“N/A”, the number of employees in the department must be shown as “0”.You will lose 5 points if your output does not have “User Input:”, “My Output:”, “-------Employee -------”, “------- Manager -------”, “------- Department -------”,or “------- Company -------”.1
Attachments: