SQL For Audit, Revenue Assurance and Data Science

Learn how to download and install toad for Oracle, create or sign into your account, choose the 32-bit trial, and complete the setup with acceptance of terms.

Learn how to connect to Oracle with Toad using the HR schema account, review configuration parameters, manage password sensitivity and case sensitivity, and set up the environment and user profile.

Design a database by creating a client table in Oracle, defining its fields and primary key, setting data types and constraints, and uploading data from Excel to populate it.

Create and load the debit notes table in Oracle, define its structure with an id and not null constraint, import from Excel, and set a foreign key to client table.

Create and populate a bank transactions table, define non-null columns, import data, and establish a relationship to the clients table via a constraint.

Explore data types and SQL clauses to filter transactions, using date ranges and numeric comparisons; learn to inspect table structures and run scripts to retrieve records.

Explore how to use SQL logical operators—and, or, not, in, not in—to filter data, compare ranges with (<, >, >=), and efficiently combine conditions for travel and non-travel transactions.

Explore data manipulation language (DML) using select, insert, and delete statements to view, modify, and verify table data, apply constraints, and perform drop operations in DB2.

Use the like operator to filter records by pattern, such as country fields ending with a string or starting with a prefix, returning a subset of data.

Learn to use subqueries and the union operator in SQL to relate client data with debit notes and to combine results from multiple tables for audit and data science insights.

Create views to store query results and use the case when clause to compute commissions across product lines, including travel transactions, for audit, revenue assurance, and data science.

Explore inner joins to combine related tables, such as client and trustee data, by matching common fields, selecting relevant client details, and filtering results with where clauses.

Recalculation of Cot I compares system calculations with recalculated values, highlights variances and revenue leakage, and demonstrates variance analysis for revenue assurance in banking and telecom contexts.

Apply case-based recalculations to verify debit transaction charges in the client and trans tables, validating the automated revenue calculations and security checks in SQL for audit and data science.

Connect to an Oracle database via a 32-bit ODBC data source, download data with a square script, and visualize revenue and client details in Power BI for insights.