Urgent help needed!! reg: nested SQL query (using Select but tricky ) 1

[sjy]

Customer-Stock-order Database:

Customer (C) (CNO, CNAME, CITY) CNO is th primary key
Order (O) (ONO, CNO, ORDER_DATE) ONO,CNO are primary keys
Order_detail (OD) (ONO, SNO, QTY) ONO,SNO are primary keys
Stock(S) ( SNO, SNAME, PRICE, SLEVEL) SNO is th primary key


1)List the names of the stock items that have been ordered by all customers.

The query looks simple but it took a lot of time to me..it is just using a couple of selects statements and nesting but finally I am ending up confused..
I have tried my best but was unable to come up with the SQL query for the above question. So could anyone please let me know the solution for this query.

Thanks for the support!!I shall be looking forward for responses..
sjy

 

[barbarul2001]

try this:

select distinct s.sname
from stocks s,order_detail od, order o
where o.ono=od.ono
and s.sno=od.sno
group by s.sname
having count(distinct o.cno)=(select count(c.cno) from customer c)

senior rdbms specialist

 

[angiole]

Hello sjy,

When you say 'by all customers', this could mean just the stock names that were ever ordered, since all orders are associated to a t least one customer.

so then ...

Customer (C) (CNO, CNAME, CITY) CNO is th primary key
Order (O) (ONO, CNO, ORDER_DATE) ONO,CNO are primary keys
Order_detail (OD) (ONO, SNO, QTY) ONO,SNO are primary keys
Stock(S) ( SNO, SNAME, PRICE, SLEVEL) SNO is th primary key

select s.sno
       s.sname
from   stock s
       order o
where  s.sno = o.sno

Expanding this, you can retrieve the count of orders and sum of quantity as follows

select s.sno
       s.sname
       count(o.ono)
       sum(o.qty)
from   stock s
       order o
where  s.sno = o.sno
group by
       s.sno
       s.sname

If your dbms allows, you may also add the following to your select to collect the distinct count of customers per stock item:

count ( distinct o.cno)

Hope this helps
Cheers AA 8~)

 

[angiole]

Hello sjy,

When you say 'by all customers', this could mean just the stock names that were ever ordered, since all orders are associated to a t least one customer.

so then ...

select s.sno
       s.sname
from   stock s
       order o
where  s.sno = o.sno

Expanding this, you can retrieve the count of orders and sum of quantity as follows

select s.sno
       s.sname
       count(o.ono)
       sum(o.qty)
from   stock s
       order o
where  s.sno = o.sno
group by
       s.sno
       s.sname

If your dbms allows, you may also add the following to your select to collect the distinct count of customers per stock item:

count ( distinct o.cno)

Hope this helps
Cheers AA 8~)