Description

This training will allow you to acquire all the skills necessary for the efficient and reliable use of the product: relational principles, concepts and practice of SQL language, physical organization and transaction management. The main orders will be analyzed and implemented.

Who is this training for ?

For whom ?

IT professionals having to develop for Oracle or wanting a first approach to administration.

Prerequisites

Basic knowledge of programming.

Training objectives

Know the architecture of an Oracle database.

Understand the relational model.

Create a database with its schema objects.

Manipulate data with SQL and PL/SQL.

Discover management tasks of a base.

Training program

• Introduction
  • The Oracle server.
  • The different versions.
  • The different editions.
  • The relational model.
  • The notions of table and relationship.
  • Integrity constraints.
  • Tools.
  • SQL Developer.
  • The ISQL*Plus tool.
  • SQL introduction.
  • Oracle data types.
  • Creating tables.
  • Introduction to setting statements up to date.
  • Using sequences.
  • Introduction to querying (SELECT).
  • Manipulating data.
  • Introduction to transactions.
• Langage d'interrogation SQL
  • Different SQL features.
  • Sorting and removing duplicates.
  • Functions and aggregates.
  • Type conversion and NULL value.
  • Queries on multiple tables.
  • Joins. Nested queries.
  • Set operators.
• Introduction to PL/SQL
  • PL/SQL language instructions.
  • Types of variables.
  • Control instructions: conditions, iterations.
  • Error management using the exception mechanism.
  • Structuring PL/SQL programs.
  • Procedures and functions.
  • Definition and use of stored procedures.
  • Specification of packages.
  • Interaction with the database.
  • Implicit and explicit cursors.
• Triggers LMD dans Oracle
  • Presentation of Oracle triggers.
  • Structure of a trigger (Event/Action).
  • Types of LMD triggers (statement and row triggers).
  • Actions of a trigger (automatic updating of data, cancellation of an illegal action, audit).
  • Using triggers.
  • Creation and use of instruction triggers.
  • Use of line triggers: NEW and: OLD in these triggers Trigger management.
• Architecture and objects of a database
  • Manipulation of tables. Creation of tables.
  • Definition of integrity constraints.
  • Deferred integrity constraints.
  • Deletion of tables and the trash.
  • Using temporary global tables.
  • Using views and synonyms: creating and using views and synonyms.
  • Organizing data: Oracle storage models. Definition and use of indexes.
  • Data dictionary: Data dictionary tables and views.
• Transaction management
  • Access to the database by transactions: partial cancellation of transactions. PL/SQL and transactions.
  • Concurrency management and data locking. Characteristics of standard management.
  • Read-only transactions, isolation levels, explicit locking.
• Security and users
  • Access rights.
  • Object privileges, system privileges and roles.
  • Assigning and revoking access rights.
  • Rights to stored views and processing.
  • User management.
  • User authentication.
  • User profiles and resource usage limits.