vmart_query_04.sql
-- vmart_query_04.sql -- IN predicate -- Find all products supplied by stores in MA
SELECT DISTINCT s.product_key, p.product_description FROM store.store_sales_fact s, public.product_dimension p WHERE s.product_key = p.product_key AND s.product_version = p.product_version AND s.store_key IN ( SELECT store_key FROM store.store_dimension WHERE store_state = 'MA') ORDER BY s.product_key;
Output
product_key | product_description -------------+---------------------------------------- 1 | Brand #1 butter 1 | Brand #2 bagels 2 | Brand #3 lamb 2 | Brand #4 brandy 2 | Brand #5 golf clubs 2 | Brand #6 chicken noodle soup 3 | Brand #10 ground beef 3 | Brand #11 vanilla ice cream 3 | Brand #7 canned chicken broth 3 | Brand #8 halibut 3 | Brand #9 camera case 4 | Brand #12 rash ointment 4 | Brand #13 low fat milk 4 | Brand #14 chocolate chip cookies 4 | Brand #15 silver polishing cream 5 | Brand #16 cod 5 | Brand #17 band aids 6 | Brand #18 bananas 6 | Brand #19 starch 6 | Brand #20 vegetable soup 6 | Brand #21 bourbon ...