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

When inserting data, you must not violate any of the constraints, or definitions setup for the schema. Why would the following INSERT statements violate the schema definition (you may want to look at the setup code for clues):

3.3.1 INSERT INTO CUSTOMER VALUES ('2000',NULL, 'K WEBSTER', 'MANCHESTER','ENGLAND', 6, 'WHEEL', 0);

3.3.2 INSERT INTO EMP (EMPNO, ENAME, HIREDATE, DEPTNO) VALUES (7955, 'HARPER', ’11-JUL-2000’, 30);

3.3.3 INSERT INTO DEPT VALUES (100, 'computing', 'MAIN SITE');

3.3.4 INSERT INTO EMP (EMPNO, ENAME, HIREDATE, DEPTNO) VALUES (7810, 'JONES', ’01-DEC-1999’, 50);

Exercise 3.4

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

The name is not unique

3.4.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.4.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.5

Why could this last command produce unexpected results? UPDATE EMP SET MGR = 7566, DEPTNO = 20 WHERE ENAME = 'CLARK';

Because in the future there could be more than one CLARK in the database and you could update more rows than expected.

Exercise 3.6

3.6.1 Produce SQL statements to list all of the information in the PROJ and EMPPROJ tables.

SELECT * FROM PROJ; 
SELECT * from EMPPROJ;

3.6.2 As part of the same transaction, create a new project called ORACLE VERSION 12C with a budget of 8000 and the manager is employee number 7782. Add 2 other existing employees to the EMPPROJ table for this new project and then add some expenses for one of these employees. Try to add the data using the CURRVAL and NEXTVAL attributes and add appropriate data for the other fields.

INSERT INTO PROJ VALUES (PROJSEQ.NEXTVAL,'ORACLE VERSION 12C',8000);
INSERT INTO EMPPROJ VALUES (7782, PROJSEQ.CURRVAL, 50);
INSERT INTO EMPPROJ VALUES (7934, PROJSEQ.CURRVAL, 30);
INSERT INTO EMPPROJ_EXPENSES VALUES (7782, PROJSEQ.CURRVAL, SYSDATE, 1500.50);
COMMIT;

Exercise 3.7

Why would this command fail:

ALTER TABLE EMP
  MODIFY (COMM NOT NULL);

Because the COMM column contains NULL values - any employee who is not a sales person does not have a commission.

Exercise 3.8

Update the PROJ table so that CLARK is the manager of project 3.

UPDATE proj SET mgrprojno = 7782 WHERE projno = 3;
COMMIT;

---

Return to the Workbook.