Professional Web Applications Themes

how to use subqueries - MySQL

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 ...

  1. #1

    Default 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 CHT=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 CHT=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 CHT=latin1;

    Thanks
    Ros

    ros Guest

  2. #2

    Default Re: how to use subqueries

    On Apr 22, 7:29 am, ros <com> wrote: 

    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

    strawberry Guest

  3. #3

    Default Re: how to use subqueries

    On 22 Apr, 07:29, ros <com> wrote: 

    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.

    Captain Guest

  4. #4

    Default Re: how to use subqueries

    On Apr 22, 2:52 am, strawberry <com> wrote: 









    >
    > 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[/ref]

    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 CHT=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 CHT=latin1;

    Thanks in advance.
    ros

    ros Guest

  5. #5

    Default Re: how to use subqueries

    On Apr 23, 1:42 am, Captain Paralytic <com> wrote: 









    >
    > 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.[/ref]

    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

    ros Guest

  6. #6

    Default Re: how to use subqueries

    On Apr 24, 10:50 am, ros <com> wrote: 
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]

    >
    > 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[/ref]

    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.

    strawberry Guest

  7. #7

    Default Re: how to use subqueries

    On Apr 25, 3:43 am, strawberry <com> wrote: 
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]


    >
    > 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.[/ref]

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

    ros Guest

Similar Threads

  1. query that uses subqueries
    By ros in forum MySQL
    Replies: 5
    Last Post: April 19th, 02:06 PM
  2. Using COALESCE for subqueries
    By marty3d in forum MySQL
    Replies: 1
    Last Post: June 15th, 09:02 PM
  3. Joins or subqueries
    By alexford in forum Coldfusion Database Access
    Replies: 1
    Last Post: April 26th, 02:31 AM
  4. subqueries in query of queries
    By helenmhudson in forum Coldfusion Database Access
    Replies: 2
    Last Post: March 7th, 04:25 PM
  5. DBI and subqueries in mysql 4.1
    By Marc Lambrichs in forum PERL Modules
    Replies: 2
    Last Post: December 13th, 08:03 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139