Oracle 9i Cram Sheet

This Cram Sheet contains last-minute reminders you can review briefly before the exam starts. You cannot take this cram sheet with you into the exam!

THE ITERATIVE SYSTEM DEVELOPMENT CYCLE

BASIC TERMINOLOGY

• SQL— Structured Query Language.

• DML— Data Manipulation Language (ROLLBACK allowed).

• DDL— Data Definition Language (ROLLBACK not allowed).

THE SELECT STATEMENT

The SELECT statement is used to retrieve data from a database. A SELECT statement is also known as a query because it queries a database.

• Simple SELECT:

  SELECT *|<column> [AS <name>|"name"][,...] FROM [<schema>.]<table>;
  

• Add an alias:

  SELECT [<alias>.]*|[<alias>.]<column> [,...]
  FROM [<schema>.]<table> [<alias>];
  

• Retrieve unique (DISTINCT) items:

  SELECT DISTINCT|UNIQUE [(]<column>[,...][)] FROM [<schema>.]<table>;
  

The WHERE Clause

The optional WHERE clause is used to filter rows in a query, an UPDATE command, or a DELETE command.

• Simple comparison:

  SELECT * FROM [<schema>.]<table>
  [WHERE [<schema>.][[<table>.]|[<alias>.]]<column>
      <comparison>[...]<column>];
  

• Conjunctive comparison using logical conditions:

  SELECT * FROM [<schema>.]<table>
  [WHERE [<schema>.][[<table>.]|[<alias>.]]<column>
      <comparison>[...]<column>
  AND [NOT]| OR [NOT] [...]<column> <comparison>[...]<column>
  AND [NOT]| OR [NOT] ... ];
  

• The UPDATE and DELETE commands:

  UPDATE [<schema>.]<table> SET ...
  [WHERE [<schema>.][[<table>.]|[<alias>.]]<column>
      <comparison>[...]<column> ...];
  
  DELETE FROM [<schema>.]<table>
  [WHERE [<schema>.][[<table>.]|[<alias>.]]<column>
      <comparison>[...]<column> ...];
  

The ORDER BY Clause

The optional ORDER BY clause is used to sort rows returned by a query, and it can also be a part of the OVER clause for analysis.

• Simple sorting:

  SELECT * FROM [<schema>.]<table> [WHERE]
  [ORDER BY {[<alias>.]<column>|<position>}[,...]];
  

• Ascending (the default) and descending sorts:

  SELECT * FROM [<schema>.]<table> [WHERE]
  [ORDER BY {[<alias>.]<column> [ASC|DESC]
      |<position> [ASC|DESC]}[,...[ASC|DESC]]];
  

• Sorting NULL values (by default returned last):

  SELECT * FROM [<schema>.]<table> [WHERE]
  [ORDER BY {[<alias>.]<column> [ASC|DESC][NULLS {FIRST|LAST}]
     |<position> [ASC|DESC][NULLS {FIRST|LAST}]}[,...[ASC|DESC]]];
  

• The OVER clause:

  SELECT {<column> [AS OVER() [ORDER BY]...]}[,...]
  FROM <table> [WHERE][ORDER BY];
  

The GROUP BY Clause

The optional GROUP BY clause is used to summarize, aggregate, and analyze groupings returned from queries. Any SELECT list elements not included in aggregation functions must be included in the GROUP BY list of elements. This includes both columns and expressions. At least one element of the SELECT list of elements must be subjected to an aggregation function.

• Simple GROUP BY:

  SELECT * FROM [<schema>.]<table> [WHERE]
  [GROUP BY [<alias>.]<column>[,...]][ORDER BY];
  

• Filtering groups with the optional HAVING clause (similar syntax to that of the WHERE clause):

  SELECT * FROM [<schema>.]<table> [WHERE]
  [GROUP BY ... [HAVING <column> <comparison> <column> [,...]]]
  [ORDER BY];
  

• Extending the GROUP BY clause with OLAP functionality using the ROLLBUP, CUBE, and GROUPING SETS clauses:

  SELECT * FROM [<schema>.]<table> [WHERE]
  [GROUP BY ... [HAVING ...]
  [ROLLUP(<column>,[...])|CUBE(<column> [,...])
      |GROUPING SETS((<group>)[,...])][ORDER BY];
  

JOINING TABLES

Tables can be joined such that results from one or more tables appear in the same query result. Joins can be an outer join, a Cartesian Product (a cross join), a table joined to itself (a self join), or a semi join.

• Oracle join syntax is placed into the WHERE clause using the (+) operator (outer join operator), determining the side of an outer join deficient in information:

  SELECT * FROM <table>
  [WHERE <column> <comparison> <column> [(+)]]
      |[WHERE <column>[(+)] <comparison> <column>]
  AND [NOT]| OR [NOT] ... ][GROUP BY][ORDER BY];
  

• ANSI join syntax is placed into the FROM clause using the JOIN keyword:

  SELECT * FROM <table> [JOIN <table> [JOIN <table> ...]]
  [WHERE][GROUP BY][ORDER BY];
  

• ANSI join syntax allows the CROSS JOIN clause to create a Cartesian Product between two tables:

  SELECT *
  FROM <table> [[CROSS] JOIN <table> [[CROSS] JOIN <table> ...]]
  [WHERE][GROUP BY][ORDER BY];
  

• ANSI join syntax can use the NATURAL keyword to create both inner and outer joins, joining tables on columns with the same name in the different tables:

  SELECT *
  FROM <table> [[NATURAL] JOIN <table> [[NATURAL] JOIN <table> ...]]
  [WHERE][GROUP BY][ORDER BY];
  

• ANSI join syntax can utilize the USING clause to specify exact column names to join on, avoiding joins on same-named columns with different meanings:

  SELECT *
  FROM <table> [JOIN <table> USING(<column>[,...])
      [JOIN <table> ...] USING(<column>[,...])]
  [WHERE][GROUP BY][ORDER BY];
  

• ANSI join syntax can utilize the ON clause to specify exact column names to join on, specifically when different-named columns are required in the join, usually requiring a table or alias reference:

  SELECT *
  FROM <table> a [JOIN <table> b
      ON (a.<column>=b.<column> [AND [NOT]| OR [NOT] ... ] [,...])
          [JOIN <table> c] ON(...)]
  [WHERE][GROUP BY][ORDER BY];
  

SPECIALIZED QUERIES

• Subqueries can return single values (scalar), multiple columns or multiple rows, or both. Subqueries can be used in a SELECT clause elements list, the FROM clause, the WHERE clause, the ORDER BY clause, an INSERT statement VALUES clause, an UPDATE statement set clause, or a CASE statement expression. Also tables and views can be created using subqueries.

• The WITH clause allows prepared execution of subquery results. Results can then be utilized by the primary calling query:

  WITH query1 AS (subquery), query2 AS (subquery)
  SELECT * FROM query1 JOIN query2 JOIN query3;
  

• Hierarchical queries allow hierarchical representations of hierarchical data. The hierarchy can be accessed from the root node or a starting point within the hierarchy (the START WITH clause). The CONNECT BY clause allows linking between a column in the current row and another value in a parent (the PRIOR operator) row:

  SELECT <column>, LEVEL FROM <table> START WITH <condition>
  CONNECT BY <current_row> <parent_column>
      = PRIOR <parent_row>
  <current_column>;
  

• Flashback queries allow a query flashback to a specific point in time, based on a timestamp or an SCN:

  SELECT * FROM ... AS OF {TIMESTAMP|SCN};
  

• A Top-N query can be used to retrieve a small number of rows from a large row set.

  SELECT * FROM (SELECT * FROM <table> ORDER BY ...)
  WHERE ROWNUM < n;
  

OPERATORS

• String concatenation operator— || concatenates two strings together; for example, 'My name is'||' '||'Joe' returns the string My name is Joe.

• Set operators— Set operators allow concatenation of results from two queries. UNION ALL returns all rows including duplicates, UNION returns all unique rows, INTERSECT returns all rows common to both queries, and MINUS returns all rows in one and not the other query.

• Hierarchical query operator— The PRIOR operator placed with the CONNECT BY clause links the current row with a parent row.

CONDITIONS

• Comparison conditions:

  • Equi (=), Anti (!=, <>, ^=), and Range (<, >, =<, >=).

  • [NOT] LIKE—Pattern matching.

  • [NOT] IN—Set membership between expressions.

  • [NOT] EXISTS—Set membership producing a Boolean result.

  • [NOT] BETWEEN—Range search between two values, inclusive of end points.

  • ANY, SOME, and ALL—Set membership conditions.

    • IS [NOT] NULL—NULL value testcondition.

• Logical conditions, in order of precedence:

  • NOT—An expression being false yields true.

  • AND—Two expressions being true yield true.

  • OR—One of two expressions being true yields true.

PSEUDOCOLUMNS

• ROWID— Relative row pointer.

• ROWNUM— A sequence number for rows returned by a query as the rows are returned.

• <sequence>.CURRVAL and <sequence>.NEXTVAL— The current and next values for a sequence.

• LEVEL— Returns the level of a row in a hierarchical query.

EXPRESSIONS

• CURSOR expression— An embedded or inline cursor inside a calling SQL statement:

  SELECT ...,CURSOR(<subquery>),... FROM ...;
  

• CASE expression— An embedded or inline CASE statement inside a SQL statement as a simple CASE statement:

  SELECT ... CASE expression WHEN condition THEN ...
      WHEN condition THEN ... ELSE ... END FROM table;
  

  Or a search CASE statement:

  SELECT ... CASE WHEN <condition> THEN ...
      WHEN <condition> THEN ... ELSE ... END FROM table;
  

FUNCTIONS

Functions can be used in a SELECT statement item selection list, WHERE and ORDER BY clauses, GROUP BY and HAVING clauses, CONNECT BY and START WITH clauses in hierarchical queries, plus INSERT VALUES and UPDATE SET clauses.

Single Row Functions

Execute an operation on each row of a query:

• Strings:

  • INSTR(<string>,<substring>[,<position>[,occurrence>]])

  • LENGTH(<string>)

  • INITCAP(<string>)

  • LOWER(<string>)

  • UPPER(<string>)

  • LPAD(<string>,<n>[,<replace>)

  • RPAD LPAD(<string>,<n>[,<replace>)

  • LTRIM(<string>,<string>)

  • RTRIM(<string>,<string>)

  • TRIM([[LEADING|TRAILING|BOTH] <character> FROM] <string>)

  • SUBSTR(<string>[,[-]<position> [,<n>]])

  • REPLACE(<string>,<search> [,<replace>])

• Numbers:

  • ABS(<n>)

  • POWER(<n>,<exponent>)

  • SQRT(<n>)

  • CEIL(<n>)

  • FLOOR(<n>)

  • ROUND(<n>,<decimal places>)

  • TRUNC(<n>,<decimal places>)

  • MOD(<numerator>,<denominator>)

  • SIGN(<n>)

• Datetime:

  • SYSDATE

  • CURRENT_DATE

  • CURRENT_TIMESTAMP(<precision>)

  • LOCALTIMESTAMP(<precision>)

  • NEXT_DAY(<date>,<weekday>)

  • LAST_DAY(<date>)

  • ADD_MONTHS(<date>,<months>)

  • MONTHS_BETWEEN(<date>,<date>)

  • EXTRACT(<format> FROM {<date>|<timestamp>)

  • ROUND(<date>[,<format>])

  • TRUNC(<date>[,<format>])

• Datatype conversions:

  • TO_NUMBER(<n>[,<format>])

  • TO_[N]CHAR(<string>[,<format>])

  • TO_DATE(<string>,<format>)

  • TO_[N]CHAR(<datetime>,<format>)

• Miscellaneous:

  • DECODE(<expression>,<search>, <replace>

    [,<search>,<replace>...],<default>)
    

    • NULLIF(<expression>,<expression>)

    • NVL(<expression>,<expression>)

    • NVL2(<expression>,<expression>,<expression>)

    • USER

    • USERENV(<parameter>)

    • GREATEST(<expression>[,<expression>...])

    • LEAST(<expression>[,<expression>...])

Aggregate Functions

Summarize repeating groups in a row set into distinct groups, creating values such as sums or averages:

• Simple summaries:

  • COUNT(*|[DISTINCT|ALL] <expression>)

  • AVG([DISTINCT|ALL] <expression>)

  • MIN([DISTINCT|ALL] <expression>)

  • MAX([DISTINCT|ALL] <expression>)

  • SUM([DISTINCT|ALL] <expression>)

• Simple statistics:

  • STDDEV([DISTINCT|ALL] <expression>)

  • VARIANCE([DISTINCT|ALL] <expression>)

  • CORR(<expression>,<expression>)

  • STDDEV_{POP|SAMP}(<expression>)

  • VAR_{POP|SAMP}(<expression>)

CHANGING DATA (DML)

INSERT INTO <table>[(<column_list>)]
    {VALUES(<expression_list>)|<subquery>};

• Conditional multiple table INSERT:

  INSERT WHEN <condition> THEN INTO <table1>
      WHEN <condition> THEN INTO <table2>
      ...
      WHEN <condition> THEN INTO <tablen>
  SELECT ... FROM ...;
  

• Non-conditional multiple table INSERT:

  INSERT ALL INTO <table1>(<column_list>) VALUES(<expression_list>)
      INTO <table2>(<column_list>) VALUES(<expression_list>)
      ...
      INTO <tablen>(<column_list>) VALUES(<expression_list>)
  SELECT ... FROM ...;
  

• UPDATE:

  UPDATE <table> SET <column>=<expression>|(<subquery>) [,...] [WHERE];
  

• DELETE:

  DELETE FROM <table> [WHERE];
  

• MERGE:

  MERGE INTO <target_table> t USING <source_table> s
      ON(s.<column>=t.<column> [AND|OR ...]
  WHEN MATCHED THEN UPDATE SET t.<column>=s.<column> ...
  WHEN NOT MATCHED THEN INSERT VALUES(<column list> ...);
  

  • Transactions are controlled by COMMIT (permanently store pending changes), ROLLBACK (undo pending changes), and SAVEPOINT (partial rollback to a SAVEPOINT label). The LOCK TABLE command can be used to temporarily lock tables. The SET TRANSACTION command can be used to control transaction behavior in general:

    SET TRANSACTION {READ ONLY|READ WRITE
        |ISOLATION LEVEL{SERIALIZABLE|READ COMMITTED}
        |USE ROLLBACK SEGMENT} [NAME '<transaction>'];