Oracle Database 10g: SQL Fundamentals II

start > courses and registration > training feature sheet

code.

10gDB-SQL-FUND-II

length.

2 days

type.

Instructor-Led

partner.

Oracle

price.

$1,200

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

There are currently no scheduled dates for this course. If you are interested in this course, request a course date with the links below.

who can benefit

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

prerequisites

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

skills gained

	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

related courses, exams and materials

	before related training and products

Oracle Database 10g: SQL Fundamentals I

course content details

	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