Oracle SQL for Data Science

Name: Oracle SQL for Data Science
Rating: 4.4 (6 reviews)

Oracle SQL skills and best practices for data science/data analytics tasks.

Created byJiang Zhou

Last updated 1/2019

English

English [Auto],

What you'll learn

Learn practical Oracle SQL skills and best practices for typical data science/data analytics tasks. Become more productive, making less mistakes, better managing the data and scripts, and stress free when performing complex data work.

Course content

8 sections • 50 lectures • 4h 23m total length

About the Instructor3:03
Dr. Jay Zhou has been involved in 3 head to head competitions to build the best models for clients and he won them all. His work has been used by top telecommunication companies and banks in America and Canada. His favorite tool is SQL. He is the author of the blog https://www.deep-data-mining.com, a Feedspot Top 30 Big Data Blogs Winner.
Content of the Course0:53
All slides used in the course are downloadable as a PDF file. The course will cover the following topics. *Why SQL for Data Science? *How to perform common tasks using SQL including: **Data Validation and Understanding **Data Cleansing and Preparation **Feature Variable Calculation. These tasks typically take 80% or more time when performing a data science project. The course will NOT cover predictive model building.
About the SQL Scripts and Testing Data Used in the Course0:38
There are 2 SQL script files used for this course. Script File 1. sql_for_ds_data_prep.sql. This file should be run first. -- This script file will create 3 tables and populate them with data. -- These tables are card_txn, sales and score. -- If these tables exist, they will be dropped first. -- There are 110, 9 and 817 records in table card_txn, sales and score, respectively. Script File 2. sql_for_ds.sql. This file contains the SQL queries presented in course. We may load them into SQL Clients such as SQL Developer and run them.

Typical Tasks for Data Science Project - Part 111:44
Identify and clean data in a data science project by validating, understanding, preparing, and calculating feature variables before modeling with SQL in-database analysis.
Typical Tasks for Data Science Project - Part 23:11
Map database tables to SAS data sets, columns to variables, and records to data points, noting SAS keeps a fixed order while database tables have no order without defined criteria.

SQL for Data Validation and Understanding - Part 111:21
SQL for Data Validation and Understanding - Part 24:31
Explore persisting query results by creating and replacing tables, compare with views that preserve source code, and compute min and max transaction amounts from the card transaction table.
SQL for Data Validation and Understanding - Part 3
SQL for Data Validation and Understanding - Part 3b2:17
SQL for Data Validation and Understanding - Part 42:54
Use count distinct to quantify unique values, drop nonvarying columns like country, and compute unique state and city combinations with a two-step query approach.
SQL for Data Validation and Understanding - Part 510:51
Calculate total transaction amount by state and its percentage of the dataset, and count transactions, using with, subqueries, joins, and ratio_to_report, noting Pennsylvania around 58 percent.
SQL for Data Validation and Understanding - Part 6
SQL for Data Validation and Understanding - Part 79:49
SQL for Data Validation and Understanding - Part 88:50
SQL for Data Validation and Understanding - Part 92:14
SQL for Data Validation and Understanding - Part 105:17
SQL for Data Validation and Understanding - Part 116:12
SQL for Data Validation and Understanding - Part 124:22
Compute the correlation between transaction amount and time of day in Oracle SQL by extracting hours since midnight with trunc(date) and 24, reporting -0.09 with the R function.
SQL for Data Validation and Understanding - Part 137:17
SQL for Data Validation and Understanding - Part 1413:17

SQL for Data Cleansing and Preparation - Part 19:15
SQL for Data Cleansing and Preparation - Part 212:21
SQL for Data Cleansing and Preparation - Part 32:53
Replace missing values with the state average instead of the global mean, after imputing missing state codes with the mode, and calculate the by-state average amount via views.
SQL for Data Cleansing and Preparation - Part 43:55
SQL for Data Cleansing and Preparation - Part 55:16
Convert continuous income into discrete categories using pini variables, creating low, medium, and high income bins with assigned weights to build a more flexible, robust model and final score.
SQL for Data Cleansing and Preparation - Part 64:04
Define manual boundaries for continuous variables with case when to create bins, and store the results in a replaceable view for data cleansing and preparation.
SQL for Data Cleansing and Preparation - Part 79:51
SQL for Data Cleansing and Preparation - Part 86:50
SQL for Data Cleansing and Preparation - Part 93:17
SQL for Data Cleansing and Preparation - Part 108:15
SQL for Data Cleansing and Preparation - Part 115:20
SQL for Data Cleansing and Preparation - Part 123:44
Mask the card numbers using a mapping table of old and new cards, and expose results via a view for on-the-fly queries; speed up joins with a card-number index.
SQL for Data Cleansing and Preparation - Part 134:00
SQL for Data Cleansing and Preparation - Part 148:44
SQL for Data Cleansing and Preparation - Part 153:14

Feature Variable Calculation - Part 12:01
Feature Variable Calculation - Part 22:28
Apply the lag function to compute time since last transaction for each card, partitioned by card number and ordered by transaction date, then calculate date differences.
Feature Variable Calculation - Part 31:46
Use lag to compute the previous transaction date in ascending order, then subtract to obtain the time lapse since the last transaction (DLF), illustrated by a 21-day example.
Feature Variable Calculation - Part 45:35
Feature Variable Calculation - Part 52:46
Feature Variable Calculation - Part 610:45
Feature Variable Calculation - Part 76:43
Feature Variable Calculation - Part 86:18
Compute lifetime high per card with max over a window ordered by the transaction date, and create 3-day, 7-day, and 2-hour features for fraud detection in view v_proc_06.
Feature Variable Calculation - Part 911:13

Requirements

Basic knowledge about SQL

Description

In this course, Dr. Jay Zhou, an industrial practitioner and and competition winner, will share his Oracle SQL skills and best practices to perform typical data science/data analytics tasks. Hopefully, after taking the course you will become a better data scientist/data analyst. In your future project, you will be more efficient, make less mistakes, manage your data and scripts better and be stress free when performing complex data work. The course will NOT cover predictive model building.

Who this course is for:

data scientist, data analyst, data engineer, statistician

Oracle SQL for Data Science

What you'll learn

Explore related topics

Course content

Introduction3 lectures • 5min

Why SQL for Data Science4 lectures • 10min

Typical Tasks for Data Science Project2 lectures • 15min

Data Validation and Understanding15 lectures • 1hr 29min

SQL for Data Cleansing and Preparation15 lectures • 1hr 31min

Feature Variable Calculation9 lectures • 50min

Summaries and Highlights1 lecture • 5min

All Slides Used in the Course1 lecture • 1min

Requirements

Description

Who this course is for: