Given following tables, how can I g 1

[slok]

Given following tables, how can I get the customers who have made an order with overall value of 500.00 in the last 30 days.


===
customer

Name Null? Type constraints
------------------------------- -------- -------- ------------
CUSTNUMB NOT NULL NUMBER(3) primary key
CUSTNAME CHAR(15)
CUSTADDR NOT NULL CHAR(25)
BALANCE NOT NULL NUMBER(7,2)
CREDLIM NOT NULL NUMBER(7,2) great than 0.0

orders

Name Null? Type constraints
------------------------------- -------- ------ -------------

ORDNUM NOT NULL NUMBER(4) primary key
ORDDATE NOT NULL DATE
CUSTNUM NOT NULL NUMBER(3) foreign key, referencing customer
PRODNUM NOT NULL NUMBER(3) foreign key, referencing product
QUANTITY NOT NULL NUMBER(5)

product

Name Null? Type constraints
------------------------------- -------- ------ -------------

PRODNUM NOT NULL NUMBER(3) primary key
PRODDES VARCHAR2(50)
PRICE NOT NULL NUMBER(5,2) non-negative

===

 

[SirWaldizmo]

Try this:
--
select
CUSTNUMB
,CUSTNAME
,CUSTADDR
,OVERALL_VALUE
from CUSTOMER
,(select
CUSTNUM
,SUM(QUANTITY * PRICE) OVERALL_VALUE
from ORDERS, PRODUCT
where ORDERS.PRODNUM = PRODUCT.PRODNUM
and SYSDATE - ORDDATE <= 30
having SUM(QUANTITY * PRICE) >= 500
group by CUSTNUM
) A
where A.CUSTNUM = CUSTOMER.CUSTNUMB;