Oracle Database 10g SQL (Osborne ORACLE Press Series)

In this chapter, you learned that

• The set operators ( UNION ALL , UNION , INTERSECT , and MINUS ) allow you to combine rows returned by two or more queries.

• TRANSLATE( x , from_string , to_string ) converts the occurrences of characters in from_string found in x to corresponding characters in to_string .

• You use DECODE( value , search_value , result , default_value ) to compare value with search_value . If the values are equal, DECODE() returns search_value; otherwise , default_value is returned. DECODE() allows you to perform if-then-else logic in SQL without having to use PL/SQL.

• You use the CASE expression to perform if-then-else logic in SQL without having to use PL/SQL. CASE works in a similar manner to DECODE() , but you should use CASE since it is ANSI-compliant.

• You may perform queries against data that is organized into a hierarchy.

• ROLLUP extends the GROUP BY clause to return rows containing a subtotal for each group.

• CUBE extends the GROUP BY clause to return rows containing a subtotal for all combinations of columns included in the CUBE clause along with a total at the end.

• The database has many built-in analytic functions that enable you to perform complex calculations, such as finding the top-selling product type for each month, the top salespersons, and so on.

• You use the new Oracle Database 10 g MODEL clause to perform inter-row calculations. The MODEL clause allows you to access a column in a row like a cell in an array. This gives you the ability to perform calculations in a similar manner to spreadsheet calculations.

In the next chapter, you ll learn about changing the contents of a table.