Oracle Database 10g: SQL Fundamentals II

 Oracle University provides a downloadable eKit containing the training materials for this course.

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

Schedule/Purchase	Training Formats	Price	Duration	Course Materials	Instruction Language
View Schedule	Instructor-Led Training	US$ 1,200	2 Days	English

 

Note: To have a more personalized experience, we invite you to: Log In!

Audience:

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

Prerequisites:

Required Prerequisites:

• Knowledge of the SQL SELECT and DML statements.
• Oracle Database 10g: SQL Fundamentals I

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