Oracle SQL for Data Science

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.