Duration
14 hours (usually 2 days including breaks)
Requirements
Recommended attendance on the “Oracle SQL for Beginners” training course
Overview
The objective of the course is to enable participants to gain a mastery of how to work with the SQL language in Oracle database for data extraction at intermediate level.
Course Outline
Limiting results
- The WHERE clause
- Comparison operators
- LIKE Condition
- Prerequisite BETWEEN … AND
- IS NULL condition
- Condition IN
- Boolean operators AND, OR and NOT
- Many of the conditions in the WHERE clause
- The order of the operators.
- DISTINCT clause
SQL functions
- The differences between the functions of one and multilines
- Features text, numeric, date,
- Explicit and implicit conversion
- Conversion functions
- Nesting functions
- Viewing the performance of the functions – dual table
- Getting the current date function SYSDATE
- Handling of NULL values
Aggregating data using the grouping function
- Grouping functions
- How grouping functions treat NULL values
- Create groups of data – the GROUP BY clause
- Grouping multiple columns
- Limiting the function result grouping – the HAVING clause
Subqueries
- Place subqueries in the SELECT command
- Subqueries single and multi-lineage
- Operators Subqueries single-line
- Features grouping in subquery
- Operators Subqueries multi-IN, ALL, ANY
- How NULL values are treated in subqueries
Operators collective
- UNION operator
- UNION ALL operator
- INTERSECT operator
- MINUS operator
Further Usage Of Joins
- Revisit Joins
- Combining Inner and Outer Joins
- Partitioned Outer Joins
- Hierarchical Queries
Further Usage Of Sub-Queries
- Revisit sub-queries
- Use of sub-queries as virtual tables/inline views and columns
- Use of the WITH construction
- Combining sub-queries and joins
Analytics functions
- OVER clause
- Partition Clause
- Windowing Clause
- Rank, Lead, Lag, First, Last functions
Retrieving data from multiple tables (if time at end)
- Types of connectors
- The use NATURAL JOIN
- Aliases tables
- Joins in the WHERE clause
- INNER JOIN
- External Merge LEFT, RIGHT, FULL OUTER JOIN
- Cartesian product
Aggregate Functions (if time at end)
- Revisit Group By function and Having clause
- Group and Rollup
- Group and Cube