I can retrieve all data with a simple "select * from flights;"
|Table Structure & Data|
Now let's explore this data and learn some SQL query language at the same time.
How many Aircraft and Flights each day?Query:
SELECT date, count(distinct hexident), count(distinct sign) FROM flights GROUP BY date;
|Unique/Distinct Aircraft and Flights|
Which Aircraft are racking up most flights?Query:
Select distinct hexident,count(distinct sign) from flights
group by hexident order by count(distinct sign);
|Number of flights per Aircraft HexID|
Who is running these busy planes?Apparently it's German Wings (GWI):
select distinct sign, hexident from flights
where hexident like '3C5EF%' order by hexident;
|German Wings keeps its planes busy...|
The fastest/slowest/highest/lowest each day?Let's focus on the the highes/lowest/fastest/slowest flights with
Select date, count(*), count(distinct hexident), count(distinct sign),
max(alti), min(alti), max(speed), min(speed) from flights group by date;
And I can look for the specifics:
- Flights below 0 feet altitude? Just a few blips from nearby Airbus plant Finkenwerder...pilots are either diving in the river or have not set proper altitude...
- Flights above 44.000 feet altitude? AOJ87D a Swiss Jet? , FPG111 - a Dassault Falcon? , MMD6424 - another Dassault Falcon? It must be nice to look down from your own private jet.
- Flights slower than 100 (knots?) - Who is CHX26? Looks like I tracked a helicopter?
- Flights faster than 600 (knots?) - Looks like aircraft passing by (Air India, Air Canada...)
The furthest away?
Let's look for the flights that are furthest away... with this query:
select date, count(*), min(lat), max(lat), min(lon), max(lon) from flights group by date;
I could do some math (difference in Lat/Long to my position), but I am trying to keep it simple.
What interesting SQL queries can you think of?
If you have some ideas let me know...you can also have access to the mySQL data and experiment yourself...