On Apr 15, 3:02 am, "strawberry" <zac.ca...@gmail.com> wrote:
> On Apr 15, 9:15 am, "ros" <ros...@gmail.com> wrote:
>
>
>
> > On Apr 14, 3:58 pm, "strawberry" <zac.ca...@gmail.com> wrote:
>
> > > On Apr 14, 10:05 pm, "Paul Lautman" <paul.laut...@btinternet.com>
> > > wrote:
>
> > > > ros wrote:
> > > > > I am completely clueless about the following query.
>
> > > > > - - Where does the longest flight go?
>
> > > > That depends on where it is leaving from. From where I am I think it is
> > > > Australia.
>
> > > > > This relates to 2 tables and the sql scripts are as follows:
>
> > > > > CREATE TABLE `my-database`.`flight_profiles` (
> > > > > `FLP_ID` decimal(8,0) default NULL,
> > > > > `FLT_NUMB` decimal(4,0) default NULL,
> > > > > `PRICE_CODE` varchar(50) default NULL,
> > > > > `FLT_FROM` varchar(50) default NULL,
> > > > > `FLT_TO` varchar(50) default NULL,
> > > > > `depart` datetime default NULL,
> > > > > `arrive` datetime default NULL
> > > > > ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
>
> > > > > CREATE TABLE `my-database`.`cities` (
> > > > > `IATA_CODE` varchar(3) default NULL,
> > > > > `COU_IATA_CODE` varchar(2) default NULL,
> > > > > `NAME` varchar(40) default NULL
> > > > > ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
>
> > > > > Would appreciate help with this one. Also I am just building my SQL
> > > > > and MySQL skills so any recommendations on resources like books or
> > > > > online resources would be most welcome.
>
> > > > > Thanks
>
> > > > > ros
>
> > > > The tables are very interesting, but what do you want us to help with?
>
> > > :-)
>
> > > isn't this a clue?
>
> > > `depart` datetime default NULL,
> > > `arrive` datetime default NULL
>
> > That was hilarious!
> > Actually I need help in writing this query which gives me the name of
> > the city where the longest flight go from these 2 tables.
> > Would be thankful for guidance.
> > ros
>
> Note that you may need to take account of timezones. Also I don't
> understand the relationship between the cities table and the
> flight_profiles table, so I've ignored the cities table for the
> purposes of this query:
>
> SELECT FLP_ID, FLT_FROM, FLT_TO, TIMESTAMPDIFF(
> SECOND , depart, arrive ) duration
> FROM flight_profiles
> ORDER BY duration DESC;

Thanks a lot Strawberry. That was extremely helpful.
I really appreciate that.
ros