In my experience, testing SQL is just like any other language. You define input data, execute the script under test, and then check the output (and possibly the state of the system) to confirm. The issue is more pronounced when you start doing DDL on the fly, but as long as you generally confine yourself to extracting data in a specific format, it's not too hard. I've done it entirely in sql - you can have a "test data" table and compare it to a "results" table that gives you an oracle of truth*.
The best is when you have rollups or other basic math that needs to be tested, so you can do parallel calculations (in another language or by hand) to ensure that the math is right. The worst is when you have small format changes that alter e.g. the order of the output - then you're left with either making your tests order-invariant or twiddling them for every ORDER parameter, which can be frustrating.
*Truth is only as good as your ability to input results, of course.
The best is when you have rollups or other basic math that needs to be tested, so you can do parallel calculations (in another language or by hand) to ensure that the math is right. The worst is when you have small format changes that alter e.g. the order of the output - then you're left with either making your tests order-invariant or twiddling them for every ORDER parameter, which can be frustrating.
*Truth is only as good as your ability to input results, of course.