SQL & Data Validation for BAs

End-to-End Handbook

SQL Skills for Business Analyst / Technical Business Analyst

Practical SQL learning focused on data validation, UAT support, report verification, defect analysis, requirements analysis, and real-world project scenarios — built from a BA / TBA perspective, not from a database developer's.

SELECTWHEREDISTINCTBETWEENLIKEINNULL ChecksORDER BYAND / OR / NOTCOUNTGROUP BYHAVINGINNER JOINLEFT JOINMulti-Table JOINsData ValidationUAT SupportDefect Analysis

SQL Learning Roadmap

The exact sequence I learned, from basics to multi-table joins

Step 01

SELECT

Retrieve all or specific columns from a table.

Step 02

WHERE

Filter records based on conditions.

Step 03

Comparison Operators

=, <, <=, >, >=, !=, <>.

Step 04

BETWEEN

Filter records within number or date ranges.

Step 05

DISTINCT

Unique values for dropdown & consistency checks.

Step 06

LIKE

Partial matches for names, case numbers, emails.

Step 07

IN

Filter by multiple allowed values.

Step 08

IS NULL / IS NOT NULL

Identify missing or available data.

Step 09

ORDER BY

Sort for UI grid & report validation.

Step 10

AND / OR / NOT

Combine multiple business conditions.

Step 11

COUNT

Counts for dashboards, reports, UAT.

Step 12

GROUP BY

Summarize by category, status, agency, etc.

Step 13

HAVING

Filter aggregated/grouped results.

Step 14

JOINS

Combine related tables using INNER & LEFT JOIN.

Step 15

Multi-Table JOINS

Cases, persons, agencies, relationship tables.

Core SQL Concepts Learned

Concept · BA/TBA usage · examples

Business Analyst Use Cases

How SQL shows up in real BA / TBA work

UAT Validation

Verify data entered in the application is correctly persisted in the database.

SELECT person_id, first_name, last_name, status, created_date
FROM persons
WHERE first_name = 'James'
  AND last_name = 'Brown';

Report Validation

Validate report data and totals against database aggregates.

SELECT status, COUNT(*) AS total_persons
FROM persons
GROUP BY status;

Dashboard Validation

Validate dashboard cards and chart values.

SELECT COUNT(*) AS active_persons
FROM persons
WHERE status = 'Active';

Defect Analysis

Investigate whether issues come from UI, backend, or data.

SELECT *
FROM persons
WHERE status = 'Active'
  AND email IS NULL;

Search Functionality

Validate UI search filters against database results.

SELECT *
FROM persons
WHERE last_name LIKE '%ar%';

Data Quality Validation

Identify missing, duplicate, inconsistent, or invalid data.

SELECT DISTINCT status
FROM persons;

Missing Relationships

Use joins to find records not properly linked across tables.

SELECT c.case_number, c.case_status
FROM cases c
LEFT JOIN case_persons cp
  ON c.case_id = cp.case_id
WHERE cp.person_id IS NULL;

Sample Tables Used for Practice

Reference dataset behind every query in this handbook

person_id	first_name	last_name	gender	status	dob	created_date	email
101	John	Smith	Male	Active	1995-02-10	2024-01-15	john@email.com
102	Priya	Rao	Female	Missing	2001-06-15	2024-02-20	priya@email.com
103	David	Lee	Male	Inactive	1988-09-21	2024-03-05	NULL
104	Maria	Garcia	Female	Active	1999-12-05	2024-03-18	maria@email.com
105	James	Brown	Male	Missing	2010-07-30	2024-04-01	NULL
106	Sara	Khan	Female	Active	1992-11-11	2024-04-10	sara@email.com

Query Practice Library

Categorized examples I run against the sample tables

SELECT *
FROM persons;

SELECT person_id, first_name, last_name, status
FROM persons;

JOINs

Validating data across related tables

INNER JOIN

Returns only records that have matching values in both tables. Use when you need valid linked data only.

SELECT c.case_number, c.case_status, a.agency_name
FROM cases c
INNER JOIN agencies a
  ON c.agency_id = a.agency_id;

LEFT JOIN

Returns all records from the left table and matching records from the right. Use to find missing relationships.

SELECT c.case_number, c.case_status, c.agency_id, a.agency_name
FROM cases c
LEFT JOIN agencies a
  ON c.agency_id = a.agency_id;

Finding Missing Agency Links

SELECT c.case_number, c.case_status, c.agency_id, a.agency_name
FROM cases c
LEFT JOIN agencies a
  ON c.agency_id = a.agency_id
WHERE a.agency_id IS NULL;

Counting Cases by Agency

SELECT a.agency_name, COUNT(*) AS total_cases
FROM cases c
INNER JOIN agencies a
  ON c.agency_id = a.agency_id
GROUP BY a.agency_name
ORDER BY total_cases DESC;

Open Cases by Agency

SELECT a.agency_name, COUNT(*) AS total_open_cases
FROM cases c
LEFT JOIN agencies a
  ON c.agency_id = a.agency_id
WHERE c.case_status = 'Open'
GROUP BY a.agency_name;

Agencies Without Cases

SELECT a.agency_id, a.agency_name, a.agency_type
FROM agencies a
LEFT JOIN cases c
  ON a.agency_id = c.agency_id
WHERE c.case_id IS NULL;

Multi-Table JOINs

Realistic case management validations across 3-4 tables

Relationship Map

agencies.agency_id   ←  cases.agency_id
cases.case_id        ←  case_persons.case_id
persons.person_id    ←  case_persons.person_id

   agencies
      ↑
      |
   cases  →  case_persons  →  persons

Cases + Persons + Roles

SELECT c.case_number, c.case_status, p.first_name, p.last_name, cp.role
FROM cases c
INNER JOIN case_persons cp
  ON c.case_id = cp.case_id
INNER JOIN persons p
  ON cp.person_id = p.person_id;

Full Case Details (4-table)

SELECT c.case_number, a.agency_name, p.first_name, p.last_name, cp.role
FROM cases c
LEFT JOIN agencies a
  ON c.agency_id = a.agency_id
LEFT JOIN case_persons cp
  ON c.case_id = cp.case_id
LEFT JOIN persons p
  ON cp.person_id = p.person_id;

Cases Without Linked Persons

SELECT c.case_number, c.case_status
FROM cases c
LEFT JOIN case_persons cp
  ON c.case_id = cp.case_id
WHERE cp.person_id IS NULL;

Persons Not Linked to Any Case

SELECT p.person_id, p.first_name, p.last_name
FROM persons p
LEFT JOIN case_persons cp
  ON p.person_id = cp.person_id
WHERE cp.case_id IS NULL;

Data Validation Scenarios

Real BA / TBA project investigations

Scenario 1

Validate Person Created in UAT

Tester created person James Brown. Validate record exists in the database.

SELECT person_id, first_name, last_name, status, created_date
FROM persons
WHERE first_name = 'James'
  AND last_name = 'Brown';

Scenario 2

Validate Active Persons Report

Report should show only Active persons.

SELECT *
FROM persons
WHERE status = 'Active';

Scenario 3

Identify Incorrect Records in Active Female Report

Report should only show Active + Female. Identify invalid records.

SELECT *
FROM persons
WHERE status <> 'Active'
   OR gender <> 'Female';

Scenario 4

Validate Partial Last Name Search

Tester searches last name containing 'ar'. Validate database results.

SELECT *
FROM persons
WHERE last_name LIKE '%ar%';

Scenario 5

Validate Mandatory Email Rule

Email is mandatory for Active persons. Find Active persons with missing email.

SELECT *
FROM persons
WHERE status = 'Active'
  AND email IS NULL;

Scenario 6

Validate Dashboard Count by Status

Dashboard shows person count by status.

SELECT status, COUNT(*) AS total_persons
FROM persons
GROUP BY status;

Scenario 7

Validate Cases Missing Agency

Every case should have a valid agency. Identify cases missing agency linkage.

SELECT c.case_number, c.case_status, c.agency_id, a.agency_name
FROM cases c
LEFT JOIN agencies a
  ON c.agency_id = a.agency_id
WHERE a.agency_id IS NULL;

Scenario 8

Validate Cases Without Linked Persons

Every case should have at least one linked person.

SELECT c.case_number, c.case_status
FROM cases c
LEFT JOIN case_persons cp
  ON c.case_id = cp.case_id
WHERE cp.person_id IS NULL;

Interview Readiness

How I explain SQL in BA / TBA interviews

Key Takeaways

What I can do with SQL today as a BA / TBA

Through this learning path I covered SQL from basic selection and filtering all the way to aggregation and multi-table joins, with a focus on how SQL is actually used in BA / TBA work — data validation, UAT support, report verification, defect analysis, and real project investigation.

Retrieve specific data from tables
Filter records using business conditions
Validate search functionality with partial matching
Identify missing mandatory data
Count records for reports and dashboards
Group data by status, gender, agency, and other categories
Filter grouped results using HAVING
Join multiple tables for cross-functional validation
Identify missing relationships using LEFT JOIN + IS NULL
Explain SQL clearly in interview scenarios

Portfolio Callout

SQL Focus Area: BA / TBA SQL

Primary Usage: Validation, UAT, reports, defect analysis

Project Contexts: Case Management, Government, HR, Payments

Current Level: Strong through joins, aggregation, multi-table

Next Goals: CASE statements, subqueries, advanced validation, interview problems

SQL Skills for Business Analyst / Technical Business Analyst

SQL Learning Roadmap

SELECT

WHERE

Comparison Operators

BETWEEN

DISTINCT

LIKE

IN

IS NULL / IS NOT NULL

ORDER BY

AND / OR / NOT

COUNT

GROUP BY

HAVING

JOINS

Multi-Table JOINS

Core SQL Concepts Learned

SELECT

WHERE

Comparison Operators

BETWEEN

DISTINCT

LIKE

IN

IS NULL / IS NOT NULL

ORDER BY

AND / OR / NOT

COUNT

GROUP BY

HAVING

Business Analyst Use Cases

UAT Validation

Report Validation

Dashboard Validation

Defect Analysis

Search Functionality

Data Quality Validation

Missing Relationships

Sample Tables Used for Practice

Query Practice Library

JOINs

INNER JOIN

LEFT JOIN

Finding Missing Agency Links

Counting Cases by Agency

Open Cases by Agency

Agencies Without Cases

Multi-Table JOINs

Cases + Persons + Roles

Full Case Details (4-table)

Cases Without Linked Persons

Persons Not Linked to Any Case

Data Validation Scenarios

Validate Person Created in UAT

Validate Active Persons Report

Identify Incorrect Records in Active Female Report

Validate Partial Last Name Search

Validate Mandatory Email Rule

Validate Dashboard Count by Status

Validate Cases Missing Agency

Validate Cases Without Linked Persons

Interview Readiness

Difference between SELECT * and selecting specific columns?

Difference between WHERE and HAVING?

Difference between INNER JOIN and LEFT JOIN?

How do you use SQL as a Business Analyst?

How do you join multiple tables?

Key Takeaways