Creating a Query in Query Builder

This topic explains how to create and edit an SQL query in dbForge Fusion for SQL Server 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 Connect to a Database.

  2. On the Fusion menu, click New Query.

  3. Drag-and-drop required 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.

    Selecting the Person table

  4. Select the FirstName and LastName checkboxes in the Person table, and the SalesLastYear checkbox 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 the Person table

  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.

    Entering values

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

    Entering conditions

  9. Click enter value and type 2 000 000.

    Completing to enter conditions

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

    Grouping columns

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

    Ordering values

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

    Final visual query

  13. Press F5 to execute the query.

    Result set

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

    The generated SQL code

    In this topic we reviewed a 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.