Oracle Database: SQL Fundamentals II

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

Participants gain the skill to use the dictionary views to retrieve metadata and create reports about their schema objects. Some of the date time functions available in Oracle 11g Database and the regular expression support in SQL are included.

In this course, students use Oracle SQL Developer as the main tool and SQL*Plus is available as an optional tool.

This is appropriate for a 10g and 11g audience too. There are minor changes between 10g and 11g features in SQL.

Learn to:

• Manage schema objects and use data dictionary views.
• Control database access to specific objects.
• 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.

A Live Virtual Class (LVC) is exclusively for registered students; unregistered individuals may not view an LVC at any time. Registered students must view the class from the country listed in the registration form. Unauthorized recording, copying, or transmission of LVC content may not be made.

Audience

• Application Developers
• Business Intelligence Developer
• Forms Developer
• Portal Developer
• Reports Developer
• System Analysts
• Technical Consultant
• Database Administrators

Course Topics Introduction

• Course Prerequisites
• Course Agenda
• The Human Resources (HR) Schema
• Review of Using Oracle SQL Developer
• SQL Statements in SQL*Plus
• Review of some basic concepts of SQL

Control User Access

• Identify the System and Object Privileges
• Create Users
• Grant System Privileges
• Create and Grant Privileges to a Role
• Change Your Password
• Grant Object Privileges
• How to Pass On Your Privileges?
• Revoke Object Privileges

Management of Schema Objects

• Add, Modify and Drop a Column
• Add, Drop and Defer a Constraint
• Enable and Disable a Constraint
• Create and Remove Indexes
• Create a Function-Based Index
• Perform Flashback Operations
• Create an External Table by Using ORACLE_LOADER and by Using ORACLE_DATAPUMP
• Query External Tables

Manage Objects with Data Dictionary Views

• What is the Data Dictionary?
• How to Use the Dictionary Views?
• USER_OBJECTS and ALL_OBJECTS Views
• Table and Column Information
• Query the dictionary views for constraint information
• Query the dictionary views for view, sequence, index and synonym information
• Add a comment to a table
• Query the dictionary views for comment information

Manipulate Large Data Sets

• Use Subqueries to Manipulate Data
• Retrieve Data Using a Subquery as Source
• Insert Using a Subquery as a Target
• Use the WITH CHECK OPTION Keyword on DML Statements
• List the types of Multitable INSERT Statements
• Identify Multitable INSERT Statements
• Merge rows in a table
• How to track Changes in Data over a period of time?

Data Management in Different Time Zones

• What are Time Zones?
• The CURRENT_DATE, CURRENT_TIMESTAMP, and LOCALTIMESTAMP functions
• Compare Date and Time in a Session’s Time Zone
• Describe DBTIMEZONE and SESSIONTIMEZONE
• List the difference between DATE and TIMESTAMP
• Identify the INTERVAL Data Types
• Usage of EXTRACT, TZ_OFFSET and FROM_TZ functions
• Use TO_TIMESTAMP,TO_YMINTERVAL and TO_DSINTERVAL

Retrieve Data Using Subqueries

• Multiple-Column Subqueries
• Pairwise and Nonpairwise Comparison
• Usage of Scalar Subquery Expressions
• Solve problems with Correlated Subqueries
• Update and Delete Rows Using Correlated Subqueries
• Use the EXISTS and NOT EXISTS operators
• Use the WITH clause
• Use the Recursive WITH clause

Regular Expression Support

• Invoke the Regular Expressions Functions and Conditions in SQL
• Implement Meta Characters with Regular Expressions
• Perform a Basic Search using the REGEXP_LIKE function
• Find patterns using the REGEXP_INSTR function
• Extract Substrings using the REGEXP_SUBSTR function
• Replace Patterns Using the REGEXP_REPLACE function
• How to use Sub-Expressions with Regular Expression Support?
• The REGEXP_COUNT function

Course Objectives

• Manipulate large data sets in the Oracle database by using subqueries.
• Manage data in different time zones.
• Write multiple-column subqueries.
• Use scalar and correlated subqueries.
• Manage schema objects.
• Use the regular expression support in SQL.
• Add new users with different levels of access privileges.
• Control database access to specific objects.
• Manage objects with data dictionary views.