structured query language (sql)

[simon47]

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);

[BubikolRamios]

3.

SELECT p.ProductID
FROM Products p left join Sales s on p.ProductID = s.ProductID
WHERE s.ProductID is null

[simon47]

Originally Posted by BubikolRamios

3.

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?

[Fou-Lu]

Originally Posted by simon47

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.

[simon47]

well i just wanted to learn that why the customer table is not mentioned in the code...but thanks anyways.....

[Old Pedant]

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.

[felgall]

Originally Posted by Old Pedant

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.