Learner can use COUNT to aggregate and write a simple INNER JOIN to check related data across two tables.
Counting rows and joining tables
COUNT(*) and INNER JOIN for verification
COUNT(*) is an aggregate function that counts how many rows match a condition. A tester can use it to verify business rules: for example, if a user should have exactly one active subscription, SELECT COUNT(*) FROM subscriptions WHERE user_id = 42 AND status = 'active'; should return 1. If it returns 0 or 2, there is a bug.
Real applications store related data in separate tables. A users table might hold (id, name) and an orders table might hold (id, user_id, amount). To see order details together with the user's name, you need to JOIN the tables. INNER JOIN returns only rows where the join condition matches in both tables:
SELECT users.name, orders.amount FROM users INNER JOIN orders ON users.id = orders.user_id;
Using these two toy tables — users: (1,'Alice'), (2,'Bob') and orders: (101,1,50), (102,1,200), (103,2,75) — the query above returns three rows: Alice/50, Alice/200, Bob/75. If Alice placed an order and it does not appear here, the application has a data integrity problem worth reporting.
Combining COUNT with a JOIN is also common: SELECT users.name, COUNT(orders.id) AS order_count FROM users INNER JOIN orders ON users.id = orders.user_id GROUP BY users.name; tells you exactly how many orders each user placed — a fast way to verify expected totals after a test scenario.
Lesson notes
COUNT(*) and INNER JOIN for verification
COUNT(*) is an aggregate function that counts how many rows match a condition. A tester can use it to verify business rules: for example, if a user should have exactly one active subscription, SELECT COUNT(*) FROM subscriptions WHERE user_id = 42 AND status = 'active'; should return 1. If it returns 0 or 2, there is a bug.
Real applications store related data in separate tables. A users table might hold (id, name) and an orders table might hold (id, user_id, amount). To see order details together with the user's name, you need to JOIN the tables. INNER JOIN returns only rows where the join condition matches in both tables:
SELECT users.name, orders.amount FROM users INNER JOIN orders ON users.id = orders.user_id;
Using these two toy tables — users: (1,'Alice'), (2,'Bob') and orders: (101,1,50), (102,1,200), (103,2,75) — the query above returns three rows: Alice/50, Alice/200, Bob/75. If Alice placed an order and it does not appear here, the application has a data integrity problem worth reporting.
Combining COUNT with a JOIN is also common: SELECT users.name, COUNT(orders.id) AS order_count FROM users INNER JOIN orders ON users.id = orders.user_id GROUP BY users.name; tells you exactly how many orders each user placed — a fast way to verify expected totals after a test scenario.