SQL Query Builder 15

SQL Query Builder

by

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

SQL Query Builder Example

Before we start using this easy SQL query designer or builder, we have to select the Database.

SQL Query Builder 1

Right-click on the query window will open the context menu. Please select the Design Query in Editor… option from it.

SQL Query Builder 2

Once you click on the Design Query in Editor… option, it will open the Query Designer in a separate window. Before designing the 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 choose the columns needed by checkmark those columns.
  2. Grid Pane: All the selected columns will display in this panes. This SQL query designer pane is handy 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 create a query for us in this pane.
SQL Query Builder 3

Right-click on the SQL query designer Diagram Pane and choose Add Table.. option to add the needed 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

As you can see, the 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 SQL query builder example, we chose the First Name, Last Name, Education, Occupation, Yearly Income, Sales Amount, Tax Amount and Orders Quantity columns from two tables

SQL Query Builder 8

SQL Query Designer Alias Example

The Alias column in Grid Pane is used to apply Alias functionality. Within the SQL Query designer Grid Pane, Please change the Column names as per your requirement.

SQL Query Builder 9

SQL Query Builder Sorting Example

Sort Type property in Grid is used to apply for SQL ORDER BY functionality. Within the Grid Pane of SQL Query designer, Please change the Sort Type to Ascending or Descending.

We are sorting First Name Ascending and Yearly Income Descending using the SQL Server query builder.

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

SQL Query Builder 11

SQL Query Builder Grouping

Typically, the SQL Query builder will not display any Grouping option in the second region. To perform Group By, Please right-click on the space in the query designer and select Add Group By option from the context menu

SQL Query Builder 12

We performed Grouping on First Name, Last Name, Education, and Occupation using Query designer. For the remaining columns, We calculated the aggregation such as Sum of Yearly Income, Sales, Tax Amount and counting Orders Quantity

SQL Query Builder 14

SQL Query Designer Filters

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

SQL Query Builder 15

Click the OK button to close the query designer. 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 an easy query builder is executing perfectly or not by clicking the execute button on the top.

SQL Query Builder 17

Comments are closed.