Developing SQL Queries for Oracle Databases
COURSE TYPE
Advanced
Course Number
234
Duration
4 Days
Increase productivity and maximize the potential of SQL to formulate intricate queries for Oracle databases. In this training course, you 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.
You Will Learn How To
- Maximize the potential of SQL to formulate complex queries
- Thoroughly test SQL queries and avoid common errors
- Select the most efficient solution to complex SQL problems
- Leverage the analytical functions of Oracle
- Employ inner joins, outer joins, cross joins, and self joins
Important Course Information
Requirements:
SQL and Oracle RDBMS knowledge at the level of: Course 925, SQL Programming Language Introduction, or Course 2226, Oracle Database 12c IntroductionSoftware:
This course is currently running on Oracle 12c
Course Outline
- Introduction and Overview
The uses of SQL queries
- Why SQL can be both easy and difficult
- Recommendations for thorough testing
Enhancing query performance
- Query optimization
- Choosing the most efficient query method
- Leveraging SQL Functions to Build Queries
Aggregate functions
- Grouping in several levels
- Grouping and NULLs
- CUBE and ROLLUP
- Building crosstab reports
- Utilizing 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
- Performing Extensive Analysis with Analytical 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
- Developing Complex Joins
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
- Building Subqueries
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
- Employing Views and Temporary Tables
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
- Introducing Oracle 12
- 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
Convenient Ways to Attend This Instructor-Led Course
Hassle-Free Enrollment: No advance payment required to reserve your seat.
Tuition due 30 days after you attend your course.
In the Classroom
Live, Online
Private Team Training
In the Classroom — OR — Live, Online
Tuition — Standard: $2990 Government: $2659
Jul 25 - 28
(4 Days)
9:00 AM - 4:30 PM EDT
Ottawa / Online (AnyWare)
Reserve Your Seat
Sep 5 - 8
(4 Days)
9:00 AM - 4:30 PM EDT
Rockville, MD / Online (AnyWare)
Reserve Your Seat
Oct 10 - 13
(4 Days)
9:00 AM - 4:30 PM EDT
Online (AnyWare)
Reserve Your Seat
Oct 17 - 20
(4 Days)
9:00 AM - 4:30 PM EDT
New York / Online (AnyWare)
Reserve Your Seat
Dec 5 - 8
(4 Days)
9:00 AM - 4:30 PM EST
Herndon, VA / Online (AnyWare)
Reserve Your Seat
Jan 16 - 19
(4 Days)
9:00 AM - 4:30 PM EST
Ottawa / Online (AnyWare)
Reserve Your Seat
Feb 27 - Mar 2
(4 Days)
9:00 AM - 4:30 PM EST
Rockville, MD / Online (AnyWare)
Reserve Your Seat
Apr 17 - 20
(4 Days)
9:00 AM - 4:30 PM EDT
New York / Online (AnyWare)
Reserve Your Seat
Jun 5 - 8
(4 Days)
9:00 AM - 4:30 PM EDT
Herndon, VA / Online (AnyWare)
Reserve Your Seat
Guaranteed to Run
Private Team Training
Enrolling at least 3 people in this course? Consider bringing this (or any course that can be custom designed) to your preferred location as a private team training.
For details, call 1-888-843-8733 or Click Here »
Tuition
Standard
Government
In Classroom or
Online
Standard
$2990
Government
$2659
Private Team Training
Course Tuition Includes:
Training Hours
Standard Course Hours: 9:00 am – 4:30 pm
*Informal discussion with instructor about your projects or areas of special interest: 4:30 pm – 5:30 pm
FREE Online Course Exam (if applicable) – Last Day: 3:30 pm – 4:30 pm
By successfully completing your FREE online course exam, you will:
- Have a record of your growth and learning results
- Bring proof of your progress back to your organization
- Earn credits toward industry certifications (if applicable)
Enhance Your Credentials with Professional Certification
Learning Tree's comprehensive training and exam preparation guarantees that you will gain the knowledge and confidence to achieve professional certification and advance your career.
Earn 23 Credits from NASBA
This course qualifies for 23 CPE credits from the National Association of State Boards of Accountancy CPE program. Read more ...