Oracle Database 10g SQL Fundamentals II

Day 1

• Introduction
• Lesson 1 : Controlling User Access
• Lesson 2 : Managing Schema Objects
• Lesson 3 : Manipulating Large Data Sets
• Lesson 4 : Generating Reports by Grouping Related Data

Day 2

• Lesson 5 : Managing Data in Different Time Zones
• Lesson 6 : Retrieving Data Using Subqueries
• Lesson 7 : Hierarchical Retrieval
• Lesson 8 : Regular Expression Support

Lesson 1 : Controlling User Access

• Differentiate system privileges from object privileges
• Grant privileges on tables
• View privileges in the data dictionary
• Grant roles
• Distinguish between privileges and roles

Lesson 2 : Managing Schema Objects

• Add constraints
• Create indexes
• Create indexes using the CREATE TABLE statement
• Creating function-based indexes
• Drop columns and set column UNUSED
• Perform FLASHBACK operations
• Create and use external tables

Lesson 3 : Manipulating Large Data Sets

• Manipulate data using subqueries
• Describe the features of multitable INSERTs
• Use the following types of multitable INSERTs
• Unconditional INSERT
• Pivoting INSERT
• Conditional ALL INSERT
• Conditional FIRST INSERT
• Merge rows in a table
• Track the changes to data over a period of time

Lesson 4 : Generating Reports by Grouping Related Data

• Use the ROLLUP operation to producesubtotal values
• Use the CUBE operation to produce cross-tabulation values
• Use the GROUPING function to identify the row values created by ROLLUP or CUBE
• Use GROUPING SETS to produce a single result set

Lesson 5 : Managing Data in Different Time Zones

• TZ_OFFSET
• FROM_TZ
• TO_TIMESTAMP
• TO_TIMESTAMP_TZ
• TO_YMINTERVAL
• TO_DSINTERVAL
• CURRENT_DATE
• CURRENT_TIMESTAMP
• LOCALTIMESTAMP
• DBTIMEZONE
• SESSIONTIMEZONE
• EXTRACT

Lesson 6 : Retrieving Data Using Subqueries

• Write a multiple-column subquery
• Use scalar subqueries in SQL
• Solve problems with correlated subqueries
• Update and delete rows using correlated subqueries
• Use the EXISTS and NOT EXISTS operators
• Use the WITH clause

Lesson 7 : Hierarchical Retrieval

• Interpret the concept of a hierarchical query
• Create a tree-structured report
• Format hierarchical data
• Exclude branches from the tree structure

Lesson 8 : Regular Expression Support

• Use regular expression support in SQL to search, match, and replace strings in terms of regular expressions