Difference between revisions of "Oracle:Section7"
From mi-linux
Jump to navigationJump to search| (6 intermediate revisions by the same user not shown) | |||
| Line 1: | Line 1: | ||
| − | |||
[[Main Page]] >> [[Oracle|Oracle and SQL]] >> [[Oracle_Workbook|Workbook]] >> Section 7 answers | [[Main Page]] >> [[Oracle|Oracle and SQL]] >> [[Oracle_Workbook|Workbook]] >> Section 7 answers | ||
| − | == Answers to Section | + | == Answers to Section 6 == |
| − | = | + | <p style="color: red"> |
| + | '''You should only look at the answers once you have attempted them yourself!''' | ||
| + | </p> | ||
| − | + | == Exercise 6.1 == | |
| − | * it should be headed 'Computing Sales Report' | + | |
| − | * each page should have 'For Your Eyes Only' at the bottom of the report | + | 6.1 Create a file which produces a report on Salesmen earning more than 300 commission. |
| − | * the output should be ordered by salary | + | |
| − | * The SQL query file should be stored as U:\salesman.sql | + | 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. | * 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' | TTITLE 'Computing Sales Report' | ||
BTITLE 'For Your Eyes Only' | BTITLE 'For Your Eyes Only' | ||
SET PAGESIZE 30 | SET PAGESIZE 30 | ||
| + | COLUMN sal FORMAT $9,999 | ||
| + | COLUMN comm FORMAT $0,999 | ||
SPOOL u:\salary.txt | SPOOL u:\salary.txt | ||
SELECT * FROM EMP | SELECT * FROM EMP | ||
| − | WHERE JOB = 'SALESMAN'; | + | WHERE JOB = 'SALESMAN' AND COMM > 300; |
| + | ORDER BY SAL; | ||
REM don't forget to switch everything off again | REM don't forget to switch everything off again | ||
SPOOL OFF | SPOOL OFF | ||
TTITLE OFF | TTITLE OFF | ||
BTITLE OFF | BTITLE OFF | ||
| − | + | CLEAR COLUMNS | |
---- | ---- | ||
Return to the [[Oracle_Workbook|Workbook]]. | Return to the [[Oracle_Workbook|Workbook]]. | ||
Latest revision as of 12:44, 18 March 2016
Main Page >> Oracle and SQL >> Workbook >> Section 7 answers
Answers to Section 6
You should only look at the answers once you have attempted them yourself!
Exercise 6.1
6.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.
