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;

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 BY tablespace_name DESC, 
         table_name ASC;

If you want the sort to be case-insensitive, you can use Oracle’s built-in UPPER function. For example:

SELECT table_name, tablespace_name
FROM user_tables
ORDER BY UPPER(table_name);

For symmetry, Oracle also has a built-in LOWER function. LOWER converts a string to lowercase; UPPER converts to uppercase.

Use the WHERE clause to restrict the rows returned by a query to those that you need to see. The following example returns a list of any invalid objects that you own:

SELECT object_name, object_type
FROM user_objects
WHERE status = 'INVALID'
ORDER BY object_type, object_name;

The expression following the WHERE clause may be any valid Boolean expression. Oracle supports all the typical operators that you would expect: +, -, /, *, <, >, <>, <=, >=, AND, OR, NOT, ||, IS NULL, LIKE, BETWEEN, and IN. Parentheses are also supported and may be used to clarify the order of evaluation.

If you are returning results from a query that might be null, or you are sorting on results that might be null, you can use Oracle’s built-in NVL function to replace null values with a selected non-null value. For example, the NUM_ROWS column in the USER_TABLES view will have a value only for tables that have been analyzed. Here, the NVL function is used to convert null values to zeros:

SELECT table_name, NVL(num_rows,0)
FROM user_tables
ORDER BY NVL(num_rows,0);

Be cautious about using NVL in a WHERE clause. Using NVL, or any other function, on an indexed column in a WHERE clause may prevent Oracle from using any index on that column.

The joins that you’ve seen so far are inner joins. An inner join is one that returns data only when both tables have a row that matches the join conditions. For example, the following query returns only tables that have constraints defined on them:

SELECT ut.table_name, uc.constraint_name
FROM user_tables ut, user_constraints uc
WHERE ut.table_name = uc.table_name;

An outer join returns rows for one table, even when there are no matching rows in the other. You specify an outer join in Oracle by placing a plus sign (+) in parentheses following the column names from the optional table in your WHERE clause. For example:

SELECT ut.table_name, uc.constraint_name
FROM user_tables ut, user_constraints uc
WHERE ut.table_name = uc.table_name(+);

The (+) following uc.table_name makes the user_constraint table optional. The query will return all tables, and where there are no corresponding constraint records, Oracle will supply a null in the constraint name column.

Aggregate functions take data from multiple rows as input and return one summarized value. For example, the following query uses the COUNT function to return the number of tables that you own:

SELECT COUNT(*)
FROM user_tables;

Oracle supports several different aggregate functions, all of which are listed in Table 1.1.

In addition to summarizing the entire results of a query, you can summarize the data for each distinct value in a column. For example, the following query returns the number of columns in each table you own:

SELECT ut.table_name, COUNT(utc.column_name)
FROM user_tables ut, user_tab_columns utc
WHERE ut.table_name = utc.table_name
GROUP BY ut.table_name
ORDER BY ut.table_name;

The following query extends the previous query and displays the number of columns in each table to which you have access. This time the grouping results in one row for each distinct owner and table name combination:

SELECT at.owner, at.table_name,
   COUNT(atc.column_name)
FROM all_tables at, all_tab_columns atc
WHERE at.table_name = atc.table_name
GROUP BY at.owner, at.table_name
ORDER BY at.owner, at.table_name;

If you want the results of a GROUP BY query returned in any particular order, you must include an ORDER BY clause. However, an ORDER BY clause is not required. At times it may appear that Oracle automatically sorts GROUP BY queries. It does, but only to a point. If you want the results sorted, you must include an ORDER BY clause.

Columns in the select list of a GROUP BY query must be either listed in the GROUP BY clause or enclosed by one of the aggregate functions listed earlier in Table 1.1.

You can use the HAVING clause to restrict the rows returned by a summary query to only the rows of interest. The HAVING clause functions just like the WHERE clause, except that the HAVING conditions are applied to the summarized results. For example, the following query returns a list of all tables for which you have not defined any indexes:

SELECT ut.table_name, COUNT(ui.index_name)
FROM user_tables ut, user_indexes ui
WHERE ut.table_name = ui.table_name(+)
GROUP BY ut.table_name
HAVING COUNT(ui.index_name) = 0;

This query works by first counting up the number of indexes on each table and then eliminating those tables with nonzero counts.

Avoid placing conditions in the HAVING clause that do not test summarized values. Consider, for example, these two queries:

SELECT at.owner, at.table_name, 
   COUNT(atc.column_name)
FROM all_tables at, all_tab_columns atc
WHERE at.table_name = atc.table_name
GROUP BY at.owner, at.table_name
HAVING at.owner <> 'SYS'
AND at.owner <> 'SYSTEM'
ORDER BY at.owner, at.table_name;

SELECT at.owner, at.table_name, 
   COUNT(atc.column_name)
FROM all_tables at, all_tab_columns atc
WHERE at.table_name = atc.table_name
AND at.owner <> 'SYS'
AND at.owner <> 'SYSTEM'
GROUP BY at.owner, at.table_name
ORDER BY at.owner, at.table_name;

Both queries return the same result—a count of rows in each table except for those tables owned by SYS or SYSTEM. The second query, however, will execute more efficiently because tables owned by SYS and SYSTEM are eliminated by the WHERE clause before the data is summarized.

The aggregate functions listed in Table 1.1 ignore null values. By default, they also exclude duplicate values. You can use the ALL and DISTINCT keywords to modify this behavior. For example:

SELECT COUNT (DISTINCT table_name)
FROM user_tab_columns;
SELECT COUNT (ALL table_name)
FROM user_tab_columns;

The first query uses the DISTINCT keyword to count up the number of tables. The second query uses the ALL keyword to count up the total number of columns defined for all those tables.