Calling all SQLers... EDIT *STILL* Need Help!!!

[rendicil]

Ok... Given:

Suppliers(SN, sname, status, city)
Parts(PN, pname, color, weight, city)
Projects(JN, jname, city)
Shipments(SN, PN, JN, qty)

(Keys are in Caps)

Quesitons:
Get Supplier numbers for suppliers supplying some project with part 'p2' in a quantity greater than the average shipment quantity of part 'p2' for that project.

I have an answer for this one but I think it's wrong:
select SN
from shipments s
where s.PN='p2'
and s.qty > (select AVG(s.qty)
from shipments s1, projects p,
where s1.jn=p.jn)

I also need answers to three other questions and I have no idea how to do them....

Get supplier numbers for suppliers who supply the same part to all projects

and

Get project numbers for projects supplied entierly by supplier 's1'

and

Get the total Quantity of part 'p1' supplied by supplier 's1'
(I think the answer for this one is:
select qty
from shipments
where pn=p1 and
sn=s1

Please help!

 

[isasir]

You go to Cooper Union (I went to NYU). Figure it out youself.

 

[rendicil]

NYU is a pox on the village (No offense)... I just need answers to compare with my own... I will be posting my solutions soon.

 

[thereds]

select SN
from shipments s
where s.PN='p2'
and s.qty > (select AVG(s.qty)
from shipments s1, projects p,
where s1.jn=p.jn)

How is PN = 'p2'

p2, I'm assuming, is a part name and not a part # as you mention.

 

[thereds]

Get the total Quantity of part 'p1' supplied by supplier 's1'

Select Supplier.sname, Parts.pname, SUM(Shipments.qty)
From Shipments, Parts, Supplier
Where Shipments.PN = Parts.PN
And Shipments.SN = Supplier.SN
And Parts.pname = 'p1'
And Supplier.sname = 's1'
Group By Parts.pname, Supplier.sname

 

[rendicil]

I think I need to change the subquery to:

select AVG(s1.qty)
from shipments s1
where s1.jn=s.jn


Can I even do this?

 

[thereds]

Originally posted by: rendicil
I think I need to change the subquery to:

select AVG(s1.qty)
from shipments s1
where s1.jn=s.jn


Can I even do this?

Yes, you can. It may not be right in this case though. I haven't looked at it.

Read my above earlier post for an answer to one of your questions.

 

[rendicil]

Thanks for that answer... i checked and I can do it in this case because I've already delcared the table for use and I'm not doing a join or a group by so it can compare case by case.

So two questions to go

 

[rendicil]

This the answer to:

Get supplier numbers for suppliers who supply the same part to all projects

select s.SN
from suppliers s
where not exists (select p.pn
from parts, p
where not exists (select s1.*
from shipments, s1
where s1.SN=s.SN and
p.PN=s1.PN)
group by s.SN

Come one people only one more question to go...

Get project numbers for projects supplied entierly by supplier 's1'

 

[RayH]

Get project numbers for projects supplied entierly by supplier 's1'

select p.JN
from Projects p,
(select s1.JN, s1.SN, count(*) cjsn
from Shipments s1
where s1.SN='s1'
group by s1.JN, s1.SN) js,
(select s2.JN, count(*) cjn
from Shipments s2
where s2.SN='s1'
group by s2.JN) j
where js.cjsn = j.cjn
and p.JN=js.JN
and p.JN=j.JN
group by p.JN

 

[rendicil]

What are these cjsn's and js's and stuff?

 

[RayH]

Originally posted by: rendicil
What are these cjsn's and js's and stuff?

cjsn and cj are aliases for the count(*)'s preceeding them; some databases might require an 'as' statement, ex. count(*) as cjsn

js and j are aliases for the derived tables (the select statements in the quotes) preceeding them; derived tables are like subqueries but are used like tables in the 'from' clause