The Create Table, Insert, and Select Oracle SQL Commands - Oracle

Fourth Command: CREATE TABLE

This is an important command; the following are the steps to use it:

1.) Login as new_admin in SQL prompt:

SQL> connect new_admin
Enter password:
Connected.

2.) Suppose you would like to create a table with the following specifications:

Table name: FAVORITE_BANDS
Column name: BAND_NAME
Column type: VARCHAR2 with length 50
Constraint: Primary key index for Field name ID
Constraint name: FAVORITE_BANDS_PK

You can create the above tables using the commands below:

SQL> CREATE TABLE "FAVORITE_BANDS"
( "ID" NUMBER (6,0),
"BAND_NAME" VARCHAR2(50),
CONSTRAINT "FAVORITE_BANDS_PK" PRIMARY KEY ("ID") ENABLE
);

If you enter the commands above in the SQL*plus, just ignore the numbers beside the syntax they are just used for labeling.

Fifth Command: INSERT INTO

This command is used to insert data into your Oracle database tables. Suppose you would like to insert the following data into your FAVORITE_BANDS table:

1.) Nirvana
2.) Offspring
3.) Smashing pumpkins

It will look like in the SQL*plus command line:

SQL> INSERT INTO FAVORITE_BANDS (ID,BAND_NAME) VALUES (1,'Nirvana');
1 row created.

SQL> INSERT INTO FAVORITE_BANDS (ID,BAND_NAME) VALUES (2,'Offspring');
1 row created.

SQL> INSERT INTO FAVORITE_BANDS (ID,BAND_NAME) VALUES (3, 'Smashing Pumpkins');
1 row created.

If you are planning to insert several pieces data into the table, it would be time-consuming to type it one by one in the SQL*plus - instead, you could put all of your Insert statements into the SQL script and then execute it in the SQL command line (do not forget to place “@” symbol before the path):

SQL> @/home/codex-m/Desktop/insertthis.sql

Just replace the path /home/codex-m/Desktop/insertthis.sql to the path of your SQL file containing the INSERT statements.

For example, this is the content of insertthis.sql:

http://www.php-developer.org/wp-content/uploads/tutorials/insertthis.sql.zip

Try inserting that statements to the FAVORITE_BANDS table by executing that script in the SQL*plus.

Sixth Command: SELECT

This command is commonly used when extracting or retrieving information from the Oracle database. There are several features of the SELECT command for Oracle.

a.) Retrieve the entire data of FAVORITE_BANDS table:

SQL> select * from FAVORITE_BANDS;

b.) Retrieving entire column data of BAND_NAME from FAVORITE_BANDS table

SQL> select BAND_NAME from FAVORITE_BANDS;

It will results in:

BAND_NAME
-----------
Nirvana
Offspring
Smashing Pumpkins
Metallica
Led Zeppelin
Sepultura
Greenday

c.) Retrieving rows

1.) Normal method (hard parse method: http://bit.ly/ToGbO)

SQL> select BAND_NAME from FAVORITE_BANDS where ID=2;

2.) Using Bind variables

This makes the query efficient because it is not as workload-extensive as the normal method (hard parsed method). The primary reason is that instead of repeating the entire statements to be executed, you are using variables.

Supposing you set “idnumber” as the bind variable representing ID column name. You need to declare it first.

SQL> variable idnumber NUMBER
SQL> exec :idnumber :=1

In the above code, you are declaring the bind variable “idnumber” as number type, then assign 1 to it initially.

The SELECT query now becomes (to use the bind variable it should have colon punctuation before it):

SQL> select BAND_NAME from FAVORITE_BANDS where ID= :idnumber;

It will output:

BAND_NAME
--------------
Nirvana

Since you have pre-assigned the bind variable as 1 that relates to “Nirvana” band.

To make another query, instead of re-typing the entire select command, you just need to assign another value to “idnumber” which is the bind variable:

SQL> exec :idnumber :=2

Then to repeat your last command, you do not need to type the SELECT statement but simply type “/” to execute the last command:

SQL> /

This will output:

BAND_NAME
----------
Offspring

“Offspring” band relates to ID of 2 in the database table.

d.) Sorting results

One important feature of the SELECT command is to sort results. Suppose you would like to sort the results by band name for all retrieved data:

SQL> select * from FAVORITE_BANDS
ORDER BY BAND_NAME;

Or if you want a descending order of the results:

SQL> select * from FAVORITE_BANDS
ORDER BY BAND_NAME desc;