BA Prince the B.A. Subscribe

Home / SQL for Business Analysts

Use SQL to Check a Business Rule Before UAT

Use SQL to Check a Business Rule Before UAT

Watch on YouTube Subscribe

Turn a business rule into a SQL query that finds the rows breaking it. Prove 'every order has a customer' by comparing counts and finding the order that fails, then check a second rule, on a sample online shop database.

Step by step

A business rule

Every order has a customer. The requirement says every order must belong to a customer. Before user acceptance testing starts, prove it with the data. In SQL, you write a query that finds the rows breaking the rule. Let's test it.
The orders table. Each order holds a customer ID. That ID should match a row in the customers table.

Count both sides

Count the orders. Start simple. How many orders are there? A hundred and forty one orders.
```
SELECT COUNT(*) AS orders
FROM orders;
```
Count the matches. Now count only the orders that find their customer. A join keeps just the orders with a matching customer ID. A hundred and forty. One order has no customer. The rule is broken, so let's find which one.
```
SELECT COUNT(*) AS matched
FROM orders AS o
JOIN customers AS c
  ON c.customer_id = o.customer_id;
```

Find the rule breaker

Find the order. Show the order ID and customer ID instead of the count. Make it a left join, so orders without a customer stay in the result. Then keep only the rows where no customer was found. Where c dot customer ID is null. There it is. Order eleven forty one points to customer ninety nine, who doesn't exist. Log it as a defect before testing starts.
```
SELECT o.order_id, o.customer_id
```
```
LEFT JOIN customers
```
```
WHERE c.customer_id IS NULL;
```

A second rule

Another rule. Same idea for any rule. Every shipped or delivered order must have a shipped date. So look for those orders where the shipped date is null. Zero rows. Nothing breaks this rule, so it passes. Save the query as evidence for the test report.
```
SELECT order_id, status, shipped_date
FROM orders
WHERE status IN ('Shipped', 'Delivered')
  AND shipped_date IS NULL;
```

Recap

Test rules with SQL. So, turn each business rule into a query that finds the rows breaking it. Zero rows means the rule holds. Any rows go in the defect log before user acceptance testing.

More in SQL for Business Analysts

SQL for Business Analysts: The 5 Queries You Actually Need

SQL SELECT Basics: Get Data Out of a Table (Beginner Tutorial)

SQL WHERE Clause: Filter Rows Like an Analyst

SQL INNER JOIN Explained With a Simple Example