There's no magic when it comes to writing a database test, you write them just like you would any other type of test. Database tests are typically a three-step process:
Setup the test. You need to put your database into a known state before running tests against it. There are several strategies for doing so.
Run the test. Using a database regression testing tool, run your database tests just like you would run your application tests.
Check the results. You'll need to be able to do "table dumps" to obtain the current values in the database so that you can compare them against the results which you expected.
The article What To Test in an RDBMS goes into greater detail.
4.3 Setting up Database Tests
To successfully test your database you must first know the exact state of the database, and the best way to do that is to simply put the database in a known state before running your test suite. There are two common strategies for doing this:
Fresh start. A common practice is to rebuild the database, including both creation of the schema as well as loading of initial test data, for every major test run (e.g. testing that you do in your project integration or pre-production test sandboxes).
Data reinitialization. For testing in developer sandboxes, something that you should do every time you rebuild the system, you may want to forgo dropping and rebuilding the database in favor of simply reinitializing the source data. You can do this either by erasing all existing data and then inserting the initial data vales back into the database, or you can simply run updates to reset the data values. The first approach is less risky and may even be faster for large amounts of data.
An important part of writing database tests is the creation of test data. You have several strategies for doing so:
Have source test data. You can maintain an external definition of the test data, perhaps in flat files, XML files, or a secondary set of tables. This data would be loaded in from the external source as needed.
Test data creation scripts. You develop and maintain scripts, perhaps using data manipulation language (DML) SQL code or simply application source code (e.g. Java or C#), which does the necessary deletions, insertions, and/or updates required to create the test data.
Self-contained test cases. Each individual test case puts the database into a known state required for the test.
These approaches to creating test data can be used alone or in combination. A significant advantage of writing creation scripts and self-contained test cases is that it is much more likely that the developers of that code will place it under configuration management (CM) control. Although it is possible to put test data itself under CM control, worst case you generate an export file that you check in, this isn’t a common practice amongst traditional data professionals and therefore may not occur as frequently as required. My advice is to put all valuable assets, including test data, under CM control.
Where does test data come from? For unit testing, I prefer to create sample data with known values. This way I can predict the actual results for the tests that I do write and I know I have the appropriate data values for those tests. For other forms of testing -- particularly load/stress, system integration, and function testing, I will use live data so as to better simulate real-world conditions.