O'Reilly logo

Oracle SQL*Plus Pocket Reference by Jonathan Gennick

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

Selecting Data

The SELECT statement is the key to getting data out of an Oracle database. It’s also very likely the most commonly executed SQL statement from SQL*Plus.

The SELECT Statement

The basic form of the SELECT statement looks like this:

SELECT column_list
FROM table_list
WHERE conditions
GROUP BY column_list
HAVING conditions
ORDER BY column_list;

The lists in this syntax are comma-delimited. The column list, for example, is a comma-delimited list of column names or expressions identifying the data that you want the query to return.

Selecting columns from a table

To retrieve columns from a table, list the columns you want following the SELECT keyword, place the table name after the FROM keyword, and execute your statement. The following query returns a list of tables that you own together with the names of their assigned tablespaces:

SELECT table_name, tablespace_name
   FROM user_tables;

Ordering query results

You can use the ORDER BY clause to sort the results of a query. The following example sorts the results by table name:

SELECT table_name, tablespace_name
FROM user_tables
ORDER BY table_name;

The default is to sort in ascending order. You can specify descending order using the DESC keyword. For example:

ORDER BY table_name DESC;

While it’s redundant, ASC may be used to specify ascending order. The following example sorts the table list first by tablespace name in descending order and then within that by table name in ascending order:

SELECT table_name, tablespace_name FROM user_tables ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required