News and Events
Full Course List
Career Information
PSU Difference
Financial Aid
Location & Directions
FAQ
More Information
Refund/Cancellation Policy
Home

Oracle 10g Database Administration

Introduction to Oracle SQL 10g

COURSE OUTLINE:
Module 1: Introduction to SQL 10g

Introduction

• List the Oracle Database 10g main features
  
• Provide an overview of: components, internet platform, apps server and developer suite
  
• Describe relational and object relational database designs
  
• Review the system development life cycle
  
• Describe different means of storing data
  
• Review the relational database concept
  
• Define the term data models
  
• Show how multiple tables can be related

Retrieving Data Using the SQL SELECT Statement

• Define projection, selection, and join terminology
  
• Review the syntaxes for the basic SQL SELECT statements
  
• Use Arithmetic and Concatenation operators in SQL statements
  
• List the differences between SQL and iSQL*Plus
  
• Log into the database using iSQL*Plus
  
• Explain the iSQL*Plus interface
  
• Categorize the different types of iSQL*Plus commands
  
• Save SQL statements to script files

Restricting and Sorting Data

• Limit rows using a selection
  
• Using the WHERE clause to retrieve specific rows
  
• Using the comparison conditions in the WHERE clause
  
• Use the LIKE condition to compare literal values
  
• List the logical conditions AND, OR, NOT
  
• Describe the rules of precedence for the conditions shown in this lesson
  
• Sort rows with the ORDER BY clause
  
• Use ampersand substitution in iSQL*Plus to restrict and sort output at run time

Using Single Row Functions to Customize Reports

• Show the differences between single row and multiple row SQL functions
  
• Categorize the character functions into case manipulation and character manipulation types
  
• Use the character manipulation functions in the SELECT and WHERE clauses
  
• Explain and use the DATE and numeric functions
  
• Use the SYSDATE function to retrieve the current date in the default format
  
• Introduce the DUAL table as a means to view function results
  
• List the rules for applying the arithmetic operators on dates
  
• Use the arithmetic operators with dates in the SELECT clause

Reporting Aggregated Data Using the Group Functions

• Describe and categorize the group functions
  
• Use the group functions
  
• Utilize the DISTINCT keyword with the group functions
  
• Describe how nulls are handled with the group functions
  
• Create groups of data with the GROUP BY clause
  
• Group data by more than one column
  
• Avoid illegal queries with the group functions
  
• Exclude groups of data with the HAVING clause

Displaying Data From Multiple Tables

• Show the join tables syntax using SQL 99 syntax
  
• Use table aliases to write shorter code and explicitly identify columns from multiple tables
  
• Issue a SQL CROSS JOIN statement to produce a cartesian product
  
• Use the NATURAL JOIN clause to retrieve data from tables with the same named columns
  
• Create a join with the USING clause to identify specific columns between tables
  
• Create a three way join with the ON clause to retrieve information from 3 tables
  
• List the types of outer joins LEFT, RIGHT, and FULL
  
• Add additional conditions when joining tables with the AND clause
  

Using Sub queries to Solve Queries

• List the syntax for sub queries in a SELECT statements WHERE clause
  
• List the guidelines for using sub queries
  
• Describe the types of sub queries
  
• Execute single row sub queries and use the group functions in a sub query
  
• Identify illegal statements with sub queries
  
• Execute multiple row sub queries
  
• Analyze how the ANY and ALL operators work in multiple row sub queries
  
• Explain how null values are handled in sub queries

Using the SET Operators

• Use the UNION operator to return all rows from multiple tables and eliminate any duplicate rows
  
• Use the UNION ALL operator to return all rows from multiple tables
  
• Describe the INTERSECT operator
  
• Use the INTERSECT operator
  
• Explain the MINUS operator
  
• Use the MINUS operator
  
• List the SET operator guidelines
  
• Order results when using the UNION operator

Manipulating Data

• Write INSERT statements to add rows to a table
  
• Copy rows from another table
  
• Create UPDATE statements to change data in a table
  
• Generate DELETE statements to remove rows from a table
  
• Use a script to manipulate data
  
• Save and discard changes to a table through transaction processing
  
• Show how read consistency works
  
• 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
  
• Introduce the schema concept
  
• Display the basic syntax for creating a table and show the DEFAULT option
  
• Explain the different types of constraints
  
• Show resulting exceptions when constraints are violated with DML statements
  
• Create a table with a sub query
  
• Describe the ALTER TABLE functionality
  
• Remove a table with the DROP statement and Rename a table

Creating Other Schema Objects

• List the main database objects and describe the naming rules for database objects
  
• Introduce the schema concept
  
• Display the basic syntax for creating a table and show the DEFAULT option
  
• Explain the different types of constraints
  
• Show resulting exceptions when constraints are violated with DML statements
  
• Create a table with a sub query and remove a table with the DROP statement
  
• Describe the ALTERTABLE functionality
  
• Rename a table

Managing Objects with Data Dictionary Views

• Describe the structure of each of the dictionary views
  
• List the purpose of each of the dictionary views
  
• Write queries that retrieve information from the dictionary views on the schema objects

Controlling User Access

• Controlling user access
  
• System versus objects privileges
  
• Creating user sessions and granting system privileges
  
• Using roles to define user groups
  
• Creating and granting privileges to a role
  
• Granting and revoking object privileges
  
• Changing your password
  
• Using Database Links

Manage Schema Objects

• Creating directories
  
• Creating and querying external tables
  
• Creating Index Organized Tables
  
• Creating Function based indexes
  
• Dropping Columns
  
• Altering the structure of tables and adding constraints
  
• Performing FLASHBACK Statement
  
• Materialized Views overview

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

• Overview of GROUP BY and Having Clause
  
• Aggregating data with ROLLUP and CUBE Operators
  
• Determine subtotal groups using GROUPING Functions
  
• Compute multiple groupings with GROUPING SETS
  
• Define levels of aggregation with Composite Columns
  
• 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

• Subquery Overview
  
• Using a Sub query
  
• Comparing several columns using Multiple-Column Sub queries
  
• Defining a Data source Using a Sub query in the FROM Clause
  
• Returning one Value using Scalar Sub query Expressions
  
• Performing ROW by-row processing with Correlated Sub queries
  
• Reusing query blocks using the WITH Clause

Hierarchical Data Retrieval

• Sample Data from the EMPLOYEES Table
  
• The Tree Structure of Employee data
  
• Hierarchical Queries
  
• Ranking Rows with LEVEL
  
• Formatting Hierarchical Reports Using LEVEL and LPAD
  
• Pruning Branches with the WHERE and CONNECT BY clauses

Performing Regular Expression Support and Case Insensitive

• Regular Expression Support Overview
  
• Describing simple and complex patterns for searching and manipulating data