/*
Find jnum of projects that have shipments with the highest number
        of 'Screw' parts
*/

/*
First: find number of 'Screw' parts per project:

   select jnum, sum(qty)
   from spj X, part P
   where X.pnum = P.pnum
     and pname = "Screw"
   group by jnum

Use this query to make a sub-relation
*/

select jnum, sum(qty)
from spj X, part P
where X.pnum = P.pnum
  and pname = "Screw"
group by jnum
having sum(qty) = (select max(s)
                   from   (select sum(qty) s
                           from spj Y, part P
                           where Y.pnum = P.pnum
                             and pname = "Screw"
                           group by jnum
                          ) A
                  )



/*

select distinct proj.jnum
from proj, spj, part
where proj.jnum = spj.jnum
  and spj.pnum = part.pnum
  and color = 'Blue'
group by proj.jnum, proj.jname
having sum(qty) = (select max(s)
                   from (select sum(qty) s
                        from spj, part
                        where spj.pnum = part.pnum
                              and color = 'Blue'
                        group by spj.jnum
                        ) A
                  )
order by proj.jnum
*/
