Database Queries - (c) George Schell Page1 Database Queries Learning Objectives • Be able to create queries using one or multiple tables. • Understand how to limit query results with single and multiple constraints. • Understand how a query can request constraint values from the query user. • Be able to use queries that look for partial values in fields. • Be able to make computations within queries on both numeric and text fields. Introduction This project uses Microsoft Access to create queries for an existing database. The initial database can be downloaded from the MBA512 web site. The database for this project, Textbook, concerns classes and the books used in those classes. The database also contains records of students who have copies of books to sell. An Access database is a single file. Although there may be many tables in a database, they are all contained in the single database file along with any queries, reports, or other components of the database. You should create a copy of the data file with your database on a regular basis. Within Access you are able to use the “Edit” command to cut and paste objects. Those objects are tables, queries, and similar components of the database. You cannot copy the database itself from within Access. To copy the entire database, such as for a backup copy, you must copy the database file using Windows Explorer or clicking on the “My Computer” icon. One last caution about using database software is that the database file size can grow quickly. Users like to use features such as the “Undo” command that can easily reverse mistakes. Such features come at a price: They require much disk space in the database file to guide the reversal of actions taken. To keep your database file at a reasonable size you should compact it on a regular basis, such as every time you exit Access. From within Access, the “Tools,” “Database Utilities,” and “Compact and Repair Database” sequence of commands will compact your database to remove all of the deletions and missteps that you may have performed while using the database. You can change the Access settings to automatically compact the database each time you exit Access. “Tools,” followed by “Options,” “General,” and “Compact on Close” will have Access automatically compact the database. However, many universities limit the ability of students to change computer settings so you may have to explicitly compact your database each time you use Access. EXAMPLE This example will generate a number of queries from the Textbook database. The Textbook database will be provided by your instructor or downloaded from the Prentice Hall Web site. Managers typically use queries to look for specific records, and so the query is constrained to records that have certain values for fields. Other queries compute new values from the values of other fields in the query. It is important for decision makers to be able to generate their own queries. First, the decision maker who can create a query gains immediate access to the power of a database. Second, the decision maker may not know exactly which records in the database are neededDatabase Queries - (c) George Schell Page2 until a query is generated, its results are seen, and more queries are created in an iterative process until the results desired are ultimately generated. The third reason is that an intuitive understanding of the database cannot occur unless the decision maker gains a hands-on understanding of the data values and relationships in the database. Information is the essential tool of a decision maker. The understanding of the database gained by writing queries trains a decision maker to use that tool better. Textbook Database The Textbook database consists of four tables that relate to each other by common values. The tables and their data fields are shown in Figure 1. The STUDENT table contains four fields; FirstName, LastName, BookNumb, and CopiesToSell. The key fields of the tables are shown in bold font. It takes the combination of values from the FirstName, LastName, and BookNumb fields to make a key (a unique value) for the STUDENT table. The ClassNumb field is the only field needed to be the key of the CLASS table. Figure 1 - Tables and Fields in Textbook Database Figure 2 - Fields and Values in the STUDENT Table The fields from different tables can be combined when a common value exists between the two tables. For example, the field BookNumb is in both the STUDENT table and the BOOK table. If the value of BookNumb is 409 in the BOOK table and in the STUDENT table then theDatabase Queries - (c) George Schell Page3 name of the student from the STUDENT table can be associated with the book title of the BOOK record. From Figure 2 you can see Amy Abner and Brian Janski both have book number 409 to sell. Book number 409 (see Figure 3) has a title of “Management Information Systems” and was authored by McLeod and Schell. For the projects and assignments in this text you will only have to use an existing database; you will not be required to create a database or tables within a database. Begin this project by opening the Access program and then opening the Textbook database. Notice from Figure 4 that the “Queries” tab is chosen. Figure 3 - Fields and Values in the BOOK Table Figure 4 - Textbook Database Screen Creating a Query with Constraints Records in the BOOK table are shown in Figure 5. If a query were constructed without constraints, all of the records in the table would be displayed in the query result. It is more productive to restrict the records displayed to those that fit some decision making criteria. Let’s look at books with a price greater than $100. Double-click on the phrase “Create query in Design view” shown in Figure 4 and Figure 6 appears. For this query we choose the BOOK table from the list by highlighting theDatabase Queries - (c) George Schell Page4 BOOK choice and clicking the “Add” button. Then we can close the screen that shows tables by clicking the “Close” button. Figure 5 - Fields and Values in the BOOK Table Figure 6 - Choosing a Table for a Query Figure 7 appears and it is in the format of a query-by-example. Query-by-example (QBE) is a format where a user can specify what is desired without having to write computer code to retrieve records from the database. Each column in QBE can be a field to be