Database Development in Rational Application Developer 7
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.
- Create new SQL Statement by right-clicking SQL Scripts → New → SQL Statement.This is shown in Figure 5.
- 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.
Figure 6: New SQL Statement
Once the SQL Statement file is created, you can start building your SQL Statement.
- Open the EMPLOYEE_AND_PHOTO.SQL file.
- From the database pane, select Derby Connection → SAMPLE → Schemas → SAMP → Tables (see Figure 7).
- Drag and drop the EMPLOYEE and EMP_PHOTO tables from the database explorer pane to the editor pane (see Figure 8).
- Now, you join the tables by using inner join. Right-click the EMPLOYEE Table and select Inner Join (see Figure 9).
- 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.
- Finally, you add a where clause in which you specify that you want all employees whose last name starts with A (see Figure 10).
- 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.
Figure 7: Open Table List
Figure 8: The EMPLOYEE and EMP_PHOTO tables are selected
Figure 9: Create an Inner join between EMPLOYEE and EMP_PHOTO
Figure 10: Add Where Clause
Figure 11: View Execution Results in the Data Output Pane
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.
Page 2 of 2