Oracle Consulting Oracle Training Oracle Support Development
Home
Catalog
Oracle Books
SQL Server Books
IT Books
Job Interview Books
eBooks
Rampant Horse Books
911 Series
Pedagogue Books

Oracle Software
image
Write for Rampant
Publish with Rampant
Rampant News
Rampant Authors
Rampant Staff
 Phone
 800-766-1884
Oracle News
Oracle Forum
Oracle Tips
Articles by our Authors
Press Releases
SQL Server Books
image
image

Oracle 11g Books

Oracle tuning

Oracle training

Oracle support

Remote Oracle

STATSPACK Viewer

Privacy Policy

 

   
 

ss

Oracle Tips by Burleson 

The SELECT statement

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         2010
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

Linux Oracle commands syntax poster

ION Oracle tuning software

Oracle data dictionary reference poster



Oracle Forum

BC Oracle consulting support training

BC remote Oracle DBA   

 

   

 Copyright © 1996 -2016 by Burleson. All rights reserved.


Oracle® is the registered trademark of Oracle Corporation. SQL Server® is the registered trademark of Microsoft Corporation. 
Many of the designations used by computer vendors to distinguish their products are claimed as Trademarks