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
