Database Development in Rational Application Developer 7, Page 2

Create a SQL Statement with SQL Builder

SQL Builder is a wizard/tool that allows you to create SQL Statements interactively. Please go through an exercise where you join two tables in a simple select.

1. Create new SQL Statement by right-clicking SQL Scripts → New → SQL Statement.This is shown in Figure 5.



Figure 5: Create new SQL Statement

2. When the SQL Statement Wizard opens (see Figure 6), you can choose between among SQL Statement Temples: SELECT, UPDATE, INSERT, DELETE, and FULLSELECT. You will select the "SELECT" Statement template. Also, make sure to select the "SQL Builder" checkbox and enter JOIN_EMPLOYEE_PHOTO as a statement name.



Click here for a larger image.

• Post a comment
• Email Article
• Print Article
• Share Articles
  • Digg
  • del.icio.us
  • Slashdot
  • DZone
  • Reddit
  • StumbleUpon
  • Facebook
  • FriendFeed
  • Furl
  • Newsvine
  • Google
  • LinkedIn
  • MySpace
  • Technorati
  • Twitter
  • Windows Live
  • YahooBuzz

Figure 6: New SQL Statement

Once the SQL Statement file is created, you can start building your SQL Statement.

1. Open the EMPLOYEE_AND_PHOTO.SQL file.
2. From the database pane, select Derby Connection → SAMPLE → Schemas → SAMP → Tables (see Figure 7).



Figure 7: Open Table List

3. Drag and drop the EMPLOYEE and EMP_PHOTO tables from the database explorer pane to the editor pane (see Figure 8).



Click here for a larger image.

Figure 8: The EMPLOYEE and EMP_PHOTO tables are selected

4. Now, you join the tables by using inner join. Right-click the EMPLOYEE Table and select Inner Join (see Figure 9).



Click here for a larger image.

Figure 9: Create an Inner join between EMPLOYEE and EMP_PHOTO

5. The next step is to select what columns the select statement shall return. This can be done simply by checking the checkboxes next to the field in the table.
6. Finally, you add a where clause in which you specify that you want all employees whose last name starts with A (see Figure 10).



Click here for a larger image.

Figure 10: Add Where Clause

7. The last step is to execute the statement and view the results. You can do so by right-clicking the SQL Statement and selecting "Run SQL". The results can be viewed in the Data Output pane, as shown in Figure 11.



Click here for a larger image.

Figure 11: View Execution Results in the Data Output Pane

Conclusion

In this article, you have learned how to create a database connection and use SQL Builder using RAD 7. In the next article, you will learn how to create SQL Stored Procedures and User-Defined Functions using RAD.

About the Author

Aleksey Shevchenko has been working with object-oriented languages for over eight years. For the past four years, he has served as a technical lead and a project manager. Aleksey has been implementing Enterprise IT Solutions for Wall Street and the manufacturing and publishing industries.