Querying Databases, You've Got to Ask the Right Questions
David Hayes, James E. Hunton · UvA-DARE (University of Amsterdam) · 2001
Enhancing the power of relationships. The power of relational databases lies not so much in their ability to store vast amounts of information, but in their capacity to sort out complex data relationships and then assemble custom reports on them. Thus, for example, a database that contains customer names and sales information can report which customers bought a particular product and any number of other relationships that link them. The key, however, is knowing how to ask the right questions of the database, and this article shows how. In relational database jargon, a question is called a query and represents a request for information from tables in one or more databases. When a query is posed and the data sorted, the underlying database doesn't change. The software just looks into the tables, searches for and processes the requested relationships and then issues the customized answers, leaving the original data untouched. Most applications built on relational databases incorporate a querying tool known as query-by-example (QBE), which uses a graphical approach to construct queries. Although QBE tools are visual and relatively easy to use, they're somewhat limited. To create complex queries, especially when dealing with multiple databases, users must turn to a language called structured query language (SQL) or structured English query language. The acronym for both is pronounced sequel. In today's world of client-server architecture and data warehouses, it's important to understand the fundamentals of SQL, for it is the basis of all database queries. We'll build queries using the QBE feature of Microsoft Access and briefly explain the SQL code underlying each request. ADDING MORE DATA Before we can begin, we must add more data to the database file that we built in earlier articles (see the box Download the Database Tables). Launch Microsoft Access and open the file--Cust_Track_2000. Click the Forms tab and open the previously created Customers form by highlighting the selection and clicking Open (exhibit 1, at right). [Exhibit 1 ILLUSTRATION OMITTED] If the toolbar isn't displayed, click View, Toolbars, Database, and it will resemble exhibit 2, at right. [Exhibit 2 ILLUSTRATION OMITTED] Add the following sales orders: For customer 1 (Mark Fly), add a sale by clicking in the OrderDate column in the first empty row and typing the date 12/20/99, as in exhibit 3, page 37. Now click in the first empty row under Order Details_Product ID and type 1; then tab to Quantity and type 40; tab to SalePrice and type 37.50. Finish the order by filling in the second row of order details to match exhibit 3. [Exhibit 3 ILLUSTRATION OMITTED] Using the navigation bar at the bottom left of the form, click on the next record button to get to customer 2. Make sure the information for Julie Fly shows. Now input a new order by typing 12/10/99 in the OrderDate column and fill in the order information found in exhibit 4, page 37. [Exhibit 4 ILLUSTRATION OMITTED] Similarly, add an order for Lora Masters (exhibit 5, page 37) and LaVonne Hayes (exhibit 6, page 38). When finished, close the Customers form by clicking on the bottom X in the upper right hand corner of the form. [Exhibits 5-6 ILLUSTRATION OMITTED] DESIGNING A SINGLE QUERY Now that the new information is entered, we'll design a query that selects just our Arkansas customers so we can telephone them. To do this we need the company name, contact first name, phone number and state (so that only Arkansas businesses can be selected). Click the Queries tab and the New query button. Highlight the Simple Query Wizard and select OK. Select Tables: Customers, if it isn't already selected. Pick the fields desired from the Customers table by moving them from Available Fields to Selected Fields using the move button as shown in exhibit 7, at right. …