#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2012
    Posts
    75
    Rep Power
    3

    Use of complicated queries


    I am currently undergoing an Intro to Database course.
    My book gives a lot examples of complicated queries. I don't understand the use of that. Since structured query language is limited (it doesn't have for loops, while loops etc.), so sometimes it is hard to do some stuff using it.

    For e.g.:- There is one question where I have to find the second smallest value of some field under some constraint.

    Using a mixture of SQL and programming language it's easy. Get all values under the constraint, sort it and give the second value. However using only SQL (specifically my book uses MySQL), it is slightly tougher.

    So my question is - what's the point of teaching complicated queries? Does it make it faster? Any benefits?
  2. #2
  3. Wiser? Not exactly.
    Devshed God 1st Plane (5500 - 5999 posts)

    Join Date
    May 2001
    Location
    Bonita Springs, FL
    Posts
    5,959
    Rep Power
    4035
    Some databases do include control structures for looping or creating conditions in their SQL dialect. These are used typically within stored procedures.

    However, you can also do quite a bit with just regular queries without having to use these structures. For your example, you'd just do something like this:
    Code:
    SELECT theField
    FROM theTable
    WHERE
        theCondition='whatever'
    ORDER BY
        theField ASC
    LIMIT 1,1
    That will grab all the rows matching the WHERE condition given, sort them by theField in ascending order, then return only the second row of that list.

    One issue with that is if you had a duplicate the smallest value then you would just get the second occurrence of that value rather than the second-smallest value. That could be resolved using either DISTINCT or GROUP BY to combine similar values.
    Recycle your old CD's, don't just trash them



    If I helped you out, show some love with some reputation, or tip with Bitcoins to 1N645HfYf63UbcvxajLKiSKpYHAq2Zxud
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2012
    Posts
    75
    Rep Power
    3
    That was just an example. Howere there are more complicated question which require nested queries and are really hard.
    They are trivial using loops and programming language features but using only SQL it's challenging.

    My question is - Is there any use of avoiding programming language and getting your job done using only SQL? Any performance benefits?
  6. #4
  7. Code Monkey V. 0.9
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Mar 2005
    Location
    A Land Down Under
    Posts
    2,136
    Rep Power
    1990
    Originally Posted by Avichal
    My question is - Is there any use of avoiding programming language and getting your job done using only SQL? Any performance benefits?
    Yes there is. BUT, the query that you use depends completely on the problem that you're trying to solve. There's no generic "it will" or "it won't" answer to should they be used. The answer to your actual question is that they are teaching that because you'll need to know it later on, and when you understand it you'll get why it's important.
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2012
    Posts
    75
    Rep Power
    3
    Originally Posted by Catacaustic
    Yes there is. BUT, the query that you use depends completely on the problem that you're trying to solve. There's no generic "it will" or "it won't" answer to should they be used. The answer to your actual question is that they are teaching that because you'll need to know it later on, and when you understand it you'll get why it's important.
    Any example as to why it would be helpful?
  10. #6
  11. Code Monkey V. 0.9
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Mar 2005
    Location
    A Land Down Under
    Posts
    2,136
    Rep Power
    1990
    Originally Posted by Avichal
    Any example as to why it would be helpful?
    Because under normal circumstances a database query engine will be faster at processing the data then it is getting the same data through a query and the program execution for whatever filtering, sorting, etc that can be done. The biggest example that I know of is using PHP and MySQL. PHP runs slow. MySQL runs fast. Passing as much work as possible off the the MySQL server makes everything run faster.
  12. #7
  13. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2012
    Posts
    75
    Rep Power
    3
    Originally Posted by Catacaustic
    Because under normal circumstances a database query engine will be faster at processing the data then it is getting the same data through a query and the program execution for whatever filtering, sorting, etc that can be done. The biggest example that I know of is using PHP and MySQL. PHP runs slow. MySQL runs fast. Passing as much work as possible off the the MySQL server makes everything run faster.
    Why should MySQL engine be faster than PHP? Is it because MySQL is specifically made for that purpose so it has some internal optimization while PHP is more general and hence slower?
    Anyways thank you! I think I got it now.
  14. #8
  15. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,959
    Rep Power
    1014
    PHP isn't equipped for data processing at all. If you think that some naive loops are as good as a MySQL query, then you don't understand the sophistication behind database systems.

    Try sorting 1,000,000 rows with PHP. Your script will probably run for 30 seconds and then crash due to a timeout or exceeding of the memory limit. Then do the same thing in MySQL with an indexed column. You'll get the result in a matter of milliseconds.

    The same goes for lookups, aggregating data, associating multiple tables etc. It's not possible to do this even halfway efficiently with PHP, because PHP is simply not made for this.

    I understand that SQL feels unfamiliar to you and that you may be tempted to circumvent it and abuse "good old PHP" as a kind of makeshift database system. But that's almost always the worst thing you can do.

    Long story short: If you're dealing with more data than the dates of birth of your family members, then you need an actual database system. It doesn't have to be MySQL or even SQL, though.
    The 6 worst sins of security ē How to (properly) access a MySQL database with PHP

    Why canít I use certain words like "drop" as part of my Security Question answers?
    There are certain words used by hackers to try to gain access to systems and manipulate data; therefore, the following words are restricted: "select," "delete," "update," "insert," "drop" and "null".

IMN logo majestic logo threadwatch logo seochat tools logo