The Create Table, Insert, and Select Oracle SQL Commands - Oracle
Oracle is one of the top relational databases in the world and has been used by Fortune 500 companies such as Exxon Mobil, Wal-mart, General Electric and many others. In this tutorial, we will show you six "must know" Oracle SQL commands.
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):
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:
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:
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;