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 10g reference poster

Oracle training & performance tuning books