Mis562 Week 2

Only available on StudyMode
  • Pages : 17 (2131 words )
  • Download(s) : 477
  • Published : November 3, 2012
Open Document
Text Preview
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 2450
SCOTT ANALYST 3000
KING PRESIDENT 5000
TURNER SALESMAN 1500
ADAMS CLERK 1100
JAMES CLERK 950
FORD ANALYST 3000
MILLER CLERK 1300
KIRK CAPTAIN 6000

15 rows selected.

SQL>

4. Show all employee names and salary that earn between $1,000 and $2,000. Use the between operator. SQL> SELECT ENAME, SAL FROM EMP WHERE SAL BETWEEN 1000 AND 2000;

ENAME SAL
---------- ----------
ALLEN 1600
WARD 1250
MARTIN 1250
TURNER 1500
ADAMS 1100
MILLER 1300

6 rows selected.

SQL>

5. Select all employees that are in department 10 and 30. Use the IN operator. SQL> SELECT * FROM EMP WHERE DEPTNO IN (10,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 7782 CLARK MANAGER 7839 09-JUN-81 2450 10 7839 KING PRESIDENT 17-NOV-81 5000 10 7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30 7900 JAMES CLERK 7698 03-DEC-81 950 30 7934 MILLER CLERK 7782 23-JAN-82 1300 10

9 rows selected.

SQL>

6. Select all employee names with an “A” in the first position of the employee name. Use the substring function or a wild card. SQL> SELECT ENAME FROM EMP WHERE SUBSTR(UPPER(ENAME), 1,1) =...
tracking img