Oracle SQL Intermediate - Data Extraction

Recommended attendance on the "Oracle SQL for Beginners" training course

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.

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