Sql Statements for tables.

[baberz]

ID NAME ADDRESS BALANCE
1 Brooks 1 Main Street 9.50
2 Smith 2 Main Street -30.00
3 Brown 3 Main Street 0.00
MEMBERS


ORDER_NO ID ITEM QUANTITY
1 1 Vegetable Oil 2
2 1 Flour 1
3 2 Cauliflower 10
4 2 Steak 2
5 2 Lettuce 2
6 2 Flour 1
ORDERS


SNAME ITEM PRICE
A Produce Vegetable Oil 3.99
A Produce Cauliflower 1.00
A Produce Lettuce 0.75
B Supply Steak 2.99
C Inc Flour 1.50
C Inc Vegetable Oil 4.00
SUPPLIERS

Given the above relations, how do i write the following queries using SQL
PLEASE IN SOLVING THESE, NOT SURE. HOW TO TACKLE.

5. Get the pair of supplier names such that the two suppliers supply a common item.
6. List all the items ordered by those members with a negative balance.
7. List all the members who have not placed any orders.
8. List all the suppliers who supply at least one item that has not yet been ordered by anyone.
9. Get the suppliers that supply every item ordered by Brooks.
10. Get the names of the members who have ordered all the things that have been ordered by Brooks.

 

[baberz]

ID NAME ADDRESS BALANCE
1 Brooks 1 Main Street 9.50
2 Smith 2 Main Street -30.00
3 Brown 3 Main Street 0.00
MEMBERS


ORDER_NO ID ITEM QUANTITY
1 1 Vegetable Oil 2
2 1 Flour 1
3 2 Cauliflower 10
4 2 Steak 2
5 2 Lettuce 2
6 2 Flour 1
ORDERS


SNAME ITEM PRICE
A Produce Vegetable Oil 3.99
A Produce Cauliflower 1.00
A Produce Lettuce 0.75
B Supply Steak 2.99
C Inc Flour 1.50
C Inc Vegetable Oil 4.00
SUPPLIERS


Get the pair of supplier names such that the two suppliers supply a common item.
List all the items ordered by those members with a negative balance.
List all the members who have not placed any orders.
List all the suppliers who supply at least one item that has not yet been ordered by anyone.
Get the suppliers that supply every item ordered by Brooks.
Get the names of the members who have ordered all the things that have been ordered by Brooks.
Can anyone help me solve these. Thanks.

 

[r937]

this sure looks like a school assignment

therefore, i'll tell you how to do some of them to get you started, without giving you the sql

5. Get the pair of supplier names such that the two suppliers supply a common item.

subquery to group on items, having count(*)>1, outer query for all suppliers of items found by subquery

6. List all the items ordered by those members with a negative balance.

subquery to select members with balance<0, outer query for all items in order table for members found in subquery

7. List all the members who have not placed any orders.

not exists subquery

8. List all the suppliers who supply at least one item that has not yet been ordered by anyone.

left outer join from suppliers to orders, selecting unmatched rows

9. Get the suppliers that supply every item ordered by Brooks.

oh, this is a real toughie

10. Get the names of the members who have ordered all the things that have been ordered by Brooks

whoa, this is even tougher



rudy