Oracle Database 19c Meet-Up Date & Time: Jul 12, 2024 11:00 AM EST
[Register here:
https://us06web.zoom.us/meeting/register/tZwsceivqTwjEtKp5UYUV_MYhJ5qMf3Ye2XB ]
I will be presenting at the Quest Oracle Database 19c SIG Meet-Up on the topic of “SQL and Analytics - Unleashing the Full Potential”. The goal of this session [in keeping with the famous William Butler Yeats quote: “Education is not the filling of a bucket, but the lighting of a fire”] is to explore numerous SQL and Analytics capabilities and methods and ignite a fire for learning and experimentation in order to help you enhance your career and solve business problems.
Abstract:
In today's rapidly evolving landscape of data analysis, SQL continues to stand tall as the unrivaled powerhouse. Despite various attempts to replace it, SQL remains an essential skill for professionals in the fields of DBA and software engineering. Now, more than ever, mastery of SQL is becoming increasingly crucial, enabling individuals to unlock the true potential of their data.
Join us for an engaging and enlightening presentation that will take you on a comprehensive journey into the realm of highly impactful SQL techniques for data analysis and actionable insights. This session will demonstrate how SQL can seamlessly incorporate big data and data science approaches, elevating your analytical capabilities to new heights.
During this high-speed tour, we will delve into the incredible versatility of SQL, unraveling its indispensable role in statistical-based analytics. Whether you are a seasoned analyst or just starting out on your data-driven journey, this session will equip you with the knowledge and tools to harness the true power of SQL in your organization.
Content Summary:
Analytics is not just for the Data Scientist. Analytics will yield Insights and influence outcomes helping us extract value from data using SQL. Topics include:
Ø Where SQL fits within the analytics landscape
Ø A jet tour through many SQL capabilities
Ø Sample SQL code
Ø Broader analytics concepts
SQL Capabilities Jet Tour
Thinking in Sets
This will come with practice, but it is indispensable for fully mastering SQL.
WITH clause subquery (Common Table Expressions)
Common Table Expressions (CTEs) are a powerful SQL feature that simplifies complex queries and makes them more readable and can be used for recursive queries, data manipulation, and organizing SQL logic.
Aggregate Functions
Aggregate functions are leverage Oracle’s highly scalable subset/sort/merge/aggregation capabilities.
Ø MIN / MAX / AVG / SUM / COUNT / VARIANCE / MEDIAN / . . .
https://docs.oracle.com/database/121/SQLRF/functions003.htm#SQLRF20035
Ø STDDEV – standard deviation
o Used for: Normal range for a variable / metric:
Rule of thumb +/- 2 standard deviations from the mean
Ø Percentiles
o quantify the importance of a variable among a set of values
o e.g. SAT scores reported as being in a certain percentile
o e.g. anomaly detection - values above 98th percentile
Ø Distributions and Histograms
o Analyze data value distributions within a variable
Ø Correlation
o Analyze data value relationship between variables
Ø Regression
o Analyze what direction the values are going (e.g. Up or down)
Ø LAG / LEAD
o LAG: See the value for the previous row in the table
o LEAD: See the value for the next row in the table
Pivot / Unpivot
How SQL can be used to transform data from a row-wise format to a column-wise format (pivoting) and vice versa (unpivoting). This is especially useful for creating summary tables or working with data from multiple sources.
XML / HTML / JSON
Oracle offers many capabilities to parse and produce data in these formats.
Joins and set operators
Again, an essential SQL mastery topic.
UNION / INTERSECT / MINUS etch clause
Another group of set-oriented capabilities of the SQL language.