Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 
 Home
 E-mail Us
 Oracle Articles
New Oracle Articles


 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog


 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

 Applications
 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

Don Burleson Blog 


 

 

 


 

 

 

 

 

SELECT Statement

Oracle SELECT Statement Tips by Donald Burleson


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


 

 

��  
 
 
Oracle Training at Sea
 
 
 
 
oracle dba poster
 

 
Follow us on Twitter 
 
Oracle performance tuning software 
 
Oracle Linux poster
 
 
 

 

Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


                    









Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


 

Copyright © 1996 -  2020

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.