Description

This very practical training aims to develop advanced aspects of the SQL language such as set operators, management of LOBs, Bfiles and XML, aggregation and statistics functions.

Who is this training for ?

Anyone indirectly involved in the execution of advanced SQL queries (developer, DBA, project owner).

Training objectives

Training program

• Additional functions
  • - Transformation functions.
  • - Analytical functions: Rank, Dense_Rank.
  • - Hierarchical function.
  • - Other functions: Rowid, etc.
  • - Practical work Formatting disparate data (dates, numerics), list of a hierarchy at several levels.
• The assembly operators
  • - Union and Union ALL.
  • - Difference Minus.
  • - Exists, Some, Any, All.
  • - Practical work Research of differences between several tables.
• Management of LOBs and Bfiles
  • - Possible uses.
  • - Definition of tables.
  • - Insertion and updates of LOBs.
  • - Management of a Bfile.
  • - Practical work Adding a photo to a table, adding a CV to a table.
• Aggregation, joins and sub-select
  • - Aggregation and statistical functions: Group By Rollup.
  • - Group by Cube.
  • - Group by Sets.
  • - Recalling joins "classics".
  • - Self-join.
  • - FULL, RIGHT, LEFT.
  • - Sub-select correlated/uncorrelated with Insert, Update , Delete.
  • - Practical work Statistics and groupings based on several criteria.
  • - Creating a table from other tables.
• Integrity management
  • - Reminder of definitions: uniqueness, referential, consistency.
  • - Implementation: Primary Key, Foreign Key, Increment, Check.
  • - Principles of use.
  • - Good practices and limits.
  • - Practical work Creation of referential integrity tables.
  • - Addition/removal of integrity constraints and checks by Check.
• XML management
  • - Storing XML.
  • - Using advanced XML functions.
  • - Practical work Managing an XML document.
• Data partitioning
  • - The different types of possible partitioning.
  • - Implementation of data partitioning.
  • - Practical work Creation of partitioned tables, reloading of data.
• Managing remote tables
  • - Reminder of the principles: schemas, ODBC, DRDA.
  • - Access to a remote table.
  • - Joins with remote tables.
  • - Work practices Reading tables from other comics.