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