Objects and SQL
Page 4 of 5

Introducing . . . SQL!

In Lesson 3, "Using Cloudview", you got a rude introduction to SQL and learned about the INSERT command. This section is a proper, more polite introduction to the language used to interact with the database management software.

You use SQL to:

  1. retrieve data from a database, to store new data, and to modify existing data

    These commands are sometimes called data modification language, or DML. They include queries, inserts, updates, and deletes.

  2. to define or modify dictionary objects such as tables, views, and indexes

    These commands are sometimes called data definition language, or DDL

Queries

An SQL statement that retrieves data is called a query; the basic keyword in a query is the word SELECT. SQL grammar allows you to specify which tables and, within tables, which columns you want to retrieve data from.

Select Data from the Sayings Table

In Lesson 3, "Using Cloudview", you used Cloudview to look at data in the Sayings and Responses tables.

In this task, you will execute an SQL-J statement to query the Sayings and Responses tables.

  1. Open a connection to the HelloWorldDB if you have not already done so (follow the instructions in Lesson 3, "Using Cloudview").
  2. Click the HelloWorldDB icon in the left-hand hierarchy window.

    The database screen appears in the right-hand window.

  3. Type or paste the following SQL-J statement into the SQL window:

    SELECT * FROM Sayings

    In SQL, an asterisk (*) stands for "all columns." Selecting * from a table selects the data in all the columns.

  4. Click the lightning bolt to execute.

    The results appear in the bottom window.

  5. Edit the SQL-J statement as follows--replace * with saying:

    SELECT saying FROM Sayings

    Specifying a column name or names allows you to retrieve data from specific columns only.

  6. Click the lightning bolt to execute.

    The results appear in the bottom window.

    Quiz: Would the following statements cause an error?

    SeLECt SAYING froM SAYINGS

    SELECT Saying FROM Sayings

    Answer: No, because command words are case-insensitive. Database object identifiers are usually not case-sensitive either.

WHERE Clauses

You can also specify which rows to retrieve by constructing a search argument. A search argument consists of a clause to the SQL statement beginning with the keyword WHERE and is called a WHERE clause. A WHERE clause specifies conditions that the values stored in rows must meet in order to be retrieved.

Execute an SQL-J Statement with a WHERE Clause

Look for the saying whose NUMBER value is 2.

  1. Type or paste in the following SQL-J statement:

    SELECT * FROM Sayings WHERE number = 2

  2. Click the lightning bolt to execute.

    The results appear in the bottom window. Only the row for which the NUMBER value is 2 appears in the window.

Joins

A join is a query in which you select data from related tables. One way to specify how the tables are related to one another is to use the JOIN . . . USING commands. JOIN means to join the two tables specified; USING specifies the column to use in joining the tables.

The two tables in the HelloWorldDB are related to each other by the NUMBER column (as you know, because you already created a foreign-key constraint on that column in the Responses table).

Execute Joins

Look for all sayings and their corresponding responses.

  1. Type or paste in the following SQL-J statement:

    SELECT SAYING, RESPONSE FROM Sayings JOIN Responses
    USING (NUMBER)

    This statement joins the two tables based on the NUMBER column, which has the same name in both tables. The value of the NUMBER column is what links a saying to its corresponding responses.

  2. Click the lightning bolt to execute.

    The results appear in the bottom window. Each saying appears with its related responses. A saying appears once for each response it has. Since in this database each saying has two responses, each saying appears twice in the result set, even though a particular saying appears only once in the Sayings table. Only those sayings with responses are shown.

  3. Resize the Saying and Response columns as needed to view the entire text.

    You can combine search arguments with a join specification. Edit the current SQL-J statement so that it returns only those rows for which the value in the NUMBER column is 3.

  4. Add the following WHERE clause to the statement:

    WHERE Sayings.NUMBER = 3

    When specifying a column in a WHERE clause for which the name is ambiguous, you must specify the table name along with the column name. Specifying the NUMBER column is ambiguous, because there is a NUMBER column in both Sayings and Responses.

  5. Click the lightning bolt to execute.

    You can also execute a join in a statement without the JOIN keyword. Instead, you use the WHERE clause to specify which columns must be related.

  6. Execute the following statement:

    SELECT SAYING, RESPONSE FROM Sayings, Responses
    WHERE SAYINGS.NUMBER = RESPONSES.NUMBER

    You should get the same results as in the first query in this section.

NOTE: In the current release, Cloudscape can often execute such a statement faster than one using the JOIN keyword.

Execute Methods in SQL-J Statements

In Executing Methods from the Object Inspector, you executed methods on objects stored in the database using the Object Inspector window. You can also execute methods within a SQL-J statement.

Even though there are no Java data types in the Sayings or Responses tables, we can execute Java methods on them because VARCHAR values are automatically mapped to the java.lang.String class. SMALLINT values are automatically mapped to the java.lang.Short class. You can execute the methods of those classes on the values stored in the Sayings and Responses tables.

To execute a method on an object stored in a column, you add the method call with any parameters to the column name. A dot separates the column name from the method call. Cloudscape executes the methods once for each value returned in the result set for the particular column.

In this task, you will get all the sayings and convert them to uppercase.

NOTE: Remember that you delimit strings within SQL-J statements with single quotation marks.

  1. Type or paste in the following SQL-J statement in the SQL window and then execute it:

    SELECT SAYING.toUpperCase() FROM Sayings

    Since toUpperCase is a Java method, its name is case-sensitive and must be typed exactly as it appears in the class definition.

    Results appear in the bottom window.

  2. Execute the following weird SQL-J statement in the SQL window:

    SELECT NUMBER.toString().concat(' hello') FROM Sayings

    Results appear in the bottom window.

    Quiz: In the query in number 2 above, the toString() method is defined in what class?

    Answer: It is defined in the java.lang.Short class. SMALLINT values automatically map to java.lang.Short.