They have a magnificent team. These people are always kind and willing to listen to your concerns or issues. Better yet, your assignment is always ready before the time, they usually send you a draft to double-check before they finalize your paper.
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:
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.
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 moreEach 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 moreThanks 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 moreYour 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 moreBy 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