Oracle Database 10g: SQL Fundamentals II

Posted by admin - 26/01/10 at 11:01 pm

What you will learn
In this course, students learn how to use the advanced features of SQL in order to query and manipulate data within the database. Advanced querying and reporting techniques are explained. Schema objects that are useful for data warehousing and other application areas are discussed in detail.Students learn about manipulating large data sets and storing and retrieving dates according to different time zones. They are also taught the concepts of controlling access and privileges for schema objects.

Learn to:

• Control privileges at the object and system level
• Use grouping operations to create reports
• Manage Schema Objects
• Search data using Advanced Sub-queries, and retrieve hierarchical data
• Manipulate data in the Oracle database by using sub-queries
• Use Regular Expression support

Audience

• Business Intelligence Developer
• Database Administrators
• Database Designers
• End Users
• Forms Developer
• PL/SQL Developer
• Portal Developer

Prerequisites

• Required Prerequisites
• Oracle Database 10g: SQL Fundamentals I

Suggested Prerequisites

• Knowledge of the SQL SELECT and DML statements.

Course Objectives

• Controlling User Access
• Managing Schema Objects
• Manipulating large Datasets
• Generating Reports by Grouping Related Data
• Managing Data in Different Time zones
• Retrieving data using Sub-queries
• Hierarchical data retrieval
• Performing Regular Expression Support

Course Topics
Controlling User Access

• Controlling User Access
• System versus Objects Privileges
• Using Roles to define user groups
• Changing Your Password
• Granting Object Privileges
• Confirming Privileges Granted
• Revoking Object Privileges
• Using Database Links

Manage Schema Objects

• Using the ALTER TABLE statement
• Adding a Column
• Modifying a Column
• Dropping a Column, Set Column UNUSED
• Adding, Enabling and Disabling Constraints
• Creating Function-Based Indexes
• Performing FLASHBACK operations
• External Tables

Manipulating Large Data Sets

• Using the MERGE Statement
• Performing DML with Sub queries
• Performing DML with a RETURNING Clause
• Overview of Multi-table INSERT Statements
• Tracking Changes in DML

Generating Reports by Grouping Related Data

• Overview of GROUP BY Clause
• Overview of 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
• Using date-time functions
• Identifying TIMESTAMP Data Types
• Differentiating between DATE and TIMESTAMP
• Performing Conversion Operations

Searching Data Using Advanced Sub queries

• Sub query 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 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

Regular Expression Support

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

Appendices

• Writing Advanced Scripts
• Oracle Architectural Components
• Using SQL Developer
• Additional Practices

This entry was posted on Tuesday, January 26th, 2010 at 11:13 pm and is filed under Oracle Administration. The entry is tagged under oracle fundamentals, sql fundamentals, sql fundamentals ii, sql fundamentals training. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.