 |
|
Oracle Concepts by Burleson
Consulting |
Declare and Use Variables in
PL/SQL
In this exercise you will declare variables
and use them in PL/SQL to solve a business problem. You have been
asked to compute and display the following:
For all sales of book_type=’computer’, display
the number of copies sold, compared to the average sales for each
book_type.
We will begin coding PL/SQL in assignment
where you receive several hints. As we progress into Tutorials 7 and
8, these hints will disappear from the problem statements, so be sure
you understand the PL/SQL code development process. Here is a code
snippet to get you started:
set
serveroutput on
DECLARE
v_average_sales sales.quantity%type;
BEGIN
select
avg(quantity)
into
v_average_sales
from
book
natural join
sales
where
book_type = 'computer';
dbms_output.put_line('Average sales for computer books is '||v_average_sales);
END;
/
The output from this code snippet should look
like this:
Average sales for computer books is 1149.
Your challenge is to enhance this PL/SQL to
display the min and sum sales for computer books.
Average sales for computer books is
1149
Minimum sales for computer books is 100
ANSWER:
set
serveroutput on
DECLARE
v_avg_sales sales.quantity%type;
v_min_sales sales.quantity%type;
v_sum_sales sales.quantity%type;
BEGIN
select
sum(quantity)
into
v_sum_sales
from
book
natural join
sales
where
book_type = 'computer';
select
min(quantity)
into
v_min_sales
from
book
natural join
sales
where
book_type = 'computer';
select
avg(quantity)
into
v_avg_sales
from
book
natural join
sales
where
book_type = 'computer';
dbms_output.put_line('Total sales for computer books is '||v_max_sales);
dbms_output.put_line('Average sales for computer books is '||v_avg_sales);
dbms_output.put_line('Minimum sales for computer books is '||v_min_sales);
END;
/
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
|
|
