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 statements output sh…
Looking for solution of this Assignment?
WHY CHOOSE US?
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 |