Oracle Certification Notes: Part VIII

A continuation of my notes as I study for the Oracle Certified Associate credential:

Comparison Operators

For character and date comparisons, each side of the comparison operator is converted into its numeric representation, and then compared. The numeric value is based on the database characterset.

!= and <> are equivalent inequality operators

LIKE command has two wildcards: % for 0 or more characters; _ for 1 character.

escape command can be used for Oracle to interpret literal value and not a function (e.g., select val from test_table where val like 'PA\%' escape '\';)

AND operator returns NULL only for the situation in which all conditions are NULL, or at least one of the statements are TRUE. (A FALSE and NULL comparison in the AND operator results in FALSE)

OR operator returns NULL only for the situation in which all conditions are NULL, or at least one of the statements is FALSE. (A TRUE or NULL comparison in the OR operator results in TRUE)

NOT condition against a NULL returns a NULL. (Another use of NOT, SELECT 'Y' FROM dual WHERE NOT ('Hello World' = 'Hello World!');)

ORDER BY

ORDER BY has the NULLS FIRST LAST option

NULL columns have the highest value (i.e., ORDER BY DESC will naturally return NULLs first, unless otherwise specified)

Unless positional sorting (e.g., ORDER BY 1) is used, ORDER BY statements are independent of the SQL projection)

SUBSTITUTION

Reading Oracle documentation, any options in italics means it can be substituted (using an ampersand '&')

Ampersand (&) substitution is executed at runtime and referred to as runtime binding or runtime substitution

Single ampersand substitution prompts the user for a value each time

Double ampersand substitution the first time prompts the user and retains the specified value for single ampersand references throughout the remaining session.

SESSION VARIABLES

UNDEFINE [variable]; command removes a variable and its value from the session.

DEFINE command can list all variables that have been defined within a session (i.e., DEFINE;).

DEFINE [variable]; command can create persistence for a particular variable throughout a session.(e.g., DEFINE v_name='JOE';).

SET DEFINE ON | OFF determines whether session variables can be created, as well as whether ampersand (&) is used as a literal or substitution character.

SET VERIFY ON | OFF allows the user to view the substitution changes (old vs. new) within the client window.

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