602SQL Documentation Index  
<­Query Designer UpQuery Designer    

Query Designer

Designing a Simple Query

We have the Companies table from the tutorial and we want to display sorted company names (example 2).

  1. Select the Queries folder on the Control Panel and run the New action. The designer window appears with an empty design.
  2. Press the Add new table button on the toolbar or run the Add table action from the pop-up menu on the FROM clause in the tree. A window for selecting tables or queries appears, select the Companies table in the list and press OK. The table will be added to the tree under the FROM clause and a line with the table is added to the grid for the FROM clause.
  3. Move to the SELECT branch in the tree and drag the Name column from the table to the Expression column (or enter it manually using the keyboard).
  4. Move to the tree root and select the Add ORDER BY command. The ORDER BY branch is inserted at the end of the tree and you can enter (or drag) the name of the column that sets the order. In this case, Name.
  5. The design is complete, it is now necessary to check the design. Select the Validate command form the toolbar or from the Design menu. A window will appear stating that everything is correct or will describe the error.
  6. Display the query result by running the Data preview command from the toolbar or from the Design menu. A window with the grid that contains the query result will appear.
  7. To display the SQL text of the query, run the Edit SQL command from the toolbar or from the Design menu. An editor window will appear with the query SQL text. Format and wrapping depends on the specified parameters in the Query designer properties window. If you perform syntax validation in the editor, the error occurrence will be displayed directly in the text.
  8. Save the design.

Designing a Complex Query

We have the tables Companies and Invoices. We want to obtain a list of customers, their average sales, omitting those that ordered only once (example 7).

  1. Select the Queries folder on the Control Panel and run the New action. The designer window appears with an empty design.
  2. Insert both tables in the same manner as in the previous example.
  3. Make sure that the Convert INNER JOIN to WHERE property is set in the Query designer properties window.
  4. Link the tables. Drag the number column from the Companies table to the Company column of the Invoices table. Both columns will connect with a line and the WHERE condition appears in the tree on the left. Select the WHERE condition and the grid will be filled with a single condition.
  5. Add another condition to the next grid line for the company status. Drag the status column and drop it on the second grid row on the Expression column. Use the keyboard to make the expression: status = 'O'. If you do not want to use the keyboard, you may enter the operator and apostrophes on the auxiliary Lexical elements window. Finally, choose the connector that will join both conditions, AND in this case.
  6. Create groups. Move the tree to the Query1 item and select the Add GROUP BY command from the pop-up menu. Drag (or enter manually) the column name to the grid that will be used for making the groups (name from the Companies table).
  7. Create the condition on the groups. Move back the tree to the Query1 item and select the Add HAVING command from the pop-up menu. Enter the expression that restricts the groups to the grid (or use the Lexical elements window again) COUNT(*)>1
  8. Complete the column list in SELECT. Move to the SELECT branch of the tree and enter (drag) columns to the grid that will compose the query columns. Each expression (column) will be in one grid row. Specify the following: name, AVG(Invoices.amount) and COUNT(*).

<­Query Designer UpQuery Designer