db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Stan Bradbury (JIRA)" <derby-...@db.apache.org>
Subject [jira] Created: (DERBY-1319) Need documentation on the toursdb schema.
Date Thu, 11 May 2006 20:00:05 GMT
Need documentation on the toursdb schema.

         Key: DERBY-1319
         URL: http://issues.apache.org/jira/browse/DERBY-1319
     Project: Derby
        Type: Sub-task

  Components: Demos/Scripts  
    Reporter: Stan Bradbury
    Priority: Minor

Docuement the toursdb schema and provide examplet queries so toursdb can be used by newbie
Derby users.  To jump start this here is a query that demonstrates how to join the tables
in the schema to list the with seats available on a given day from one city to another.  It's
not pretty - a good candidate for a view. (sans the hardcoded values listed below:  

This example hardcodes the following string values:
       Travel Date:  '2004-04-12' ,  Departing city: Copenhagen , Destination city: Paris

select  f.flight_id, a.airline_full, 
fa.flight_date|| ' ' || f.depart_time Departing,
    CAST(orig_city.city_name ||', ' || orig_ctry.country as varchar(30)) as Origin, 
   f.flying_time Hours, f.arrive_time Arriving,
   CAST(dest_city.city_name ||', ' || dest_ctry.country as varchar(30))  as Destination
from flights f, airlines a, countries orig_ctry, cities orig_city, 
   countries dest_ctry, cities dest_city, flightavailability fa
where f.flight_id = fa.flight_id and f.segment_number = fa.segment_number
  and orig_city.airport = f.orig_airport and dest_city.airport = f.dest_airport
  and orig_city.country_iso_code = orig_ctry.country_iso_code
  and dest_city.country_iso_code = dest_ctry.country_iso_code
  and f.flight_id like a.airline || '%'
   and fa.flight_date = '2004-04-12' 
  and (a.business_seats - fa.business_seats_taken) > 0
  and orig_city.city_name = 'Copenhagen' and dest_city.city_name = 'Paris'
order by f.flight_id, f.segment_number

NOTE: uses some kludgy formatting tricks

To cut N paste  the query all at once into IJ set maximumdisplaywidth larger (about 985 characters
in the query)

This message is automatically generated by JIRA.
If you think it was sent incorrectly contact one of the administrators:
For more information on JIRA, see:

View raw message