DB2 9.5 and IBM Data Studio: Building an SQL Statement - Page 2
February 5, 2008
IBM Data Studio supports drag-and-drop operations from the Data Definition view to the SQL Builder. (Simply right-click and drag the table you want to add to the Tables window, and release the mouse button to move the table to this region.)
Drag-and-drop support allows you to circumvent using the Add Table option outlined in this step. This gives you a table selection process that provides a more natural query-building experience (although when you use this method you cant directly define a table alias):
6. Right-click a blank area where the tables are shown in the SQL Builder, and select Create Join to create a join between the two tables using the join conditions shown below:
You can also use the drag-and-drop method to create a join directly from this part of the Query Builder too. Simply hover over the desired join column on one table and release the mouse button on the join column on the target table, as shown below:
7. Click the check boxes that correspond to the columns that you want to include in the result set for this SQL statement from both tables. For this example, select the following columns: DEPARTMENT.DEPTNAME, DEPARTMENT.LOCATION, EMPLOYEE.EMPNO, EMPLOYEE.FIRSTNME, EMPLOYEE.LASTNAME, and EMPLOYEE.PHONENO, as shown below:
Notice how the SQL statement that you are building in the SQL Builder continues to evolve as you perform different operations such as adding or removing tables, projecting or restricting columns, specifying join predicates, and so on.
You can also use the Columns tab at the bottom of the SQL Builder to add columns to your SQL statement from there, simply click an empty field in the Column column, and select the column that you want to add, as shown below. (Repeat this process for all the columns that you want to add to your SQL statement if this is the manner in which you want to specify including columns for your query.)
8. Using the Columns tab, apply any additional sorting order using the Sort Type and Sort Order columns by clicking them and selecting the corresponding options, as shown below:
In the previous figure, you can see that the output of this SQL statement will be sorted first by LASTNAME in descending order from Z->A (a personal bias of mine), and second by FIRSTNAME (in the same descending order, as indicated in the Sort Type column). The sort order gives a hierarchical order to the different sorting definitions for your SQL statement.
9. Apply a WHERE predicate to your SQL statement by selecting the Conditions tab and building the condition by clicking each respective column, as shown below:
For this example, use the condition shown in the previous figure. To enter a value in the Value field, simply click the field and type the value instead of building the expression.
Note: Dont forget that for the EMPLOYEE table, the SEX column is a CHAR-based column; therefore, the value you specify in the Value column should be delimited with single quotes and case matters.
10. Apply any grouping options that you want to add to your SQL statement using the Groups and Group Conditions tabs at the bottom of the SQL Builder.
For this example, you can skip this step, but its good to know its there if the SQL statement you are building requires it.
11. Press Ctrl+S to save your query. Your query should now look similar to this:
12. To run the query, select it from the SQL Scripts folder, right-click, and select Run SQL. The output of this action and the associated result set are shown in the Data Output tab at the bottom of the IBM Data Studio IDE:
Once you have your SQL statement, you can right-click to see a number of other actions that you can perform on it (aside from just running it to see if it returns the results you are expecting).
For example, through Visual Explain you can quickly generate an access plan graph of the SQL statement you just created:
Ill actually take you through many of these options in future installments of this series.
Wrapping it up...
In this article, I showed you how to use the IBM Data Studio SQL Builder to build an SQL statement that can be subsequently used for many purposes. In the next part of this series, Im going to show you some really cool features that are part of the SQL Builder such as formatting, syntax colorization, content assist, content tips, and a whole lot more! Having a sound understanding of these capabilities can really boost a developers productivity.
IBM and DB2 are trademarks or registered trademarks of International Business Machines Corporation in the United States, other countries, or both.
Other company, product, or service names may be trademarks or service marks of others.
Copyright International Business Machines Corporation, 2008. All rights reserved.
The opinions, solutions, and advice in this article are from the authors experiences and are not intended to represent official communication from IBM or an endorsement of any products listed within. Neither the author nor IBM is liable for any of the contents in this article. The accuracy of the information in this article is based on the authors knowledge at the time of writing.