### Data Validation Examples - Oracle
[](LICENSE "Creative Commons Zero License by DataResearchLabs (effectively = Public Domain")
[](http://www.DataResearchLabs.com)
#### [Return to Data Validation Home Page](https://github.com/DataResearchLabs/sql_scripts/blob/main/data_validation_scripts.md)
# Rule Set #1 - Row Counts
## Table of Contents
- T001 - "Full" Row Count
- T002 - "Partial" Row Count
- T003 - "Relative" Row Count
- T004 - "Recent" Row Count
### T001 - "Full" Row Count
Verify full row count for a table or view. For example, to verify that table countries has exactly 25 rows:
```sql
SELECT CASE WHEN COUNT(*) <> 25 THEN 'FAIL' ELSE 'P' END AS status
FROM demo_hr.countries;
```
### T002 - "Partial" Row Count
Verify partial row count for a subset of a table or view. For example, to verify that table countries has exactly 8 rows where region_id = 1 (Europe):
```sql
SELECT CASE WHEN COUNT(*) <> 8 THEN 'FAIL' ELSE 'P' END AS status
FROM demo_hr.countries
WHERE region_id = 1;
```
### T003 - "Relative" Row Count
Verify relative row counts between tables or views. The example below verifies that table countries has at least 5 times the number of rows as table regions. The inner query returns one row with two columns: (a) count of contries, and (b) count of regions. The outer query can then apply the business logic of comparing the counts and returning a Pass or Fail.
```sql
SELECT CASE WHEN countries_count < 5 * regions_count THEN 'FAIL' ELSE 'P' END AS status
FROM (
SELECT (SELECT COUNT(*) AS row_count FROM demo_hr.countries) AS countries_count
, (SELECT COUNT(*) AS row_count FROM demo_hr.regions) AS regions_count
FROM dual
);
```
### T004 - "Recent" Row Count
Verify recent row counts. The example below verifies that table countries has had at least 5 rows updated in the past 10 days based on the date stamp in field date_last_updated. The inner query uses SYSDATE (oracle current date time stamp) and subtracts 10 days. The inner query counts how many rows in table countries have a date_last_updated with the past 10 days. The outer query can then confirm that at least 5 rows are recent for a Pass...if not then it returns a Fail.
```sql
SELECT CASE WHEN row_count < 5 THEN 'FAIL' ELSE 'P' END AS status
FROM (
SELECT COUNT(*) AS row_count
FROM demo_hr.countries
WHERE date_last_updated >= SYSDATE - 10
);
```