Oracle SQL for Data Science

Explore why structured query language is essential for data science, enabling in-database processing of relational data, cross-database portability, and concise calculations like moving averages.

Discover why SQL powers in-database data processing with security via username-password access privileges and encryption, partitioned big tables, and efficient indexing for fast account-based queries, backed by backups.

a relational database uses transactions to protect data consistency, ensuring operations complete fully or not at all, so related records like employee and salary stay synchronized.

Explore how sql enables seamless model deployment in data science by keeping development and production within the database, avoiding translation gaps from sas or r.

Identify and clean data in a data science project by validating, understanding, preparing, and calculating feature variables before modeling with SQL in-database analysis.

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.

Demonstrate data validation and understanding with Oracle SQL by inspecting a credit card transactions table, sampling ten percent, counting records by state, and creating a view to store the results.

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.

Compute the minimum and maximum transaction amounts per state, then compute the mean and average transaction amounts for each state using a group by state.

Learn practical data validation in oracle sql by counting nulls and missing values in transaction city and amount, using count(*) and count(column) plus case expressions.

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.

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.

Learn to create a histogram by bucketing a transaction amount into discrete intervals using a SQL function, converting a continuous variable into discrete buckets with boundaries 0 to 500.

Calculate a histogram by placing transactions into pockets, handle boundaries and missing values, store the results in a view, and verify the distribution sums to one.

Use the row_number analytic function to generate a unique rank for transactions by descending amount. Identify the top five records and understand tie behavior for data validation.

Generate a unique rank for each state code using row_number and rank in ascending order, then select the top 10 records from the score transaction table for data validation.

Identify the most frequent transaction state by counting records per state (group by), rank the results by total transactions, and use the mode function as an alternative.

Compare Oracle SQL variance and stddev with a manual calculation using the standard deviation definition, and learn that count(*) ignores nulls while counting transaction amount reveals the true data size.

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.

Learn to compute the cumulative distribution of transaction amounts with a window function, ordering by amount and optionally partitioning by state to produce state-specific distributions.

Explore how a gain chart evaluates predictive models for fraud detection, comparing random stopping to a score-based cutoff to maximize detected fraud while minimizing reviewed transactions.

This lecture teaches detecting and removing duplicates in an accounts table using Oracle SQL, grouping by customer_id and account_type, and using row_number to keep the smallest account_number.

Learn how to handle missing values in data cleansing, keep records instead of chopping data, and apply SQL imputation using a special category, mode, or mean.

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.

Create a new SQL view from two tables to replace missing values with the mean and join on transaction state, illustrating view-based data cleansing in a data science workflow.

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.

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.

Create and review bins for transaction amounts with histogram-like views to cleanse and prepare data for analytics. Apply equal-width and equal-frequency binning using cumulative distribution to understand the data.

This lecture shows how to store fixed bin boundaries in a view for transaction data, use the lead function to define edges, and fill gaps.

Create a view named v_proc to apply pin boundary logic for data cleansing. Join A and B boundary tables on amount and compute min and max transaction amounts.

Learn how to cleanse data streams by examining ASCII codes, trimming and converting to lower or upper case, and using regex-based replaces to squeeze multiple blanks and fix city names.

Learn how to mask credit card numbers by mapping each number to a unique random value, ensuring one-to-one correspondence and storing results in a table for consistent, analyzable data.

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.

Use row_number over partition by card_number order by transaction_date desc on card_transaction to keep the most recent 15 transactions per card, then create and verify a view of these records.

In Oracle SQL for data cleansing, compare analytic max with partition by card number to capture the latest date, and group by card number to summarize before filtering three days.

Learn a two-step random sampling approach to data cleansing using dbms_random.value to select 70 percent of card numbers and extract their transactions.

Learn how feature variable calculations create derived variables for predictive models, using rfm (recency, frequency, monetary), and by forming ratios, bmi, and other combinations.

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.

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.

Learn how to use the lag function in Oracle SQL to compute the previous product offered for each user across nine weeks, enriching features for a predictive advertising model.

Calculate rolling totals for each card number over a seven-day window with an analytic sum function to forecast fraud, summing transaction counts and amounts.

Explore seven-day transaction windows with sum, count, and average using the over clause, and compare range between and rows between definitions by date versus preceding rows.

Learn how to exclude the current row from calculations using a tiny number, and control historical aggregations with rows between and unbounded preceding in Oracle SQL for data science.

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.

Learn how to create 70/30 random splits for training and testing, generate transaction identifiers with row_number, and build feature variables from past transactions.

Discover how Oracle SQL enables data science inside the database, using views and indexes to keep data, logic, and model deployment synchronized, secure, and scalable.