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

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


Free Oracle App Server Tips


Oracle support

Oracle training

Oracle tuning


Remote Oracle

Custom Oracle Training


  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); 

books for dummies
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
  avg(quantity) qty
  store join sales using (store_key)
group by store_name; 

-- create table t2
create table
  avg(qty) Average_Sales
from t1; 

--  Create the Report 

set feedback off trimspool on 

   initcap (store_name) "Store Name"
   qty > (select average_sales from t2)
order by

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

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:

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.