Procedures consist of many SQL statements to access the database. Because of several SQL statements that are consolidated into a stored procedure it is a little hard to understand how to test such a procedure separately or all together?
Another approach that might help with the portability is to run you SQL from an application test harness. If you use the SQL inline with this you have the ability to take the exe to any installation and just run it. To expand on using a testing framework there are several really good articles on Simple Talk about Unit testing sql code. They are good reads.Sept 07 - http://www.simple-talk.com/dotnet/.net-tools/testing-times-ahead-extending-nunit/March 08 http://www.simple-talk.com/dotnet/.net-tools/testing-times-ahead-extending-nunit/Jan 2010 http://www.simple-talk.com/sql/database-administration/close-those-loopholes-lessons-learned-from-unit-testing-t-sql/
Your application uses an API to interact with the database. It is possible to write your API in such a way that it presents correct results to the application and yet still uses the database in the wrong way.
For example, imagine a database with an EMPLOYEE table and a MANAGER table. The tables are alike -- e.g. each contains a first name, last name, company email address, and salary -- but the EMPLOYEE table is only for non-managers and the MANAGER table is only for managers.
Now imagine a database API with two classes, one per table. The programmer writes the employee code first. The manager code is almost the same, so the programmer makes a copy of the employee code and then edits it. He changes the name of the class, and he changes the method names. And then right before he's about to change the name of the table from EMPLOYEE to MANAGER, he's distracted: he decides to grab another cup of coffee, or he remembers he hasn't read the latest XKCD, or he notices a new insightful SQA comment from Sam Woods.
By the time the programmer gets back to work, he's forgotten about changing that table name, so he compiles the code and then writes some unit tests. The tests for both employee and manager do the same kinds of things. One test creates the object, writes it, and then reads it back. Another test tries to read an object that isn't in the database. Maybe there's an update test and a remove test or a test to write several objects and read them back. Those tests all pass, so the programmer declares victory. Of course, there's a bug in the code: manager records are written to the EMPLOYEE table.
There are different kinds of tests you could have used to find this particular problem. One way would have been to call the method that retrieves the entire contents of each table. But if the application doesn't need that method, it may not occur to you to add it just for test purposes. Another way would have been to query the database using something more trustworthy than your database API, i.e. raw SQL.
My example is contrived but I think it gets the point across. You can find a similar question (and some answers) at Is it OK to use the classes under test to initialize the database for the tests?.
Well, I think that the problem is that you are trying to search for all types of events and since AU_ENTITY_ID allow NULLs you might receive such error.
Please try to use LEFT/RIGHT/FULL JOIN (according to what you are trying to achieve) and not using INNER JOIN since converting NULL to INT value might result a failure (unless you are protecting your search with ISNULL()).
This is common problem, usually solved by object-relation mapper (ORM) helper library. There are many for Java (too many), so it is not easy to choose a good one. Do your research before you commit to one of them.
I found this to be a useful, though high level article about data validation. It's more about how to approach the testing than specific tests, but it does contain some specific tests and has a lot of useful information: http://msdn.microsoft.com/en-us/library/gg261774.aspx.
That sounds basically right. The only change I might make is instead of loading up to a CSV, you can just take a mysqldump and load the DB from the dump at the beginning of the script.
[W]hat sort of direction should I head in for learning and practicing my SQL?
W3Schools has good info on SQL. There are many flavors of SQL (MySQL and T-SQL are the big ones) but the basics are the same for the queries you'll likely use.
What SQL concepts should I absolutely know before I head in?
If you can SELECT and JOIN, you probably have enough to validate data.
What kind of test topics should I expect?
Who knows really. Cover your bases and check out the links Chris Kenst shared in his answer. I like to ask questions not related to testing but rather problem solving; "How many ping-pong balls will it take to fill up a limo?" From your questions it sounds like an entry-level position so they won't expect you to know everything but you should make it clear that you know how to find the answers. "I don't know, but I am sure but I am sure I can find out using Google and/or StackOVerflow." Do not try to BS your way through any question.
