0

I have two tables:

create table product
(
product_id number primary key,
item_name varchar2(128 byte) not null,
pricenumber not null,
available_from date not null
);

create table orders
(
order_id number primary key,
product_id number not null,
amount number not null,
order_price number not null,
date_dispatched date not null,
foreign key (product_id) references product(product_id)
);

I need a query to find all products that have less than 20 orders in the last year, excluding products that have been available for less than 30 days.

Here is what I suggest. Any comments and corrections are appreciated.

First solution with EXISTS:
Select product_id from products 
where available_from > ADD_MONTHS(systimestamp,-1)
AND NOT EXISTS (
Select product_id
from orders 
where dispatch_date > ADD_MONTHS(systimestamp,-12)
group by product_id 
having count(*) > 10
)

Second solution with JOIN:
Select p.product_id, count(*)
from products p left outer join orders o on p.product_id = o.product_id 
where p.available_from < ADD_MONTHS(systimestamp,-1)
and o.dispatch_date < ADD_MONTHS(systimestamp,-12)
group by p.product_id
having count(*) <  10
8
  • 1
    OK. I assume this is a homework question. What have you tried so far? What problem(s) did you encounter? Feb 19 '16 at 14:49
  • So, which bit are you stuck on? Feb 19 '16 at 14:49
  • basically I don't have a server to test it...
    – Nickpick
    Feb 19 '16 at 14:49
  • See here: sqlfiddle.com for a testing environment Feb 19 '16 at 14:51
  • 1
    livesql.oracle.com is another option. So is apex.oracle.com. Or you could download the database software from Oracle and install it on your local machine. Or download a pre-built VM from Oracle. Feb 19 '16 at 15:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.