Building a Query in Visual Mode

In this walkthrough you will learn how to use Query Builder in generating SELECT statements.

Retrieving Data from Tables Using Query Builder

Suppose you need to know what department the employee belongs to. To accomplish this task, it is needed to get employee’s name and department internal code from one table (Emp) and name of the department from another table (Dept). Creating Inner Join between Two Tables

  1. In Database Explorer, make sure that connection to your server containing the base (Demobase) is active.
  2. On the Start page, click New Query. You can also click the New Query button on the Standard toolbar which will open a blank query document.
  3. Drag the Emp table from Database Explorer onto the query diagram of the document. A table shape should appear containing a list of the table columns. Do the same for Dept.
  4. Select the EName check box field in the Emp table shape and the DName check box field in the Dept table shape to include them into the SELECT list. These fields appear in the Selection tab of the tabbed editor below the query diagram.
  5. Drag the DeptNo field from the Emp table shape onto the same field at the Dept table shape. A connector between two tables appear, indicating the relation has been established.
  6. Switch to the Joins tab of the tabbed editor to make sure the relation has been established properly. You can see Emp.DeptNo=Dept.DeptNo string.
  7. Click the Execute button on the Query toolbar to perform the query.
EName DName
Smith Sales
Allen Sales2
Jones Sales
Martin Sales2
Blake Sales2
King Accounting

Creating an Outer Join Between Two Tables

Now when data from several tables can be retrieved at the same query, let’s have a closer look at the SQL statement that was just composed. A distinctive feature of the INNER JOIN is that if an employee has DeptNo, which is never encountered in the Dept table, then this employee will not be listed in the query result. You can set value of DeptNo to other than 10, 20 or 30 to make sure that after performing the same query the corresponding entry disappears from the returned data. An OUTER JOIN should be used to save the situation.

To create an OUTER JOIN:

  1. Right-click the connector between two tables. The shortcut menu appears.
  2. Estimate the position of table shapes on the query diagram. If the table shape representing table Emp is to the left of the connector, choose Select all rows from Emp from the shortcut menu. Note that the middle of the connection has changed its form.
  3. Switch to the Joins tab of the tabbed editor to see the changes. To click the red-colored join kind and to choose Left Outer Join from the shortcut menu is an alternative way to set up the relation.
  4. Click the Execute button on the Query toolbar to execute the query.

    The result of this query should be exactly as of previous. To see how the outer join works, change a value of DeptNo in either of the tables to a unique value and re-execute the query.

Customizing a Query Using Query Builder

There are six clauses in the SELECT statement and for every clause there is a tab in the tabbed editor. The tabbed editor is the easiest way to adjust a query to your needs. Now the previous query should become a bit more complex. Suppose it is needed to find out how much money employees earn in each department. Let’s exclude a president from the list and sort it by money amount in descending order.

  1. Switch to the Design view of the query that was built in the previous part of the walkthrough.
  2. In the Emp table shape, unselect the EName field to exclude it from SELECT list.
  3. Switch to the Selection tab of the tabbed editor. Click the first empty cell and select Emp.Sal (SAL (emp)) field from the drop-down list.
  4. In the same row, click the Alias column and enter a pseudonym for the field. Let it be Money.
  5. In the same row, click the Function column and choose Sum from the drop-down list. Note that the changes that occurred in the table shape related to the Emp table.
  6. On the Where tab of the tabbed editor, click Filter Grey Add.
  7. Click the left <enter a value> field, then select Emp.Job from the drop-down list.
  8. Click the condition operator (‘=’) and choose does not equal from the drop-down list.
  9. Click the right <enter a value> field and type President.
  10. On the Group By tab, select the Dept.DNamefield and click Right Arrow Button. Use up arrow and down arrow buttons to change order of columns. If you have missed and added a wrong field to the Group By list, select it and click the left arrow button. You should not do anything at the Having tab. It is absolutely similar to the Where tab.
  11. On the Order By tab, click Money, then click RightArrowButton.
  12. Select this field in the Ordered By list and click Change Order Button. Notice what happened to table shapes after all these manipulations.
  13. At any moment you can switch to the Text view of a document to inspect a query you have built using Query Builder. To do it, click the Text button at the bottom part of a document. If the Text button is not visible, use a corresponding item in the shortcutm menu. Another way to change document view is to use commands from the View menu.
  14. In either Design or Text view, click Execute button.

If you have followed the instruction correctly and used source data provided in the previous topic, the following table should appear:

DName Money
Sales2 6950
Sales 3775

If you have made changes to the tables involved in the query, your results may differ.

Note
Depending on the product you use, the retrieval data may differ from this example.