Course Code

osqlide

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

Testimonials

★★★★★

Great teacher with in depth knowledge and real life, relevant examples. Engaging and always ensures a section is understood before moving on to next.

Sade Okiji

Course:SQL Fundamentals

The course content was adapted well to suit my current knowledge and capabilities. Specific topics I requested were covered well.

Steven Sirman - Corus Group

Course:SQL Fundamentals

The trainer is a great trainer and has very professional tone. I would definitely like to attend a SQL Advanced course in the near future which is hosted by the same trainer.

Mitul. P. Parmar - Cancer Research UK

Course:SQL Fundamentals

Having a 1-1 training course was very effective, it meant I was less intimidated as I could stop the trainer to ask questions where I may otherwise have been reluctant to ask. The trainer was very good at explaining everything that I needed to know and took the time to make sure going back where needed to re explain when I got stuck. The training was very clear and concise and tailored very well to my own individual needs. The training has covered all the points it needed too.

Robert Craft - Zurich Employment Services Limited

Course:SQL Fundamentals

This course was truly fantastic, spot-on! It covers the basics but also details of SQL language. I will definitely recommended this course to my colleagues. There simply isn't another course out there that would give you so much in such a short time, great value for money. A must for any beginner or intermediate SQL user!

Piotr Olczak Planing Officer - Brunel University

Course:SQL Fundamentals

I really can't praise this course and the trainers highly enough! They were both extremely well qualified and made the subject interesting. The pace of the course was excellent and I've come away feeling very satisfied with the skills I've learned.

Susan Elder - Merrill Lynch

Course:SQL Fundamentals

The trainer was extremely knowledgeable and ensured that I properly understand at every step. I am very happy with the course.

Takuya Yorita - Mizuho Corporate Bank Ltd

Course:SQL Fundamentals

Having a 1-1 training course was very effective, it meant I was less intimidated as I could stop the trainer to ask questions where I may otherwise have been reluctant to ask.

Robert Craft - Zurich Employment Services Limited

Course:SQL Fundamentals

The course content was adapted well to suit my current knowledge and capabilities. Specific topics I requested were covered well.

Steven Sirman - Corus Group

Course:SQL Fundamentals

The format was really good, the exercises were not too difficult and helped us to fully understand the tool.

Juan Salvador Rios Hoyos- Oracle

Course:Oracle SQL Advanced

The trainer was enthusiastic & would answer whatever we needed.

Erica May - Big Fish Games, Inc

Course:SQL Fundamentals

The course I loved that it was just Andrew (the trainer) & my team. It was very personalized. Thanks for flying cross-country for us!.

Bronwyn Bailey - PEGCC

Course:SQL Fundamentals

Trainer was great--nothing to improve. The way that Andrew (the trainer) customized to first day of training to the level of our group.

Josh Cox - PEGCC

Course:SQL Fundamentals

What did you like the most about the training?: Catering :D and informal atmosphere in class. Teacher was really helpful when I had questions.

Andrei Falileev - Citibank International Limited

Course:Oracle SQL for beginners

What did you like the most about the training?: Focused on important stuff, helpful with exercises.

Kamil Jarosz - Citibank International Limited

Course:Oracle SQL for beginners

What did you like the most about the training?: It was really practical, I found it really useful. The trainer use our feedback and provide a really good personalize training.

Diego Moreno Galan - Credit Suisse (Poland) Sp. z o.o.

Course:ORACLE SQL Fundamentals

Training exceeded my expectations. Trainer provided additional topics / exercises since we have finished the programmed ahead of time.

Artur Czeczil - Credit Suisse (Poland) Sp. z o.o.

Course:ORACLE SQL Fundamentals

What did you like the most about the training? Phil catered for all our needs and even though we had a manual to work through he did not stick to it but was willing to explain our questions about how things can be used in our work and had a lot of patience explaining stuff to us.

Lorett Bredenkamp - Save the Children

Course:Oracle SQL Intermediate

What did you like the most about the training?: Everything was completely understandable.

Joanna Staniszewska - Citibank International Limited

Course:Oracle SQL for beginners

What did you like the most about the training?: The trainer, he was knowledgeable, engaging, and easy to learn from. He encouraged a lot of hands-on learning.

Shawn McAndrew - Halifax Regional Municipality

Course:SQL Fundamentals

What did you like the most about the training?: That it was well organized. Topic was divided into parts and for every part there were exercises, I wasn't lost at all.

Monika Klimczak - LUXOFT POLAND SP z. o. o.

Course:SQL Fundamentals

Trainer provided some topic and support it with plenty of exercises. We had a chance to apply knowledge by doing them on our own.

UBS Business Solutions Poland Sp. z o.o.

Course:Oracle SQL Intermediate

I enjoyed the access to trainers individual support in resolving exercises.

Tomasz Czornak

Course:Oracle SQL Intermediate

The trainer, he was knowledgeable, engaging, and easy to learn from. He encouraged a lot of hands-on learning.

Shawn McAndrew

Course:SQL Fundamentals

I learned a LOOOOOT.

Kamil Szmid

Course:SQL Fundamentals

very logical and step by step but not too much

Anne Homer - States of Jersey

Course:SQL Fundamentals

I enjoyed the flexibility to explore different areas outside the set training materials.

BMW Group

Course:SQL Fundamentals

n/d

Artur Bryś - Centrum Innowacji ProLearning

Course:SQL Fundamentals

Phil was incredibly knowledgeable, worked at our pace and took the time to talk us through multiple ways of doing different queries. He consistently took the time throughout the sessions to check both myself and my colleague were on board with what was being discussed, no question was too big/too small. This was my first time taking a training course remotely and I'm completely sold on it - Phil is a credit to you.

Chris Teasdale - Openreach

Course:SQL Fundamentals

Pace of the training and detail of content was perfect for a virtual session

Chris Hopton - Openreach

Course:SQL Fundamentals

The exercise

Federal Demographic Council

Course:Oracle SQL for beginners

the exercises

Amna AlAli - Federal Demographic Council

Course:Oracle SQL for beginners

The explanation are very clear and the exercises help understand better