Different Select Statements in Oracle SQL .

We know that to perform a query the select command is used. Apart from just providing information this command can be clubbed with some DDL and DML statements to perform relational operations.

 

Select command to create a table

We can also create a table and copy the records into it with a single statement, by including a ‘select’ clause in a create table command. The syntax is given below.

 

create table <new_table_name> as select column_names from <existing_table_name>;

 

At PQR ENTERPRISES a replicate of the table vendor_master is needed for future use. The following example will create a new table vend_details from the existing table vendor_master along with its records.

 

Example 

SQL> create table vend_details as select from vendor_master;

 

Querying the vend_details table will display the same set of records available in the vendor_master table.

SQL> select from vend_details;

VENCO  VENNAME       VENADD1   VENADD2   VENADD3         TEL NO

——-  ————-   ———-  ———-  ————-     ——-

v001   frances       10        first st   korattur       611892

v002   James         20        lock st    mambalam       453657

v003   Ramesh                                            231218

v004   Somesh                                            542879

v005   Raju          21/1      second avenue Anna nagar  621453

 

At PQR ENTERPRISES a new table is to be created which would include only the vencode and venname to act as a checklist on the different vendors the company has as its distribution network. Copying part of a table into a new table can be done. Example shows this possibility.

 

Example 

SQL> create table ven_mas (venid, name) as select vencode, venname from vendor_master;

 

If a new table has to be created based on a certain requirement then this can be included in a ‘where’ clause.

The marketing division at PQR ENTERPRISES requires a separate list of the products, which fall in the category of ‘spares’. The following example will create a new table based on the itemfile table satisfying the ‘where’ condition.

 

Example 

SQL> create table list as select * from itemfile where p_category=’spares’;

 

The table list will be created with those records which are bound by the ‘where’ condition. By performing a query on the new table ‘List’ the effect of the create command in example can be seen.

SQL> select * from list;

 

ITEMC  ITEMDESC      P_CATEGORY         QTY_HAND RE_LEVEL MAX_LEVEL

——– ————–  ——————   ———  ———- ———–

i201   nuts          spares             100       50      300

i202   bolts         spares             95        125     300

i204   holders       spares             18        30      75

i206   brackets      spares             150       73      200

 

It is also possible to copy only the structure of a table to a new one by specifying a where condition that will never satisfy. For example to create a new table based on the itemfile table’s structure, but without its records the following example will suffice

.

Example 

SQL> create table new_itemfile as select * from itemfile where 1=2;

 

The condition 1=2 will never satisfy. Hence a new table will be created with only the structure of the itemfile table but not its records. This can be verified by querying the new table.

 

SQL> select * from new_itemfile;

 

The output of this command will be

 

No rows selected

 

Select command to Insert Records

Alternatively inserting records from one table into another can also be accomplished. In this case either a table should be created or be existing prior to performing such an insert.

The syntax is

Insert into <table_name> (select column_names from <existing_table_name>);

 

The marketing division of PQR ENTERPRISES requests for a copy of the orders being handled, as a separate object. In the example shown below a new table, order_mast_copy is created and then records from order_master are copied into it.

 

Example 

SQL> create table order_mas_copy (orderno varchar2 (5), odate date, vencode varchar2   (5), ostatus char (1), del_date date);

 

SQL> insert into order_mas_copy (select from order_master);

The number of rows inserted into the new table will be displayed.

 

Note: Care should be taken to ensure that the two table structures are the same. In the above example, order_master and order_mas_copy have the same structure.

 

Select command for Column Aliases

The select command can be used to temporarily change a column name, when the query result is displayed. This change will be an alias for the actual column name and in will not have a direct bearing on it. Column aliases come in handy, to shorten column names or if there is a need to hide the actual column name from being displayed. The syntax is given de below.

Select column_name <alias_name> from table_name;

 

The example given below replaces the column name vencode with an alias id at the time of display.

 

Example 

SQL> select vencode id from vendor_master;

 

The output will be

 

ID

——-

v001

v002

v003

v004

v005