SQL CERTIFICATION PREPARATION AND PERFECTION -Oracle Database SQL OCA

A complete Oracle training to discover all the concepts of the system and the different ways of using them concretely while having a clear view on the overall architecture of ORACLE.

  • View restricted and sorted data using sort functions and column selection
  • Using functions
  • Restore aggregated data using group functions (group by)
  • View data from multiple tables
  • Use subqueries
  • Use the “Set Operators”
  • Manipulate data (insert, update, delete, transactions)
  • Use statements to create and maintain tables (LDD)
  • Handling the objects seen, sequences and synonyms
  • Managing objects with data dictionary views
  • Control user access
  • Manage schema objects (constraints, index, external table, …)
  • Handling large data sets
  • Generate reports using groups of data (rollup, cube, grouping)
  • Manage data in different time zones
  • Manipulate data using subqueries
  • Write hierarchical queries
  • Use regular expressions.

This Oracle SQL OCA training is intended for developers using the SQL language and wishing to improve the efficiency of requests in response time, readability, maintenance, and wishing to take the Oracle Database SQL OCA certification exam.

Operational knowledge of SQL language is essential to obtain Oracle SQL certification.

Concretely you must know how to write SQL queries, joins, use functions, implement integrity constraints and know most of the concepts of this revision program.

However, you can also approach this training as a SQL improvement on the various themes of the program (or even a discovery for certain targeted chapters) without making certification a priority objective. In addition, you can work on your own and take the exam when you feel ready if you don’t have a specific deadline.

If you do not feel comfortable enough in SQL, we recommend that you take the Oracle 12c Certification: SQL Fundamentals training course, ref. BSFC, or the Oracle 11g Certification: SQL Fundamentals training, ref. BSFG.

5 Days.

Practical exercises and demonstrations will allow you to put into practice the theoretical concepts presented.

For “distance” training, they are carried out with a Teams or Zoom type videoconferencing tool, depending on the case, allowing the trainer to adapt his teaching methods.
Find all the details on distance sessions or virtual classes on our website.

Presentation of the environment for the examination

Presentation of administrative elements (Pearson Vue account, exam procedures, results, etc.)
The technical environment of the exam
Getting started with the database used during the exam (it will serve as a common thread throughout the training in order to familiarize you with the vocabulary and the database schema: names of tables, types of data, etc.)
Examples of questions asked to give you the exam stamp

Filter and sort data

Limit the rows retrieved by a query
Sort the rows retrieved by a query
Use substitution variables
Limit the rows returned
Create queries using the PIVOT and UNPIVOT clause
Recognize patterns in a table (pattern)

Use functions to customize the output

Know the main families of functions
Know how to use the conversion functions (to_char, to_number, to_date)
Know how to use analytical functions (PERCENTILE_CONT, STDDEV, LAG, LEAD) in SELECT statements

Aggregate data

Understand the philosophy of data aggregation
Know how to implement the group by clause
Include or exclude grouped rows using the HAVING clause

Extract data from multiple tables

Understand and implement the Cartesian product
The basic join (equijoin) and its variants
Self-join (data from a single table)
The outer join
Use the cross_outer_apply_clause clause

Write subqueries

When do we build subqueries?
Variants around subqueries
Implementation

Use the SET operator

Understanding the role of the SET operator
Example of formatting the results of a query
Use SET to combine multiple queries into a single query
Control the order of returned rows

Manipulate data

What are DML instructions?
Insert rows into a table (insert)
Update rows in a table (update)
Delete rows from a table (delete)
Principles of a transaction (commit, rollback)

Manage tables on a daily basis

What are DDL statements?
Instructions for creating and modifying the structure of tables
Know the families of the objects constituting an Oracle database
Examine the structure of a table
Know the data types of columns
Create a simple table
Manage integrity constraints (creation, modification, deletion)
Use 12c enhancements in DEFAULT clause, invisible columns, virtual columns and identity columns in table creation / modification

Diagram objects

Store objects created by users in a logical storage structure (schema)
How to access and get information about the objects of a diagram?
Create simple and complex views
Create, maintain and use sequences
Create and maintain indexes (explicit, implicit)
Removing columns, UNUSED clause
Create private and public synonyms
Create and use external tables

User access control

Differentiate system privileges from object privileges
Grant privileges on tables and on a user
Show privileges in the data dictionary
Assign roles
Distinguish between privileges and roles

Handling large volumes of data

Manipulate data using subqueries
Describe the characteristics of multi-table INSERTs
Use multi table inserts
Unconditional insert
INSERT swivel
ALL INSERT conditional
FIRST INSERT conditional
Merge rows into a table
Track changes to data over a period of time
Use explicit defaults in INSERT and UPDATE statements

Generate reports by grouping related data

Use the ROLLUP operation to produce subtotal values
Use the CUBE operation to generate crosstab values
Use the GROUPING function to identify row values ​​created by ROLLUP or CUBE
Use GROUPING SETS to produce a single set of results

Data management in different time zones

Know how to use different date and time functions such as:
TZ_OFFSET
FROM_TZ
TO_TIMESTAMP
TO_TIMESTAMP_TZ
TO_YMINTERVAL
TO_DSINTERVAL
CURRENT_DATE
CURRENT_TIMESTAMP
LOCALTIMESTAMP
DBTIMEZONE
SESSIONTIMEZONE
EXTRACT

Subqueries

Use multiple column subqueries
Use scalar subqueries
Use correlated subqueries
Update and delete rows using correlated subqueries
Use the EXISTS and NOT EXISTS operators
Use the WITH clause

Hierarchical queries

Knowing how to write a hierarchical query
Understand the output format
Know how to exclude branches from the tree structure

Use regular expressions

Use metacharacters (wildcard syntax)
Use regular expression functions to find, match, and replace
Use the replacement models (pattern)
Regular expressions and constraint checking