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