Module 5

Reviewer for BA 183

Structured Query Language (SQL)

The standard language for interacting with relational databases.

CRUD

Allows users to create, read, update, and delete data.

Data Definition Language (DDL)

Purpose: Define and modify database structure.

Data Manipulation Language (DML)

Purpose: Query and modify data.

Data Control Language (DCL)

Purpose: Control access to data.

Transaction Control Language (TCL)

Purpose: Manage transactions.

SELECT

Used for retrieving data.

Aggregate Functions

Functions used for calculations such as SUM, COUNT, AVG, MIN, MAX.

JOIN

Combines rows from two or more tables based on a related column.

INNER JOIN

Returns only rows with matching values in both tables.

LEFT JOIN

Returns all rows from the left table, with matched rows from the right (NULLs if no match).

RIGHT JOIN

Returns all rows from the right table, with matched rows from the left.

FULL OUTER JOIN

Returns all rows from both tables, with NULLs where there's no match.

GROUP BY and HAVING

Used for grouping and filtering aggregates.

Subqueries

Queries nested within another query (e.g., finding journal entries with amounts above the average debit).

Data integration

Combines data from different sources into a unified, consistent view for analysis and reporting.

ETL Process

Standing for Extract, Transform, Load, it is a process for integrating data.

Extract

ETL PHASE

Pull data from source systems.

Transform

ETL PHASE

Clean, standardize, and reconcile data.

Load

ETL PHASE

Load transformed data into target system.

Excel (Power Query)

Desktop ETL: Import, transform, and combine data from multiple sources.

SQL (UNION, JOIN)

Database-level: Combine data from multiple tables or databases.

APIs

Application-level: Programmatic data exchange between systems.

Zapier / Power Automate

Cloud automation: Automated workflows connecting cloud applications.

Talend / Informatica

Enterprise ETL: Enterprise-grade data integration platforms.

Computer-Assisted Audit Tools and Techniques (CAATTs)

Software tools and methods used by auditors to analyze and evaluate data and IT systems more efficiently and comprehensively than manual methods.

Generalized Audit Software (GAS)

Purpose-built software for audit data analysis — sorting, filtering, sampling, gap detection.

Spreadsheet Tools

Excel-based techniques using formulas, pivot tables, and conditional formatting for audit tasks.

Database Query Tools

SQL queries against client databases for transaction testing or duplicate detection.

Continuous Auditing Tools

Automated, ongoing monitoring of transactions against predefined rules.

Data Visualization

Interactive dashboards for pattern identification.

Gap/Sequence Testing

Detect missing items in a sequence (check numbers, invoice numbers, PO numbers).

Duplicate Detection

Identify duplicate payments, invoices, or journal entries.

Stratification/Aging

Categorize data by ranges (e.g., receivables aging) to reveal risk concentration.

Benford's Law Analysis

Compare the distribution of leading digits in a dataset to the expected distribution to detect potential manipulation.

Journal Entry Testing

Identify unusual journal entries such as round amounts, entries posted outside business hours, or entries by unauthorized users.

Recalculation

Recalculate totals, taxes, or balances to verify accuracy.

Matching/Reconciliation

Match records across systems (e.g., bank statement vs. GL, vendor invoices vs. purchase orders).