SQL Query Builder 15

SQL Query Builder

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

SQL Query Builder Example

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

Please Choose the Database in the Management Studio 1

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

Choose Design Query in Editor option from context menu 2

Once you click on the option, it will open a separate window, as shown below. Before designing anything, you should understand the different panes present in the SQL Query builder or designer.

  1. Diagram Pane: This pane selects the tables, views, or functions. The GUI 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 these panes. This section is handy for applying sorting (ORDER BY ASC and ORDER BY DESC) and also grouping the column names (GROUPING Statement)
  3. Query Pane: The SQL designer will automatically create statements for us in this pane.
SQL Query Designer Window 3

Right-click on the SQL query designer Diagram Pane and choose to Add Table.. option to add the needed table to the builder.

Add a Table 4

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

Select the Required Table Name 5

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

Choose Table Name in SQL Query Builder 6

As you can see, SQL Query Builder or designer automatically Joined the two tables, DimCustomers and FactInternetSales, using the CustomerKey column.

shows Tables with Relationships 7

Please select the required columns by check-marking the column names in the Diagram Pane.

In this SQL query builder or designer example, we chose the First Name, Last Name, Education, Occupation, Yearly Income, Sales Amount, Tax Amount, and Orders Quantity columns from two tables.

Choose Required Column Names in SQL Query Builder 8

SQL Query Designer Alias Example

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

Alias Names 9

Sorting Example

Sort Type property in Grid allows applying for ORDER BY functionality. Please change the Sort Type within the Grid Pane to Ascending or Descending.

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

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

Sort Type options 11

SQL Query Builder or Designer Grouping

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

Add Group By 12

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

Group By and Aggregate Functions 14

Filters inside the Query Designer

Within the query builder or designer, we add >1000 filters on Sales Amount. Since we are applying a filter condition on aggregated data Sum(Sales Amount), it is using the HAVING Clause. Otherwise, it will use the WHERE Clause.

Apply Filters or Where Clause 15

Click the OK button to close the window. The below code snippet will show you the final Transact statement 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 builder is executing perfectly or not by clicking the execute button on the top.

SQL Query Builder 17

Comments are closed.