Call for Oracle support & training (800) 766-1884
Free Oracle Tips

Home
Corporate Oracle Training
Custom Oracle Training
Oracle New Features Training
Advanced Oracle DBA Classes
Oracle Tuning Courses
Oracle Tips & Tricks
Oracle Training Links
Oracle Training Links
Oracle Training Links

We are top for USA Oracle Training Clients

 

Free Oracle Tips


 
HTML Text AOL

Free Oracle App Server Tips


 
HTML Text

Oracle support

Oracle training

Oracle tuning

Rednecks!

Remote Oracle

Custom Oracle Training

 

   
  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

 
 
 

Oracle performance tuning book

 

 

Oracle performance tuning software

 
Oracle performance tuning software
 
Oracle performance Tuning 10g reference poster
 
Oracle training in Linux commands
 
Oracle training Excel
 
 
 
 

 

email BC:


Copyright © 1996 -  2014 by Burleson Inc. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.