Part I
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 DEPT as described below.
CREATE TABLE dept
( DEPTNO NUMBER(4) PRIMARY KEY,
DNAME VARCHAR2(14),
LOC VARCHAR2(13));
4) Populate the DEPT table.
INSERT INTO dept VALUES(10, 'ACCOUNTING', 'NEW YORK');
INSERT INTO dept VALUES(20, 'RESEARCH', 'DALLAS');
INSERT INTO dept VALUES(30, 'SALES', 'CHICAGO');
INSERT INTO dept VALUES(40, 'OPERATIONS', 'BOSTON');
COMMIT;
5) Create table EMP as described below.
CREATE TABLE emp
( EMPNO NUMBER(4) PRIMARY KEY,
ENAME VARCHAR2(10),
JOB VARCHAR2(9),
MGR NUMBER(4),
HIREDATE DATE,
SAL NUMBER(7, 2),
COMM NUMBER(7, 2),
DEPTNO NUMBER(4) REFERENCES DEPT(DEPTNO));
6) Populate the EMP table.
INSERT INTO emp VALUES(7839, 'KING', 'PRESIDENT', NULL, '17-NOV-81', 5000, 0, 10);
INSERT INTO emp VALUES(7698, 'BLAKE', 'MANAGER', 7839, '01-MAY-81', 2850, 0, 30);
INSERT INTO emp VALUES(7782, 'CLARK', 'MANAGER', 7839, '09-JUN-81', 2450, 0, 10);
INSERT INTO emp VALUES(7566, 'JONES', 'MANAGER', 7839, '02-APR-81', 2975, 0, 20);
INSERT INTO emp VALUES(7654, 'MARTIN', 'SALESMAN', 7698, '28-SEP-81', 1250, 1400, 30);
INSERT INTO emp VALUES(7499, 'ALLEN', 'SALESMAN', 7698, '20-FEB-81', 1600, 300, 30);
INSERT INTO emp VALUES(7844, 'TURNER', 'SALESMAN', 7698, '08-SEP-81', 1500, 0, 30);
INSERT INTO emp VALUES(7900, 'JAMES', 'CLERK', 7698, '03-DEC-81', 950, 0, 30);
INSERT INTO emp VALUES(7521, 'WARD', 'SALESMAN', 7698, '22-FEB-81', 1250, 500, 30);
INSERT INTO emp VALUES(7902, 'FORD', 'ANALYST', 7566, '03-DEC-81', 3000, 0, 20);
INSERT INTO emp VALUES(7369, 'SMITH', 'CLERK', 7902, '17-DEC-81', 800, 0, 20);
INSERT INTO emp VALUES(7788, 'SCOTT', 'ANALYST', 7566, '09-DEC-82', 3000, 0, 20);
INSERT INTO emp VALUES(7876, 'ADAMS', 'CLERK', 7788, '12-JAN-83', 1100, 0, 20);
INSERT INTO emp VALUES(7934, 'MILLER', 'CLERK', 7782, '22-JAN-82', 1300, 0, 10);
COMMIT;
Part II
1) Write a query to display the employee name along with their manager's name. Make sure that employees without managers are included as well. Sort the data by the employee name.
Hint: You may need to use an OUTER JOIN.
The output of your program should match the following:
Employee Name Manager Name
------------- ------------
ADAMS SCOTT
ALLEN BLAKE
BLAKE KING
CLARK KING
FORD JONES
JAMES BLAKE
JONES KING
KING
MARTIN BLAKE
MILLER CLARK
SCOTT JONES
SMITH FORD
TURNER BLAKE
WARD BLAKE
2) Write a query to find all employees who earn more than the average salary in their departments. Sort the data by the employee name. The output of your program should match the following:
EMP_NAME SALARY DEPT_NAME DEPT_AVG_SAL
---------- ---------------------- -------------- ----------------------
ALLEN 1600 SALES 1566.67
BLAKE 2850 SALES 1566.67
FORD 3000 RESEARCH 2175
JONES 2975 RESEARCH 2175
KING 5000 ACCOUNTING 2916.67
SCOTT 3000 RESEARCH 2175
No comments:
Post a Comment