11g: SQL Fundamentals II

What you will learn:

This course is designed for application developers with basic SQL language skills. In this course, students learn to control privileges at the object and system level. This course covers creating indexes and constraints, and altering existing schema objects. Students also learn to create and query external tables and use the advanced features of SQL to query and manipulate data within the database.

Students learn to use the dictionary views to retrieve metadata and create reports about their schema objects. Students learn some of the date time functions available in the Oracle 11g Database and use the regular expression support in SQL. In this course, students use Oracle SQL Developer as the main tool and SQL*Plus is introduced as an optional tool. This course counts towards the Hands-on course requirement for the Oracle Database 11g Administrator Certification. Only instructor-led inclass or instructor-led online formats of this course will meet the Certification Hands-on Requirement. Self Study CD-Rom and Knowledge Center courses DO NOT meet the Hands-on Requirement. This is appropriate for a 10g audience too. There are few minor changes between 10g and 11g features.

 

 

 

 

 

 

 

Learn To:

• Control database access to specific objects
• Manage schema objects and use data dictionary views
• Manipulate large data sets in the Oracle database by using subqueries
• Manage data in different time zones
• Retrieve data by using advanced subqueries
• Use the regular expression support in SQL to search, match, and replace strings in terms of regular expressions

Audience: Application Developers
Business Intelligence Developer
Database Administrators
Developer
Forms Developer
PL/SQL Developer
Support Engineer
System Analyst

Prerequisites: Basic Knowledge of SQL and Familiarity with Oracle SQL Developer and SQL*Plus

Next Course:

Relevant Certification:

Course Objectives: Introduction
The Human Resources (HR) Schema
Review of Using Oracle SQL Developer
Review of some basic concepts of SQL

Controlling User Access
System versus Object Privileges
Creating Users
Granting System Privileges
Creating and Granting Privileges to a Role
Changing Your Password
Granting Object Privileges
Passing On Your Privileges
Revoking Object Privileges

Managing Schema Objects
Adding, Modifying and Dropping a Column
Adding, Dropping and Deferring a Constraint
Enabling and Disabling a Constraint
Creating and Removing Indexes
Creating a Function-Based Index
Performing Flashback Operations
Creating an External Table by Using ORACLE_LOADER and by Using ORACLE_DATAPUMP
Querying External Tables

Managing Objects with Data Dictionary Views
The Data Dictionary
How to Use the Dictionary Views
USER_OBJECTS and ALL_OBJECTS Views
Table and Column Information
Querying the dictionary views for constraint information
Querying the dictionary views for view, sequence, index and synonym information
Adding a comment to a table
Querying the dictionary views for comment information

Manipulating Large Data Sets
Using Subqueries to Manipulate Data
Retrieving Data Using a Subquery as Source
Inserting Using a Subquery as a Target
Using the WITH CHECK OPTION Keyword on DML Statements
Types of Multitable INSERT Statements
Using Multitable INSERT Statements
Merging rows in a table
Tracking Changes in Data over a period of time

Managing Data in Different Time Zones
Time Zones
CURRENT_DATE, CURRENT_TIMESTAMP, and LOCALTIMESTAMP
Comparing Date and Time in a Session’s Time Zone
DBTIMEZONE and SESSIONTIMEZONE
Difference between DATE and TIMESTAMP
INTERVAL Data Types
Using EXTRACT, TZ_OFFSET and FROM_TZ
Using TO_TIMESTAMP,TO_YMINTERVAL and TO_DSINTERVAL

Retrieving Data Using Subqueries
Multiple-Column Subqueries
Pairwise and Nonpairwise Comparison
Using Scalar Subquery Expressions
Solving problems with Correlated Subqueries
Updating and Deleting Rows Using Correlated Subqueries
Using the EXISTS and NOT EXISTS operators
Using the WITH clause

Regular Expression Support
Using the Regular Expressions Functions and Conditions in SQL
Using Meta Characters with Regular Expressions
Performing a Basic Search using the REGEXP_LIKE function
Finding patterns using the REGEXP_INSTR function
Extracting Substrings using the REGEXP_SUBSTR function
Replacing Patterns Using the REGEXP_REPLACE function
Using Sub-Expressions with Regular Expression Support
Using the REGEXP_COUNT function

Course Topics: