Tutorial Gateway

SQL Query Builder

by

The SQL Query Builder makes the developer life easy to design the SQL Queries. If you don’t know how to write the SQL Queries or if you find it difficult to perform grouping, sorting and join operations or if you find it difficult to design the complex SQL Queries then you can use this Query builder to design your desired queries using Graphical User Interface.

SQL Query Builder Example

Before we start using the query builder,We have to select the Database as shown below.

SQL Query Builder 1

Right click on the query window will open the context menu. Please select the Design Query in Editor… option from th context menu as shown below.

SQL Query Builder 2

Once you click on the Design Query in Editor… option it will open the Query Designer in separate window. Before designing the SQL query you should understand the different panes present in the SQL Query builder.

  1. Diagram Pane: This pane is used to select the tables, Views or functions. SQL Query designer will automatically join the tables as per the database relations. After selecting the required tables we can select the required columns by check marking the columns.
  2. Grid Pane: All the selected columns will be displayed in this panes. This pane is very useful to apply sorting (ORDER BY ASC and ORDER BY DESC) and also grouping the column names (GROUPING Statement)
  3. SQL Pane: SQL Query designer will automatically build the SQL query for us in this pane.

SQL Query Builder 3

Right Click on the Diagram Pane and select Add Table.. option to add the required table.

SQL Query Builder 4

From the below screenshot you can observe that, we selected the DimCustomers table.

SQL Query Builder 5

Let us add one more table (FactInternetSales) as well to show the SQL Joins.

SQL Query Builder 6

From the below screenshot you can observe that, SQL Query builder automatically Joined the two tables DimCustomers and FactInternetSales using the CustomerKey column.

SQL Query Builder 7

Please select the required columns by check marking the column names in the Diagram Pane. In this example we selected the First Name, Last Name, Education, Occupation, Yearly Income, Sales Amount, Tax Amount and Orders Quantity columns from two tables

SQL Query Builder 8

Alias

Alias property in Grid Pane is used to apply SQL Alias functionality. Within the Grid Pane, Please change the Column names as per you requirement.

SQL Query Builder 9

Sorting

Sort Type property in Grid Pane is used to apply SQL ORDER BY functionality. Within the Grid Pane, Please change the Sort Type to Ascending or Descending as per you requirement. In this example we are sorting First Name Ascending and Yearly Income Descending

SQL Query Builder 10

Please change the Sort Order option as per your requirement. For this example, SQL query builder will sort the data by First Name in the Ascending order first and then it will sort the Data by Yearly Income in descending order.

SQL Query Builder 11

Grouping

Normally, SQL Query builder will not display any Grouping option in second region. To perform SQL Group By, Please right-click on the empty space and select Add Group By option from the context menu as shown below

SQL Query Builder 12

From the below screenshot you can observe that, We performed Grouping on First Name, Last Name, Education and Occupation.

SQL Query Builder 13

For the remaining columns, We calculated the aggregation such as Sum of Yearly Income, Sales Amount, Tax Amount and counting Orders Quantity

SQL Query Builder 14

Filters

We are adding >1000 filter on Sales Amount. Since we are applying filter condition on aggregated data Sum(Sales Amount), Query builder is using HAVING Clause otherwise, WHERE Clause will be used

SQL Query Builder 15

Click OK button to close the SQL query builder. Below code snippet will show you the final T-SQL Query generated.

SELECT DimCustomer.FirstName AS [First Name], 
       DimCustomer.LastName AS [Last Name], 
       DimCustomer.EnglishEducation AS Education, 
       DimCustomer.EnglishOccupation AS Occupation, 
       SUM(DimCustomer.YearlyIncome) AS [Anual Income], 
       COUNT(FactInternetSales.OrderQuantity) AS [Total Orders], 
       SUM(FactInternetSales.SalesAmount) AS Sales, 
       SUM(FactInternetSales.TaxAmt) AS Tax
FROM DimCustomer INNER JOIN
       FactInternetSales ON 
     DimCustomer.CustomerKey = FactInternetSales.CustomerKey
GROUP BY DimCustomer.FirstName, DimCustomer.LastName, 
         DimCustomer.EnglishEducation, DimCustomer.EnglishOccupation
HAVING   (SUM(FactInternetSales.SalesAmount) > 1000)
ORDER BY [First Name], SUM(DimCustomer.YearlyIncome) DESC

Let us see whether the query designed by SQL query builder is executing perfectly or not by clicking the execute button on the top.

SQL Query Builder 17

Thank You for Visiting Our Blog

Placed Under: SQL