This Cramsession prepares you for the Oracle 9i certification exam #1Z0-007, "Introduction to Oracle9i: SQL." Passing this exam, plus one other test, makes you an Oracle9i Certified Database Associate (an “OCA”).

This exam covers the SQL language in depth. Test topics include SQL language elements (operators, precedence, notation, and syntax), SELECT statements (keywords, selection, sorting, joins, subqueries, and CASE expressions), functions (single-row and group), updating data (INSERT, UPDATE, DELETE, and MERGE statements), creating common objects (including tables, views, indexes, clusters, sequences, and synonyms), the security system (user accounts, privileges, profiles, and roles), and using SQL*Plus (formatting output, scripts, and commands).

You need to know the SQL language well to pass this exam. This includes operators, syntax, notation, special characters, and the like.

The SQL covered is that typically used by developers in their database work. This includes DML statements (SELECT, INSERT, UPDATE, DELETE, MERGE), transaction control statements (COMMIT, ROLLBACK, SET TRANSACTION), and the DDL statements that manage high-level objects (CREATE / ALTER / DROP TABLE, INDEX, VIEW, SYNONYM, SEQUENCE, CLUSTER).

The test does not cover the physical SQL statements used by DBAs (CREATE / ALTER / DROP DATABASE, TABLESPACE, partitioned tablespaces, or detailed TABLE storage parameters).

Know the SELECT statement, all its keywords and options, very well. Understand Joins and how to perform them. Given a Join statement, be able to determine the result set.

Know both the traditional Oracle syntax for Joins and also the new ANSI-compliant syntax that Oracle9I introduces.

Given a SELECT having a subquery, be able to determine its result set. Also know why one use subqueries.

Understand the common uses of the INSERT, UPDATE, DELETE, and MERGE statements and their syntax.

Know about transactions, how they are used and why they are important. Know why you issue COMMIT and ROLLBACK statements.

Know the difference between single-row and group functions. The exam covers functions in more detail than previously so this Cramsession lists all Oracle9I functions.

Know how to create and alter tables, views, indexes, synonyms, clusters, and sequences and know what each object is used for.

Know Oracle9I's datatypes and how you declare them when creating tables.

Understand Oracle’s security system. Know what user accounts, roles, and profiles are. Know how privileges are assigned or withdrawn from users and roles and know how you use the GRANT and REVOKE statements.

Since SQL*Plus is the basic interface to Oracle, you must understand its use and commands. Know how to format report output and know about scripts and how are they saved, edited, and executed.