Oracle DBA cert exam details

Oracle  9i OCA  DBA certification is for  IT professionals who have a foundation of knowledge that will allow them to act as a  team member working with database administrators and application developers. The Oracle 9i OCA was introduced in late 2001 and will likely stay very popular until the arrival of the Oracle 10g certification. There is no specific prerequisite for this certification; however, having some hands on experience with  SQL and  Oracle or other competitor database would definitely help.

This certification is well suited to IT personnel, with titles such as database administrator, database analyst, and database developer. The Oracle 9i OCA designation is achieved by passing two exams. If you choose the newer version of one of the exams, you must take itonline.

Although Oracle certification paths eventually retire, you may continue to use a retired designation as it denotes the version of Oracle it is associated with. Also, Oracle normally provides a one exam upgrade to a newer version of a particular certification.
Helpful: Compare Oracle9i DBA OCA with other certifications
Search: Find Articles for Oracle9i DBA OCA

Restricting and Sorting Data

WHERE Clause –

Comparison Operators;

Logical Operator;

Using the Logical Operator, AND;

Using the logical Operator, OR;

Using the Logical Operator, NOT;

Nesting Logical Operators;

Other Comparison Keywords;

Using the Keyword, LIKE;

Using the Keywords, BETWEEN...AND;

Using the Keyword, IN;

Using the Keyword, IS NULL

ORDER BY Clause - Using the ORDER BY Clause

Logical Operators

There are six logical operators in SQL, and after introducing them, we'll see how they're used:

=	Equal
<> or != (see manual)	Not Equal
<	Less Than
>	Greater Than
<=	Less Than or Equal To
>=	Greater Than or Equal To

The WHERE clause is used to specify that only certain rows of the table are displayed, based on the criteria described in that WHERE clause. It is most easily understood by looking at a couple of examples.

If you wanted to see the EMPLOYEEIDNO's of those making at or over $50,000, use the following:

SELECT EMPLOYEEIDNO
FROM EMPLOYEESTATISTICSTABLE
WHERE SALARY >= 50000;

Notice that the >= (greater than or equal to) sign is used, as we wanted to see those who made greater than $50,000, or equal to $50,000, listed together. This displays:

EMPLOYEEIDNO
------------
010
105
152
215
244

The WHERE description, SALARY >= 50000, is known as a condition. The same can be done for text columns:

SELECT EMPLOYEEIDNO
FROM EMPLOYEESTATISTICSTABLE
WHERE POSITION = 'Manager';

This displays the ID Numbers of all Managers. Generally, with text columns, stick to equal to or not equal to conditions, and make sure that any text the appears in the statement is surrounded by single quotes (').

More Complex Conditions: Compound Conditions

The AND operator joins two or more conditions, and displays a row only if that row's data satisfies ALL conditions listed (i.e. all conditions hold true). For example, to display all staff making over $40,000, use:

SELECT EMPLOYEEIDNO
FROM EMPLOYEESTATISTICSTABLE
WHERE SALARY > 40000 AND POSITION = 'Staff';

The OR operator joins two or more conditions, but returns a row if ANY of the conditions listed hold true. To see all those who make less than $40,000 or have less than $10,000 in benefits, listed together, use the following query:

SELECT EMPLOYEEIDNO
FROM EMPLOYEESTATISTICSTABLE
WHERE SALARY <>

AND & OR can be combined, for example:

SELECT EMPLOYEEIDNO
FROM EMPLOYEESTATISTICSTABLE
WHERE POSITION = 'Manager' AND SALARY > 60000 OR BENEFITS > 12000;

First, SQL finds the rows where the salary is greater than $60,000 or the benefits is greaterthan $12,000, then taking this new list of rows, SQL then sees if any of these rows satisfies the condition that the Position column if equal to 'Manager'. Subsequently, SQL only displays this second new list of rows, as the AND operator forces SQL to only display such rows satisfying the Position column condition. Also note that the OR operation is done first.

To generalize this process, SQL performs the OR operation(s) to determine the rows where the OR operation(s) hold true (remember: any one of the conditions is true), then these results are used to compare with the AND conditions, and only display those remaining rows where the conditions joined by the AND operator hold true.

To perform AND's before OR's, like if you wanted to see a list of managers or anyone making a large salary (>$50,000) and a large benefit package (>$10,000), whether he or she is or is not a manager, use parentheses:

SELECT EMPLOYEEIDNO
FROM EMPLOYEESTATISTICSTABLE
WHERE POSITION = 'Manager' OR (SALARY > 50000 AND BENEFIT > 10000);

IN & BETWEEN

An easier method of using compound conditions uses IN or BETWEEN. For example, if you wanted to list all managers and staff:

SELECT EMPLOYEEIDNO
FROM EMPLOYEESTATISTICSTABLE
WHERE POSITION IN ('Manager', 'Staff');

or to list those making greater than or equal to $30,000, but less than or equal to $50,000, use:

SELECT EMPLOYEEIDNO
FROM EMPLOYEESTATISTICSTABLE
WHERE SALARY BETWEEN 30000 AND 50000;

To list everyone not in this range, try:

SELECT EMPLOYEEIDNO
FROM EMPLOYEESTATISTICSTABLE
WHERE SALARY NOT BETWEEN 30000 AND 50000;

Similarly, NOT IN lists all rows excluded from the IN list.

Using LIKE

Look at the EmployeeStatisticsTable, and say you wanted to see all people whose last names started with "L"; try:

SELECT EMPLOYEEIDNO
FROM EMPLOYEEADDRESSTABLE
WHERE LASTNAME LIKE 'L%';

SQL: Single Row Functions

Single Row Functions

List of Single row functions in Oracle

A)Numeric Functions
ABS
ACOS
ASIN
ATAN
ATAN2
BITAND
CEIL
COS
COSH
EXP
FLOOR
LN
LOG
MOD
NANVL
POWER
REMAINDER
ROUND (number)
SIGN
SIN
SINH
SQRT
TAN
TANH
TRUNC (number)
WIDTH_BUCKET


B)Character Functions that return Character Values
CHR
CONCAT
INITCAP
LOWER
LPAD
LTRIM
NLS_INITCAP
NLS_LOWER
NLSSORT
NLS_UPPER
REGEXP_REPLACE
REGEXP_SUBSTR
REPLACE
RPAD
RTRIM
SOUNDEX
SUBSTR
TRANSLATE
TREAT
TRIM
UPPER


C)NLS Character Function that return information about character set
NLS_CHARSET_DECL_LEN
NLS_CHARSET_ID
NLS_CHARSET_NAME

D)Character Functions that return Number Value
ASCII
INSTR
LENGTH
REGEXP_INSTR

E)Datetime Functions
ADD_MONTHS
CURRENT_DATE
CURRENT_TIMESTAMP
DBTIMEZONE
EXTRACT (datetime)
FROM_TZ
LAST_DAY
LOCALTIMESTAMP
MONTHS_BETWEEN
NEW_TIME
NEXT_DAY
NUMTODSINTERVAL
NUMTOYMINTERVAL
ROUND (date)
SESSIONTIMEZONE
SYS_EXTRACT_UTC
SYSDATE
SYSTIMESTAMP
TO_CHAR (datetime)
TO_TIMESTAMP
TO_TIMESTAMP_TZ
TO_DSINTERVAL
TO_YMINTERVAL
TRUNC (date)
TZ_OFFSET

F)General Comparison Functions
GREATEST
LEAST

G)Conversion Functions
ASCIISTR
BIN_TO_NUM
CAST
CHARTOROWID
COMPOSE
CONVERT
DECOMPOSE
HEXTORAW
NUMTODSINTERVAL
NUMTOYMINTERVAL
RAWTOHEX
RAWTONHEX
ROWIDTOCHAR
ROWIDTONCHAR
SCN_TO_TIMESTAMP
TIMESTAMP_TO_SCN
TO_BINARY_DOUBLE
TO_BINARY_FLOAT
TO_CHAR (character)
TO_CHAR (datetime)
TO_CHAR (number)
TO_CLOB
TO_DATE
TO_DSINTERVAL
TO_LOB
TO_MULTI_BYTE
TO_NCHAR (character)
TO_NCHAR (datetime)
TO_NCHAR (number)
TO_NCLOB
TO_NUMBER
TO_DSINTERVAL
TO_SINGLE_BYTE
TO_TIMESTAMP
TO_TIMESTAMP_TZ
TO_YMINTERVAL
TO_YMINTERVAL
TRANSLATE ... USING
UNISTR

H)Large Object Functions
BFILENAME
EMPTY_BLOB
EMPTY_CLOB

I)Collection Functions
CARDINALITY
COLLECT
POWERMULTISET
POWERMULTISET_BY_CARDINALITY
SET


J)Hierarchical Function
SYS_CONNECT_BY_PATH


K)Data Mining Functions
CLUSTER_ID
CLUSTER_PROBABILITY
CLUSTER_SET
FEATURE_ID
FEATURE_SET
FEATURE_VALUE
PREDICTION
PREDICTION_COST
PREDICTION_DETAILS
PREDICTION_PROBABILITY
PREDICTION_SET


L)XML Functions
APPENDCHILDXML
DELETEXML
DEPTH
EXTRACT (XML)
EXISTSNODE
EXTRACTVALUE
INSERTCHILDXML
INSERTXMLBEFORE
PATH
SYS_DBURIGEN
SYS_XMLAGG
SYS_XMLGEN
UPDATEXML
XMLAGG
XMLCDATA
XMLCOLATTVAL
XMLCOMMENT
XMLCONCAT
XMLFOREST
XMLPARSE
XMLPI
XMLQUERY
XMLROOT
XMLSEQUENCE
XMLSERIALIZE
XMLTABLE
XMLTRANSFORM

M)Encoding and Decoding Functions
DECODE
DUMP
ORA_HASH
VSIZE


N)NULL-Related Functions
COALESCE
LNNVL
NULLIF
NVL
NVL2

O)Environment and Identifier Functions
SYS_CONTEXT
SYS_GUID
SYS_TYPEID
UID
USER
USERENV

•