 |
|
Oracle Concepts by Burleson
Consulting |
Write a SQL Query
After reading all of the reading assignments for
this tutorial and completing exercises 1A and 1B, you are now ready to
access your sample database and write some SQL queries. The process of
creating and executing SQL queries requires that you take a
step-by-step approach to the creation of the query.
In this assignment, your task will be to
reproduce the result set below, and submit the SQL file to your
instructor. Viewing the schema diagram for the sample database can
identify the columns in this report.
The steps to create this query are:
1 – Write the basic query to join the tables and
display the desired columns
2 – Add the order by clause
3 – Add SQL*Plus column statements for each
column
4 – Insert break, compute, and title statements
5 – write the report to a file with the spool
command
Below is a sample of the finished report.
Once you get your SQL to reproduce this report, submit the SQL to your
instructor.
Mon May 27
page 1
Total Salaries
by Publisher
Employee
Last
Job
Publisher Name
Description Salary
----------------- --------------- --------------- ---------
Big City Tokheim
Editor
$63,000
*****************
---------
sum
$63,000
Bookhouse King
Salesperson $95,000
Korn
Marketer $28,000
*****************
---------
sum
$123,000
Learning Works Brannigan
Manager
$66,666
*****************
---------
sum
$66,666
Mammoth House
Baker
Manager
$51,000
*****************
---------
sum
$51,000
Nitpick And Sons Coleman
Manager
$73,000
Jackson Salesperson
$35,000
Johnson
Editor
$31,000
Levender Editor
$14,000
Linus
Marketer $45,000
*****************
---------
sum
$198,000
ANSWER
set pagesize
999;
spool
publisher_report.lst
ttitle 'Total Salaries|by Publisher'
column c1
heading 'Publisher'
format a17
column c2 heading 'Employee|Last|Name' format a15
column c3 heading 'Job|Description' format a15
column c4 heading 'Salary'
format $999,999
break on c1
skip 2
compute sum of
c4 on c1
select
initcap(pub_name) c1,
initcap(emp_last_name) c2,
job_name
c3,
emp_salary c4
from
publisher p,
emp
e,
job
j
where
p.pub_key = e.pub_key
and
e.job_key = j.job_key
order by
pub_name,
emp_last_name
;
spool off;
For more details, see the "Easy
Oracle Series" a set of books especially designed by Oracle
experts to get you started fast with Oracle database technology.
|
Download your Oracle scripts now:
www.oracle-script.com
The
definitive Oracle Script collection for every Oracle professional DBA
|
|
