how to use subqueries

Hi,

I am working on this query; I am trying to practice subqueries for
MySQL. Am also working on the book Visual Quick Start Guide to SQL.
But I didn't find much help for this query there.

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

I tried but it is wrong and I got confused because this query deals
with 3 tables; Would be thankful if you guys could help me with this
query. My try is pasted below:

select f.flt_numb, f.flt_to
from flight_profiles f
where f.flt_to in
(select a.cty_iata_code
from airports a, cities c
where a.cty_iata_code = c.iata_code);

The tables are as follows:

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`.`airports` (
`IATA_CODE` varchar(3) default NULL,
`NAME` varchar(30) default NULL,
`CTY_IATA_CODE` varchar(3) 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 22, 7:29 am, ros <ros...@gmail.com> wrote:
> Hi,
>
> I am working on this query; I am trying to practice subqueries for
> MySQL. Am also working on the book Visual Quick Start Guide to SQL.
> But I didn't find much help for this query there.
>
> "return a list of all flight numbers, their intended destination and
> other airports in the same city that they could land at if required"
>
> I tried but it is wrong and I got confused because this query deals
> with 3 tables; Would be thankful if you guys could help me with this
> query. My try is pasted below:
>
> select f.flt_numb, f.flt_to
> from flight_profiles f
> where f.flt_to in
> (select a.cty_iata_code
> from airports a, cities c
> where a.cty_iata_code = c.iata_code);
>
> The tables are as follows:
>
> 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`.`airports` (
> `IATA_CODE` varchar(3) default NULL,
> `NAME` varchar(30) default NULL,
> `CTY_IATA_CODE` varchar(3) 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

Ros,

I'll try to keep my answer appropriately "high-level" ;-)
There's no need to use a subquery for this problem - however, I
appreciate that you want to practice using them.

So, at the very least, you must ensure that you remember to name all
derived tables:

SELECT a.* FROM
(SELECT * FROM my_table)a;

Then just try to break the problem down into its constituent parts:

1.return a list of all flight numbers and their destination airport
codenames

2.for each result, return the codename of the associated city

3.return a list of all airports in the same city

Unfortunately, it's difficult to see exactly how the tables relate to
each other. FLT_TO is VARCHAR(50) and airport IATA_CODE is VARCHAR(3)
so it seems odd that these might contain the same information. I could
make a guess, but without seeing a sample from each table, it's
difficult to help further.

Good luck

On 22 Apr, 07:29, ros <ros...@gmail.com> wrote:
> Hi,
>
> I am working on this query; I am trying to practice subqueries for
> MySQL. Am also working on the book Visual Quick Start Guide to SQL.
> But I didn't find much help for this query there.
>
> "return a list of all flight numbers, their intended destination and
> other airports in the same city that they could land at if required"
>
> I tried but it is wrong and I got confused because this query deals
> with 3 tables; Would be thankful if you guys could help me with this
> query. My try is pasted below:
>
> select f.flt_numb, f.flt_to
> from flight_profiles f
> where f.flt_to in
> (select a.cty_iata_code
> from airports a, cities c
> where a.cty_iata_code = c.iata_code);
>
> The tables are as follows:
>
> 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`.`airports` (
> `IATA_CODE` varchar(3) default NULL,
> `NAME` varchar(30) default NULL,
> `CTY_IATA_CODE` varchar(3) 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

I would strngly suggest that you spend time practising joins and lef
joins. Understand these and you have a far better toolbox t your
disposal than a subquery, and many many times more efficient too.

On Apr 22, 2:52 am, strawberry <zac.ca...@gmail.com> wrote:
> On Apr 22, 7:29 am, ros <ros...@gmail.com> wrote:
>
>
>
> > Hi,
>
> > I am working on this query; I am trying to practice subqueries for
> > MySQL. Am also working on the book Visual Quick Start Guide to SQL.
> > But I didn't find much help for this query there.
>
> > "return a list of all flight numbers, their intended destination and
> > other airports in the same city that they could land at if required"
>
> > I tried but it is wrong and I got confused because this query deals
> > with 3 tables; Would be thankful if you guys could help me with this
> > query. My try is pasted below:
>
> > select f.flt_numb, f.flt_to
> > from flight_profiles f
> > where f.flt_to in
> > (select a.cty_iata_code
> > from airports a, cities c
> > where a.cty_iata_code = c.iata_code);
>
> > The tables are as follows:
>
> > 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`.`airports` (
> > `IATA_CODE` varchar(3) default NULL,
> > `NAME` varchar(30) default NULL,
> > `CTY_IATA_CODE` varchar(3) 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
>
> Ros,
>
> I'll try to keep my answer appropriately "high-level" ;-)
> There's no need to use a subquery for this problem - however, I
> appreciate that you want to practice using them.
>
> So, at the very least, you must ensure that you remember to name all
> derived tables:
>
> SELECT a.* FROM
> (SELECT * FROM my_table)a;
>
> Then just try to break the problem down into its constituent parts:
>
> 1.return a list of all flight numbers and their destination airport
> codenames
>
> 2.for each result, return the codename of the associated city
>
> 3.return a list of all airports in the same city
>
> Unfortunately, it's difficult to see exactly how the tables relate to
> each other. FLT_TO is VARCHAR(50) and airport IATA_CODE is VARCHAR(3)
> so it seems odd that these might contain the same information. I could
> make a guess, but without seeing a sample from each table, it's
> difficult to help further.
>
> Good luck

Thanks for the reply Strawberry.

Really appreciate you took time to answer my question. I am trying to
practice Subqueries etc; actually I am doing all this stuff on my own.
I don't go to college or any institution; I am learning on my own.

So when I get stuck I dont have any other kind of help except forums
like this one.

And both the FLT_TO and IATA_CODE are VARCHAR(3) actually. I just used
50 by mistake. I have corrected it.

I am still studying the previous queries but now there is another
query and I would be thankful if you could help me with this one.

This uses 2 tables and my try is pasted below; I don't understand how
to use count, max functions here.

"return the name of the airport with the most flights departing from
it. Only return one row and one column of data."

select a.name
from flight_profiles f, airports a
where f.flt_from = a.iata_code
and f.flt_from
order by f.flt_from DESC
LIMIT 1;

CREATE TABLE `my-database`.`airports` (
`IATA_CODE` varchar(3) default NULL,
`NAME` varchar(30) default NULL,
`CTY_IATA_CODE` varchar(3) 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(3) default NULL,
`FLT_FROM` varchar(3) default NULL,
`FLT_TO` varchar(50) default NULL,
`depart` datetime default NULL,
`arrive` datetime default NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Thanks in advance.
ros

On Apr 23, 1:42 am, Captain Paralytic <paul_laut...@yahoo.com> wrote:
> On 22 Apr, 07:29, ros <ros...@gmail.com> wrote:
>
>
>
> > Hi,
>
> > I am working on this query; I am trying to practice subqueries for
> > MySQL. Am also working on the book Visual Quick Start Guide to SQL.
> > But I didn't find much help for this query there.
>
> > "return a list of all flight numbers, their intended destination and
> > other airports in the same city that they could land at if required"
>
> > I tried but it is wrong and I got confused because this query deals
> > with 3 tables; Would be thankful if you guys could help me with this
> > query. My try is pasted below:
>
> > select f.flt_numb, f.flt_to
> > from flight_profiles f
> > where f.flt_to in
> > (select a.cty_iata_code
> > from airports a, cities c
> > where a.cty_iata_code = c.iata_code);
>
> > The tables are as follows:
>
> > 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`.`airports` (
> > `IATA_CODE` varchar(3) default NULL,
> > `NAME` varchar(30) default NULL,
> > `CTY_IATA_CODE` varchar(3) 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
>
> I would strngly suggest that you spend time practising joins and lef
> joins. Understand these and you have a far better toolbox t your
> disposal than a subquery, and many many times more efficient too.

Thanks for the suggestion Captain. I just want to get familiar with
Subqueries for now but shall keep your advice in mind and try to focus
on Joins in the long run.

Thanks
Much appreciated.
ros

On Apr 24, 10:50 am, ros <ros...@gmail.com> wrote:
> On Apr 23, 1:42 am, Captain Paralytic <paul_laut...@yahoo.com> wrote:
>
>
>
> > On 22 Apr, 07:29, ros <ros...@gmail.com> wrote:
>
> > > Hi,
>
> > > I am working on this query; I am trying to practice subqueries for
> > > MySQL. Am also working on the book Visual Quick Start Guide to SQL.
> > > But I didn't find much help for this query there.
>
> > > "return a list of all flight numbers, their intended destination and
> > > other airports in the same city that they could land at if required"
>
> > > I tried but it is wrong and I got confused because this query deals
> > > with 3 tables; Would be thankful if you guys could help me with this
> > > query. My try is pasted below:
>
> > > select f.flt_numb, f.flt_to
> > > from flight_profiles f
> > > where f.flt_to in
> > > (select a.cty_iata_code
> > > from airports a, cities c
> > > where a.cty_iata_code = c.iata_code);
>
> > > The tables are as follows:
>
> > > 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`.`airports` (
> > > `IATA_CODE` varchar(3) default NULL,
> > > `NAME` varchar(30) default NULL,
> > > `CTY_IATA_CODE` varchar(3) 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
>
> > I would strngly suggest that you spend time practising joins and lef
> > joins. Understand these and you have a far better toolbox t your
> > disposal than a subquery, and many many times more efficient too.
>
> Thanks for the suggestion Captain. I just want to get familiar with
> Subqueries for now but shall keep your advice in mind and try to focus
> on Joins in the long run.
>
> Thanks
> Much appreciated.
> ros

I strongly recommend the manual for much of this. The tutorial,
example queries and functions lists - as well as user comments - are a
great way to get to grips with these aspects of mysql.

On Apr 25, 3:43 am, strawberry <zac.ca...@gmail.com> wrote:
> On Apr 24, 10:50 am, ros <ros...@gmail.com> wrote:
>
>
>
> > On Apr 23, 1:42 am, Captain Paralytic <paul_laut...@yahoo.com> wrote:
>
> > > On 22 Apr, 07:29, ros <ros...@gmail.com> wrote:
>
> > > > Hi,
>
> > > > I am working on this query; I am trying to practice subqueries for
> > > > MySQL. Am also working on the book Visual Quick Start Guide to SQL.
> > > > But I didn't find much help for this query there.
>
> > > > "return a list of all flight numbers, their intended destination and
> > > > other airports in the same city that they could land at if required"
>
> > > > I tried but it is wrong and I got confused because this query deals
> > > > with 3 tables; Would be thankful if you guys could help me with this
> > > > query. My try is pasted below:
>
> > > > select f.flt_numb, f.flt_to
> > > > from flight_profiles f
> > > > where f.flt_to in
> > > > (select a.cty_iata_code
> > > > from airports a, cities c
> > > > where a.cty_iata_code = c.iata_code);
>
> > > > The tables are as follows:
>
> > > > 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`.`airports` (
> > > > `IATA_CODE` varchar(3) default NULL,
> > > > `NAME` varchar(30) default NULL,
> > > > `CTY_IATA_CODE` varchar(3) 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
>
> > > I would strngly suggest that you spend time practising joins and lef
> > > joins. Understand these and you have a far better toolbox t your
> > > disposal than a subquery, and many many times more efficient too.
>
> > Thanks for the suggestion Captain. I just want to get familiar with
> > Subqueries for now but shall keep your advice in mind and try to focus
> > on Joins in the long run.
>
> > Thanks
> > Much appreciated.
> > ros
>
> I strongly recommend the manual for much of this. The tutorial,
> example queries and functions lists - as well as user comments - are a
> great way to get to grips with these aspects of mysql.

Thanks a lot for the advice Strawberry. Much appreciated.
Ros