query that uses subqueries

Hi,

I have to write the following query using SUBQUERIES. Can anybody
please help with this. Shall be grateful.

"Write a query that returns a list of all flight numbers, their
intended destination and other airports in the same destination city
that the flight could land at if required."

Am pasting the tables below:

CREATE TABLE `my-database`.`airports` (
`IATA_CODE` varchar(3) default NULL,
`NAME` varchar(30) default NULL,
`CTY_IATA_CODE` varchar(3) default NULL,
`UTC_OFFSET` decimal(2,0) default NULL,
`ICAO_CODE` varchar(4) default NULL,
`ROUTE_SWARE_APT_NME` varchar(100) 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;

CREATE TABLE `my-database`.`flight_profiles` (
`FLP_ID` decimal(8,0) default NULL,
`FLT_NUMB` decimal(4,0) default NULL,
`SEATS` int(11) 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;

Thanks
ros

On Apr 18, 10:14 pm, ros <ros...@gmail.com> wrote:
> Hi,
>
> I have to write the following query using SUBQUERIES. Can anybody
> please help with this. Shall be grateful.
>
> "Write a query that returns a list of all flight numbers, their
> intended destination and other airports in the same destination city
> that the flight could land at if required."
>
> Am pasting the tables below:
>
> CREATE TABLE `my-database`.`airports` (
> `IATA_CODE` varchar(3) default NULL,
> `NAME` varchar(30) default NULL,
> `CTY_IATA_CODE` varchar(3) default NULL,
> `UTC_OFFSET` decimal(2,0) default NULL,
> `ICAO_CODE` varchar(4) default NULL,
> `ROUTE_SWARE_APT_NME` varchar(100) 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;
>
> CREATE TABLE `my-database`.`flight_profiles` (
> `FLP_ID` decimal(8,0) default NULL,
> `FLT_NUMB` decimal(4,0) default NULL,
> `SEATS` int(11) 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;
>
> Thanks
> ros


If YOU have to do it, why are YOU asking US?

On Apr 18, 2:18 pm, strawberry <zac.ca...@gmail.com> wrote:
> On Apr 18, 10:14 pm, ros <ros...@gmail.com> wrote:
>
>
>
> > Hi,
>
> > I have to write the following query using SUBQUERIES. Can anybody
> > please help with this. Shall be grateful.
>
> > "Write a query that returns a list of all flight numbers, their
> > intended destination and other airports in the same destination city
> > that the flight could land at if required."
>
> > Am pasting the tables below:
>
> > CREATE TABLE `my-database`.`airports` (
> > `IATA_CODE` varchar(3) default NULL,
> > `NAME` varchar(30) default NULL,
> > `CTY_IATA_CODE` varchar(3) default NULL,
> > `UTC_OFFSET` decimal(2,0) default NULL,
> > `ICAO_CODE` varchar(4) default NULL,
> > `ROUTE_SWARE_APT_NME` varchar(100) 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;
>
> > CREATE TABLE `my-database`.`flight_profiles` (
> > `FLP_ID` decimal(8,0) default NULL,
> > `FLT_NUMB` decimal(4,0) default NULL,
> > `SEATS` int(11) 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;
>
> > Thanks
> > ros
>
> If YOU have to do it, why are YOU asking US?

O I am sorry, I just wanted a high-level description so that I could
get started. I am new to programming, databases etc. I am trying this
book Visual Quickstart guide but if I have a high-level understanding
then I could start and then seek help her on a more specific issue.
Thanks
ros

ros schrieb:
> O I am sorry, I just wanted a high-level description so that I could
> get started.

I'll try.

In a SELECT statement, wherever you'd write a field, you can use another
query in parentheses.
For the gory details, Google for "subquery site:dev.mysql.com" and you
shall find.

Hope that's the level you're aiming at...

Regards,
Jo

On Apr 19, 12:47 am, Joachim Durchholz <j...@durchholz.org> wrote:
> ros schrieb:
>
> > O I am sorry, I just wanted a high-level description so that I could
> > get started.
>
> I'll try.
>
> In a SELECT statement, wherever you'd write a field, you can use another
> query in parentheses.
> For the gory details, Google for "subquery site:dev.mysql.com" and you
> shall find.
>
> Hope that's the level you're aiming at...
>
> Regards,
> Jo

Thanks for the advice Joachim. I found a tutorial by "google-ing" and
am going through that. I'll try this query in a bit and then post for
all you guys' advice.

Ros