Oracle:Section3

Main Page >> Oracle and SQL >> Workbook >> Section 3 answers

Answers to Section 3

You should only look at the answers 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:

SELECT EMPNO, ENAME, SAL, DEPTNO 
FROM EMP 
WHERE JOB = 'SALESMAN' 
AND DEPTNO = 30 AND SAL >= 1500;

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

3.1.2 List information about managers and clerks in department 10:

SELECT *
FROM EMP
WHERE DEPTNO = 10
AND (JOB = 'MANAGER' OR JOB = 'CLERK');

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:

SELECT EMPNO, ENAME, JOB
FROM EMP
WHERE ENAME LIKE '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:

SELECT EMPNO, ENAME, JOB, SAL
FROM EMP
WHERE SAL NOT BETWEEN 1200 AND 1400;

Or:

SELECT EMPNO, ENAME, JOB, SAL
FROM EMP
WHERE SAL <= 1200 OR SAL >= 1400;

     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:

SELECT EMPNO, ENAME, JOB, DEPTNO
FROM EMP
WHERE JOB IN ('CLERK', 'ANALYST', 'SALESMAN');

Or:

SELECT EMPNO, ENAME, JOB, DEPTNO
FROM EMP
WHERE JOB = 'CLERK' OR JOB = 'ANALYST' OR JOB = 'SALESMAN';

     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:

SELECT DISTINCT DEPTNO
FROM EMP;

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

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

SELECT ENAME, JOB, SAL
FROM EMP
ORDER BY JOB, SAL DESC;

     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.

Exercise 3.2

3.2.1 Now create a new EMP record with the following values – EMPNO: 9000, ENAME: your surname, JOB: MANAGER, DEPTNO: 90, COMM: 0, MGR: 7839, HIREDATE: today's date and give yourself a value for SAL.

Do not forget - you need to add department 90 first in the DEPT table for this to work.

INSERT INTO EMP VALUES (9000,'MY_NAME','MANAGER',7839, SYSDATE, 4000, 0, 90);
COMMIT;

The system should respond with the following messages:

1 row created.

Commit complete.

Exercise 3.3

3.3.1 Why do we need to specify a number as well as the name when deleting the record?

The name is not unique

3.3.2 Could the WHERE statement be shortened?

Yes if the WHERE clause includes the primary key and so uniquely identifies the row being deleted, anything else is unnecessary. The command could be shortened to:

DELETE FROM EMP 
WHERE EMPNO = 7945;

3.3.3 What command verifies the record has been deleted?

SELECT * FROM EMP 
AND EMPNO = 7954

The system should respond with the message:

 - no rows selected

This confirms that the record has been deleted. Do not forget to commit the change afterwards!

Exercise 3.4

3.4.1 List all of the information in the PROJ and EMPPROJ tables. Check the primary key values are as expected.

SELECT * FROM PROJ; 
SELECT * from EMPPROJ;