Mis562 Week 2

MIS562 Week 2 Assignment

Homework week 2

Part 1

1. SQL> --1. List all employee information in department 30.
SQL> SELECT * FROM EMP WHERE DEPTNO = 30;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ---------- 7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30 7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30 7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30 7698 BLAKE MANAGER 7839 01-MAY-81 2850 30 7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30 7900 JAMES CLERK 7698 03-DEC-81 950 30

6 rows selected.

SQL>

2. SQL> --2. List employees name, job, and salary that is a manager and has a salary > $1,000
SQL>
SQL> SELECT ENAME, JOB, SAL FROM EMP 2 WHERE EMPNO IN 3 (SELECT e.MGR FROM EMP e WHERE e.MGR IS NOT NULL) 4 AND SAL > 1000;

ENAME JOB SAL
---------- --------- ----------
FORD ANALYST 3000
BLAKE MANAGER 2850
KING PRESIDENT 5000
JONES MANAGER 2975
SCOTT ANALYST 3000
CLARK MANAGER 2450

6 rows selected.

SQL>

3. SQL> --3. Repeat exercise 2 for any employee that is not a manager or earns a salary > $1,000
SQL> SELECT ENAME, JOB, SAL FROM EMP WHERE EMPNO NOT IN (SELECT MGR FROM EMP WHERE MGR IS NOT NULL) OR SAL > 1000;

ENAME JOB SAL
---------- --------- ----------
SMITH CLERK 800
ALLEN SALESMAN 1600
WARD SALESMAN 1250
JONES MANAGER 2975
MARTIN SALESMAN 1250
BLAKE MANAGER 2850
CLARK MANAGER