Oracle Database: SQL Fundamentals II DB-SQL-FUN2

Audience:

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

Prerequisites:

• Oracle Database: SQL Fundamentals I

Objectives:

• Manage schema objects.
• Manage objects with data dictionary views.
• 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.
• Use the regular expression support in SQL.
• Add new users with different levels of access privileges.
• Control database access to specific objects.

Topics:

Introduction

• Course Prerequisites
• Course Agenda
• 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
• View 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 data 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 differences 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 Recursive WITH clause

Regular Expression Support

• Invoke 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