Find online courses made by experts from around the world.
Take your courses with you and learn anywhere, anytime.
Learn and practice real-world skills and achieve your goals.
This course teaches people in Database reporting, Business Intelligence or any field that requires creating database reports in Oracle how to use some of the cool functions in Oracle to answer real-world questions. Each lesson covers one or more reporting and analysis function / technique in Oracle and uses Oracle 11gR2 for demos. You will get a combination of slideshows and hands-on query exercises, including challenges and brain-teasers to help you think deeper into problems and write simple queries that answer these questions.
All the queries in this course are included for download. Also, make sure you attempt all the challenge exercises before you look at the solutions.
You should take this course if you are looking to up your game in database querying, data mining and business intelligence using SQL queries. A special section is dedicated to Analytic Functions, a really cool set of functions in Oracle that make it easy to write highly scalable, high performance queries with very little code. It starts wit an introduction of Analytic functions and then goes deeper into using these functions to answer business questions. Have fun, as more examples are uploaded to this course.
Not for you? No problem.
30 day money back guarantee.
Learn on the go.
Desktop, iOS and Android.
Certificate of completion.
|Section 1: Basics first|
Unlocking the Sample SchemaPreview
Download the script to install the HR schema if you don't find it pre-installed in your database, and instructions on how to install it.
Also download the SQL script that contains all the statements run in this course. With this, you can follow the course at your own pace.
|Section 2: Exploring Data Aggregation, Temporary Tables and Conditionals in Reports|
Learn how to use Subqueries to resolve queries in Oracle. Use the challenge that follows to learn how to solve complex problems with correlated subqueries.
This challenge will help you understand what a correlated subquery is, and how to use correlated subqueries to answer business questions.
Flipping rows to columns is a powerful reporting tool in data analysis. If you have worked with Excel before, then you have seen the PIVOT function. learn how to use the PIVOT function in Oracle to transpose rows to column headings.
In this lesson, you will learn how to use temporary tables (Called Common Table Expressions in other databases management systems). These temporary object are really valuable in reducing query complexity, especially when dealing with aggregate functions.
Use this brain teaser to test your knowledge of temporary tables in oracle.
Evaluating conditions within a SQL Query
How to aggregate ROWS in Oracle
Row Aggregation Brain Teaser
|Section 3: Introducing Reporting and Analysis Functions in Oracle|
What analytic functions are
Analytic Functions versus Regular SQL - a taste testPreview
Syntax of Analytic Functions in Oracle
What the PARTITION BY clause does in Analytic Functions
What the ORDER BY clause does in Analytic Functions
Piecing the ORDER BY and PARTITION BY clauses together
Restricting rows with WINDOWING
|Section 4: Querying Hierarchical Data in Oracle|
The nature of Hierarchical DataPreview
Hierarchical statements - the CONNECT BY clause
Hierarchical statements - the START WITH keyword
Top-Down versus Bottom-Up hierarchical query
Generating complete parent-child relationship paths
Catching cyclic hierarchical relationships
|Section 5: Using PARTITION BY / RIGHT OUTER JOIN|
Overview and Data used
What we will accomplish
Partition by Right Outer Join Demo
|Section 6: Working with the MERGE statement|
Introducing the MERGE statement
Data used in this section
Revisiting regular INSERT, UPDATE, DELETE statements
The MERGE syntax
MERGE Demo I
MERGE Demo II
I'm a professionally trained database developer, administrator and a Spatial Ecologist. My work experience spans multiple disciplines, including developing and maintaining databases for nonprofit organizations, performing data science tasks for research institutions, doing extensive data mining in the Oil and gas sector as well as spatial ecology. With over 10 years of experience, I currently coordinate database development for one of Canada's biggest nonprofit organizations. If you need services in oracle, PLSQL, TSQL, Python geospatial scripting or R programming, talk to me!!