INTRODUCTION
Describe relational and object relational database designs
Review the system development life cycle & the relational database concept
RETRIEVING DATA
Define projection, selection, and join terminology
Use Arithmetic and Concatenation operators in SQL statements
Save SQL statements to script files
RESTRICTING & SORTING DATA
Limit rows using a selection
Use the LIKE condition to compare literal values
List the logical conditions AND, OR, NOT
Sort rows with the ORDER BY clause
USING SINGLE ROW FUNCTIONS
Explain and use the DATE, numeric and character functions
List the rules for applying the arithmetic operators on dates
REPORTING AGGREGATED DATA
Use the group functions
Create groups of data with the GROUP BY clause
Exclude groups of data with the HAVING clause
DISPLAYING DATA FROM MULTIPLE TABLES
Show the join tables syntax using SQL 99 syntax
List the types of outer joins LEFT, RIGHT, and FULL
USING SUB QUERIES
Describe the types of sub queries
Analyze how the ANY and ALL operators work
USING THE SET OPERATORS
Use the UNION & UNION ALL operators
Describe & use the INTERSECT operator
Explain & use the MINUS operator
MANIPULATING DATA
Write INSERT, UPDATE, & DELETE statements
Copy rows from another table
Save and discard changes
Describe the TRUNCATE statement
USING DDL STATEMENTS TO CREATE AND MANAGE TABLES
List the main database objects and describe the naming rules for database objects
Explain the different types of constraints
Create a table with a sub query
Describe the ALTER TABLE & DROP statement functionality
CREATING OTHER SCHEMA OBJECTS
MANAGING OBJECTS WITH DATA DICTIONARY VIEWS
Describe the structure & list the purpose of each of the dictionary views
Write queries that retrieve information from the dictionary views
CONTROLLING USER ACCESS
System versus objects privileges
Creating and granting privileges to a role
Granting and revoking object privileges
Changing your password
MANAGE SCHEMA OBJECTS
Creating directories & querying external tables
Creating Index Organized Tables, Function based indexes
Dropping Columns
Altering the structure of tables and adding constraints
Performing FLASHBACK Statement
MANIPULATING LARGE DATA SETS
Using the MERGE Statement
Performing DML with Subqueries
Performing DML with a RETURNING Clause
Overview of Multitable INSERT Statements
Tracking Changes in DML
GENERATING REPORTS BY GROUPING RELATED DATA
Aggregating data with ROLLUP and CUBE Operators
Compute multiple groupings with GROUPING SETS
Create combinations with Concatenated Groupings
MANAGING DATA IN DIFFERENT TIME ZONES
TIME ZONES
Oracle9i Date time Support
Conversion operations
SEARCHING DATA USING ADVANCED SUB QUERIES
Comparing several columns using Multiple-Column Sub queries
Defining a Data source Using a Sub query in the FROM Clause
Performing Correlated Sub queries
HIERARCHICAL DATA RETRIEVAL
Hierarchical Queries
Ranking Rows with LEVEL
Formatting Hierarchical Reports Using LEVEL and LPAD
Pruning Branches with the WHERE and CONNECT BY clauses
REGULAR EXPRESSION
Regular Expression Support Overview
Describing simple and complex patterns for searching and manipulating data