SQL Query Builder
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.
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 SQL 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 the required columns by check marking the columns.
- 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)
- SQL Pane: SQL Query designer will automatically build the SQL query for us in this pane.
Right Click on the Diagram Pane 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 example we selected the First Name, Last Name, Education, Occupation, Yearly Income, Sales Amount, Tax Amount and Orders Quantity columns from two tables
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.
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
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.
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.
For the remaining columns, We calculated the aggregation such as Sum of Yearly Income, Sales Amount, Tax Amount and counting Orders Quantity
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
Click OK button to close the SQL query builder. Below code snippet will show you the final T-SQL Query generated.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
|
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.
Thank You for Visiting Our Blog
