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

Create an SQL Subquery

This exercise will allow you to critique a query and re-write the query in a more readable and efficient fashion.  An SQL neophyte has come to you with the following query, complain that the query takes a long time to execute.

The query was intended to display all book titles that have not yet received any sales, but the logic is quite convoluted and obtuse.

Your tasks for this exercise are:

Step 1 – Copy the query below into you c:\Burleson directory and execute against the sample database.

Step 2 – Re-write the query in a more readable and efficient fashion, ensuring that your query returns the same values as the original query.

select
  initcap(book_title)
from  book

where
  book_title not in (
                select
                distinct
                  book_title
                from
                  book,
                  sales
                where
                  book.book_key = sales.book_key
                and
                  quantity > 0);


ANSWER

Either of the following equivalents is acceptable:

select
   book_title
from
   book
where
   book_key not in (select book_key from sales);

select
   book_title,
   nvl(quantity,0)
from
   book t,
   sales  s
where
   t.book_key = s.book_key(+)
and
   quantity is null
;

RESULT SET

BOOK_TITLE                                                                      
----------------------------------------                                       
Was George Washington Gay?                                                     
Cooking Light                                                                   
Never Eat Boogers                                                              
How To Housebreak Your Horse                                                   


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.