203

-1-
Show all merchandiser id and merchandiser name that join after 15 June 2019 and have total cost more than 1000 Bath.
.
SELECT m.merchandiser_id, m.merchandiser_name, m.join_date, r.total_cost
FROM merchandiser m
JOIN receipt r
ON m.merchandiser_id = r.merchandiser_id
WHERE m.join_date > '15-JUN-2019'
AND r.total_cost > 1000;


-2-
Show all data of promotion that are now available order by end date with descending order.
.
SELECT p.promotion_id, p.promotion_name, p.detail, p.start_date, p.end_date
FROM promotion p
WHERE SYSDATE >= p.start_date
AND SYSDATE <= p.end_date
ORDER BY p.end_date DESC;


-3-
show data of merchandiser owner whose age is under 25 years-old or live in Bangkok and be a man.
.
SELECT mo.*, EXTRACT(YEAR FROM SYSDATE) - EXTRACT(YEAR FROM mo.date_of_birth) AS AGE
FROM merchandiserowner mo
JOIN merchandiserowneraddress moa
	ON moa.merchandiser_owner_id=mo.merchandiser_owner_id
WHERE moa.province='Bangkok'
    AND mo.gender='M'
    AND EXTRACT(YEAR FROM SYSDATE) - EXTRACT(YEAR FROM mo.date_of_birth) < 40
ORDER BY mo.merchandiser_owner_id;


-4-
show all data of receipt product service which price is more than the avarage of all receipt product service price and be a product.
.
SELECT * FROM receiptproductservice r
WHERE price >
    (SELECT AVG(price) FROM receiptproductservice
        WHERE r.receipt_id = receipt_id
        GROUP BY receipt_id
        )
INTERSECT
(SELECT * FROM receiptproductservice r
WHERE r.product_service_type = 'product');


-5-
show data of all merchandiseowner that has exactly same last name and the differerenet in their age are not more than 5 years.
.
SELECT * FROM merchandiserowner mo1
LEFT OUTER JOIN merchandiserowner mo2
	ON mo2.merchandiser_owner_id=mo1.merchandiser_owner_id
WHERE mo1.last_name=mo2.last_name
    AND mo1.first_name<>mo2.first_name
	AND months_between(mo1.date_of_birth, mo1.date_of_birth)/12 Between -5 AND 5;