Get in Touch
 Duration 21 hours

Course Outline

Extracting Data from Databases

  • SQL syntax fundamentals
  • Retrieving all columns
  • Column projection
  • Performing arithmetic operations in SQL
  • Using column aliases
  • Working with literals
  • String concatenation

Filtering Result Sets

  • Utilizing the WHERE clause
  • Comparison operators
  • The LIKE condition
  • The BETWEEN...AND condition
  • Handling NULL values with IS NULL
  • The IN condition
  • Logical operators: AND, OR, and NOT
  • Combining multiple conditions in a WHERE clause
  • Operator precedence
  • Removing duplicates with the DISTINCT clause

Ordering Result Sets

  • The ORDER BY clause
  • Sorting by multiple columns or complex expressions

SQL Functions

  • Distinguishing between single-row and multi-row functions
  • Character, numeric, and DateTime functions
  • Explicit versus implicit data conversion
  • Using conversion functions
  • Nesting functions
  • The Dual table: Oracle versus other database systems
  • Retrieving current date and time using various functions

Aggregating Data with Aggregate Functions

  • Overview of aggregate functions
  • Handling NULL values in aggregate functions
  • The GROUP BY clause
  • Grouping by multiple or different columns
  • Filtering aggregated results with the HAVING clause
  • Multi-dimensional grouping using ROLLUP and CUBE operators
  • Identifying summary rows with the GROUPING function
  • The GROUPING SETS operator

Querying Multiple Tables

  • Various types of joins
  • NATURAL JOIN
  • Applying table aliases
  • Oracle syntax: specifying join conditions in the WHERE clause
  • SQL99 syntax: INNER JOIN
  • SQL99 syntax: LEFT, RIGHT, and FULL OUTER JOINs
  • Cartesian products: Oracle and SQL99 syntax

Subqueries

  • Placement and usage contexts for subqueries
  • Single-row and multi-row subqueries
  • Operators for single-row subqueries
  • Incorporating aggregate functions into subqueries
  • Operators for multi-row subqueries: IN, ALL, and ANY

Set Operators

  • UNION
  • UNION ALL
  • INTERSECT
  • MINUS or EXCEPT

Transactions

  • Managing transactions with COMMIT, ROLLBACK, and SAVEPOINT statements

Other Schema Objects

  • Sequences
  • Synonyms
  • Views

Hierarchical Queries and Sampling

  • Constructing trees using CONNECT BY PRIOR and START WITH clauses
  • The SYS_CONNECT_BY_PATH function

Conditional Expressions

  • CASE expressions
  • DECODE expressions

Managing Data Across Time Zones

  • Understanding time zones
  • TIMESTAMP data types
  • Distinguishing between DATE and TIMESTAMP
  • Time zone conversion operations

Analytic Functions

  • Application and usage
  • Partitions
  • Windows
  • Ranking functions
  • Reporting functions
  • LAG and LEAD functions
  • FIRST and LAST functions
  • Reverse percentile functions
  • Hypothetical ranking functions
  • WIDTH_BUCKET functions
  • Statistical functions

Requirements

There are no specific prior requirements for enrollment in this course.

Custom Corporate Training

Training solutions designed exclusively for businesses.

  • Customized Content: We adapt the syllabus and practical exercises to the real goals and needs of your project.
  • Flexible Schedule: Dates and times adapted to your team's agenda.
  • Format: Online (live), In-company (at your offices), or Hybrid.
Investment

Price per private group, online live training, starting from 4350 € + VAT*

Contact us for an exact quote and to hear our latest promotions

Testimonials (7)

Upcoming Courses

Related Categories