Oracle:Section7

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

Exercise 7.1

7.1 Create a file which produces a report on Salesmen earning more than 300 commission.

The report should have the following features:

• it should be headed 'Computing Sales Report';
• each page should have 'For Your Eyes Only' at the bottom of the report;
• show both the sal and comm columns as currency;
• show the comm as a 4 figure number with leading 0s;
• the output should be ordered by salary;
• The SQL query file should be stored as U:\salesman.sql;
• save the output of the query into a file U:\salary.txt.

To start the editor:

ed u:\saleman

then add the following:

TTITLE 'Computing Sales Report'
BTITLE 'For Your Eyes Only'
SET PAGESIZE 30
COLUMN sal FORMAT $9,999
COLUMN comm FORMAT $0,999
SPOOL u:\salary.txt
SELECT * FROM EMP
WHERE JOB = 'SALESMAN' AND COMM > 300;
ORDER BY SAL;
REM don't forget to switch everything off again
SPOOL OFF
TTITLE OFF
BTITLE OFF
CLEAR COLUMNS

---

Return to the Workbook.