Audience
This course is aimed at those already experienced with SQL for data retrieval. It covers the use and maintenance of other database objects. It provides an overview of techniques for making SQL scripts more efficient and also an introduction to some of the more common Oracle SQL analytic functions.
This course is suitable for all recent versions of Oracle including 12c, 18c, and 19c.
Prerequisites
Delegates should have a good basic understanding of Oracle SQL. Previous attendance of our Oracle SQL course is highly recommended.
Duration
2 days. Hands on.
This course is available on site only. Please call for details.
NOTE: This course may be combined with Oracle SQL and run over a total of five days, please contact us for more information.
Course Objectives
This course is highly interactive and consists of theory coupled with plenty practical sessions. It will introduce delegates to the creation, security and maintenance of objects in an Oracle database.
Together with Oracle SQL, this course provides the perfect prerequisite knowledge to embark on training for Oracle PL/SQL Programming, or administrative roles such as Oracle Database Administration.
On completion of the course delegates will be able to:
- Create and maintain a range of database objects.
- Control access to those objects for different users and roles
- Identify the appropriate objects for use with particular aspects of query design
- Monitor how Oracle runs an SQL script and identify problem areas
- Explore and implement better querying access paths
- Use SQL analytic functions
Course Content
Database Definition
Tables
Table constraints
Temporary tables
External tables
Different types of indexes
Index only tables
Partitioned tables
Clusters
Views
Synonyms
Sequences
Oracle Data Dictionary
Data dictionary view categories
Data dictionary view relationships
Data dictionary usage
Oracle Security Facilities
System privileges
Object privileges
Access control for users and roles
Tracking SQL
Interpretation of EXPLAIN PLAN
Interpretation of AUTOTRACE
Reading and configuring SQL TRACE output
Indexes
Use of different index types
Different index access types in EXPLAIN PLAN & AUTOTRACE
Effects of different SQL expressions on index usage
Analytic Functions
Analytic summary functions
Analytic non-summary functions
Use of the OVER clause
Use of PARTITION clause with the OVER clause
Use of WINDOW clause with the OVER clause
Hierarchical Queries
CONNECT BY, PRIOR and START WITH
LEVEL pseudo column