Oracle Certification Notes: Part VII

This is a continuation of my notes as I prepare for the Oracle Certified Associate credential:

SQL SELECT

projection - the ability to limit the columns returned within a result set

selection - the ability to limit the number of rows (or tuples) returned within a result set

joining - the ability to match against related tables (or relations or entities) as part of retrieving a result set

Alias Projection

aliases - a user-friendly tag may be applied to a projected column by providing an alias.

Aliases not enclosed within double-quotes will revert to uppercase. To keep case sensitivity, use double-quotes around the alias name.

The option AS tag may also be used before the alias name, as it is good SQL coding practice (although not required).

Quote (q) Operator: q'[delimiter][string][delimiter]'

Literal values used within SQL are enclosed by single quotes. (e.g., SELECT 'Y' FROM dual;)

For plural's, add an additional single quote (e.g., SELECT 'Y''s' FROM dual;)

For several plural's, use the quote (q) operator delimited with any character or bracket (e.g., SELECT q'!It's Joe's Rows!' FROM dual; or SELECT q'XIt's Joe's RowsX' FROM dual; or SELECT q'[It's Joe's Rows]' FROM dual;)

NULL

NULL is the absence of data

Anything arithmetically executed against NULL will result in NULL

A primary key cannot contain a NULL value; whereas, a foreign key may contain a NULL value.

Datatypes

NUMBER(x,y) - A number may have up to x-positions, where at least y-digits are required to the right of the decimal point. (e.g., NUMBER(8,2) contains valid numbers such as 123456.78, 12345.678 (resulting in 12345.68) & 123456 (resulting in 123456.00). But not 12345678 or 1234567.8.

DATE arithmetic results in a NUMBER value being returned. (e.g., END_DT - START_DT = NUMBER)

DATE minus another DATE represents the days BETWEEN (think inclusive) less one day (e.g., 12/31/2008 - 12/30/2008 is inclusively 2 days, and less one day = 1)

DATE formats: CC (2 digit century), RR (2 digit year where CC is calculated based on current year and specified year)

Posted via Woodland Blog (http://dramse01.blogspot.com)