SQL & Data Validation for BAs

Section

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

Why SQL Matters for Business Analysts

Validate what happens behind the UI — data, reports, defects, UAT, integrations

Intro

SQL helps Business Analysts validate what happens behind the UI. It is used to confirm data, reports, defects, UAT results, integrations, migrations, and business rules.

BA Situation	SQL Use
UAT validation	Verify data saved correctly after user action
Defect analysis	Check whether issue is UI, data, or logic
Report validation	Compare dashboard / report totals with database
Data migration	Validate source-to-target mapping
Requirement analysis	Understand existing data patterns
Business rules	Confirm rules are applied correctly
Integration testing	Verify inbound / outbound data
Audit checks	Validate created / updated dates and users
Data quality	Find duplicates, nulls, invalid values

Interview Answer

“SQL is important for a Business Analyst because it helps validate data, reports, requirements, defects, and UAT results beyond the UI. I use SQL to check whether records are created or updated correctly, validate status changes, compare report totals, identify missing or duplicate data, verify data mapping, support migration validation, and investigate defects.”

SQL Command Categories

DDL · DML · DQL · DCL · TCL — what every BA should recognize

Category	Meaning	Common Commands	BA Relevance
DDL	Data Definition Language	CREATE, ALTER, DROP, TRUNCATE	Understand table / column structure
DML	Data Manipulation Language	INSERT, UPDATE, DELETE	Understand how data is created / changed
DQL	Data Query Language	SELECT	Retrieve and validate data
DCL	Data Control Language	GRANT, REVOKE	Understand access / security permissions
TCL	Transaction Control Language	COMMIT, ROLLBACK	Understand transaction safety

Interview Answer

“SQL commands group into DDL, DML, DQL, DCL, and TCL. As a BA, I mostly use SELECT for validation, but understanding CREATE, ALTER, INSERT, UPDATE, DELETE, primary keys, foreign keys, and constraints helps me understand how data is structured, stored, and controlled.”

Tables, Columns & Data Types

The structural building blocks of a database

What is a Table?

A table stores data for one business entity — users, customers, cases, requests, payments, orders. Tables contain columns (fields), rows (records), a primary key, optional foreign keys, and constraints.

Business Entity	Table
User	users
Customer	customers
Case	cases
Request	requests
Payment	payments
Order	orders

CREATE TABLE users (
  user_id     INT PRIMARY KEY,
  first_name  VARCHAR(50) NOT NULL,
  last_name   VARCHAR(50) NOT NULL,
  email       VARCHAR(100) UNIQUE,
  role_name   VARCHAR(50),
  status      VARCHAR(20) DEFAULT 'Active',
  created_date DATE
);

BA Meaning

Enforces requirements: user must have first/last name, email must be unique, status defaults to Active, every user has a unique ID.

Common Data Types

Type	Meaning	Example
INT	Whole number	user_id, age, quantity
DECIMAL(10,2)	Decimal number	payment_amount, price
VARCHAR(100)	Variable text	email, first_name
CHAR(2)	Fixed-length text	state_code
DATE	Date only	created_date, dob
DATETIME / TIMESTAMP	Date and time	created_at, updated_at
BOOLEAN	True / false	is_active
TEXT	Long text	comments, description

Interview Answer

“Data types define what kind of value a column can store. Understanding them helps me document field rules, validate forms, review data mapping, and catch issues like storing numbers as text or missing date/time rules.”

Keys & Relationships

Primary keys, foreign keys, composite keys, 1:M and M:M relationships

Primary Key

Uniquely identifies each row. Must be unique and NOT NULL.

CREATE TABLE customers (
  customer_id   INT PRIMARY KEY,
  customer_name VARCHAR(100) NOT NULL,
  email         VARCHAR(100)
);

Foreign Key

Links one table to another by referencing its primary key.

CREATE TABLE orders (
  order_id     INT PRIMARY KEY,
  customer_id  INT,
  order_date   DATE,
  order_status VARCHAR(20),
  FOREIGN KEY (customer_id)
    REFERENCES customers(customer_id)
);

Primary vs Foreign Key

Primary Key	Foreign Key
Uniquely identifies a row	Links to another table
Cannot be NULL	Can sometimes be NULL
Unique within its own table	References another table's PK
customer_id in customers	customer_id in orders
Used to find one record	Used to connect related records

One-to-Many (1:M)

One customer → many orders.

┌────────────┐     ┌────────────┐
│ customers  │ 1 ─→│   orders   │  M
│ customer_id│     │ customer_id│
└────────────┘     └────────────┘

Many-to-Many (M:M)

Students ↔ Courses via a bridge table.

students  →  student_courses  ←  courses
         (composite PK: student_id + course_id)

CREATE TABLE student_courses (
  student_id      INT,
  course_id       INT,
  enrollment_date DATE,
  PRIMARY KEY (student_id, course_id),
  FOREIGN KEY (student_id) REFERENCES students(student_id),
  FOREIGN KEY (course_id)  REFERENCES courses(course_id)
);

Interview Answer — Composite Key

“A composite primary key uses more than one column to uniquely identify a record. It is often used in bridge tables to model many-to-many relationships.”

Constraints — Business Rules in the Database

NOT NULL · UNIQUE · DEFAULT · CHECK · PRIMARY KEY · FOREIGN KEY

Constraint	Meaning	Example
PRIMARY KEY	Unique identifier	user_id
FOREIGN KEY	Links tables	customer_id
NOT NULL	Value is required	first_name NOT NULL
UNIQUE	No duplicate values	email UNIQUE
DEFAULT	Default value if not entered	status DEFAULT 'Active'
CHECK	Validates allowed values / range	age >= 18

Business Rule → Constraint Mapping

Business Rule	SQL Constraint
First name is mandatory	NOT NULL
Email cannot duplicate	UNIQUE
New request starts as Draft	DEFAULT 'Draft'
Age must be 18 or above	CHECK (age >= 18)
Request must belong to valid user	FOREIGN KEY
Each request must have unique ID	PRIMARY KEY

-- CHECK with allowed values
CREATE TABLE requests (
  request_id INT PRIMARY KEY,
  status     VARCHAR(20)
    CHECK (status IN
      ('Draft','Submitted','Approved','Rejected'))
);

-- DEFAULT value
CREATE TABLE requests (
  request_id INT PRIMARY KEY,
  status     VARCHAR(20) DEFAULT 'Draft'
);

Interview Answer

“Many business rules can be enforced at the database level using constraints. Mandatory fields use NOT NULL, unique email uses UNIQUE, valid status values use CHECK, and relationships between entities use FOREIGN KEYs.”

Insert · Update · Delete (DML)

How data is created, changed, and removed — with BA cautions

INSERT — single & multiple rows

INSERT INTO users
  (user_id, first_name, last_name, email, role_name, status, created_date)
VALUES (1, 'John', 'Smith', 'john@email.com', 'Admin', 'Active', '2026-06-09');

INSERT INTO users
  (user_id, first_name, last_name, email, role_name, status, created_date)
VALUES
  (2, 'Sara', 'Lee',   'sara@email.com',  'Supervisor', 'Active', '2026-06-09'),
  (3, 'Mike', 'Brown', 'mike@email.com',  'User',       'Active', '2026-06-09');

UPDATE — change existing data

UPDATE requests
SET status = 'Submitted'
WHERE request_id = 101;

Always use WHERE

Without WHERE, every row in the table will be updated. BA workflow rules to validate: Draft → Submitted → Approved / Rejected.

DELETE — remove records

DELETE FROM requests
WHERE request_id = 101;

Soft Delete is Often Preferred

Enterprise systems usually update a status (e.g. Inactive) instead of physically deleting. This preserves history, audit, reporting, compliance, and recovery.

-- Soft delete pattern
UPDATE users
SET status = 'Inactive'
WHERE user_id = 1;

DELETE vs TRUNCATE vs DROP

Command	Data Impact	Structure Impact
DELETE	Removes selected/all rows	Table remains
TRUNCATE	Removes all rows quickly	Table remains
DROP	Removes data	Removes table structure

Interview Answer

“DELETE removes records and can use WHERE. TRUNCATE removes all rows from a table quickly. DROP removes the entire table structure and data. BAs should understand these because they affect data retention, testing, and release risk.”

ALTER TABLE & Auto-Increment

Schema evolution and database-generated IDs (dialect differences)

-- Add / Modify / Drop columns
ALTER TABLE users ADD phone_number VARCHAR(20);

-- Modify (varies by dialect)
-- SQL Server
ALTER TABLE users ALTER COLUMN phone_number VARCHAR(30);
-- MySQL
ALTER TABLE users MODIFY phone_number VARCHAR(30);
-- PostgreSQL
ALTER TABLE users ALTER COLUMN phone_number TYPE VARCHAR(30);

ALTER TABLE users DROP COLUMN phone_number;

BA Warning

Dropping a column can remove data permanently. It should be governed through impact analysis and release approval.

-- Add PK / FK after table creation
ALTER TABLE users
  ADD PRIMARY KEY (user_id);

ALTER TABLE orders
  ADD CONSTRAINT fk_orders_customers
  FOREIGN KEY (customer_id)
  REFERENCES customers(customer_id);

Auto-Increment by Dialect

Database	Syntax
SQL Server	user_id INT IDENTITY(1,1) PRIMARY KEY
MySQL	user_id INT AUTO_INCREMENT PRIMARY KEY
PostgreSQL	user_id SERIAL PRIMARY KEY
Oracle	GENERATED ALWAYS AS IDENTITY

Full Example — Request Management Schema

Users · Requests · Comments — a complete relational BA scenario

CREATE TABLE users (
  user_id      INT PRIMARY KEY,
  full_name    VARCHAR(100) NOT NULL,
  email        VARCHAR(100) UNIQUE NOT NULL,
  role_name    VARCHAR(50) NOT NULL,
  status       VARCHAR(20) DEFAULT 'Active',
  created_date DATE
);

CREATE TABLE requests (
  request_id          INT PRIMARY KEY,
  request_type        VARCHAR(50) NOT NULL,
  request_description TEXT,
  status              VARCHAR(20) DEFAULT 'Draft',
  created_by          INT NOT NULL,
  created_date        DATE,
  FOREIGN KEY (created_by) REFERENCES users(user_id),
  CHECK (status IN
    ('Draft','Submitted','Approved','Rejected','Closed'))
);

CREATE TABLE request_comments (
  comment_id   INT PRIMARY KEY,
  request_id   INT NOT NULL,
  comment_text TEXT NOT NULL,
  created_by   INT NOT NULL,
  created_date DATE,
  FOREIGN KEY (request_id) REFERENCES requests(request_id),
  FOREIGN KEY (created_by) REFERENCES users(user_id)
);

-- Sample data
INSERT INTO users VALUES
  (1,'John Smith','john@email.com','User','Active','2026-06-09'),
  (2,'Sara Lee','sara@email.com','Supervisor','Active','2026-06-09');

INSERT INTO requests VALUES
  (101,'Access Request','Reporting dashboard access',
   'Draft',1,'2026-06-09');

INSERT INTO request_comments VALUES
  (1001,101,'Initial request created',1,'2026-06-09');

-- Validate inserted data via JOIN
SELECT r.request_id,
       r.request_type,
       r.status,
       u.full_name AS created_by
FROM requests r
JOIN users u ON r.created_by = u.user_id;

Supports: users can create requests · requests must have a creator · status must be valid · comments belong to a request · comments are created by a user.

Data Model, ERD & Lookup Tables

Translating business entities into database structures

Business / Data Model	Database Implementation
Entity	Table
Attribute	Column
Relationship	Foreign Key
Mandatory field	NOT NULL
Unique field	UNIQUE constraint
Allowed values	CHECK constraint or lookup table
Business object ID	Primary Key

Lookup Table Pattern

CREATE TABLE request_status_lookup (
  status_code        VARCHAR(20) PRIMARY KEY,
  status_description VARCHAR(100)
);

INSERT INTO request_status_lookup VALUES
  ('Draft','Request is being prepared'),
  ('Submitted','Submitted for review'),
  ('Approved','Request approved'),
  ('Rejected','Request rejected'),
  ('Closed','Request closed');

Interview Answer

“A lookup table stores allowed values such as statuses, types, categories, or codes. It supports data consistency, dropdowns, validations, and reporting.”

Validation Layers — UI · API · Logic · Database

Layer	Example
UI	Required field message
API	Reject missing value
Database	NOT NULL constraint
Business logic	Status transition rule

Entity Design Questions Every BA Should Ask

What business entity are we storing?
Which field uniquely identifies the record?
What fields are mandatory?
What fields must be unique?
What allowed values exist?
What relationships exist with other entities?
Should records be deleted or deactivated?
What audit fields are needed?
Who can create, update, or delete the record?
What reports use this data?
Does data need to migrate from another system?
What is the data volume now and in 12 months?

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;

CASE Statement — Conditional Logic

Convert raw data into business-friendly categories

SELECT request_id,
       status,
       CASE
         WHEN status = 'Approved' THEN 'Completed'
         WHEN status = 'Rejected' THEN 'Closed'
         ELSE 'In Progress'
       END AS status_category
FROM requests;

BA Uses

Categorizing statuses · building report labels · validating business rules · calculated fields · grouping business outcomes.

Interview Answer

“CASE applies conditional logic in SQL. It converts raw data into business-friendly categories, such as grouping multiple statuses into broader report categories.”

Date · String · NULL Handling

Practical functions for UAT, reports, aging, SLA, data quality

Dates

-- Records created today (PostgreSQL)
SELECT * FROM requests
WHERE CAST(created_date AS DATE) = CURRENT_DATE;

-- Last 7 days
SELECT * FROM requests
WHERE created_date >= CURRENT_DATE - INTERVAL '7 days';

-- Monthly count
SELECT EXTRACT(YEAR  FROM created_date) AS yr,
       EXTRACT(MONTH FROM created_date) AS mo,
       COUNT(*) AS total
FROM requests
GROUP BY EXTRACT(YEAR FROM created_date),
         EXTRACT(MONTH FROM created_date);

Database	Common Functions
SQL Server	GETDATE(), DATEADD(), DATEDIFF()
MySQL	CURDATE(), DATE_SUB(), DATEDIFF()
PostgreSQL	CURRENT_DATE, INTERVAL, EXTRACT()
Oracle	SYSDATE, ADD_MONTHS()

Strings

Function	Purpose
UPPER() / LOWER()	Change case
TRIM()	Remove extra spaces
LENGTH() / LEN()	Count characters
SUBSTRING()	Extract part of text
CONCAT()	Combine text
REPLACE()	Replace characters / text

-- Find records with extra spaces
SELECT * FROM users
WHERE first_name <> TRIM(first_name);

-- Find emails not in lowercase
SELECT email FROM users
WHERE email <> LOWER(email);

-- Validate field length
SELECT * FROM persons
WHERE LENGTH(first_name) > 50;

NULL — Missing vs Blank

-- Missing email (NULL)
SELECT * FROM users WHERE email IS NULL;

-- Blank email (empty string)
SELECT * FROM users WHERE email = '';

-- Substitute in reports
SELECT user_id,
       COALESCE(email, 'Email Missing') AS email_status
FROM users;

Interview Answer

“NULL means a missing or unknown value — it is different from blank or zero. As a BA, I check NULL because missing data impacts validations, reports, integrations, and business rules. I always use IS NULL / IS NOT NULL, never = NULL.”

Data Quality & Duplicate Detection

The core of BA data validation — missing, duplicate, invalid, out of range

Find Duplicates

-- Duplicate emails
SELECT email, COUNT(*) AS duplicate_count
FROM users
GROUP BY email
HAVING COUNT(*) > 1;

-- Duplicate persons by name + DOB
SELECT first_name, last_name, date_of_birth,
       COUNT(*) AS duplicate_count
FROM persons
GROUP BY first_name, last_name, date_of_birth
HAVING COUNT(*) > 1;

Invalid / Out-of-Range Values

-- Invalid status
SELECT * FROM requests
WHERE status NOT IN
  ('Draft','Submitted','Approved','Rejected','Closed');

-- DOB in the future
SELECT * FROM persons
WHERE date_of_birth > CURRENT_DATE;

-- Negative payment amount
SELECT * FROM payments
WHERE payment_amount < 0;

Data Quality Checklist

Check	Example
Missing values	Required fields are NULL
Duplicate records	Same person appears twice
Invalid formats	Email missing @
Invalid dates	DOB in future
Invalid status	Status not in approved list
Broken relationships	Foreign key has no parent
Out-of-range values	Amount below zero
Inconsistent data	Closed case with open task

UI · Report · Migration Validation

End-to-end SQL recipes for the most common BA verification work

UI Validation (Form → DB)

SELECT request_id, request_type, status,
       created_by, created_date
FROM requests
WHERE request_id = 1050;

Validate record created, status correct, created_by correct, timestamp populated, required fields saved.

Report Validation

SELECT status, COUNT(*) AS total_requests
FROM requests
WHERE created_date BETWEEN '2026-01-01' AND '2026-01-31'
GROUP BY status;

Steps: source → filters → columns → calculations → grouping → totals → access → export.

Migration Validation

-- Count comparison
SELECT COUNT(*) AS source_count FROM legacy_customers;
SELECT COUNT(*) AS target_count FROM new_customers;

-- Missing migrated records
SELECT l.customer_id
FROM legacy_customers l
LEFT JOIN new_customers n
  ON l.customer_id = n.legacy_customer_id
WHERE n.legacy_customer_id IS NULL;

Counts · field mapping · transformations · mandatory fields · duplicates · referential integrity · exceptions.

Reconciliation Pattern

SELECT SUM(source_amount) AS source_total,
       SUM(target_amount) AS target_total
FROM reconciliation_summary;

CTEs · Subqueries · Window Functions

Advanced techniques for Technical BAs validating complex datasets

CTE (WITH)

WITH open_cases AS (
  SELECT case_id, case_status, created_date
  FROM cases
  WHERE case_status = 'Open'
)
SELECT * FROM open_cases;

Subquery

SELECT *
FROM requests
WHERE user_id IN (
  SELECT user_id FROM users
  WHERE status = 'Active'
);

Window — Latest Record per User

SELECT *
FROM (
  SELECT user_id, status, updated_date,
    ROW_NUMBER() OVER (
      PARTITION BY user_id
      ORDER BY updated_date DESC
    ) AS row_num
  FROM user_status_history
) x
WHERE row_num = 1;

Window Function	Purpose
ROW_NUMBER()	Assign unique row number
RANK() / DENSE_RANK()	Rank with / without gaps
COUNT() OVER()	Count while keeping row detail
SUM() OVER()	Running total
LAG() / LEAD()	Previous / next row value

CTE Interview Answer

“A CTE structures complex SQL via a temporary named query. It improves readability and is useful when validating reports, transformations, or multi-step business logic.”

Window Interview Answer

“Window functions analyze records while keeping row-level detail. ROW_NUMBER with PARTITION BY can identify the latest record per user or case — useful for audit, reporting, and defect investigation.”

Views · Stored Procedures · Transactions

What BAs should recognize even when they don't write them

View — Virtual Table

A saved SQL query exposed as a table. Common in reporting and dashboards.

SELECT * FROM active_request_summary_view;

Stored Procedure

Saved SQL logic — used for report generation, batch jobs, business rules, migration steps.

EXEC sp_generate_monthly_report
  @month = 6, @year = 2026;

Transaction

A group of operations that succeed or roll back together. Payment, invoice, balance.

BEGIN;
  INSERT INTO payments ...;
  UPDATE invoices  SET status = 'Paid' ...;
  UPDATE accounts  SET balance = ... ;
COMMIT;  -- or ROLLBACK on failure

CRUD Map

Letter	Meaning	BA Example
C	Create	Add new request
R	Read	View request
U	Update	Edit request
D	Delete	Remove (or soft-delete) request

Defect · UAT · Audit · Role · Status · Integration

The investigation queries Technical BAs run every week

Defect Investigation

SELECT request_id, status, updated_by, updated_date
FROM requests
WHERE request_id = 1050;

Was the record saved? status updated? related table updated? integration failed? duplicate exists? report filter wrong?

UAT Support

SELECT request_id, status, assigned_to, updated_date
FROM requests
WHERE created_by = 'uat_user1'
ORDER BY updated_date DESC;

Audit Log Validation

SELECT *
FROM audit_log
WHERE record_id = 1050
ORDER BY action_date DESC;

Created by · created date · updated by · updated date · action type · old/new value.

Status Transition

SELECT request_id, old_status, new_status, changed_date
FROM request_status_history
WHERE request_id = 1050
ORDER BY changed_date;

Valid: Draft → Submitted → Approved → Closed. Invalid: Draft → Closed.

Role-Based Access

SELECT u.user_id, r.role_name, p.permission_name
FROM user_roles u
JOIN roles r            ON u.role_id = r.role_id
JOIN role_permissions p ON r.role_id = p.role_id
WHERE u.user_id = 101;

Integration / Batch Validation

SELECT batch_id, total_records,
       success_count, failure_count, batch_status
FROM batch_audit
WHERE batch_id = 5001;

SQL for Requirements Analysis

SELECT request_type, COUNT(*) AS total
FROM requests
GROUP BY request_type
ORDER BY total DESC;

Understand existing volumes, common transaction types, missing data, historical patterns before writing requirements.

SQL for Non-Functional Requirements

NFR	SQL Use
Performance	Count record volume
Reporting	Understand report load size
Archiving	Identify old records
Security	Check roles / permissions
Audit	Verify audit trail
Data retention	Check record age

Common SQL Mistakes BAs Should Avoid

The pitfalls that cause missed defects and bad validation results

Mistake	Why It Matters
Using INNER JOIN when LEFT JOIN is needed	Missing records get hidden
Forgetting WHERE clause	Query returns too much data
Confusing WHERE and HAVING	Aggregated filters may fail
Treating NULL as blank	Missing data checks are wrong
Not validating record counts	Migration / report issues missed
Not checking duplicates	Data quality issues missed
Using SELECT * always	Harder to review specific fields
Not confirming date logic	Reports may be inaccurate
Ignoring role / security filters	Report access may be wrong
Not understanding source tables	Query validates wrong data
DELETE / UPDATE without WHERE	Unintended records changed or removed
Ignoring SQL dialect differences	Query may fail in another database

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

SQL Interview Question Bank

60 BA-focused questions — click to expand the answer

Final SQL Master Answer

The one-paragraph answer to ‘How do you use SQL as a BA?’

At a BA level, I am comfortable with SELECT, WHERE, filtering, aggregate functions, GROUP BY, HAVING, joins, CASE statements, subqueries, date functions, and data validation queries. I also understand database structure concepts such as tables, columns, data types, primary keys, foreign keys, constraints, lookup tables, and relationships. For more technical analysis, I understand CTEs, views, window functions, audit log checks, batch validation, and reconciliation. My goal is not just to write queries, but to use SQL to confirm that system behavior matches business requirements.”

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