CERTguide Oracle 9i SQL 1Z0-007 Study Guide
What is the Oracle 9i SQL Exam all about?
1Z0-007 Introduction to Oracle9i SQL – this is an exam required to earn Oracle9i Certified Database Associate.
According to Oracle:
“The Oracle Certification Program begins with the Associate level. At this apprentice skill level, Oracle Certified Associates have a foundation of knowledge that will allow them to act as a junior team member working with database administrators or application developers. Earning your Oracle Certified Associate credential as a beginning IT professional will give you recognition for your foundation of knowledge using Oracle technologies. This recognition will differentiate you from those without any credentials, and can help you build your career by obtaining access to entry-level opportunities. To become an Oracle Certified Associate, you must pass the exams required for the Oracle Certified Associate level of your selected job role. Typically, two exams are required; the first one can be taken via the Internet, while subsequent exams must be taken in a proctored environment.”
Note that this exam is an online based exam. You do not need to go to any Prometric testing centre for registration. Instead, you can register with Oracle directly:
“ The new exam is un-proctored, meaning that you can take the exam unsupervised at your own location. If you do not have good internet access, proctored exams are delivered in a secure testing environment, at an Oracle University Training Center or Authorized Prometric Testing Center.”
Know the specifications:
This exam focuses on SQL in general under 9i, not PL/SQL specific elements. PL/SQL is an Oracle extension of the SQL statement set which allows the developer to impose flow control and logic design onto unstructured SQL command blocks. PL/SQL also implements basic exception handling. Somehow PL/SQL is not being emphasized by Oracle in the OCA track.
What is SQL?
Oracle describes Structured Query Language (SQL) as the set of statements with which all programs and users access data in an Oracle database. Application programs and Oracle tools often allow users access to the database without using SQL directly, but these applications in turn must use SQL when executing the user's request.
Note that the official SQL standard is maintained by ANSI. Regarding Oracle’s SQL compatibility with ANSI SQL (The latest SQL standard was adopted in July 1999 and is often called SQL:99), refer to what Oracle says here:
"Oracle Corporation strives to comply with industry-accepted standards and participates actively in SQL standards committees. Industry-accepted committees are the American National Standards Institute (ANSI) and the International Standards Organization (ISO), which is affiliated with the International Electrotechnical Commission (IEC). Both ANSI and the ISO/IEC have accepted SQL as the standard language for relational databases. When a new SQL standard is simultaneously published by these organizations, the names of the standards conform to conventions used by the organization, but the standards are technically identical.”
For a discussion on ANSI SQL vs Oracle SQL, visit http://download-west.oracle.com/otndoc/oracle9i/901_doc/server.901/a90125/ap_standard_sql.htm#4963
The features of SQL include:
n It processes sets of data as groups rather than as individual units.
n It provides automatic navigation to the data.
n It uses statements that are complex and powerful individually.
With SQL, you can achieve the following:
n Querying data
n Inserting, updating, and deleting rows in a table
n Creating, replacing, altering, and dropping objects
n Controlling access to the database and its objects
n Guaranteeing database consistency and integrity
Types of SQL Statements
n Data Definition Language (DDL) Statements
n Data Manipulation Language (DML) Statements
n Transaction Control Statements
n Session Control Statements
n System Control Statements
DDL statements
n Create, alter, and drop schema objects
n Grant and revoke privileges and roles
n Analyze information on a table, index, or cluster
n Establish auditing options
n Add comments to the data dictionary
DML statements
n query and manipulate data in existing schema objects
n do not implicitly commit the current transaction.
n Statements include:
u CALL
u DELETE
u EXPLAIN PLAN
u INSERT
u LOCK TABLE
u MERGE
u SELECT
u UPDATE
Transaction Control Statements
n manage changes made by DML statements.
n Statements include:
u COMMIT
u ROLLBACK
u SAVEPOINT
u SET TRANSACTION
Session Control Statements
n dynamically manage the properties of a user session
n do not implicitly commit the current transaction.
n PL/SQL does not support session control statements
n Statements include:
u ALTER SESSION
u SET ROLE
System Control Statement
n dynamically manages the properties of an Oracle instance
n does not implicitly commit the current transaction.
n Statements include:
u ALTER SYSTEM
The elements of SQL – Operators
n manipulate individual data items - operands or arguments
n Operators - represented by special characters or by keywords
n two general classes of operators are:
n unary - operates on only one operand
n binary - operates on two operands
n Precedence is the order in which Oracle evaluates different operators in the same expression
n When evaluating an expression containing multiple operators, Oracle evaluates operators with higher precedence before evaluating those with lower precedence.
The elements of SQL – Expressions
n combination of one or more values, operators, and SQL functions
n always evaluate to a value
n generally assumes the datatype of its components.
n You can use expressions in:
u The select list of the SELECT statement
u A condition of the WHERE clause and HAVING clause
u The CONNECT BY, START WITH, and ORDER BY clauses
u The VALUES clause of the INSERT statement
u The SET clause of the UPDATE statement
The elements of SQL – Conditions
n combines one or more expressions and logical operators
n returns a value of:
u TRUE
u FALSE
u unknown.
n You can use a condition in the WHERE clause of these statements:
u DELETE
u SELECT
u UPDATE
n You can use a condition in any of these clauses of the SELECT statement:
u WHERE
u START WITH
u CONNECT BY
u HAVING
n Logical conditions can combine multiple conditions into a single condition.
n Precedence is the order in which Oracle evaluates different conditions in the same expression.
n When evaluating an expression containing multiple conditions, Oracle evaluates conditions with higher precedence before evaluating those with lower precedence.
The elements of SQL – SQL Functions
n built into Oracle
n available for use in various appropriate SQL statements
n when you call a SQL function with an argument of a datatype other than the datatype expected by the SQL function, Oracle implicitly converts the argument to the expected datatype before performing the SQL function
n when you call a SQL function with a null argument, the SQL function automatically returns null
n detailed information about the various Oracle functions is available at
http://download-west.oracle.com/otndoc/oracle9i/901_doc/server.901/a90125/functions2.htm#80856
The elements of SQL – Queries
n an operation that retrieves data from one or more tables or views
n top-level SELECT statement is called a query
n query nested within another SQL statement is called a subquery.
n subqueries should be used for the following purposes:
u To define the set of rows to be inserted into the target table of an INSERT or CREATE TABLE statement
u To define the set of rows to be included in a view or materialized view in a CREATE VIEW or CREATE MATERIALIZED VIEW statement
u To define one or more values to be assigned to existing rows in an UPDATE statement
u To provide values for conditions in a WHERE clause, HAVING clause, or START WITH clause of SELECT, UPDATE, and DELETE statements
u To define a table to be operated on by a containing query.
n list of expressions that appears after the SELECT keyword and before the FROM clause is called the select list
n Within the select list you specify one or more columns in the set of rows you want Oracle to return from one or more tables, views, or materialized views.
n The number of columns and their datatype and length are all determined by the elements of the select list.
n If multiple tables have some column names in common, you must qualify column names with names of tables.
n You can use a column alias, c_alias, to label the preceding expression in the select list so that the column is displayed with a new heading.
n You can use comments in a SELECT statement to pass instructions, or hints, to the Oracle optimizer.
n SQL optimizer uses hints to choose an execution plan for the statement.
n correlated queries – According to Oracle:
“Oracle performs a correlated subquery when the subquery references a column from a table referred to in the parent statement. A correlated subquery is evaluated once for each row processed by the parent statement. The parent statement can be a SELECT, UPDATE, or DELETE statement. A correlated subquery answers a multiple-part question whose answer depends on the value in each row processed by the parent statement. For example, you can use a correlated subquery to determine which employees earn more than the average salaries for their departments. In this case, the correlated subquery specifically computes the average salary for each department.” http://download-west.oracle.com/otndoc/oracle9i/901_doc/server.901/a90125/queries2.htm#2053895
Joins
n a query that combines rows from two or more tables, views, or materialized views.
n a join is performed whenever multiple tables appear in the query's FROM clause
n if any two of the joined tables have a column name in common, you must qualify all references to these columns throughout the query with table names
n WHERE clause conditions that compare two columns is called a join condition.
n WHERE clause of a join query can contain other conditions that refer to columns of only one table
n You cannot specify LOB columns in the WHERE clause if the WHERE clause contains any joins
n Equijoins
u a join with a join condition containing an equality operator
u combines rows that have equivalent values for the specified columns
n Self Joins
u a join of a table to itself
u table appears twice in the FROM clause and is followed by table aliases that qualify column names in the join condition
n Cartesian Products
u If two tables in a join query have no join condition, Oracle returns their Cartesian product - combines each row of one table with each row of the other
u always generates many rows
u rarely useful
n Inner Joins
u also called a simple join
u a join of two or more tables that returns only those rows that satisfy the join condition.
n Outer Joins
u extends the result of a simple join
u returns all rows that satisfy the join condition
u also returns some or all of those rows from one table for which no rows from the other satisfy the join condition.
Learn
SQL is a 4GL. It requires hands on practice!
To help you master the basics of this language, I strongly recommend the “A Gentle Introduction to SQL” site at http://sqlzoo.net//
Another site full of SQL tutorial resources is the “SQL Tutorial” site at http://www.1keydata.com/sql/sql.html :
Practice
Although you don’t have to practice SQL strictly under Oracle, it is best that you download an eval copy of 9i, install and then play with it. The Oracle download center is at http://otn.oracle.com/software/content.html . You may practice on the 9i Personal edition.
Reference Books
SQL: The Complete Reference -- by James R. Groff, Paul N. Weinberg; Paperback
Mastering Oracle SQL -- by Sanjay Mishra, Alan Beaulieu; Paperback
OCA/OCP: Introduction to Oracle9i SQL Study Guide -- by Chip Dawes, Biju Thomas; Hardcover
Oracle Sql Plus: The Definitive Guide
by Jonathan Gennick, et al (Paperback - March 1999)
This study guide is developed by Michael Yu Chak Tin. He can be reached at Michael@examreview.net.