The eLearning Center for Gatlin Education Services

This series is designed to help those who have some experience with relational databases to prepare for the Oracle9i 1Z0-007 exam.

Introduction to SQL
Time: 3.0 hour(s)
Summary:
This course provides an introduction to the Oracle9i relational database management system and basic database concepts.
Objectives:
* Understand basic database concepts.
* List new SQL features in Oracle9i.
* Use the four available SQL Plus tools.
* Enter and execute basic queries using the SELECT command.
Topics:
* Basic database concepts
* SQL Plus command line
* SQL Plus window
* SQL Plus worksheet
* iSQL Plus
* Using the SELECT statement
* Writing SQL commands
* Writing queries

Intermediate Queries
Time: 2.0 hour(s)
Summary:
This course provides information about using the SELECT statement and using the WHERE and ORDER BY clauses in Oracle9i SQL.
Objectives:
* Use the WHERE clause to filter out rows from query results.
* Format query results.
* Use multiple filters in a query.
* Query the DUAL table.
* Sort query results using the ORDER BY clause.
* Handle null values in query results.
Topics:
* Using the WHERE clause
* Formatting query results
* Using multiple filters
* Dual and pseudocolumns
* Using the ORDER BY clause
* Sorting rules for null values

Queries with Single-Row Functions
Time: 2.0 hour(s)
Summary:
This course examines the types and uses of functions available for various data types in Oracle9i SQL.
Objectives:
* Describe the most commonly-used functions available in SQL.
* Use character, number, and date functions in SELECT statements.
* Use conversion functions.
* Find online documentation for more information about functions as needed.
Topics:
* String functions
* Number functions
* Date functions
* Miscellaneous functions
* Conversion functions
* Combining functions

Combining Tables
Time: 2.0 hour(s)
Summary:
This course examines joining tables in Oracle9i SQL using the ANSI format JOIN clause and the Oracle proprietary format.
Objectives:
Write SELECT statements to access data from more than one table using equality and nonequality joins.
View data that generally does not meet a join condition by using outer joins.
Join a table to itself using a self join.
Create various types of join queries.
Identify the differences between Oracle proprietary format and ANSI format for joining tables.
Topics:
* Joining tables
* Cross joins
* Natural joins
* The USING and the ON clause
* Outer joins
* Left outer joins
* Right outer joins
* Self joins
* Other types of joins

Grouping and Summarizing Data
Time: 1.0 hour(s)
Summary:
This course examines the summarizing of query results into groups using the GROUP BY clause in Oracle9i SQL.
Objectives:
* Explain how group functions work.
* Use aggregate functions.
* Use DISTINCT and ALL clauses.
* Group and sort rows using the GROUP BY clause.
* Use the HAVING clause to restrict data.
* Use the ROLLUP, CUBE, and GROUPING SETS clauses.
Topics:
* The GROUP BY clause and group functions
* Aggregate functions
* Grouping rows and sorting with GROUP BY
* Restricting Data
* Extending the GROUP BY clause

Advanced Queries and Views
Time: 2.0 hour(s)
Summary:
This course provides an in-depth look at using subqueries and views in Oracle9i SQL.
Objectives:
* List the types of subqueries.
* Use subqueries in Oracle SQL commands.
* Add and drop views.
* Change views.
* Modify data using views.
Topics:
* Subqueries
* Types and uses of views
* Simple, constraint and complex views
* Changing and dropping views
* Working with views
* Inserting, updating and deleting in views

Working with Data
Time: 2.0 hour(s)
Summary:
This course examines the four main commands used in SQL to add, modify, remove, and merge rows of data in database tables.
Objectives:
* Distinguish between Oracle9i and user-defined datatypes.
* Use the four Oracle9i DML commands INSERT, UPDATE, DELETE, and MERGE.
* Understand transactions and the commands used to manipulate their behavior.
Topics:
* Datatypes
* Data Manipulation Language
* Using transactions
* Adding and changing data
* Deleting and merging data

Creating Tables and Constraints
Time: 2.0 hour(s)
Summary:
This course examines creating and changing tables and defining constraints on tables in Oracle9i SQL.
Objectives:
* Create, modify, rebuild, and remove tables.
* Use the TRUNCATE command.
* Create, modify and drop constraints.
* Use primary key constraints.
* Use foreign key contraints.
* Add comments to SQL scripts and schema objects.
Topics:
* Creating tables
* Relational and temporary tables
* Adding comments
* Changing table structures
* Renaming, truncating and dropping tables
* Constraints
* Primary key, unique and foreign key constraints
* Table level and check constraints
* Adding, modifying and dropping constraints

Database Objects and User Security
Time: 4.0 hour(s)
Summary:
This course examines how to create database objects including sequences, synonyms, and indexes in Oracle9i SQL. It also covers sharing table data with other users through privileges and roles.
Objectives:
* Create, modify, and delete sequences.
* Create, modify, and delete synonyms.
* Create, modify, and delete indexes.
* Create, modify, and delete users.
* Grant and revoke system and object privileges.
* Create, grant, revoke, enable, disable, and delete roles.
Topics:
* Working with sequences
* Working with synonyms
* Understanding indexes
* Working with indexes
* Understanding users
* Working with users
* Granting privileges
* Revoking privileges
* Creating, granting, and revoking roles
* Enabling, disabling, and dropping roles

SQL*Plus And iSQL*Plus Reporting and PL/SQL
Time: 3.0 hour(s)
Summary:
This course examines how to use environment settings, variables, and special SQL Plus commands to generate professional looking reports in Oracle9i SQL.
Objectives:
* Create a well formatted report with SQL Plus.
* Know when and how to use variables.
* Use iSQL Plus.
* Use Top-N Analysis.
* Identify the basic concepts of PL/SQL.
Topics:
* SQL Plus reports
* Environment settings
* Using environment settings
* Variables and scripts
* Generating reports
* Reporting features
* Running reports
* Top-N queries
* PL/SQL
* Retrieving data in PL/SQL
* Dynamic SQL
* Control structures
* Iteration and repitition

Practice Exam
Time: 1.0 hour(s)
Summary:
This course provides questions to prepare users who plan to take exam 1Z0-007: Introduction to Oracle9i: SQL.
Objectives:
* Answer practice questions in the format of the 1Z0-007 Introduction to Oracle 9i SQL exam.
* Assess readiness to take the exam and identify topic areas requiring further practice.
Topics:
* SQL Plus reports
* Writing basic SQL select statements
* Restricting and sorting data
* Single-row functions
* Displaying data from multiple tables
* Aggregating data using group functions
* Subqueries
* Producing readable output with iSQL Plus
* Manipulating data
* Creating and managing tables
* Including constraints
* Creating views
* Creating other database objects
* Controlling user access