This Oracle SQL Queries training course will help you increase productivity and maximise the potential of SQL to formulate intricate queries for Oracle databases. Learn to solve business problems easily, efficiently, and elegantly with only a few lines of SQL code, and gain the practical skills to choose the best query method — ensuring accuracy and avoiding common errors or pitfalls.
SQL and Oracle RDBMS knowledge at the level of: Course 925, SQL Programming Language Introduction, or Course 2226, Oracle Database 12c Introduction
This course is currently running on Oracle 12c
Developing SQL Queries for Oracle Databases Delivery Methods
- After-course instructor coaching benefit
- Learning Tree end-of-course exam included
Developing SQL Queries for Oracle Databases Course Benefits
Maximise the potential of SQL to formulate complex queriesThoroughly test SQL queries and avoid common errorsSelect the most efficient solution to complex SQL problemsLeverage the analytical functions of OracleEmploy inner joins, outer joins, cross joins, and self joins
Oracle SQL Queries Course Outline
The uses of SQL queries
- Why SQL can be both easy and difficult
- Recommendations for thorough testing
Enhancing query performance
- Query optimisation
- Choosing the most efficient query method
Aggregate functions
- Grouping in several levels
- Grouping and NULLs
- CUBE and ROLLUP
- Building crosstab reports
- Utilising the PIVOT operator in Oracle 11g
Single-row functions
- String-manipulation functions
- Functions for date and time manipulation
- Simulating IF ... THEN ... ELSE with functions
- Handling regular expressions with Oracle 10g functions
Calculating ranks
- RANK and DENSE_RANK
- ROW_NUMBER depending on ORDER BY
Extending the use of aggregates
- Partitioning in multiple levels
- Computing running totals
- Comparing row and aggregate values
- Defining sliding window boundaries
Creating inner and outer joins
- Building multiple table joins
- Grouping and joins
How and when to use self-joins
- Implementing recursive self-joins with CONNECT BY
- CONNECT BY and join simultaneously
- Oracle 10g enhancements to CONNECT BY
Applying the ANSI standard join syntax
- INNER JOIN
- CROSS JOIN
- LEFT, RIGHT and FULL OUTER JOIN
- Adding filter conditions to OUTER JOINS
- Differences between new ANSI and old Oracle style
Manipulating the set operators
- UNION and UNION ALL
- INTERSECT
- MINUS
Noncorrelated subqueries
- Subqueries that return NULL
- Multiple row subqueries
- Multiple column subqueries
Handling correlated subqueries
- Fetching main query values
- The EXISTS operator
- Avoiding accidental correlation
Subqueries in the FROM clause
- Breaking up a complex problem into manageable pieces
- Factoring subqueries for reusability
- Applying recursive factored subqueries in Oracle 11g R2
Subqueries as expressions
- Subqueries as parameters to functions
- Correlated and noncorrelated subqueries in expressions
Overcoming obstacles with views
- Multiple group levels in one query
- How views impact performance
Temporary tables as alternatives to views
- Avoiding interference from other users
- Tailoring temporary tables
- Limiting the number of rows returned with FETCH
- Retrieving partly results with OFFSET
- Simulating joins with lateral views and APPLY
- Declaring local functions in the WITH clause
