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

 
 Home
 E-mail Us
 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 


 

 

 


 

 

 

 
 

Oracle Concepts - SQL Query Basics

Oracle Tips by Burleson Consulting

SQL and SQL*Plus

There is no magic to writing SQL

Relational databases and SQL were developed in the early 1970s at IBM.  SQL stands for Structured Query Language.  The idea was to define a common method of storing data that would allow you to retrieve specific information without detailed knowledge of the underlying database engine.  In 1979, Oracle released the first commercial relational database that used SQL. 

In 1986, the American National Standards Institute (ANSI) began publishing SQL standards.  SQL is currently the standard query method of all major database management systems. 

All about SQL

In Oracle, SQL is divided into two basic groups: data definition language (DDL) or data manipulation language (DML).   DDL is used to define objects within the database just as creating tables or indexes.  DML is used to insert, update and delete data in the database.  Finally, there is data retrieval, which is the SELECT statement. 

The examples in this book are created using SQL*Plus.  SQL*Plus is the command line interface to the Oracle Database.  The first step is to start SQL*Plus and connect to the database.  In Windows, open a terminal window.  In Linux/Unix go to the command line and ensure that the database environment is set.  To start SQL*Plus just enter ?sqlplus?. 

If the program is not found, make sure the ORACLE_HOME is set in the path.

[oracle@appsvr oracle]$ sqlplus
SQL*Plus: Release 10.1.0.2.0 - Production on Thu Jan 20 20:23:44 2005
Copyright (c) 1982, 2004, Oracle.  All rights reserved.
Enter user-name:

My database is called DEVDB.  We can start SQL*Plus and log on in one command like below.

[oracle@appsvr oracle]$ sqlplus pubs/pubs@devdb
SQL*Plus: Release 10.1.0.2.0 - Production on Thu Jan 20 20:28:11 2005
Copyright (c) 1982, 2004, Oracle.  All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL>

The log on format is:

username/password@database_service_name. 

The database service name is the name of the entry in the TNSNAMES.ora file located in ORACLE_HOME/ network/admin/tnsnames.ora.  You may need to get with your DBA to setup the TNSNAME.ORA file.  If you are running SQL*Plus on the computer that you installed Oracle on, the installation program created a TNSNAMES entry that matches the database name, in my case DEVDB.

If there was someone watching me log on and we didn?t want them to see my password, we could not include it and SQL*Plus will ask for it and not echo the password to the screen.

[oracle@appsvr oracle]$ sqlplus pubs@devdb
SQL*Plus: Release 10.1.0.2.0 - Production on Thu Jan 20 20:29:54 2005
Copyright (c) 1982, 2004, Oracle.  All rights reserved.
Enter password:
Connected to:
Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL>

Now that we are connected, let?s get to the SQL.  We would recommend that you print a copy of the pubs.ppt slide in the Code Depot for easy reference.  Look at the AUTHOR table.  You can also see what makes up the AUTHOR table by describing the table. 

SQL> desc author
 
 Name                                      Null?    Type
 ----------------------------------------- -------- ------------
 AUTHOR_KEY                                         VARCHAR2(11)
 AUTHOR_LAST_NAME                                   VARCHAR2(40)
 AUTHOR_FIRST_NAME                                  VARCHAR2(20)
 AUTHOR_PHONE                                       VARCHAR2(12)
 AUTHOR_STREET                                      VARCHAR2(40)
 AUTHOR_CITY                                        VARCHAR2(20)
 AUTHOR_STATE                                       VARCHAR2(2)
 AUTHOR_ZIP                                         VARCHAR2(5)
 AUTHOR_CONTRACT_NBR                                NUMBER(5)
 

This command lists the columns and their definitions. 

SQL*Plus places each command into a buffer.  You can edit the SQL*Plus buffer, but it is more efficient to create and run scripts (unless you are a ?vi? cowboy) (if you don?t know what that is don?t worry, you are not one).  When you enter a command, SQL*Plus will continue to place it into the buffer until it encounters a semicolon.  This tells SQL*Plus to execute the command.  You can re-execute the previous command by entering a forward slash (/) or by entering RUN.  To list the current buffer, enter ?L?.  When you enter a carriage return without a semicolon, SQL*Plus assumes you are still entering a command and will provide another line.  The Windows version of SQL*Plus also has a command history that you can cycle through using the Up/Down arrows.

As your queries become more complicated, you will want to be able to edit your queries.  The easiest way to do this is to use the host command.  Enter host notepad <filename> at the SQL prompt to open a text file in notepad.  Write/edit the query, save and close the file, then execute it with the @<filename> command.  To re-edit the file, hit the up arrow to bring the host command back, enter and edit the query.  In this way, you can quickly go from editing to execution and back to editing.

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 we want to retrieve more than one column, we list them, separated by a comma.  The order that we 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.  We 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 we 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.  We 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 we wanted to select all the columns, we 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.  We can also 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
.  .  .

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.

Conclusion on SQL

The SELECT statement is the foundation of Oracle SQL, but there are many complex features of SQL that allow you to solve complex queries using Oracle SQL syntax.

This is just a taste of Oracle SQL and we highly recommend the $19.95 book ?Easy Oracle SQL? for more complete details on using Oracle SQL to query your database.

 
Get the Complete
Oracle SQL Tuning Information 

The landmark book "Advanced Oracle SQL Tuning  The Definitive Reference"  is filled with valuable information on Oracle SQL Tuning. This book includes scripts and tools to hypercharge Oracle 11g performance and you can buy it for 30% off directly from the publisher.


 

 

  
 

 
 
 
 
 

 
 
 
 
 
 
Oracle training Excel
 
Oracle performance tuning software 
 

 

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

All rights reserved by Burleson

Oracle ? is the registered trademark of Oracle Corporation.