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