[top]
[prev]
[next]

Road Map
Glossary
Table of Contents
Index
Documentation Top
Global Index
|
Querying toursDB
The toursDB database is a richer database than HelloWorldDB for learning more complex SQL-J.
Return to your open toursDB connection by selecting the toursDB icon.
Execute Queries in toursDB
Show all the data in the Countries table.
- Select the toursDB database icon in the left-hand window so that you can execute SQL statements against that database.
- Type or paste in the following simple query in the SQL window:
SELECT * FROM Countries
Table and column names in toursDB are not case-sensitive, because they were not created as delimited identifiers by Cloudview. That means that you can refer to the table and column names using mixed upper- and lowercase, as long as you do not delimit them with double quotation marks.
- Click the lightning bolt to execute.
The results appear in the bottom right-hand window.
Use a WHERE Clause
Find those flights that originate from JFK, the New York City airport.
- Type or paste in the following query:
SELECT * FROM Flights WHERE orig_airport = 'JFK'
Data inside strings is case-sensitive.
- Click the lightning bolt to execute.
Results appear in the bottom window.
Use More Complex Search Conditions
You can add clauses to a WHERE clause using the keyword AND. Find flights that originate from JFK and that are longer than five hours:
- Try the following query:
SELECT * FROM Flights WHERE orig_airport = 'JFK' AND flying_time > 5
- Narrow the search even further to find only those flights over 4000 miles:
SELECT * FROM Flights WHERE orig_airport = 'JFK' AND flying_time > 5 AND miles > 4000
Join the Countries and Cities Tables
Find out which cities are in which countries, using a join.
- Try the following query:
SELECT Country, City FROM Countries JOIN Cities ON Countries.country_ISO_code = Cities.country_ISO_code
- Click the lightning bolt to execute.
Results appear in the bottom window.
This kind of join is called an inner join. For each table, it returns only rows for which there is joined data in the other table. For example, countries that have no corresponding cities do not appear in the result set.
A country has one row for each city, so some countries appear in more than one row.
A join that includes rows that have no corresponding values in the joined table is called an outer join.
To see all countries, including those that have no corresponding cities, you need to modify this query to make it a left outer join. A left outer join means that the left (or first) table in the query is the one for which the extra rows are preserved.
- Try the following query:
SELECT Country, City FROM Countries LEFT OUTER JOIN Cities ON Countries.country_ISO_code = Cities.country_ISO_code
- Click the lightning bolt to execute.
Results appear in the bottom window. Scroll through the results if you like. Notice that some countries, such as Armenia and Angola, do not have any cities associated with them in this database, so the city column is blank (null) for their rows.
Disconnect, Exit (and Shut Down Cloudscape)
- Disconnect from the database by choosing Close from the Edit menu on the toursDB and HelloWorldDB icons.
- Shut down Cloudview by exiting the application.
Shutting down Cloudview issues a shutdown command to Cloudscape in an embedded environment. (In a client/server environment, it does not shut down Cloudscape.)
|