Java Reference
In-Depth Information
A MySQL dump of the modiied database can be found at this topic's website:
http://www.packtpub.com/support
.
The database contains the following tables:
aircraft
,
aircraft_models
,
aircraft_types
,
aircraft_engines
, and
aircraft_engine_types
. The database structure can be seen in
the following diagram:
The FlightStats database uses the default MyISAM MySQL
engine, which does not support referential integrity
(foreign keys). That is the reason for which we don't
see any arrows in the diagram indicating dependencies
between the tables.
Let us create a report that will show the most powerful aircraft in the database, say,
those with a horsepower of 1000 or above. The report will show the aircraft tail
number, aircraft serial number, the aircraft model, and the aircraft's engine model.
The following query will give us these results:
select a.tail_num, a.aircraft_serial, am.model as aircraft_model,
ae.model as engine_model
from aircraft a, aircraft_models am, aircraft_engines ae
where a.aircraft_engine_code in ( select aircraft_engine_code
from aircraft_engines
