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 DEPARTMENT table 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 maximum total 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 sh…

Looking for solution of this Assignment?


We deliver quality original papers

Our experts write quality original papers using academic databases.We dont use AI in our work. We refund your money if AI is detected  

Free revisions

We offer our clients multiple free revisions just to ensure you get what you want.

Discounted prices

All our prices are discounted which makes it affordable to you. Use code FIRST15 to get your discount

100% originality

We deliver papers that are written from scratch to deliver 100% originality. Our papers are free from plagiarism and NO similarity.We have ZERO TOLERANCE TO USE OF AI

On-time delivery

We will deliver your paper on time even on short notice or  short deadline, overnight essay or even an urgent essay