SQL For Dummies®

Chapter 22. Ten Retrieval Tips

In This Chapter

• Verifying the structure of your database

• Using test databases

• Scrutinizing any queries containing joins

• Examining queries containing subselects

• Using GROUP BY with the SET functions

• Being aware of restrictions on the GROUP BY clause

• Using parentheses in expressions

• Protecting your database by controlling privileges

• Backing up your database regularly

• Anticipating and handling errors

A database can be a virtual treasure trove of information, but like the treasure of the Caribbean pirates of long ago, the stuff that you really want is probably buried and hidden from view. The SQL SELECT statement is your tool for digging up this hidden information. Even if you have a clear idea of what you want to retrieve, translating that idea into SQL can be a challenge. If your formulation is just a little off, you may end up with the wrong results — but results that are so close to what you expected that they mislead you. To reduce your chances of being misled, use the following ten principles.

Verify the Database Structure

If you retrieve data from a database and your results don't seem reasonable, check the database design. Many poorly designed databases are in use, and if you're working with one, fix the design before you try any other remedy. Remember — good design is a prerequisite of data integrity.

Try Queries on a Test Database

Create a test database that has the same structure as your production database, but with only a few representative rows in the tables. ...