Introduction to SQL
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
Introduction to PL/SQL
o What is PL/SQL
o PL/SQL Environment
o Benefits of PL/SQL
o Overview of the Types of PL/SQL blocks
o Create and Execute a Simple Anonymous Block
o Generate Output from a PL/SQL Block
o SQL*Plus as PL/SQL Programming Environment
Declaring PL/SQL Identifiers
o Identify the Different Types of Identifiers in a PL/SQL subprogram
o Use the Declarative Section to Define Identifiers
o List the Uses for Variables
o Store Data in Variables
o Declare PL/SQL Variables
Writing Executable Statements
o Describe Basic Block Syntax Guidelines
o Use Literals in PL/SQL
o Customize Identifier Assignments with SQL Functions
o Use Nested Blocks as Statements
o Reference an Identifier Value in a Nested Block
o Qualify an Identifier with a Label
o Use Operators in PL/SQL
o Use Proper PL/SQL Block Syntax and Guidelines
Interacting with the Oracle Server
o Identify the SQL Statements You Can Use in PL/SQL
o Include SELECT Statements in PL/SQL
o Retrieve Data in PL/SQL with the SELECT Statement
o Avoid Errors by Using Naming Conventions When Using Retrieval and DML Statements
o Manipulate Data in the Server Using PL/SQL
o The SQL Cursor concept
o Use SQL Cursor Attributes to Obtain Feedback on DML
o Save and Discard Transactions
Writing Control Structures
o Control PL/SQL Flow of Execution
o Conditional processing Using IF Statements
o Conditional Processing CASE Statements
o Handle Nulls to Avoid Common Mistakes
o Build Boolean Conditions with Logical Operators
o Use Iterative Control with Looping Statements
Working with Composite Data Types
o Learn the Composite Data Types of PL/SQL Records and Tables
o Use PL/SQL Records to Hold Multiple Values of Different Types
o Inserting and Updating with PL/SQL Records
o Use INDEX BY Tables to Hold Multiple Values of the Same Data Type
Using Explicit Cursors
o Cursor FOR Loops Using Sub-queries
o Increase the Flexibility of Cursors By Using Parameters
o Use the FOR UPDATE Clause to Lock Rows
o Use the WHERE CURRENT Clause to Reference the Current Row
o Use Explicit Cursors to Process Rows
o Explicit Cursor Attributes
o Cursors and Records
Handling Exceptions
o Handling Exceptions with PL/SQL
o Predefined Exceptions
o Trapping Non-predefined Oracle Server Errors
o Functions that Return Information on Encountered Exceptions
o Trapping User-Defined Exceptions
o Propagate Exceptions
o Use The RAISE_APPLICATION_ERROR Procedure To Report Errors To Applications
Creating Stored Procedures
o Describe the block structure for PL/SQL stored procedures
o Invoke a stored procedure/function from different tools
o Call a stored procedure with host variables from SQL*Plus, Forms, Java, C, etc
o Invoke a stored procedure from an anonymous block or another stored procedure
o List the CREATE OR REPLACE PROCEDURE syntax
o Identify the development steps for creating a stored procedure
o Use the SHOW ERRORS command
o View source code in the USER_SOURCE dictionary view
Creating Stored Functions
o Describe stored functions
o List the CREATE OR REPLACE FUNCTION syntax
o Identify the steps to create a stored function
o Execute a stored function
o Identify the advantages of using stored functions in SQL statements
o Identify the restrictions of calling functions from SQL statements
o Remove a function
Creating Packages
o List the benefits or using PL/SQL packages
o Differentiate between a package specification and a package body
o Create packages
o Include public and private constructs in a package
o Call public and private constructs in a package
o Remove packages
Using More Package Concepts
o Overload procedure and function definitions
o Use forward declarations
o Create a one-time package initialization block
o Follow the persistent state of constructs in packages
o Use PL/SQL tables and records in packages
o Wrap code to hide the source
Utilizing Oracle Supplied Packages in Application Development
o List the various uses for the Oracle supplied packages
o Reuse pre-packaged code to complete various tasks from developer to DBA purposes
o Use the DESCRIBE command to view the package specifications and overloading
o Describe how DBMS_OUTPUT works
o Use UTL_FILE to direct output to operating system files
o Use the HTP package to generate a simple web page
o Describe the main features of UTL_MAIL
o Call the DBMS_SCHEDULER package to schedule PL/SQL code to run
Dynamic SQL and Metadata
o Describe using native dynamic SQL
o List the execution flow of SQL
o Write dynamic SQL using the EXECUTE IMMEDIATE syntax
o Write dynamic SQL with the DBMS_SQL package
o Generate DDL from metadata using the DBMS_METADATA package
Design Considerations for PL/SQL Code
o Standardize constants with a constant package
o Standardize exceptions with an exception package
o Write PL/SQL code that uses local subprograms
o Use the NOCOPY compiler hint to pass parameters by reference
o Use the PARALLEL ENABLE hint for optimization
o Use the AUTONOMOUS TRANSACTION pragma to run independent transactions within a single transaction
o Set the AUTHID directive to execute programs with the privileges of the calling user instead of the creating user
o Use bulk binding for multi-row operations
Managing Dependencies
o Describe dependent and referenced objects
o Track procedural dependencies with dictionary views
o Predict the effect of changing a database object upon stored procedures and functions
o Manage local and remote procedural dependencies
Manipulating Large Objects
o Describe a LOB object
o Create and maintain LOB data types
o Differentiate between internal and external LOBs
o Use the DBMS_LOB PL/SQL package to control LOBs
o Describe the use of temporary LOBs
Creating Triggers
o Describe different types of triggers
o Describe database triggers and their use
o Create database triggers
o Describe database trigger firing rules
o Remove database triggers
Applications for Triggers
o Create database and system event triggers
o Create triggers on DDL statements
o Use the CALL statement in triggers to invoke procedures
o Explain the rules for reading and writing to tables with triggers
o Describe business application scenarios for implementing with triggers
o Manage trigger code
Understanding and Influencing the PL/SQL Compiler
o Describe native compilation and interpreted compilation
o List the features of native compilation
o Switch between native and interpreted compilation for compiled PL/SQL code
o Set the parameters to control aspects of PL/SQL compilation
o Write a query to retrieve information from the dictionary views on how the PL/SQL code is compiled
o Explain the compiler warning mechanism
o List the steps to use the compiler warnings
o Use DBMS_WARNING to implement compiler warnings
