The SQL Query Builder makes the developer life easy to design the SQL Query. If you don’t know how to write the Queries or if you find it difficult to perform grouping, sorting and join operations or if you find it difficult to design the complex Queries then you can use this SQL Query builder to design your desired queries using Graphical User Interface.
SQL Query Builder Example
Before we start using this easy SQL query builder, We have to select the Database as shown below.
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.
Once you click on the Design Query in Editor… option it will open the Query Designer in separate window. Before designing the query you should understand the different panes present in the SQL Query builder.
- 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 required columns by checkmark those columns.
- Grid Pane: All the selected columns will be displayed in this panes. This Sql query designer pane is very useful to apply sorting (ORDER BY ASC and ORDER BY DESC) and also grouping the column names (GROUPING Statement)
- SQL Pane: SQL Query designer will automatically build the SQL query for us in this pane.
Right Click on the Diagram Pane present in Sql query designer, and select Add Table.. option to add the required table.
From the below screenshot you can observe that, we selected the DimCustomers table.
Let us add one more table (FactInternetSales) as well to show the SQL Joins.
From the below screenshot you can observe that, SQL Query builder automatically Joined the two tables DimCustomers and FactInternetSales using the CustomerKey column.
Please select the required columns by check marking the column names in the Diagram Pane.
In this Sql query builder 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 Alias Example
The Alias column in Grid Pane is used to apply SQL Alias functionality. Within the SQL Query designer Grid Pane, Please change the Column names as per you requirement.
SQL Query Builder Sorting Example
Sort Type property in Grid is used to apply SQL ORDER BY functionality. Within the Grid Pane of SQL Query designer, 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 using Sql Server query builder.
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 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
From the below screenshot you can observe that, We performed Grouping on First Name, Last Name, Education and Occupation using SQL 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 Filters
We are adding >1000 filter on Sales Amount. Since we are applying filter condition on aggregated data Sum(Sales Amount), Sql Server Query builder is using HAVING Clause otherwise, WHERE Clause will be used
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 an easy SQL query builder is executing perfectly or not by clicking the execute button on the top.
Thank You for Visiting Our Blog