Creating a Query in Query Builder

This topic explains how to create and edit an SQL query in dbForge Studio using the Query Builder features.

We are going to create a sample query by using visual query designer. We will use the AdventureWorks2012 database. Assume we need to display all the employees whose total sales result for the preceding year is greater than 2 000 000.

To create a query visually:

  1. Create a server connection. For more information about how to create a server connection see How To: Connect to a Database.
  2. On the Start page click SQL Development and then click Query Builder.
  3. Drag-and-drop tables from Database Explorer to the editor. Holding CTRL, select the Person table and the SalesPerson table, and drag-and-drop them over to the visual editor.

    Tables on Diagram

  4. Select the FirstName and LastName check-boxes in the Person table, and the SalesLastYear check-box in the SalesPerson table.
  5. Create a relationship between tables based on the BusinessEntityID primary key. To do this, select BusinessEntityID in the Person table and drag it over the BusinessEntityID in the SalesPerson table.

    Selecting columns

  6. On the Where tab, insert the condition. Click the green plus icon.

    Where tab

  7. Click enter value. In the Table drop-down list select the SalesPerson table, and then select the SalesLastYear column.

    Enter a value

  8. Click the equals sign and select greater than instead.

    greater than

  9. Click enter value and type 2 000 000.

    enter value

  10. On the Group By tab select columns you want to group by the output data.

    group by

  11. On the Order By tab select the sort order.

    order by

  12. Finally, the visual query will look as follows:

    final query

  13. Press F5 to execute the query.

    result

  14. You can also view and edit (if needed) the generated automatically SQL code.

    Generated query

In this topic we reviewed quite simple query, however it demonstrates the convenience of the approach on creating queries visually.

Visual query editor will save a lot of your time in case of more complex queries to multiple tables and views.