Oracle:Section3

Answers to Section 3

You should only look at these once you have attempted them yourself!

Exercise 3.1

3.1.1 Find all salespeople in Department 30 whose salary is greater than or equal to £1,500:

     EMPNO ENAME             SAL     DEPTNO
---------- ---------- ---------- ----------
      7499 ALLEN            1600         30
      7844 TURNER           1500         30

3.1.2 List information about managers and clerks in department 10:

EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
----- ---------- --------- ---------- --------- ---------- ---------- ----------
 7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10
 7934 MILLER     CLERK           7782 23-JAN-82       1300                    10

3.1.3 List the empno, name and jobs of employees whose names begin with M:

     EMPNO ENAME      JOB
---------- ---------- ---------
      7654 MARTIN     SALESMAN
      7934 MILLER     CLERK

3.1.4 List employees whose salaries are not between £1,200 and £1,400:

     EMPNO ENAME      JOB              SAL
---------- ---------- --------- ----------
      7839 KING       PRESIDENT       5000
      7566 JONES      MANAGER         2975
      7788 SCOTT      ANALYST         3000
      7876 ADAMS      CLERK           1100
      7902 FORD       ANALYST         3000
      7369 SMITH      CLERK            800
      7698 BLAKE      MANAGER         2850
      7499 ALLEN      SALESMAN        1600
      7844 TURNER     SALESMAN        1500
      7900 JAMES      CLERK            950
      7782 CLARK      MANAGER         2450

11 rows selected.

3.1.5 List names and departments of employees who are clerks, analysts or salesmen:

     EMPNO ENAME      JOB           DEPTNO
---------- ---------- --------- ----------
      7788 SCOTT      ANALYST           20
      7876 ADAMS      CLERK             20
      7902 FORD       ANALYST           20
      7369 SMITH      CLERK             20
      7499 ALLEN      SALESMAN          30
      7521 WARD       SALESMAN          30
      7654 MARTIN     SALESMAN          30
      7844 TURNER     SALESMAN          30
      7900 JAMES      CLERK             30
      7934 MILLER     CLERK             10

10 rows selected.

3.1.6 To list all the distinct Departments in EMP:

    DEPTNO
----------
        30
        20
        10

3.1.7 To sort employees by job and then in descending order by salary:

     EMPNO ENAME      JOB              SAL
---------- ---------- --------- ----------
      7788 SCOTT      ANALYST         3000
      7902 FORD       ANALYST         3000
      7934 MILLER     CLERK           1300
      7876 ADAMS      CLERK           1100
      7900 JAMES      CLERK            950
      7369 SMITH      CLERK            800
      7566 JONES      MANAGER         2975
      7698 BLAKE      MANAGER         2850
      7782 CLARK      MANAGER         2450
      7839 KING       PRESIDENT       5000
      7499 ALLEN      SALESMAN        1600
      7844 TURNER     SALESMAN        1500
      7521 WARD       SALESMAN        1250
      7654 MARTIN     SALESMAN        1250

14 rows selected.