Oracle SQL Intermediate - Data Extraction 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.
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
Requirements
Recommended attendance on the "Oracle SQL for Beginners" training course
Open Training Courses require 5+ participants.
Oracle SQL Intermediate - Data Extraction Training Course - Booking
Oracle SQL Intermediate - Data Extraction Training Course - Enquiry
Oracle SQL Intermediate - Data Extraction - Consultancy Enquiry
Consultancy Enquiry
Testimonials (5)
Łukasz's way of leading
Damian - Sygnity
Course - Oracle SQL - advanced
Machine Translated
I thought that the information was interesting.
Allison May
Course - Data Visualization
I like the exercises done.
Nour Assaf
Course - Data Mining and Analysis
The trainer was so knowledgeable and included areas I was interested in.
Mohamed Salama
Course - Data Mining & Machine Learning with R
Much to explore after the training
Klaudia Klebek
Course - Data Mining z wykorzystaniem R
Machine Translated
Provisional Courses
Related Courses
From Data to Decision with Big Data and Predictive Analytics
21 HoursAudience
If you try to make sense out of the data you have access to or want to analyse unstructured data available on the net (like Twitter, Linked in, etc...) this course is for you.
It is mostly aimed at decision makers and people who need to choose what data is worth collecting and what is worth analyzing.
It is not aimed at people configuring the solution, those people will benefit from the big picture though.
Delivery Mode
During the course delegates will be presented with working examples of mostly open source technologies.
Short lectures will be followed by presentation and simple exercises by the participants
Content and Software used
All software used is updated each time the course is run, so we check the newest versions possible.
It covers the process from obtaining, formatting, processing and analysing the data, to explain how to automate decision making process with machine learning.
Data Mining and Analysis
28 HoursObjective:
Delegates be able to analyse big data sets, extract patterns, choose the right variable impacting the results so that a new model is forecasted with predictive results.
Data Mining
21 HoursCourse can be provided with any tools, including free open-source data mining software and applications
Data Mining with R
14 HoursR is an open-source free programming language for statistical computing, data analysis, and graphics. R is used by a growing number of managers and data analysts inside corporations and academia. R has a wide variety of packages for data mining.
Data Visualization
28 HoursThis course is intended for engineers and decision makers working in data mining and knoweldge discovery.
You will learn how to create effective plots and ways to present and represent your data in a way that will appeal to the decision makers and help them to understand hidden information.
Data Mining & Machine Learning with R
14 HoursR is an open-source free programming language for statistical computing, data analysis, and graphics. R is used by a growing number of managers and data analysts inside corporations and academia. R has a wide variety of packages for data mining.
Oracle SQL - introduction to working with the database
21 HoursLevel
Basic
Form
Lecture, Presentation, Workshop
Purpose of the training
The training is dedicated to people with a user, programmer or administrator profile who are starting to work with Oracle database.
Benefits of completing the training
The training will provide participants with the skills to work with Oracle database using SQL language and enables them to participate in further directional training in this field.
The training prepares you to pass the Oracle Database 12c: SQL Fundamentals (1Z0-061) certification exam, which is an official, internationally respected validation of your skills and required for most Oracle certifications in database administration and programming.
Oracle SQL - advanced
14 HoursBy the end of this training, participants will be able to:
The training is dedicated to people with the profile of user, programmer or administrator who already have some knowledge of working with Oracle database and want to deepen and systematize it. It is a complement to the ORA_S1 training course.
By the end of this training, participants will be able to:
- The training will provide participants with the skills to efficiently store and process data using SQL, introducing knowledge of optimization, analysis and various methods of data extraction. At the same time, it will provide knowledge of the database architecture and its advanced capabilities to create effective applications.
Format of the Course
- Interactive lecture and discussion.
- Lots of exercises and practice.
- Hands-on implementation in a live-lab environment.
Course Customization Options
- To request a customized training for this course, please contact us to arrange.
Oracle SQL for analysts - fundamentals
21 HoursLevel
Basic
Form
Lecture, Presentation, Intensive workshop
Purpose of the training
The training is addressed to data analysts and users who need to efficiently and flexibly extract data from the database using SQL language commands directly.
Benefits of completing the training
Learn the basic techniques of data analysis using SQL language.
Oracle SQL for analysts - advanced
14 HoursLevel
Advanced
Form
Lecture, Presentation, Intensive workshop
Purpose of the training
The training is addressed to data analysts and users who need to efficiently and flexibly extract data from the database using SQL language commands directly. It complements the ORA-SA1 training with advanced SQL language mechanisms.
Benefits of completing the training
Learn advanced data analysis techniques using SQL language for more flexible and efficient reporting.
Oracle SQL for data analysts - programming and performance
21 HoursThe training is addressed to data analysts and users who need to flexibly and efficiently extract data from the database using SQL commands directly. It is a complement to the ORA_SA1 training and focuses on the efficiency of executing SQL statements and on scenarios in which the SELECT command alone is not enough and one should use the much more powerful PL/SQL language.
Oracle SQL for Administrators
21 HoursThis instructor-led, live training in Poland (online or onsite) is aimed at beginner-level database administrators and developers who wish to learn SQL to efficiently manage, query, and manipulate data in Oracle databases for administrative and operational tasks.
By the end of this training, participants will be able to:
- Understand Oracle database architecture, including schemas, users, and sessions.
- Write and optimize SQL queries for data retrieval, filtering, and manipulation.
- Use joins, subqueries, and set operators to work with multiple tables and complex queries.
- Perform data aggregation, grouping, and analysis using SQL functions.
- Manage schema objects like views, sequences, and indexes to enhance database operations.
Oracle SQL for Developers
35 HoursThis instructor-led, live training in Poland (online or onsite) is aimed at intermediate-level developers and database administrators who wish to use Oracle SQL to design, manage, and optimize database applications and automate tasks efficiently.
By the end of this training, participants will be able to:
- Understand Oracle database architecture and manage users, schemas, and permissions.
- Write optimized SQL queries for data retrieval, manipulation, and analysis.
- Use advanced SQL techniques, including joins, subqueries, and hierarchical queries.
- Design and manage database objects like tables, indexes, views, and sequences.
Introductory R for Biologists
28 HoursR is an open-source free programming language for statistical computing, data analysis, and graphics. R is used by a growing number of managers and data analysts inside corporations and academia. R has also found followers among statisticians, engineers and scientists without computer programming skills who find it easy to use. Its popularity is due to the increasing use of data mining for various goals such as set ad prices, find new drugs more quickly or fine-tune financial models. R has a wide variety of packages for data mining.
Statistics with SPSS Predictive Analytics Software
14 HoursGoal:
Learning to work with SPSS at the level of independence
The addressees:
Analysts, researchers, scientists, students and all those who want to acquire the ability to use SPSS package and learn popular data mining techniques.