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)

  • Leave a Comment

    Your email address will not be published. Required fields are marked *

    This site uses Akismet to reduce spam. Learn how your comment data is processed.