Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 7 of 7
  1. #1
    New to the CF scene
    Join Date
    Jan 2013
    Posts
    6
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Question structured query language (sql)

    can someone please help me with this sql coding.... the diagram is given below..

    List the all products which haven’t been bought by any customer....
    (i tried this but i m not sure if this is correct)

    SELECT ProductID, CustomerID
    FROM Products , Customers
    WHERE ProductID NOT IN (SELECT CustomerID);
    Attached Thumbnails Attached Thumbnails structured query language (sql)-sql.jpg  
    Last edited by simon47; 02-13-2013 at 05:47 AM.

  • #2
    Senior Coder
    Join Date
    Dec 2005
    Location
    Slovenia
    Posts
    1,994
    Thanks
    120
    Thanked 76 Times in 76 Posts
    3.

    Code:
    SELECT p.ProductID
    FROM Products p left join Sales s on p.ProductID = s.ProductID
    WHERE s.ProductID is null
    Found a flower or bug and don't know what it is ?
    agrozoo.net galery
    if you don't spot search button at once, there is search form:
    agrozoo.net galery search

  • #3
    New to the CF scene
    Join Date
    Jan 2013
    Posts
    6
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by BubikolRamios View Post
    3.

    Code:
    SELECT p.ProductID
    FROM Products p left join Sales s on p.ProductID = s.ProductID
    WHERE s.ProductID is null
    the customer table is missing from the code section...customer table isnt mentioned anywhere in code?

  • #4
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,994
    Thanks
    4
    Thanked 2,662 Times in 2,631 Posts
    Quote Originally Posted by simon47 View Post
    the customer table is missing from the code section...customer table isnt mentioned anywhere in code?
    Why would it need to be? Your query specifies to find products that haven't been bought by customers. Since customers doesn't have any direct relationship to product, there's no need to include the customers table as anything not bought will not have a reference within the sales table.
    So all you need to find is anything within the product table that has no related record within sales. Since the product has never been sold, there is no reason to collect any Customer information from it.

    This is clearly homework as well, so he really shouldn't have given you the answer to the third one. The first two are simply aggregates and groups.

    This is also access, which is. . . not great SQL wise. MySQL code will not run properly within it for two reasons: the functions are different for anything proprietary, and Access is incredibly easy to confuse if you start joining more than two tables together.
    So this belongs in the other databases section.
    PHP Code:
    header('HTTP/1.1 420 Enhance Your Calm'); 
    Been gone for a few months, and haven't programmed in that long of a time. Meh, I'll wing it ;)

  • #5
    New to the CF scene
    Join Date
    Jan 2013
    Posts
    6
    Thanks
    1
    Thanked 0 Times in 0 Posts
    well i just wanted to learn that why the customer table is not mentioned in the code...but thanks anyways.....

  • #6
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,617
    Thanks
    80
    Thanked 4,635 Times in 4,597 Posts
    Probably the LEFT JOIN answer is beyond where he is in the class or book, anyway.

    Probably the instructor is expecting him to use a NOT IN( ) clause at this stage in the class.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #7
    Master Coder felgall's Avatar
    Join Date
    Sep 2005
    Location
    Sydney, Australia
    Posts
    6,642
    Thanks
    0
    Thanked 649 Times in 639 Posts
    Quote Originally Posted by Old Pedant View Post
    Probably the LEFT JOIN answer is beyond where he is in the class or book, anyway.

    Probably the instructor is expecting him to use a NOT IN( ) clause at this stage in the class.
    That's one of the problems with giving answers to homework - the answer will almost certainly include code that is well beyond that so far covered in the course. Of course that makes it far easier for the teacher to tell who cheated since anyone using code that is beyond where they are in class would need to be able to explain exactly how that code works in order to prove that they really are ahead of the class and if they can't then it will be obvious they cheated. Most likely the teacher already knows who is ahead anyway and so wouldn't even need to ask.
    Stephen
    Learn Modern JavaScript - http://javascriptexample.net/
    Helping others to solve their computer problem at http://www.felgall.com/

    Don't forget to start your JavaScript code with "use strict"; which makes it easier to find errors in your code.


  •  

    Posting Permissions

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