Oracle:Section2

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

Answers to Section 2

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

Exercise 2.1

Write an SQL command that lists all the departments, saving the the output to a file called: output.txt Do not forget to use SPOOL OFF at the end

SPOOL output.txt
SELECT * FROM DEPT;
SPOOL OFF

Exercise 2.2

2.2.1 What do you need to type to run the emp20.sql file?

start emp20

or

@ emp20

2.2.2 Create a query that lists all the clerks and save the results in a file called clerk.txt.

spool clerk.txt
SELECT * FROM EMP
WHERE JOB = 'CLERK';
spool off

The output should be similar to:

EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
----- ---------- --------- ---------- --------- ---------- ---------- ----------
 7876 ADAMS      CLERK           7788 12-JAN-83       1100                    20
 7369 SMITH      CLERK           7902 17-DEC-80        800                    20
 7900 JAMES      CLERK           7698 03-DEC-81        950                    30
 7934 MILLER     CLERK           7782 23-JAN-82       1300                    10

2.2.3 Use the editor to create a file called query3, which displays the name, salary and commission of all salesmen.

Start editor:

ed query 3

The contents should be:

SELECT ENAME, SAL, COMM
FROM EMP
WHERE JOB = 'SALESMAN';

When you type:

start query3

the results should be similar to:

ENAME             SAL       COMM
---------- ---------- ----------
ALLEN            1600        300
WARD             1250        500
MARTIN           1250       1400
TURNER           1500          0