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.
