Contents

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 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. Click Query Builder on the SQL Development tab of Start Page.
3. Drag the table Emp from Database Explorer onto the query diagram of the document. A table shape should appear containing a list of 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 field DeptNo 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
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:

1. Right-click the connector between two tables.
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 rowsfrom Emp on the shortcut menu. Notice 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 on 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, clear the check-box next to the EName field to exclude it from the 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 column Alias 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. Pay attention to the changes that occurred in the table shape related to the Emp table.
6. On the Where tab of the tabbed editor, click the button.
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 field Dept.DName and click . 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 on 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 the right arrow button.
12. Select this field in the Ordered By list and click . 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 this, click on 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 view is to use commands on the View menu.
14. In either Design or Text view, click the 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

Depend on the product you use the retrieval data may differ from this example.

Want to Find out More?

• Previous
• Next