Home » Blog » Identifying Test Cases #1

Identifying Test Cases #1

2010-06-08 - General

It takes a lot of practice to write test cases for SQL code, and much of that practice happens on the job. One of the most important aspects in writing test cases is simply identifying what test cases to write. Normally I start by writing my first test case before I touch the code at all, but today we're going to look at code first and then talk about what test cases are needed. The examples here will be using the tables in the AdventureWorks database for SQL Server.

Example 1 - Expressions

SELECT AVG(DATALENGTH(Comments)) AS AvgLen FROM Production.ProductReview

This query is simply returning the average length of the Comments column. Here are some of the tests I think are needed:

1. There are no rows in the ProductReview table

2. There is one row in the ProductReview table

3. The Comments column contains a null value (Comments is a nullable column, we want to make sure that the value returned is appropriate)

4. The Comments column contains an empty string

5. There are multiple rows in the ProductReview table

6. There are multiple rows in the ProductReview table and for some of them the Comments column is a null value or empty string (testing that null values and empty strings are being handled the way necessary for my application)

7. There is a very large text in the Comments column (testing that we do not overflow the value returned by AVG or DATALENGTH functions)

Example 2 - Filter Criteria

SELECT ProductNumber, Name FROM Production.Product WHERE Color != 'Silver'

This query gets us the product numbers and names of products which are not Silver. Here are some test cases:

1. A single row with a Color of 'Silver'

2. A single row with a Color other than 'Silver', e.g. 'Red'

3. A single row with a null Color (the way the query is written now, this row would not be returned)

4. A single row with a null ProductNumber

5. A single row with a null Name. This case and the previous one are especially important if this is part of code that will consume the values of this query. If these columns are not-nullable, then there should be a test case elsewhere to assert that design.

6. Multiple rows which match the criteria and do not match the criteria

7. Depending on the needs for case-sensitivity, I would also have test cases that varied the case of the word 'Silver'

Example 3 - A Simple Join

SELECT Store.Name, Customer.AccountNumber
FROM Sales.Store
JOIN Sales.Customer ON Store.CustomerID = Customer.CustomerID

We're joining on CustomerID which is the primary key in both of these tables, and it is a foreign key from the Sales table to the Customer table. Assuming these constraints are unit tested somewhere else, we might have the following test cases for this SELECT statement:

1. No rows in either table

2. A row in Customer which does not have a corresponding row in Store

3. A row in Customer which does have a corresponding row in Store

Homework

1. Take another look at Example 3. What additional test cases would we have if Store.CustomerID was not the primary key of the Store table and was instead a nullable foreign key column?

2. Why do we have to pay such close attention to null?

3. Are there any test cases missing in the examples above?

Looking ahead
Next time, we'll have some more advanced query examples and work with parameters. Then later, we'll discuss some simple practices and tools that can help you identify test cases while you're programming.

Categories: General