SQL 3 : Data Manipulation and Retrieval Operations

11. Get all combination of suppliers and parts info in a way that suppliers and parts are colocated

This is a very clean example of doing S natural join P but other things should be noticed. I made this example to make it clear to show a column can be called several time in no order.Even though I wrote FROM S, P THAT DOES NOT MEAN YOU HAVE TO FOLLOW THE SEQUENCE. P.PNO CAN COME BEFORE S.SNO.

12. Get the Max and Min quantity of part P1

Interesting huh? You may use AS or not does not matter. some people prefer AS just it makes easy for them to read or understand.

13. Get The part no and the total quantity for each part supplied

Above example can be thought little different way as well as follows:

14. Get city names for cities in which at least two suppliers are located.






15. Get parts no for parts which are supplied more than 2 suppliers.







16. Get suppliers numbers for supplier with status less than current maximum status in supplier table.






5. Get all pairs of supplier numbers such that the two suppliers are located in the same city




Don't be fooled with the identifiers s1 and s2. For the above example, you may also write it like this:





6








7. Find all supplier names and the total quantity supplied by them









8. Find all supplier names and the total quantity supplied by them as a desired output