Enhancing the power of relationships.

When Querying Databases,
You’ve Got to Ask
the Right Question

BY DAVID C. HAYES AND JAMES E. HUNTON

he 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).

If the toolbar isn’t displayed, click View, Toolbars, Database, and it will resemble exhibit 2.

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. 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.

Navigation bar.

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.

Next record button.

Similarly, add an order for Lora Masters (exhibit 5) and LaVonne Hayes (exhibit 6). When finished, close the Customers form by clicking on the bottom X in the upper right hand corner of the form.

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).

New button.

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.

Move button.

Click on Next and accept the prefilled Customers Query by clicking Finish. At this point, the query will report all customers from all states. To limit it to Arkansas, select the design button to modify the query using the QBE feature. To select Arkansas, type Arkansas in the Criteria row under the StateOrProvince column, as shown in exhibit 8.

Design button.

Now execute the query by clicking on the run button. This should produce a list of Arkansas customers, as shown in exhibit 9.

Run button.

It isn’t necessary to design the same query every time the underlying data change; the database automatically updates as you enter new information. Thus, the next time you run the same query, all current information will be displayed.

You can view the SQL programming code by clicking View, SQL View, as shown in exhibit 10.

Exhibit 11 shows the programming code behind the scenes as you use QBE. When you are finished viewing, close the query and save the changes when prompted, calling the query Customers Query.

To understand some of the SQL code terminology, see the sidebar, “What the SQL Terms Mean,” below. The SELECT statement specifies the columns to include in the query. Notice that Access places the table name (Customers) and a period (.) before each column name. While this isn’t necessary for single table queries, it’s required when specifying more than one table because they might share a common name. The FROM statement indicates which table should be used in the query (Customers), and the WHERE statement limits the query to Arkansas.

DESIGNING A TWO-TABLE QUERY

Next we’ll design a query that monitors customers’ purchases—specifically how long it’s been since their last order—and that will require accessing two tables. To start the query, click the New query button and use the Design View option. Click on OK. The information needed is in the Customers and Orders tables, so highlight them. Click Add and close the Show Table. You can adjust the Customers table by clicking and dragging as needed until it looks like exhibit 12.

New button.

Now select the desired fields (Company Name, ContactFirstName, PhoneNumber and OrderDate) from the tables by double-clicking on them. Since only the most current sales order is needed, turn on the totals feature by selecting the Totals button on the toolbar. To select the most recent (last) order date, click on the Group By box in the Orders column and click on the down arrow key to select Max as shown in exhibit 13.

Totals button.

To organize the list, sort the results so the oldest will appear on top. To do this, click the Sort box under Orders (exhibit 14), and select Ascending.

Now click the Run query button and the results will resemble exhibit 15.

Run button.

When finished, close the query and a prompt will ask if you wish to save changes. Select Yes and type a query name, Q_Old_Orders, and click OK.

EXAMINING THE TWO-TABLE SQL CODE

In order to examine the SQL programming code, highlight Q_Old_Orders, click Open, View and SQL View. The SQL code for the two-table query is shown in exhibit 16.

The SELECT statement specifies the columns to include in the query from the Customers and Orders tables. Notice the query asks for a data value equal to the maximum (max) order date (which Access named MaxOfOrderDate) AS of the most recent date in the OrderDate column of the Orders table. The FROM statement indicates that we are using the Customers table and joining (INNER JOIN) it with the Orders table based on common values in the CustomerID column of both tables. The GROUP BY clause tells the query to group the results by CompanyName and to show the ContactFirstName and Phone Number along with the CompanyName. Finally, the ORDER BY clause tells the computer to sort the query in ascending order starting with the oldest OrderDate in the query.

DESIGNING A MULTIPLE TABLE QUERY

How can SQL queries be used in real situations? Let’s say that after taking an inventory, the owners discovered the business was running low on shirts. While shirt sales were high, December net income fell from November. To find out why, perform the following new query.

Click New query and select OK for the highlighted Design View. Highlight and Add the following tables: Customers, Orders, Order Details and Products. Close the Show Table box and arrange the tables to view all the items, as shown in exhibit 17.

Double-click on the following from the tables to add the fields to the query: Company Name, Order Date, Product Name, Cost, Quantity and Sale Price. Next, add calculations to the query by using the bottom scroll bar to show more empty columns to the right. In the first empty box, click in the Field row and type Profit Per Unit: [Saleprice]-[cost], as in exhibit 18. The words before the colon are the column heading, and the words after it are the calculation commands. This will calculate the profit per unit for each item.

To calculate the profit per sale, click in the next empty box in the Field row and type Order Profit: [Profit Per Unit]*[Quantity], as in exhibit 19.

To display this item as currency, click in the Table row under the Order Profit column, click on the Properties button and in the Field Properties’ Format cell, highlight Currency, as in exhibit 20, and close the Field Properties box.

Properties button.

To calculate the total sales dollars for each order, click in the next empty box in the Field row and type Sales $: [SalePrice]*[Quantity]. To display the total sales dollars as currency, repeat the above operation in the Table row under the Sales $ column.

To understand the drop in net income, calculate the profit margin by clicking in the next empty box in the Field row and type Profit Margin: [Profit Per Unit]/[SalePrice]. To display this information as a percentage, click in the Table row under the Order Profit column, click on the Properties button and in the Field Properties’ Format cell, choose Percent, and close the Field Properties box. To display only the shirt sales, type shirts in the Criteria row in the ProductName column, as shown in exhibit 21.

Finally, to organize the query by order date, select the Descending option in the Sort row for the OrderDate column and then click on the Run button to get the results, as shown in exhibit 22.

Run button.

Based on these data, it appears the inventory level is down because the company sold shirts at a lower price in December. It also appears that the December net income might be less than in November due to the smaller profit margin.

EXAMINING MULTIPLE TABLE SQL CODE

To examine the SQL code, click View, SQL View. The code will look like exhibit 23.

The query says SELECT from Customers, Orders, Order Details and Products tables. Notice the use of the AS clause in the SELECT statement. For instance, we created a data item in the query called Order Profit, which is obtained by multiplying Profit Per Unit by (*) Quantity. The FROM clause indicates that the Products table will be joined (INNER JOIN) with the Customers table; however, the join must be threaded through the Orders and Order Details tables. The INNER JOIN statement begins by searching for a match on the Customer ID values between the Customers and Orders tables. When one is found, the INNER JOIN next matches the Order ID columns in the Orders and Order Details tables. Last, the INNER JOIN links the Product ID columns of the Orders Details and Products tables using the ON clause. As a result of this series of INNER JOIN commands, we know which customers placed orders and what products they ordered. The WHERE statement limits the Product Name to Shirts and the ORDER BY clause requests that the query results be shown in descending order (DESC) based on the OrderDate in the Orders table.

When you’re ready to close the query, a prompt will ask if you wish to save changes; select Yes and type Q_Net_Income_Down and click OK.

AUTOMATING QUERIES

To automate this query for other users, make the following adjustments to match exhibit 24.

Highlight the query Q Net_Income_Down and click on the Design View button. If the SQL is showing, click on View and Design View. In the Criteria row under the OrderDate column, type Between [Type Beginning Sales Date ‘MM-DD-YY’] And [Ending Sales Date ‘MM-DD-YY’]. This allows the user to specify the beginning and ending sales dates. Similarly, in the Criteria row under the ProductName column, type [Type of Product - ‘Jeans’ / ‘Shirts’ / Etc]. This allows the user to specify the type of product to examine.

Design view button.

Click on the Run button to get the Enter Parameter Value box in exhibit 25.

Run button.

Type 12-01-99 for the beginning date and select OK, and 12-31-99 for the end date and click OK. Note that Access converts the hypens (–) in the date to slashes (/). Then type Jeans as the type of product and click OK. This should display the results shown in exhibit 26. After checking the results, close the query and save the changes by clicking on Yes.

As you can see, relational databases can do more than organize information and provide lists. They can calculate data that are stored in those lists.

To be sure, relational databases have considerable power and flexibility—once you learn how to ask the right questions in the right way. While users often rely on QBE tools to produce simple queries, there are times when one must use SQL to refine database queries. This is particularly true in distributed database and data warehouse environments. There is much more that databases can do, and you’d be wise to begin using this application in your work.