< Previous | Next >

Adding joins, a query condition, and a GROUP BY clause

In this exercise, you restrict the query results to the customers who are currently renting videos and to the videos that are currently being rented. You also limit the query results to a specific customer whose name you specify when you run the query. And lastly, you organize the query results by the day of the week that the rented videos are due. Within each day of the week, you order the videos by the video title, and for each video title, by customer name.

Joining tables

A join operation lets you retrieve data from two or more tables based on matching column values. The data in the tables is linked into a single result. Two joins are needed for this query. The query result needs to include the RENTALS and CUSTOMERS table entries that have matching CUST_ID column values. The query result must also include the RENTALS and VIDEOS table entries that have matching video IDs (VID_ID column values).

To join tables:

  1. In the Tables pane, right-click the header of the R table, and then click Create Join on the pop-up menu.
  2. In the Create Join window under Source, make the following selections:
    1. In the Table (alias) list, click RENTALS (R).
    2. In the Column list, click CUST_ID.
  3. Under Target, make the following selections:
    1. In the Table (alias) list, click CUSTOMERS (C).
    2. In the Column list, click CUST_ID.
  4. Click OK. A join connector appears between the two columns.
  5. In the Tables pane, drag your pointer from the VID_ID column in the R (RENTALS) table to the VID_ID column in the V (VIDEOS) table.
Look at the SQL Source pane to see the joins in the source code:
   FROM
      XMLTEST.VIDEOS AS V JOIN XMLTEST.RENTALS AS R JOIN XMLTEST.CUSTOMERS AS C
      ON R.CUST_ID = C.CUST_ID ON V.VID_ID = R.VID_ID

In the Tables pane, both joins have been created as shown here:

Table joins

You can change the join type (for example, from the default inner join to a left, right, or full outer join) in the Tables pane by right-clicking the connector, clicking Specify Join Type on the pop-up menu, and then selecting the join type that you want in the Specify Join window.

Creating a query condition

Next, the query needs a query condition so that the query extracts only result rows that have the customer name that you want. You add conditions to the query by using the Conditions page in the Design pane.

To create a query condition:

  1. Click the Conditions tab to see the Conditions page.
  2. In the first row, double-click the cell in the Column column and then click C.NAME in the list.
  3. In the same row, double-click the cell in the Operator column and then click the = operator.
  4. In that row, double-click the cell in the Value column and then enter :custName. A colon followed by a variable name is the SQL syntax for a variable that will be substituted with a value when you run the query. You will see how this works when you run the SQL query.
The Conditions page now looks like the image shown here:

Design pane - Conditions page

Adding a GROUP BY clause

You will group the query by the day of the week, then by title, and then by customer name.

To create a GROUP BY clause in the SQL builder, use the Groups page in the Design pane. In this view, you can also create more advanced groupings in your query result by using column expressions, nested groups, grouping sets (in DB2® only), and the ROLLUP and CUBE grouping functions (in Oracle and DB2).

To add a GROUP BY clause:

  1. In the Design pane, click the Groups tab.
  2. Add the DAYOFWEEK function as a result column.
    1. In the Column table, double-click the first row, click Build Expression in the list, and then press Enter.
    2. On the Expression Types page of the wizard, click Function, and then click Next. The Function Expression Builder page opens.
    3. In the Select a function category list, click Date and time.
    4. In the Select a function list, click DAYOFWEEK.
    5. In the Select a function signature list, click DAYOFWEEK(DATE) --> INTEGER. The function signature shows that the function requires one argument.
    6. In the Value column of the argument table, click the cell, click R.DATE in the list, and then press Enter.
    7. Click Finish. The DAYOFWEEK function is shown in the first cell of the Column table.
  3. In the second row of the Column table, select the V.TITLE column from the list, and then in the third row, select C.NAME. The Groups page now looks like the following image:

    Design pane - Groups page

The query is now complete. The query looks like this in the SQL builder:

Completed query

< Previous | Next >