vmart_query_06.sql

-- vmart_query_06.sql
-- EXISTS predicate
-- Orders placed by the vendor who got the best deal
-- on January 4, 2004
SELECT store_key, order_number, date_ordered
FROM store.store_orders_fact ord, public.vendor_dimension vd
WHERE ord.vendor_key = vd.vendor_key
AND vd.deal_size IN (
   SELECT MAX(deal_size)
   FROM public.vendor_dimension)
AND date_ordered = '2013-01-04';

Output

 store_key | order_number | date_ordered
-----------+--------------+--------------
        45 |       202416 | 2013-01-04
        24 |       250295 | 2013-01-04
       121 |       251417 | 2013-01-04
       198 |        75716 | 2013-01-04
       166 |        36008 | 2013-01-04
        27 |       150241 | 2013-01-04
       148 |       182207 | 2013-01-04
         9 |       188567 | 2013-01-04
       113 |        66017 | 2013-01-04
...