[ http://issues.apache.org/jira/browse/DERBY-1319?page=comments#action_12418764 ]
Andrew McIntyre commented on DERBY-1319: ---------------------------------------- Since we added the toursdb example database with 10.2, this is a good candidate for FixIn 10.2, though not critical. > 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 > Versions: 10.2.0.0 > 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: http://issues.apache.org/jira/secure/Administrators.jspa - For more information on JIRA, see: http://www.atlassian.com/software/jira
