Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2011
    Posts
    62

    Unanswered: SQL Homework Solution Feedback

    Hi all!

    So I've just hit the SQL section of my web programming class, after having blown through HTML/CSS/JavaScript/PHP, and I've become painfully aware of how amateur I am with SQL.

    I'll present a problem, my solution (correct and sufficient for the assignment, but my queries are pedestrian), and I'd like to seek your criticism and suggestions for doing the same thing, but in smarter/more advanced ways. I know the way I'm doing it is terribly amateur, and I believe there are better ways but so far in reading/referencing, things I've tried haven't worked.

    Here's a basic example of what I'm doing right now:

    1. We're using this site, it provides a database with stuff in it and a box you can stuff select queries into: Active Learning Lab

    2. Here's an example question I have to answer: "How many orders did Margaret Peacock sell?" The answer is 156. (I only have to provide the answer, how I get the answer doesn't actually matter for the assignment - but I just want don't want to do it the dumb way.)

    3. I can get the answer by issuing the following 2 queries:
    //Look in the employees table to return the row for peacock
    //This tells me her employee id is 4
    SELECT * FROM EMPLOYEES WHERE LASTNAME='PEACOCK' AND FIRSTNAME='MARGARET'
    //count how many entries there are in the orders table with that employee id
    SELECT COUNT(*) FROM ORDERS WHERE EMPLOYEEID='4'

    Now, I'd like to look at how someone who knows what they are doing would answer that same question, so I can reference the syntax and commands to better understand and apply it.

    Your input is appreciated. I'm not looking for you to do my homework and I don't need answers - I'd just like to see better ways to do the same thing. I feel like I could probably do a single query with a count and an inner join on the two tables, however I haven't got the syntax right or I'm doing something else wrong.
    Last edited by I.M.O.G.; 02-21-14 at 23:06.

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by I.M.O.G. View Post
    I feel like I could probably do a single query with a count and an inner join on the two tables, however I haven't got the syntax right or I'm doing something else wrong.
    Greetings! it is great to see you back again!

    Your "feeling" would be perfectly correct! If you check the JOIN syntax from the MySQL manual, you'll find a mix of good and bad examples.

    The problem with the example quality in the manual is forced by the way that MySQL implements JOIN operations... In MySQL the JOIN and the CROSS JOIN and the INNER JOIN are all the same thing but in standard SQL they are not.

    Because the MySQL documentation shows at least as many incorrect answers as correct answers (and all of them will work correctly in MySQL!) I'll break one of my own cardinal rules and give you this answer... Since you could follow the documentation and come up with more wrong answers than correct ones (and all of them would work!!!), there is no point in making you beat yourself bloody!
    Code:
    SELECT Count(*) AS PEACOCK_MARGARET_ORDERS
       FROM EMPLOYEES
       INNER JOIN ORDERS
          ON (ORDER.EMPLOYEEID = EMPLOYEE.EMPLOYEEID)
       WHERE  EMPLOYEE.LASTNAME='PEACOCK'
          AND EMPLOYEE.FIRSTNAME='MARGARET';
    Once again, it is GREAT to see you back at DBForums!

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Sep 2011
    Posts
    62
    Awesome, thanks Pat, that's helpful!

    Here's another that another friend showed me as well. Though after reading your post, I'm not sure how to tell if its correct and it works or it's wrong and it works! lol That seems like a rabbit hole which may be best left a mystery.

    Code:
    SELECT COUNT(*) FROM orders, employees
       WHERE orders.employeeid=employees.employeeid AND employees.lastname='PEACOCK' AND employees.firstname='MARGARET';
    Any feedback on the relative strength/weakness of one solution over the other and why? I figure I should begin to recognize this on my own with further practice, as I can when analyzing familiar code for what is efficient and what's doing it the roundabout/hard way, but at this point in my journey I'm only recognizing "works" or "dudn't work".
    Last edited by I.M.O.G.; 02-22-14 at 00:55.

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    The example that your friend supplied (using a comma delimited list of tables and views) is the SQL-89 syntax.

    The example that I provided uses the current join syntax (with the join conditions in the ON clause), which I'm pretty sure was first introduced in SQL-92. This syntax avoids some logical problems that can occur when mixing inner and outer join operations that can cause ambiguous results.

    Both statements should work, and should return the same results. I prefer the newer syntax because it is unambiguous.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

Posting Permissions

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