Oracle Aggregation and in-line views

Remote DBA

Remote DBA Plans

Oracle:

Aggregation and in-line views

Now, that we see how and line views allow us to put some select statements in the from clause of SQL, let’s take a look at how we might be able to use in and line view to solve a problem using SQL notation.

Referring to our pubs database, suppose that we had a need to compare the individual sales of books for each store with the total number of sales for all of the stores.

Without an in-line view, we would be required to create a temporary table to store the sum of sales for all of the stores:

drop table t1;

create table t1 as select sum(quantity) tot_qty from sales;

select

store_name,

tot_qty,

sum(quantity)

from

store

natural join

sales,

group by

store_name,

tot_qty

;

Note the use of the “natural join” SQL operator here. This alleviates the need to specify the join criteria for the tables in the where clause. The output of this SQL looks like this:

STORE_NAME TOT_QTY_ALL_STORES STORE_QTY

------------------------- ------------------ ----------

barnes and Noble 110550 2180

blue ride booksellers 110550 5400

books for dummies 110550 13000

borders 110550 21860

eaton books 110550 12120

hot wet and sexy books 110550 24700

ignoramus and dufus 110550 3610

quagmire books 110550 7900

specialty bookstore 110550 6080

wee bee books 110550 13700

Using the concept of in-line views, your assignment is to re-write this query as a single SQL statement. This will involve moving the query that computes the sum of all sales into the from clause of the original query.

ANSWER

select

store_name,

tot_qty_all_stores,

sum(quantity) store_qty

from

store

natural join

sales,

(select sum(quantity) tot_qty_all_stores from sales)

group by

store_name,