11 Aug 2000

Due:  18 Aug 2000
				Assignment 3
                                ~~~~~~~~~~~~
You are given tables with following schema :

Supplier ( sno, sname, city )
Part ( pno, pname, colour, weight, city )
Project ( jno, jname, city )
SPJ( sno, pno, jno, qty )

(This time these tables are already created.  The script  to create the
data is in the file createSPJ.sql).
You have to write and execute the following queries on them :

Note : Access the table as dbis.<table_name>
	e.g. dbis.supplier, dbis.part etc.

1. For every supplier, find supplier number and total number of parts supplied by that supplier.

2. Get part number and the maximum and minimum quantity for every part supplied.

3. Find the supplier names supplying more than one part.

4. For each part supplied, get the part number and total quantity.

5. Get part numbers of parts supplied to some project in an average quantity of
   more than 20.

6. For all red & blue parts such that the total quantity supplied is greater 
   than 100, get the part number, weight, colour. 

7. For every city, give city name and total weight in grams of the parts manufactured in that city. 
   Note : Assume the weight stored in the Parts table is in kgs.

8. For every part supplied to a project, find project no, part no and total quantity of that part supplied to that project.

9. Get part numbers for all parts supplied by more than one supplier.

10. Find supplier numbers of all the suppliers who supply at least one part
   supplied by supplier 'S1'.

11. Find supplier numbers of the suppliers who do not supply any part
   supplied by supplier 'S1'.

12. Find supplier no, part no, project no such that the quantity of parts 
    supplied by the supplier to the project is minimum of all quantities.