Oracle SQL*Plus Pocket Reference, 3rd Edition

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:

WITH subqueries
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 you want the query to return.

Selecting columns from a table

To retrieve columns from a table, list the columns you want after the SELECT keyword, place the table name after the FROM keyword, and execute your statement. The following query returns a list of tables you own 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 ORDER ...