How to Build SQL Statement Using Graphical Query Tool in SQL Server?

In SQL Server, the Graphical Query Tool can be utilised for writing SQL statements. The following instructions can guide you in a step-by-step fashion for creating a valid SQL statement:

· The database is located under the server and the server group and hence it is necessary for you to expand the server first and the server group next in the process for writing a SQL query statement.

· When you see the data Transformation services folder, right click on it and choose or click on New packages so as to get the DTS package window.

· Now from the menu choose Data from, Microsoft OLE DB provider for SQL server. For your query to run you should get connected to the server first and hence create a connection to the SQL server.

· Now you will get one connection window that will take inputs from you. Specify or enter the details of your SQL server name and after giving the correct authentication mode and database, click the Ok button.

· For creating the exact SQL query, after selecting the Task, one DTS package window will appear and note to select the Execute SQL Task from this window. After correctly selecting the execute SQL Task option, you will get a pop-up Execute SQL Properties window.

· Now from the Execute SQL properties window click on the build query button and you will get an additional window which is nothing but the data Transformation services Query Designer window. This is the place where from you can start building your SQL query statements using a graphical tool

· For selecting a table, you can either drag and drop the table from the left side frame onto the upper right side frame or drag and drop the table and from the left frame onto the upper- right frame or right click either in the upper-right frame or right click in the upper left frame , and after choosing the add table property and after selecting the table name, say Customers and Orders tables, you can choose Ok and close the Add Table window

· For adding the Grid pane to the Data Transformation services Query designer Window, right click on the upper left window and choose the show panes option.

· Now you will have 4 frames. The upper right frame is the Diagram pane, the left frame is the Table/view list, the lower right frame is the SQL pane and the middle or right frame is the Grid panel.

· From the Data Transformation services Query Designer window you will be able to hide the view or table list jut by clicking the far left toolbar button.

· Now you can drag one of the fields name, say the company name field from the customer table from the diagram pane and drop it to the Grid pane’s first row.

· Now for getting the Order ID field drag the field from the orders table that is shown in Diagram pane and drop it in the Grid pane’s second row.

· For the select Group By filter in the menu you can add alias num orders.

· Now select the Group By from the menu after right clicking in the Grid pane.

· When you see in Grid pane the order Id row, click the group by Column and change the value to count.

· Now you can select the sort option by clicking the sort Type column from the Order ID row and choose the Ascending option.

· Now you can define a criteria say that COUNT must be higher that 7. For achieving this, enter the value >7 in the criteria field after clicking it in the order ID row.

Now you have successfully built one SQL query, which is very similar to any other listing type SQL statement. You can also the see the text format of the SQL query you have just built in the SQL pane of the Data Transformation services Query designer window.

· At this stage it is also possible for you to use your SQL query statement in any of the ASP script by just copy-paste action on the SQL statement.

· Once you click on the Ok button in the Data Transformation services query designer window the job of creating a SQL query statement will be over.

The appeared SQL properties window will help in executing the SQL query and if you click Ok it will allow you to finish the query building process.

· Now you can close the DTS Designer window

When it comes to creating and executing SQL statements and queries, there are many other graphical tools that can come to your aid and if you can have the Visual Inter Dev 6.0, then you can make use of it for creating SQL scripts much similar to the DTS query Designer in SQL server 7.0