 |
|
Oracle Tips by Burleson |
Create Table as Select
The boss just had a great idea, let’s give an
award to all the stores that have above average sales. Of course, we
have to determine which stores have above average sales. Let’s look
at the data we will need to determine this. First, we need to know
the average sales for each store. Next, we need to know the average
of the average sales by store. Finally, we need to know which stores
have average sales that are above the average.
We can easily create a table based on a query and
insert the results of the query into the table. We want a list of
store names and their average sales, and we will use this data in a
number of other queries so let’s store it temporarily in a table.
SQL> create
table t1 as
2 select
3 store_name,
4 avg(quantity) qty
5 from
6 store join sales using (store_key)
7 group by store_name;
Table
created.
SQL> desc t1
Name Null? Type
---------------------------------- -------- -----------------
STORE_NAME VARCHAR2(40)
QTY NUMBER
The first statement uses the query to create a table. The
columns in the query define the new table column definitions and their
data types are taken from the original tables. The column
avg(quantity) had to be aliased because avg(quantity) is not a valid
column name. In this case, I aliased the column to qty. The quantity
column in the sales table is defined as NUMBER(5). Because the
quantity values were passed through the average function, the database
used the default NUMBER as the results data type. A NUMBER is the
same as NUMBER(38). By selecting from the T1 table, we can see that
the database also inserted all the rows from the query results into
the table.
SQL> select *
from t1;
STORE_NAME
QTY
---------------------------------------- ----------
barnes and Noble 545
blue ride booksellers 540
books for dummies 1181B81818
borders 1821B66667
eaton books 1346B66667
hot wet and mushy books 1452B94118
ignoramus and dufus 401B111111
quagmire books 790
specialty bookstore 1013B33333
wee bee books 1141B66667
The one command created a new table and loaded it
with the requested data. Now, we can use table T1 in our other
queries. Let’s create another table that contains the average of the
qty column in T1.
SQL> create
table t2 as
2 select
3 avg(qty) Average_Sales
4 from t1;
Table created.
SQL> desc t2
Name Null? Type
----------------------------------------- -------- --------
AVERAGE_SALES NUMBER
I can now create a query that will give us the
store names for stores with above average sales.
SQL> select
2 store_name
3 from
4 t1
5 where qty > (select average_sales from t2);
STORE_NAME
----------------------------------------
books for dummies
borders
eaton books
hot wet and mushy books
wee bee books
5 rows
selected.
Above is the list of stores with above average
sales. This is too good to waste, so let’s turn this into a script
that we can use over and over again.
-- Compute
the Stores with above average sales.
set pages 999
line 74
-- create
table t1
create table t1 as
select
store_name,
avg(quantity) qty
from
store join sales using (store_key)
group by store_name;
-- create
table t2
create table
t2
as
select
avg(qty) Average_Sales
from t1;
-- Create
the Report
set feedback
off trimspool on
select
initcap (store_name) "Store Name"
from
t1
where
qty > (select average_sales from t2)
order by
qty;
set pages 999
line 74 feedback on
The script works, but when we run it there are
errors on the CTAS
queries.
SQL> @avg_sales
create table t1 as
*
ERROR at line 1:
ORA-00955: name is already used by an existing object
create table
t2 as
*
ERROR at line 1:
ORA-00955: name is already used by an existing object
Store Name
----------------------------------------
Wee Bee Books
Books For Dummies
Eaton Books
Hot Wet And Mushy Books
Borders
The problem is that T1 and T2 are real, permanent tables in the
database. To rerun the script, we need to first remove these tables
from the database.
The above text is
an excerpt from:
Easy Oracle SQL
Get Started Fast Writing SQL Reports with SQL*Plus
ISBN 0-9727513-7-8
by John Garmany
|
Download your Oracle scripts now:
www.oracle-script.com
The
definitive Oracle Script collection for every Oracle professional DBA
|
|
