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.

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

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.

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. 

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. 

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.

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.

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.

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.

If we wanted to select all the columns, we would ?select * from sales?.

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.

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.