I have an SQL exam wednesday
and I'm making some exercises in order to prepare myself.
But I don't totally understand the one that's below. :s
The correct solution was already given to me.
But it isn't very clear for me. I've added my intrepretation of what the query does, in comment.
Especially: where su1.name ='Baumgarten R.'
and not exists(
looks odd to me.
If someone understands it more than I do, please feel free to comment.
Thanks in advance.
Select the names of the suppliers who have at least as much offers as 'Baumgarten R.'(name of a supplier) does.
Using the following db scheme:
specid : a unique id for a specie
specie : name of the specie
artcode : a unique code
name : name of the plant
specid : specie
sucode : a unique supplier code
name: name of the supplier
sucode : code of the supplier
artcode : code of the plant
price : price of the offer
[plants] * -> 1 [specie]
[offers] * -> 1 [plants]
[suppliers] 1 -> * [offers]
select name from suppliers su
where not exists ( /* select suppliers
who < not not > have offers
for the same species as baumgarten
>> so only the suppliers who have at least as much offers for species where
baumgarten also has an offer for..
select * from suppliers su1
inner join offers o1 on su1.sucode= o1.sucode
inner join plants p1 on o1.artcode = p1.artcode
where su1.name ='Baumgarten R.'
and not exists( /* suppliers name must be equal to 'baumgarten' */
/* and only select offers that don't go with 'baumgarten'*/
select * from offers o2
inner join plants p2 on o2.artcode = p2.artcode
where su.sucode=o2.sucode and /* all offers that */
p2.specid = p1.specid )) /* belong to <x> and where the specie appears */
/* with baumgarten */
and name <> 'Baumgarten R.'