Building Queries

Understanding Aqua Data Studio's Query Building Process

To build a query in Visual Query Builder, perform the following steps:

1. Connect to a Data Source.
2. The list of tables for the selected Database is displayed in the Table Pane. From the Data Pane, select the database objects.
3. Drag-and-drop objects to the Diagram Pane and Select Deck.
4. Establish relationships between objects.
5. Create query conditions in the Where, Group By, Having, Order By decks.
6. Verify the query in the SQL Pane. Execute the query and view results.

To understand the query building process more clearly, let us connect to a data source and try to build a SQL query for the below scenario:

Find the Employee Number, First and Last Name of Employees who are assigned to a Project, who were hired after 1/1/2003 and whose Job Designation is Manager. The two tables involved are EMPLOYEE and PROJECT.

Let us use Aqua Data Studio's Visual Query Builder and build the complex SQL in a few easy steps:

1. Connect to the Data Source, IBM DB2 UDB in our case.
2. Under the Data Pane, locate and select the EMPLOYEE and PROJECT tables by [Control Clicking] to multiselect them (OS X use [Command Click]).
3. Drag-and-drop the objects to the Diagram Pane.

4. Table Entities appear in the Diagram Pane. Click on EMPNO from the EMPLOYEE table, drag and drop it over RESPEMP in the PROJECT table to create a JOIN.

5. Drag-and-drop EMPNO, FIRSTNME and LASTNAME into the Select Deck.

6. From the EMPLOYEE table, drag-and-drop HIREDATE into the Where Deck. Select the [operator >] enter a value 1/1/2003.

7. Next drag-and-drop JOB from the EMPLOYEE table into the Where Deck. Select the [operator =] and enter the title MANAGER.

8. Navigate to the SQL Pane and you can see that a complete SQL statement based on your criteria is automatically generated. You should see a SELECT statement that takes into account each of the items you dragged into place. You cannot type in this area. The SQL statement in the SQL Pane should resemble:

select
	"EMPLOYEE"."EMPNO",
	"EMPLOYEE"."FIRSTNME",
	"EMPLOYEE"."LASTNAME" 
from
	"ADMIN"."EMPLOYEE" "EMPLOYEE" 
		inner join "ADMIN"."PROJECT" "PROJECT" 
		on "EMPLOYEE"."EMPNO" = "PROJECT"."RESPEMP" 
where
	("EMPLOYEE"."HIREDATE" > '1/1/2003') AND
	("EMPLOYEE"."JOB" = 'MANAGER')

Press [Ctrl + E] ([Cmd + E] on OS X) or click on the [Execute button] in the Query Builder Toolbar to view the result of the generated SQL Query.

These basic actions are used throughout the Query Builder. Drag your column either from the Data Pane or Table Entity into a Deck, or, for JOINs, drag your column from one Table Entity onto a column in another Table Entity.