Virtual Tables, External Data, and Aggregates
Page 2 of 6

Virtual (Derived) Tables and Views

Cloudscape's flexible SQL-92 grammar allows you to construct virtual or derived tables on the fly within an SQL-J statement. You learned about the SQL-92 VALUES clause in Lesson 6, "Working with SQL-J's Java Extensions"; the VALUES clause allows you to construct a virtual table that contains columns and rows that you can treat like any other table. Another way to think about it is as a constructed result set that can be referred to within an SQL-J statement.

Work with a Virtual Table

  1. Start Cloudview as described in Start Cloudview in the Home Directory. Then open a connection to toursDB as described in Open a Connection to toursDB.
  2. Then select the toursDB icon in the left-hand hierarchy window to display the SQL window.
  3. Execute the following SQL-J statement:

    VALUES ('orange', 'orange'), ('apple', 'red'),
    ('banana', 'yellow');

    The results it returns look like a result set retrieved from a table; it returns three rows of two columns.

    SQL-J allows you to treat such a virtual table as a real table within an SQL-J statement; you can SELECT an item from it, or use it in a UNION or JOIN operation. When a virtual table is part of a SELECT statement, it is enclosed within parentheses. In such a context, it is called a table expression. You will create such table expressions in the tasks in this section.

    You typically must name your virtual table to perform such operations. This name is not permanent; it exists only within the scope of the statement.

    You name the virtual table using the AS keyword to give it a correlation name; column names are placed inside parentheses after the correlation name.

  4. Name your virtual table Fruits_And_Colors; name the first column fruit and the second column color. Select all columns (*) from this table.

    SELECT *
    FROM
    (VALUES ('orange', 'orange'), ('apple', 'red'),
    ('banana', 'yellow'))
    AS Fruits_And_Colors(fruit, color);

  5. Create another virtual table called Fruits_And_Foods, with two columns, fruit and food. Select all columns (*) from this table:

    SELECT *
    FROM
    (VALUES ('orange', 'orange juice'), ('apple', 'pie'),
    ('apple', 'muffins'),('banana', 'bread'),
    ('banana', 'meringue pie'))
    AS Fruits_And_Foods(fruit, food);

  6. Now join the two virtual tables:

    SELECT color, food
    FROM
    (VALUES ('orange', 'orange'), ('apple', 'red'),
    ('banana', 'yellow'))
    AS Fruits_And_Colors(fruit, color)
    JOIN
    (VALUES ('orange', 'orange juice'), ('apple', 'pie'),
    ('apple', 'muffins'),('banana', 'bread'),
    ('banana', 'meringue pie'))
    AS Fruits_And_Foods(fruit, food)
    USING (fruit)

Work with a View

A view is a permanent virtual table that you create by naming an SQL-J statement. The toursDB schema contains one view. Once you create a view, it is always available within the database until you drop it, so it is similar to a stored prepared statement. However, there are some differences between views and stored prepared statements, as shown in Table 1:

Table 1 Differences Between Views and Stored Prepared Statements

Feature

Views

Stored Prepared Statements

Are named by user

Yes

Yes

Are precompiled

No

Yes

Contain dynamic parameters

No

Yes

Are virtual tables and can take the place of a table in any SQL-J statement

Yes

No

Since views create virtual tables, views can be part of SELECT statements. Views help you simplify or virtualize a database schema. For example, if your application interacts with views instead of with the underlying tables, you can make changes to the underlying tables and then alter the views to shield the application from the underlying changes.

  1. Click the "+" next to the View icon in the left-hand hierarchy window to display the views within toursDB.
  2. Select the view Segments_Seatbookings.
  3. Select the View tab.

    The Query window displays the text of the statement used to create the view. In a view statement, unlike in a table expression, you state the name of the view and its columns at the beginning of the statement. The text of the actual statement follows the AS keyword.

    The view offers a simple view of the FlightBookings table.

    The FlightBookings table is a bit complex, because it consists of one row for each flight booking. In the JBMSTours application, a Flight can consist of one or two flight segments. The FlightBookings table has two extra columns to store the flight_id and segment_number of the second segment if a group's flight consists of two segments. If not, these two columns are null.

    The organization of this table works well because it presents each flight, whether one or two parts, as a unit. For example, you can tell at a glance how many flights were booked.

    However, what if you wanted to gather data about individual segments? The Segments_Seatbookings view presents the data as if the segments were booked individually. (You will use this view in the next section when you work with aggregates.)

  4. Click the Execute button.
  5. Clicking the Execute button is equivalent to executing the view's SQL-J statement:

    SELECT * FROM Segments_SeatBookings

    When you execute this statement, Cloudscape compiles the view's underlying statement and then executes it.

    You can execute any SQL-J statement against the view.

  6. Select the toursDB icon to get back to the SQL window. Then execute the following statement:

    SELECT Airline_Full, number_seats
    FROM Airlines, Segments_SeatBookings
    WHERE Airline = flight_id.substring(0,2)