Building a Query in Visual Mode

The topic describes how to use Query Builder for generating SELECT statements.

Retrieving Data from Tables Using Query Builder

Suppose you need to know what department the employee belongs to. To accomplish the task, we need to get the employee’s name and the department internal code from one table (Emp) and the name of the department from another table (Dept).

Creating Inner Join between Two Tables

In Database Explorer, make sure that the connection to the server is active.
In the SQL Development section of the Start Page, click Query Builder.
Drag the Emp and Dept tables from Database Explorer to the query diagram.
Select EName in the Emp table shape, and Dname in the Dept table shape to include them to the SELECT list. These fields appear in the Selection tab of the tabbed editor below the query diagram.
Drag the DeptNo field from the Emp table shape to the same field of the Dept table shape. A connector between two tables appears, what indicates that the relation is established.
Switch to the Joins tab of the tabbed editor to make sure the relation has been established properly. The string should like Emp.DeptNo=Dept.DeptNo.
To perform the query, click on the Query toolbar.

EName	Dname
Smith	Sales
Allen	Sales2
Ward	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:

Right-click the connector between two tables. The shortcut menu appears.
Estimate the position of table shapes on the query diagram. If the table shape representing the Emp table is at the left of the connector, click Select all rows from Emp on the shortcut menu. Notice, that the middle of the connection has changed its form.
Switch to the Joins tab of the tabbed editor to see the changes. Alternatively, click the red-colored join kind and select Left Outer Join on the shortcut menu.
To execute the query, click on the Query toolbar .

The result of this query should be exactly the same as the previous. To see how the outer join works, change a value of DeptNo in any 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 there is a separate tab for every clause 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 we need to find out, how much money employees earn in each department. Let’s exclude the president from the list and sort it by money amount in descending order.

Switch to the Design view of the query that was built in the previous section of the walkthrough.
In the Emp table shape, clear the EName field to exclude it from the SELECT list.
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.
In the same row, click the Alias column and enter a alias for the field, for example Money.
In the same row, click the Function column and select Sum from the drop-down list. Pay attention to the changes that occurred in the table shape related to the Emp table.
On the Where tab of the tabbed editor, click the button.
Click the left < enter a value > field, then select Emp.Job from the drop-down list.
Click the condition operator (=) and select Does not equal from the drop-down list.
Click the right < enter a value > field and type President.
On the Group By tab, select the Dept.DName field and click . Use up and down arrow buttons to change the columns order. If you mistakenly added a wrong field to the Group By list, exclude it by selecting it and clicking the left arrow button. Keep the Having tab intact. It is absolutely similar to the Where tab.
On the Order By tab, click Money, and then click .
Select this field in the Ordered By list and click . Note what happened to table shapes after all these manipulations.
At any moment you can switch to the Text view of a document to examine a query you built with Query Builder. Click the Text button at the bottom part of a document. If the Text button is not visible, use a corresponding item in the shortcut menu. Another way to change document’s view is to use commands on the View menu.
In either Design or Text view, click the Execute button.