Suppose you have a database named “timewasteblogCOM” loaded with user details like user id, user_ name, email,phone number,join_date,donation in table “USERS”.

Now you want to see list of all records  loaded into the table “USERS”  table.

1.You should have access to database “timewasteblogCOM”.

2.Start SQLPLUS or Isqlplus and connect to database.

3.Next need to execute SQL statment which retrive desired data.

4.Now we already learnt SELECT is keyword we need to use to retrieve data.

First Part of SQL statement : SELECT

5. What to retrieve ? all of  user names.  * means all

Next SQL statement part  *

6. Now we need to tell in which table data loaded . So in our case  table name will be “USERS”

Next SQL statement part USERS

So  Here is  the complete SQL  statment : SELECT  *  from USERS

Bingo ! your first SQL statement completed. 

Now lets see more  details of SELECT

One more example:

1.Suppose you want to select 2 columns named as user_names,email from our database “timewasteblogCOM” .SQL statement will look like as below.

SELECT  user_name,email FROM USERS;

Note:  Every SQL statement should end with ‘;’

Suppose if I want to know how it looks if each user donation doubles [Interesting ! isn't], then SQL quesry will look like below.

SELECT user_name,donation, donation*2 FROM USERS

Above SQL statment will show records related to user_name, donation along with it shows new column with heading “donation*2″ with digits which are equal  to donation multiply by 2.

New column “donations*2″ is virtual one, its not real. Just display purpose only.

Now lets discuss about Operator Precedence.SQL Multiplication and division takes priority than addition and subtraction.You can force priority by using Parentheses.

Example: Conider Mr.John given $10 donation then following SQL query will show  “2*donation+100″ column with value $22.

SELECT user_name,donation, 2*donation+100 FROM USER

If we enforce Arithmetic Precedence with Parentheses result will be  $220 ( I like it )

SELECT user_name,donation, 2* (donation+100)

A record in a table is unavailable,unassigned,unknown, or inapplicable then that is called NULL value. ZERO is NOT equal to NULL value.Just blank space also not NULL value.In Arithmetic operation with NULL value results NULL only.

In above example we saw user_name column showing user name. Now I want to show more meaningful name like NAME in query result you can use Alias feature.By default SQlPLUS  uses name of the selected column as column name in query result. Some times  those columns names are not descriptive.So using Alias feature you can show meaningful column names instead of original column names.

SELECT user_name AS Name  from USERS;

Above SQL query result shows list of user names under column “Name” instead of original column name “User_Name”.

If Alias contains spaces or special characters oris case sensitive you need to put Alias in double quotations.

SQL keyword DISTINCT helps to eliminates duplicate enties in SQL query result.

SELECT  DISTINCT last_name from USERS

Above query results rows which are duplicate free of user last_names.

Next we going to see some LAB simulations related to current lesson.

Please leave your comments/questions here. Response guaranteed .

Other Posts related to current topic

1. Passed OCA PAPER -1 Oracle 9i sql 1Z0-007
2. How to apply for OCA exam 1Z0-007
3. 01. Lets learn SQL statement SELECT 1Z0-007 (This post)
4. Oracle DBA and SQL basics video tutorials - 00
5. SQL vs SQLPLUS - Learn SQL online
6. Project OCA DBA DAY-1 schedule details.FREE OCA tutorial
7. Let's start PROJECT OCA
8. Oracle OCA DBA certification details and guidance
9. How to convert as oracle DBA from system administrator
10. How Oracle Works in single shot
11. Best books to learn Oracle DBA
12. Got the software now time to gather Oracle 10g books
13. Oracle 10g Express vs Standard vs Enterprise
14. Become a Oracle DBA within 25 days