Modeling Complex SQL Visually

Not everyone is comfortable with writing SQL, especially complex joins with group operations, HAVING clauses, and sorts. What many people want is a visual query builder that enables them to model what they want and that can then generate the SQL that's required. For this need, TOAD offers the SQL Modeler shown in Figure 9.8 and located on the main menu at Database, SQL Modeler. And though this screen may look very simple, it's one of the most feature-rich and powerful screens within TOAD. There are very few SQL coding techniques that cannot be successfully modeled here and then generated.

Figure 9.8. TOAD SQL Modeler ”basic operation.

When you first enter this screen, both the top left and bottom portions of the screen will be empty. These are the model area and Criteria/Generated Query/Results/Explain Plan tab area, respectively. Only the top right portion of the screen will contain information. This area is known as the table selector. There are splitters between all three of these areas for resizing to best suit your screen resolution and other needs or tastes.

There are just three simple steps to begin using the TOAD SQL Modeler. First, you must select a schema from the drop-down list in the table selector area, which then populates the objects from which you can select. Then you drag and drop tables, views, or synonyms from the table selector area into the model area. And finally, you check those columns in the selected objects of the model area that you want projected . While you're doing these basic steps, the Criteria tab area will begin to fill in as shown in Figure 9.8. That's all there is to it. You're now visually creating your SQL queries (that is, SELECT commands). However, there is a lot more you can do with this screen than these basic steps.

The SQL Modeler has numerous options that warrant discussion before proceeding with describing how this screen works. Unlike many other TOAD screens where the options can be discussed last as they do not radically change screen interaction, the options for the SQL Modeler must be discussed first because they do in fact significantly change the way you work with the screen. If you open a context menu in the model area as shown in Figure 9.9, you will see numerous options. You can specify on the Show submenu what level of information will be displayed in the model area for a selected object. In the example, Join Text, Primary Keys, and Indexes have been selected. Note that if you choose too many display options, the objects in the model area become much bigger and things very quickly become cramped.

Figure 9.9. TOAD SQL Modeler ”model area options.

Two other options worth noting from this menu are Copy Model Image to Clipboard and Full Screen Mode [F2]. Some people will use the model image with data type, primary keys, and indexes selected as sort of a poor man's ERD (entity relationship diagram). And toggling back and forth between full-screen modes is often necessary when working with more than a few objects.

Note that there are many options within TOAD related to using the SQL Modeler screen. The TOAD Options screen can be opened either by using the Toolbox icon on the main toolbar or from the main menu at View, Options. The basic SQL Modeler options shown in Figure 9.10 fall into one of three categories. First, there are options for defining fonts and colors for text and relationship lines. Second, there are options for defining the functions available for WHERE clauses and calculated fields (covered in the following paragraphs). And third, there are options that control the behavior of objects newly selected into the model area:

Figure 9.10. TOAD basic options for SQL Modeler.

• Automatic Autojoin is a relationship line automatically drawn between related objects.

• Use Schema Name in Generated SQL is the generated SQL format schema.object.column or object.column .

• Automatically Select All Columns selects all the columns of the selected objects already checked.

However, it's the less obvious options shown in Figure 9.11 that are most problematic . Probably the most frequently asked technical support question is: How do I get views and synonyms to show up in the table selector for the SQL Modeler? The answer is simply to check both Show Views on Table Selector and Show Synonyms on Table Selector in the TOAD Options for Editors (the default for both is unchecked). So few people find this on their own, that beginning with TOAD 7.4 the defaults have been changed to those options being checked. So this issue will fade as time goes on.

Figure 9.11. TOAD less obvious options for SQL Modeler.

With all your SQL Modeler options properly set, you can now effectively work on visually constructing and generating your SQL queries. Look back at the model area in Figure 9.8; the tables CUSTOMER and MOVIERENTAL were selected ”the column CUSTOMERID joins them. In addition, the columns FIRSTNAME, LASTNAME, RENTALDATE, and TOTALCHARGE were selected for projection. Thus the field grid in the Criteria tab area has four columns, one for each of the selected table columns. But this in itself would do nothing more than generate a simple SELECT column FROM table command. It's now time to begin filling in the details that will result in more useful and meaningful queries. There are a lot of steps to go through, but that's because the SELECT command has so many options and the SQL Modeler addresses most of them.

The goal is to build a query using the selections in Figure 9.8 that finds all customers who have rentals in the past five years that totaled at least $2.00 and to display their total charges and the average number of days they kept a movie. Sounds pretty easy in English, but the steps to model and the code generated will most likely surprise you. That's OK, because TOAD's SQL Modeler can handle this and much more complex needs.

The first few steps are quite easy and involve merely making some very minor Criteria tab field grid selections. You'll want the resulting data sorted on output, so you'll need to define the sort options. You merely double-click in the column's Sort cell as shown in Figure 9.12 and select either No Sort, Ascending, or Descending. You can even define multiple sort fields as shown in Figure 9.13: ASC(1) and ASC(2). These last two screens have in effect added a multicolumn ORDER BY clause to the generated SQL.

Figure 9.12. TOAD SQL Modeler ”defining sorts.

Figure 9.13. TOAD SQL Modeler ” multiple sorts.

Next you need to specify that the TOTALCHARGE column should sum all of the total charges. For this, you merely double-click in the column's Aggregate F. cell as shown in Figure 9.14 and select the appropriate group function ”in this case SUM . This will result in a SELECT SUM(TOTALCHARGE) in the generated code.

Figure 9.14. TOAD SQL Modeler ” group function.

Then you need to define the restriction that only movie rentals for the past five years are included. Note that there are two issues here. First, you don't need to project this column, just restrict on it. So you can mark it as non-displayed by double-clicking in the Visible cell for the column to toggle from Show to Not Shown, as shown in Figure 9.15.

Figure 9.15. TOAD SQL Modeler ”not shown.

Second, you need to define the restriction (that is, the WHERE clause) by simply double-clicking in the Condition cell for the desired column. This will launch the WHERE Definition screen shown in Figure 9.16. Here you simply define the restriction operator and its value or values. You can also specify a subquery from another SQL Modeler query (that is, a .DML file) or switch to expert mode as shown in Figure 9.17. Note that after you switch to expert mode for a particular field, you cannot switch back.

Figure 9.16. TOAD SQL Modeler ”simple restriction.

Figure 9.17. TOAD SQL Modeler ”expert restriction.

Now comes a complex question: How do you display the days rented when there is no such column? Neither CUSTOMER nor MOVIERENTAL has a DAYS_RENTED column. But look again at MOVIERENTAL; it does have columns for RENTALDATE and DUEDATE. If you assume that the DUEDATE minus the RENTALDATE is the number of days rented, you can calculate this value. TOAD's SQL Modeler offers the Calculate Fields toolbar button (the sigma symbol) for just this scenario. You click this button to launch the Calculated Fields screen as shown in Figure 9.18.

Figure 9.18. TOAD SQL Modeler ” calculated fields.

You supply a calculated field name and press the + key, which launches the Calculated Field Definition screen as shown in Figure 9.19. Here you define the calculation expression to use. This is essentially the same screen as the WHERE Definition screen back in Figure 9.17 (minus the outer-join option).

Figure 9.19. TOAD SQL Modeler ”define calculation.

Then after the expression is defined, you must finally associate the calculated field with a table as shown in Figure 9.20. This is where the calculated field will show up back in the model area as shown in Figure 9.21. See how DAYS_RENTED is now part of the MOVIERENTAL table? Finally, note that DAYS_RENTED also has its Aggregate F. value set to AVG because this was part of the original request. This was done manually after defining the calculated field.

Figure 9.20. TOAD SQL Modeler ” associate calculation.

Figure 9.21. TOAD SQL Modeler ”calculated field's home.

All that's left now is to define the grouping information and you're ready to go. There's just one simple golden rule to remember ” all displayed fields that do not have aggregate functions must be grouped. So in Figure 9.22, the fields for FIRSTNAME and LASTNAME have been indicated as grouped. This was accomplished by merely double-clicking in their Group cells to toggle from ungrouped to grouped.

Figure 9.22. TOAD SQL Modeler ”grouped columns.

But wait, there was one more key requirement ”only for total charges whose sum was at least $2.00. The idea is to eliminate people with one-time rentals for less than two bucks. You cannot accomplish this with a WHERE clause because it must be restricted based upon the results of the aggregate or group operation. Therefore you must click the toolbar button for Global Having Condition editor (the H button) and launch the screen shown in Figure 9.23.

Figure 9.23. TOAD SQL Modeler ”global HAVING conditions.

Here you simply press the + key to create a new HAVING condition, and it launches you into the Global Having Definition screen shown in Figure 9.24. As with calculated fields, this is essentially the same screen as the WHERE Definition screen back in Figure 9.17 (minus the outer-join option). With that, you've now fully modeled the requested query.

Figure 9.24. TOAD SQL Modeler ”Global HAVING expression.

The Generated Query tab will now contain the generated SQL as shown in Figure 9.25. For many people, writing a query such as this would have been beyond their skill set. Yet with the aid of a screen like the SQL Modeler, just about anyone can easily write effective and efficient SQL queries against even the most complex database design. And although you can run the queries here directly as shown in Figure 9.26, you can just as easily send the generated SQL to the TOAD SQL Editor by using the View SQL in SQL Window button on the SQL Modeler toolbar. Now you have all of TOAD's SQL editing, tuning, execution, save as and printing options (refer back to Chapter 3). So even very experienced SQL developers could use the SQL Modeler to initiate the construction of their SQL code.

Figure 9.25. TOAD SQL Modeler ”generated SQL.

Figure 9.26. TOAD SQL Modeler ”query results.