Introduction to Oracle9i: SQL

What you will learn:

Audience:

• Database Administrators
• System Analysts

Prerequisites:

Required Prerequisites:

• Familiarity with data processing concepts and techniques

Suggested Prerequisites:

• Ability to use a graphical user interface (GUI)

Course Objectives:

• Describe relational and object relational database concepts
• Retrieve, insert, update, and delete data
• Use the Single row and Group functions
• Retrieve data using additional advanced techniques like using ROLLUP, CUBE, set operators, correlated subqueries and hierarchical queries
• Solve problems by using multicolumn subqueries, subqueries in the from clause of a SELECT statement, correlated subqueries, and scalar subqueries
• Use the iSQL*Plus environment
• Write scripts to generate script files
• Generate reports using iSQL*Plus
• Create and maintain database objects
• Control transactions
• Control data/user access
• Use the Oracle9i Single row functions
• Use GROUPING SETS and the WITH clause
• Use the Oracle9i extensions to DML and DDL statements
• Apply techniques in real life simulation

Course Topics:

Introduction

• Describing the Life Cycle Development Phases
• Defining a Relational Database
• Discussing the Theoretical, Conceptual, and Physical Aspects of a Relational Database
• Describing How a Relational Database Management System (RDBMS) Is Used to Manage a Relational Database
• Describing the Oracle Implementation of Both the RDBMS and the Object Relational Database Management System (ORDBMS)
• Describing How SQL Is Used in the Oracle Product Set

Writing a Basic SQL Statement

• Describing the SQL Select Capabilities
• Executing a Basic Select Statement with the Mandatory Clauses
• Differentiating Between SQL and iSQL*Plus Commands

Restricting and Sorting Data

• Limiting the Rows Retrieved by a Query
• Sorting the Rows Retrieved by a Query

Single Row Functions

• Describing Various Types of Functions Available in SQL
• Using a Variety of Character, Number, and Date Functions in SELECT Statements
• Explaining What the Conversion Functions Are and How They Are Used
• Using Control Statements

Displaying Data from Multiple Tables

• Writing SELECT Statements to Access Data from More Than One Table
• Describing the Cartesian Product
• Describing and Using the Different Types of Joins
• Writing Joins Using the Tips Provided

Aggregating Data by Using Group Functions

• Identifying the Different Group Functions Available
• Explaining the Use of Group Functions
• Grouping Data by Using the GROUP BY Clause

Writing Subqueries

• Describing the Types of Problems That Subqueries Can Solve
• Describing Subqueries
• Listing the Types of Subqueries
• Writing Single-Row and Multi-Row Subqueries
• Describing and Explaining the Behavior of Subqueries When NULL Values Are Retrieved

Producing Readable Output with iSQL*Plus

• Producing Queries That Require an Input Variable
• Customizing the iSQL*Plus Environment
• Producing More Readable Output
• Creating and Executing Script Files

Manipulating Data

• Describing Each Data Manipulation Language (DML) Command
• Inserting Rows into a Table
• Updating Rows in a Table
• Deleting Rows from a Table
• Merging Rows into a Table
• Controlling Transactions
• Describing Transaction Processing
• Describing Read Consistency and Implicit and Explicit Locking

Creating and Managing Tables

• Describing the Main Database Objects
• Creating Tables
• Describing the Oracle Data Types
• Altering Table Definitions
• Dropping, Renaming, and Truncating Tables

Including Constraints

• Describing Constraints
• Creating and Maintaining Constraints

Creating Views

• Describing Views and Their Uses
• Creating a View
• Retrieving Data by Means of a View
• Inserting, Updating, and Deleting Data Through Views
• Dropping Views
• Altering the Definition of a View
• Inline Views
• Top 'N' Analysis

Other Database Objects

• Creating, Maintaining, and Using Sequences
• Creating and Maintaining Indexes
• Creating Private and Public Synonyms

Controlling User Access

• Understanding the Concepts of Users, Roles, and Privileges
• Granting and Revoking Object Privileges
• Creating Roles and Granting Privileges to Roles
• Creating Synonyms for Ease of Table Access

SQL Workshop

• Applying Techniques Learned in This Course
• Preparing for Future Oracle Courses

Using Set Operators

• Describing the Set Operators
• Obeying the Set Operators Rules and Guidelines
• Using a Set Operator to Combine Multiple Queries into a Single Subquery
• Controlling the Order of Rows Returned

Oracle 9i Datetime Functions

• Using DATETIME Functions
• Using the NVL2 Function to Handle NULL Values

Enhancements to the GROUP BY Clause

• Using ROLLUP as an Extension to the GROUP BY Clause to Produce Subtotal Values
• Using CUBE as an Extension to the GROUP BY Clause to Produce Cross-Tabulation Values
• Using the GROUPING Function to Identify the Row Values Created by ROLLUP or CUBE Operators
• Using GROUPING SETS to Produce a Single Result Set That Is Equivalent to a UNION ALL Approach
• Using the WITH Clause

Advanced Subqueries

• Multiple-Column Subqueries
• Writing a Subquery in the FROM Clause
• Writing and describing Correlated Subquery
• Using EXISTS and NOT EXISTS Operators
• Updating and Deleting Rows Using Correlated Subqueries
• Using Scalar Subqueries in SQL

Hierarchical Retrieval

• Discussing the Benefits of the Hierarchical Query
• Ordering the Rows Retrieved by a Query in a Hierarchical Manner
• Formatting Hierarchical Data so That It Is Easy to Read
• Excluding Branches from the Tree Structure

Oracle9i Extensions to DML and DDL Statements

• Discussing Multitable Inserts
• Creating and Using External Tables
• Naming the Index and Using the CREATE INDEX Command at the Time of Creating Primary Key Constraint