The SELECT statement is used to retrieve data from the database.  The format is:

select columns from tables;

Let's get a list of author last names. 

SQL> SELECT author_last_name FROM author;

AUTHOR_LAST_NAME
----------------------------------------
jones
hester
weaton
jeckle
withers
petty
clark
mee
shagger
smith

10 rows selected.

In SQL*Plus , statements sent to the database must end with a semicolon.  SQL*Plus will continue to add lines to the buffer until it get a semicolon.  The command below will give the same results.  Notice that if you press ENTER and there is no semicolon, SQL*Plus assumes that you are entering another line.

SQL> SELECT
  2    author_last_name
  3  FROM
  4    author;

AUTHOR_LAST_NAME
----------------------------------------
jones
hester
weaton
jeckle
withers
petty
clark
mee
shagger
smith

10 rows selected.

This is important because formatting commands will help you avoid errors.  As our queries become more complicated, formatting becomes more important.  If I want to retrieve more than one column, I list them, separated by a comma.  The order that I list the columns in the query will be the order that they are returned.

SQL> SELECT
  2    author_last_name,
  3    author_first_name,
  4    author_city
  5  FROM
  6    author;

AUTHOR_LAST_NAME                         AUTHOR_FIRST_NAME
---------------------------------------- --------------------
AUTHOR_CITY
--------------------
jones                                    mark
st. louis

hester                                   alvis
st. louis

weaton                                   erin
st. louis

AUTHOR_LAST_NAME                         AUTHOR_FIRST_NAME
---------------------------------------- --------------------
AUTHOR_CITY
--------------------
jeckle                                   pierre
north hollywood

withers                                  lester
pie town

petty                                    juan
happyville

AUTHOR_LAST_NAME                         AUTHOR_FIRST_NAME
---------------------------------------- --------------------
AUTHOR_CITY
--------------------
clark                                    louis
rose garden

mee                                      minnie
belaire

shagger                                  dirk
cross trax

AUTHOR_LAST_NAME                         AUTHOR_FIRST_NAME
---------------------------------------- --------------------
AUTHOR_CITY
--------------------
smith                                    diego
tweedle

10 rows selected.

We will get to cleaning up the output in a moment.  First a few points about queries.

SQL is not case sensitive.  I placed the key words in caps, but that is not a requirement.  Case is important when we get to actual data, but only for the data.  In other words, if I query looking for "John", then "JOHN" and "john" will not be returned.

Formatting makes the query more readable to humans; it has no effect on the results or the performance.  Tabs can be used to indent; however, some programs do not play well with tabs so it is best to just indent with spaces.

Oracle stores database metadata (table names, index names, etc) in upper case.  User data is stored as it is entered.

At this point, let's jump to the SALES table and do some work with numbers.  First, describe the table.

SQL> desc sales

 Name                                                      Null?    Type
 ----------------------------------------- -------- ------------------ STORE_KEY                                                      VARCHAR2(4)
 BOOK_KEY                                                       VARCHAR2(6)
 ORDER_NUMBER                                             VARCHAR2(20)
 ORDER_DATE                                                   DATE
 QUANTITY                                                        NUMBER(5)

Now, retrieve a list of order_numbers and quantities.

SQL> SELECT
  2    order_number,
  3    quantity
FROM 

  sales;

ORDER_NUMBER                  QUANTITY
---------------------------   -------------
O101                          1000
O102                          10
O103                          200
O104                          400
O105                          800
O106                          180
O107                          900|
…..
O198                          8900
O199                          8800

ORDER_NUMBER                     QUANTITY
--------------------------       ------------
O200                             100

100 rows selected.

I cut out the middle part of the result set.  Notice that the character column is left justified and the number column is right justified.  This is how SQL*Plus returns the data.  I can also change the column heading by aliasing the columns.  You can alias a column using the AS keyword, or you can leave it out.  If your new column name includes a space, you need to enclose the alias in quotes.

SQL> SELECT
  2    order_number AS "Order Number",
  3    quantity qty
  4  FROM
  5    sales;

Order Number                            QTY
--------------------------             -------------
O101                                   1000
O102                                   10
O103                                   200
O104                                   400
O105                                   800
O106                                   180
 . . .

If I wanted to select all the columns, I would "select * from sales".

SQL> SELECT * FROM sales;

STOR   BOOK_K    ORDER_NUMBER         ORDER_DAT   QUANTITY
-------  -----------     --------------------          ----------------   --------------
S101     B101             O101                               02-JAN-02                   1000
S102     B102             O102                               02-JAN-02                       10
S103     B102             O103                               02-JAN-02                     200
S104     B102             O104                               03-JAN-02                     400
S105     B102             O105                               03-JAN-02                     800
S106     B103             O106                               03-JAN-02                     180
S107     B103             O107                               04-JAN-02                     900
.  .  .

You can also do math on number columns.  Math in SQL follows the normal order of precedence.  Multiplication (*) and Division (/) before Addition (+) and Subtraction (-).  Operators of the same priority are evaluated left to right.  Use parentheses to change the order of evaluation.

SQL> SELECT
  2    order_number Ord,
  3    quantity,
  4    2*quantity+10 num     
  5  FROM
  6    sales;

ORD       QUANTITY  NUM
------------------  ---------------    ----------
O101              1000         2015
O102               10             30
O103               200           410
O104                400           810
O105                800         1610
.  .  .

Notice in the example above that the multiplication happened before the addition.  A NULL values is a column value that has not been assigned or has been set to NULL.  It is not a blank space or a zero.  It is undefined.  Because a NULL is undefined, there is no such thing as NULL math.  A NULL + 4 = NULL.  NULL * 3 = NULL.  Since NULL is undefined, all math using a NULL returns a NULL.

The above book excerpt is from:

Easy Oracle SQL

Get Started Fast writing SQL Reports with SQL*Plus

ISBN 0-9727513-7-8

John Garmany 

http://www.rampant-books.com/book_2005_1_easy_sql.htm