Portland State University
 
 
ABOUT US
REGISTRATION
CATALOG/SCHEDULE
CONTACT US
PDC HOME
Information Technology

Oracle 10g Database Administration

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