Building Queries Using Enterprise Manager

You can build your own queries to examine rows in tables using Enterprise Manager. In this section, you'll learn how to build and run a query to view the orders placed by the customer with a CustomerID of ALFKI, along with the order details and products for the order with an OrderID of 10643. Specifically, you'll be selecting the following columns:

• The CustomerID and CompanyName columns from the Customers table

• The OrderID and OrderDate columns from the Orders table

• The ProductID and Quantity columns from the Order Details table

To start building the query, select the Customers table in Enterprise Manager from the Tables node of the Databases folder for the Northwind database. Click the right mouse button and select Open Table ➣ Query. This opens the query builder, as shown in Figure 2.18.

Figure 2.18: The query builder

The upper pane is called the Diagram Pane, and it shows the tables that are used in the query. As you can see, the Customers table is initially shown in the Diagram Pane. The pane below is called the Grid Pane, and it shows the details for the columns and rows to be retrieved from the tables. Initially, all rows are to be retrieved from the Customers table, as indicated by the asterisk (*) in the Grid Pane. Below the Grid Pane is the SQL Pane, and it shows the SQL statement for the query.

Note 

SQL is a text-based language for accessing a database, and you'll learn all about SQL in the next chapter. For now, you can click the SQL button on the toolbar to hide the SQL Pane-unless you want to view the SQL statement that is constructed by the query builder.

Below the SQL Pane is the Results Pane, which shows any rows retrieved by the query. This is initially empty because no query has yet been run. Use the following steps to build the query:

1. Remove the asterisk (*) from the Grid Pane by clicking the right mouse button on the box on the left of the row containing the asterisk and selecting Delete. This stops all columns from being retrieved from the Customers table.

2. Click the right mouse button in the Diagram Pane, and select Add Table. Add the Orders and Order Details tables so that you can query these tables. You can also click the Add table button on the toolbar to add tables. You'll notice that after you add the tables, they appear in the Diagram Pane along with lines that connect the parent and child tables through the foreign key. For example, the Customers and Orders tables are connected through the CustomerID column. Similarly, the Orders and Order Details tables are connected through the OrderID column.

3. Select the CustomerID and CompanyName columns from the Customers table by selecting the check boxes to the left of the column names in the Diagram Pane.

4. Select the OrderID and OrderDate columns from the Orders table.

5. Select the ProductID and Quantity columns from the Order Details table.

6. In the Grid Pane, set the Criteria for the CustomerID column to = 'ALFKI'. This causes the query to retrieve only the rows from the Customers table where the CustomerID column is equal to ALFKI.

7. In the Grid Pane, set the Criteria for the OrderID column to = 10643. This causes the query to retrieve only the rows from the Orders table where the OrderID column is equal to 10643.

8. Run the query by clicking the Run button on the toolbar.

Figure 2.19 shows the final result of building and running the query.

Figure 2.19: Building and running a query

As you'll see in the next chapter, you can also build and run queries using Visual Studio .NET. In the next section, you'll learn how to create a table using Enterprise Manager.