This is a continuation of my notes as I prepare for the Oracle Certified Associate credential:
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
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 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.
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)