CMDBID: 102260 | Course Code: SQL,II | Duration: 2 Days
Overview>
This Oracle® Database: SQL Workshop II helps you enhance your skills as an Application developer by learning how to manage the database objects, data dictionary views and multicolumn subqueries.
Audience>
Each participant will leave with a recognised certificate
Skills Gained>
Completion of this course will enable particicpants to:
Grant and revoke privileges and roles.
Manage schema objects and data dictionary views.
Write efficient and complex subqueries.
Use the in-built functions supporting the usage of regular expressions and time zone.
Prerequisites>
Required prerequisites to undertake this course are:
Familiarity with SQL Developer, SQL*Plus
Basic Knowledge of SQL
Outline>
Expert Oracle instructors will teach you how to write more efficient queries using the functions that support timezone and regular expressions.
Introduction
Course Objectives, Course Agenda and Appendixes Used in this Course
Provide an insight of the development environments
Tables used in the Course
Review of the SQL Concepts
Introduction to Data Dictionary Views
Introduction to Data Dictionary
Describe the Data Dictionary Structure
Using the Data Dictionary Views
Querying the Data Dictionary Views
Creating Sequences, Synonyms, Indexes
Overview of sequences
Overview of synonyms
Overview of indexes
Creating Views
Overview of views
Managing Schema Objects
Managing constraints
Creating and using temporary tables
Creating and using external tables
Retrieving Data by Using Subqueries
Retrieving Data by Using a Subquery as Source
Working with Multiple-Column subqueries
Using Scalar subqueries in SQL
Correlated Subqueries
Working with the WITH clause
Manipulating Data by Using Subqueries
Using Subqueries to Manipulate Data
Inserting by Using a Subquery as a Target
Using the WITH CHECK OPTION Keyword on DML Statements
Using Correlated Subqueries to Update and Delete rows
Controlling User Access
System privileges
Creating a role
Object privileges
Revoking object privileges
Manipulating Data
Overview of the Explicit Default Feature
Using multitable INSERTs
Using the MERGE statement
Performing flashback operations
Tracking Changes in Data
Managing Data in Different Time Zones
Working with CURRENT_DATE, CURRENT_TIMESTAMP,and LOCALTIMESTAMP
Working with INTERVAL data types
Using Datetime functions in queries
Overview of views Managing Schema Objects
Our Clients
Our clients have included prestigious national organisations such as Oxford University Press, multi-national private corporations such as JP Morgan and HSBC, as well
as public sector institutions such as the Department of Defence and the Department of Health.