Queries
 

 

Queries.  Some general discussion of queries seems warranted before we start using them.  This discussion may seem somewhat abstract until you have more experience.  But as you read through my examples you should refer back to this webpage with the hope that this discussion will gain meaning with each example.

We have been working towards developing efficient and effective databases. 

  • We have focused on creating entities/tables that are meaningful. 
  • We have also focused on developing attributes/fields that are important.
  • We have tried to make the importance of developing simple and representative primary keys very clear.
  • We have also tried to emphasize how making use of these primary keys in another table as foreign keys improves our overall database design and development.
  • We have repeatedly developed data dictionaries when we are developing tables and fields.
  • We have also drawn out our relationships between entities/tables based on keys and foreign keys.
  • We have described a basic set of possible structures for these relationships.
    • one-to-many
    • one-to-one
    • many-to-one
    • many-to-many
  • We have also talked about the importance of creating and maintaining certain types of integrity in our databases.
    • referential integrity
    • entity integrity
    • domain constraints
    • action assertions
  • Normalization has been defined and exemplified in a number of examples.  The increased effectiveness and efficiency of using normalized tables has been emphasized.

Now we need to focus more on how to make use of our databases.

One of the most important things we can do with our data is query it to garner information from the data we've collected.  These queries need to be able to happen within single tables and across many tables.  But essentially, queries are developed to answer specific questions from the database. 

  • Maybe you want to know the percentage of customers that come from each of the zip codes in your area.
  • Maybe you want to know what orders a particular firm has placed.
  • Maybe you want to be able to rank all your salespeople each month.
  • Maybe you want to determine what percentage of your students are getting financial aid.
  • Maybe you need to determine which vendor is best at getting their supplies to you on time.

Obviously, this list can go on and on.

I am definitely of the opinion that the best way to learn more about queries is to see plenty of examples.  After some discussion of queries and how they are approached in Access and elsewhere in the next webpage, we will focus on examples for the rest of the week's webpages.