Introduction
o List the Oracle Database 10g Main Features
o An Overview of: components, internet platform, apps server and developer suite
o Describe Relational and Object Relational Database Designs
o Review the System Development Life Cycle
o Define the term Data Models
o Describe different means of Sorting Data
o Show how Multiple Tables can be related
o Describe how SQL Communicates to the Database
Writing SQL SELECT Statements
o Define projection, selection, and join terminology
o Review the basic SQL SELECT statement syntax
o Select all columns using a wildcard notation from a table
o State simple rules and guidelines for writing SQL statements
o Write a query containing the arithmetic operators
o Create a character expression with the concatenation operator
o Using the Oracle SQL Developer Environment
Restricting and Sorting Data
o Limit rows using a selection
o Using the WHERE clause to retrieve specific rows
o Using the comparison conditions in the WHERE clause
o Use the LIKE condition to compare literal values
o List the logical conditions AND, OR, NOT
o Describe the rules of precedence for the conditions
o Sort rows with the ORDER BY clause
o Use ampersand substitution in SQL*Plus to restrict and sort output at run time
Using Single-Row Functions to Customize Output
o Show the differences between single row and multiple row SQL functions
o Categorize the character functions into case manipulation and character manipulation types
o Use the character manipulation functions in the SELECT and WHERE clauses
o Explain and use the DATE and numeric functions
o Use the SYSDATE function to retrieve the current date in the default format
o Introduce the DUAL table as a means to view function results
o List the rules for applying the arithmetic operators on dates
o Use the arithmetic operators with dates in the SELECT clause
Reporting Aggregated Data Using the Group Functions
o Describe and categorize the group functions
o Use the group functions
o Utilize the DISTINCT keyword with the group functions
o Describe how nulls are handled with the group functions
o Create groups of data with the GROUP BY clause
o Group data by more than one column
o Avoid illegal queries with the group functions
o Exclude groups of data with the HAVING clause
Displaying Data from Multiple Tables
o Identify Types of Joins
o Retrieve Records with Natural Joins
o Use Table Aliases to write shorter code and explicitly identify columns from multiple tables
o Create a Join with the USING clause to identify specific columns between tables
o Use the ON clause to specify arbitrary conditions or specify columns to Join
o Create a Three-way join with the ON clause to retrieve information from 3 tables
o List the Types of Outer Joins LEFT, RIGHT, and FULL
o Generating a Cartesian Product
Using Sub queries to Solve Queries
o List the syntax for sub queries in a SELECT statements WHERE clause
o List the guidelines for using sub queries
o Describe the types of sub queries
o Execute single row sub queries and use the group functions in a sub query
o Identify illegal statements with sub queries
o Execute multiple row sub queries
o Analyze how the ANY and ALL operators work in multiple row sub queries
Using the SET Operators
o Use the UNION operator to return all rows from multiple tables and eliminate any duplicate rows
o Use the UNION ALL operator to return all rows from multiple tables
o Describe the INTERSECT operator
o Use the INTERSECT operator
o Explain the MINUS operator
o Use the MINUS operator
o List the SET operator guidelines
o Order results when using the UNION operator
Manipulating Data
o Write INSERT statements to add rows to a table
o Copy rows from another table
o Create UPDATE statements to change data in a table
o Generate DELETE statements to remove rows from a table
o Use a script to manipulate data
o Save and discard changes to a table through transaction processing
o Show how read consistency works
o Describe the TRUNCATE statement
Using DDL Statements to Create and Manage Tables
o List the main database objects and describe the naming rules for database objects
o Introduce the schema concept
o Display the basic syntax for creating a table and show the DEFAULT option
o Explain the different types of constraints
o Show resulting exceptions when constraints are violated with DML statements
o Create a table with a sub query
o Describe the ALTER TABLE functionality
o Remove a table with the DROP statement and Rename a table
Creating Other Schema Objects
o Categorize simple and complex views and compare them
o Create a view
o Retrieve data from a view
o Explain a read-only view
o List the rules for performing DML on complex views
o Create a sequence
o List the basic rules for when to create and not create an index
o Create a synonym
Managing Objects with Data Dictionary Views
o Describe the structure of each of the dictionary views
o List the purpose of each of the dictionary views
o Write queries that retrieve information from the dictionary views on the schema objects
o Use the COMMENT command to document objects
Controlling User Access
o Controlling User Access
o System versus Objects Privileges
o Using Roles to define user groups
o Changing Your Password
o Granting Object Privileges
o Confirming Privileges Granted
o Revoking Object Privileges
o Using Database Links
Manage Schema Objects
o Using the ALTER TABLE statement
o Adding a Column
o Modifying a Column
o Dropping a Column, Set Column UNUSED
o Adding, Enabling and Disabling Constraints
o Creating Function-Based Indexes
o Performing FLASHBACK operations
o External Tables
Manipulating Large Data Sets
o Using the MERGE Statement
o Performing DML with Sub queries
o Performing DML with a RETURNING Clause
o Overview of Multi-table INSERT Statements
o Tracking Changes in DML
Generating Reports by Grouping Related Data
o Overview of GROUP BY Clause
o Overview of Having Clause
o Aggregating data with ROLLUP and CUBE Operators
o Determine subtotal groups using GROUPING Functions
o Compute multiple groupings with GROUPING SETS
o Define levels of aggregation with Composite Columns
o Create combinations with Concatenated Groupings
Managing Data in Different Time Zones
o Time Zones
o Using date and time functions
o Identifying TIMESTAMP Data Types
o Differentiating between DATE and TIMESTAMP
o Performing Conversion Operations
Searching Data Using Advanced Sub queries
o Sub query Overview
o Using a Sub query
o Comparing several columns using Multiple-Column Sub queries
o Defining a Data source Using a Sub query in the FROM Clause
o Returning one Value using Scalar Sub query Expressions
o Performing ROW by-row processing with Correlated Sub queries
o Reusing query blocks using the WITH Clause
Hierarchical Retrieval
o Sample Data from the EMPLOYEES Table
o The Tree Structure of Employee data
o Hierarchical Queries
o Ranking Rows with LEVEL
o Formatting Hierarchical Reports Using LEVEL and LPAD
o Pruning Branches with the WHERE and CONNECT BY clauses
Regular Expression Support
o Regular Expression Support Overview
o Describing simple and complex patterns for searching and manipulating data
