The QUERY I wrote DOES NOT WORK DON\'T KNOW WHY: SELECT DISTINCT p.name, p.order
ID: 3856569 • Letter: T
Question
The QUERY I wrote DOES NOT WORK DON'T KNOW WHY:
SELECT DISTINCT p.name, p.order_date FROM grocery.Belongs_To p INNER JOIN grocery.Product_Batch b ON p.product_id=b.product_id WHERE b.category_name=’baked goods’ AND (p.order_date > current_date or p.order_date IS NULL);
Write an SQL ORACLE query in the GROCERY database:
1) What baked goods were ordered before the current date?
Further Information:
Grocery Database tables:
SELECT table_name FROM all_tables WHERE owner='GROCERY';
TABLE_NAME
------------------------------
BELONGS_TO
BUYER
CAN_SUPPLY
CATEGORY
DEPENDENT
DEPENDENT_FAMILY
EMPLOYEE
IN_STORE_LOCATION
PRODUCT_BATCH
PRODUCT_BATCH_PURCHASED
SALARIED
TABLE_NAME
------------------------------
SHELVES
STORE
STORE_ORDER
TRANSACTION
VENDOR
VENDOR_ADDRESS
VENDOR_CONTACT
WAGED
WORKS_IN
SQL> SELECT * FROM grocery.product_batch;
PRODUCT_ID SALES_TAX NAME EXPIRATIO POTENTIAL_QUANTITY
---------- ---------- -------------------- --------- ------------------
ORDER_DAT POTENTIAL_ORDER_PRICE_PER_ITEM REQUIRED_MIN_QUANTITY EXPECTED_
--------- ------------------------------ --------------------- ---------
1 8.875 coco milk A 17-DEC-10 1000
14-NOV-10 3.8 100 07-NOV-10
2 8.875 soy milk A 28-NOV-10 1000
31-OCT-10 4.1 90 12-NOV-10
3 8.875 2% milk 26-NOV-10 1000
28-OCT-10 4.1 90 12-NOV-10
PRODUCT_ID SALES_TAX NAME EXPIRATIO POTENTIAL_QUANTITY
---------- ---------- -------------------- --------- ------------------
ORDER_DAT POTENTIAL_ORDER_PRICE_PER_ITEM REQUIRED_MIN_QUANTITY EXPECTED_
--------- ------------------------------ --------------------- ---------
4 8.875 whole milk 26-NOV-10 1000
01-NOV-10 4.1 90 14-NOV-10
5 8.875 brie Alex 07-DEC-10 100
21-OCT-10 8.9 10 10-NOV-10
6 8.875 cheddar Vincent 05-DEC-10 120
21-OCT-10 2.9 15 15-NOV-10
PRODUCT_ID SALES_TAX NAME EXPIRATIO POTENTIAL_QUANTITY
---------- ---------- -------------------- --------- ------------------
ORDER_DAT POTENTIAL_ORDER_PRICE_PER_ITEM REQUIRED_MIN_QUANTITY EXPECTED_
--------- ------------------------------ --------------------- ---------
7 8.875 vanilla ice cream A 20-NOV-10 500
11-OCT-10 3.9 30 22-OCT-10
8 8.875 rocky road ice cream 22-NOV-10 500
11-OCT-10 5.1 30 22-OCT-10
9 8.875 cookie dough 18-NOV-10 700
30-OCT-10 7.2 50 02-NOV-10
PRODUCT_ID SALES_TAX NAME EXPIRATIO POTENTIAL_QUANTITY
---------- ---------- -------------------- --------- ------------------
ORDER_DAT POTENTIAL_ORDER_PRICE_PER_ITEM REQUIRED_MIN_QUANTITY EXPECTED_
--------- ------------------------------ --------------------- ---------
10 8.875 2% lean beef 30-NOV-10 1000
30-OCT-10 1.99 200 05-NOV-10
11 8.875 40% fat beef 03-DEC-10 1200
10-NOV-10 .99 200 15-NOV-10
12 8.875 chicken breast 13-DEC-10 800
08-NOV-10 3.99 100 13-NOV-10
PRODUCT_ID SALES_TAX NAME EXPIRATIO POTENTIAL_QUANTITY
---------- ---------- -------------------- --------- ------------------
ORDER_DAT POTENTIAL_ORDER_PRICE_PER_ITEM REQUIRED_MIN_QUANTITY EXPECTED_
--------- ------------------------------ --------------------- ---------
13 8.875 jumbo shrimp 03-DEC-10 2000
10-NOV-10 15.99 100 16-NOV-10
14 8.875 frenzied salmon 05-DEC-10 1000
10-NOV-10 5.99 100 14-NOV-10
15 8.875 man eating tuna 05-MAR-11 5000
12-NOV-10 5.99 100 20-NOV-10
PRODUCT_ID SALES_TAX NAME EXPIRATIO POTENTIAL_QUANTITY
---------- ---------- -------------------- --------- ------------------
ORDER_DAT POTENTIAL_ORDER_PRICE_PER_ITEM REQUIRED_MIN_QUANTITY EXPECTED_
--------- ------------------------------ --------------------- ---------
16 8.875 purple banana 05-FEB-11 500
12-NOV-10 4.79 499 20-NOV-10
17 8.875 angsty pineapple 05-JAN-11 700
01-AUG-10 10.96 100 10-AUG-10
18 8.875 lonely grapple 10-DEC-10 900
01-AUG-10 17.96 100 12-AUG-10
PRODUCT_ID SALES_TAX NAME EXPIRATIO POTENTIAL_QUANTITY
---------- ---------- -------------------- --------- ------------------
ORDER_DAT POTENTIAL_ORDER_PRICE_PER_ITEM REQUIRED_MIN_QUANTITY EXPECTED_
--------- ------------------------------ --------------------- ---------
19 8.875 green cucumber 08-DEC-10 600
03-AUG-10 2.99 100 12-AUG-10
20 8.875 blue tomato 08-DEC-10 1600
15-AUG-10 1.99 100 22-AUG-10
21 8.875 furry celery 01-DEC-10 200
15-AUG-10 1.99 100 30-AUG-10
PRODUCT_ID SALES_TAX NAME EXPIRATIO POTENTIAL_QUANTITY
---------- ---------- -------------------- --------- ------------------
ORDER_DAT POTENTIAL_ORDER_PRICE_PER_ITEM REQUIRED_MIN_QUANTITY EXPECTED_
--------- ------------------------------ --------------------- ---------
22 8.875 real apple juice 01-MAY-11 500
15-AUG-10 1.99 100 27-AUG-10
23 8.875 unreal apple juice 01-MAY-19 500
15-AUG-10 11.99 100 27-AUG-10
24 8.875 fake apple juice 01-MAY-49 22
15-AUG-10 .99 11 27-AUG-10
PRODUCT_ID SALES_TAX NAME EXPIRATIO POTENTIAL_QUANTITY
---------- ---------- -------------------- --------- ------------------
ORDER_DAT POTENTIAL_ORDER_PRICE_PER_ITEM REQUIRED_MIN_QUANTITY EXPECTED_
--------- ------------------------------ --------------------- ---------
25 8.875 cherry cream soda 01-MAY-49 5000
27-SEP-10 5.99 1000 10-OCT-10
26 8.875 potato flavored coke 01-MAY-30 5000
27-SEP-10 3.99 1000 15-OCT-10
27 8.875 soylent green 01-MAY-30 500
17-SEP-10 153.99 100 15-OCT-10
PRODUCT_ID SALES_TAX NAME EXPIRATIO POTENTIAL_QUANTITY
---------- ---------- -------------------- --------- ------------------
ORDER_DAT POTENTIAL_ORDER_PRICE_PER_ITEM REQUIRED_MIN_QUANTITY EXPECTED_
--------- ------------------------------ --------------------- ---------
28 8.875 sprite flavored coke 01-JUN-37 500
17-SEP-10 53.99 100 12-OCT-10
29 8.875 heavy water 01-JUN-12 5000
17-SEP-10 .99 100 12-OCT-10
30 8.875 distilled water 03-JUN-12 5000
17-SEP-10 2.99 200 12-OCT-10
PRODUCT_ID SALES_TAX NAME EXPIRATIO POTENTIAL_QUANTITY
---------- ---------- -------------------- --------- ------------------
ORDER_DAT POTENTIAL_ORDER_PRICE_PER_ITEM REQUIRED_MIN_QUANTITY EXPECTED_
--------- ------------------------------ --------------------- ---------
31 8.875 swamp water 03-JUN-22 5000
23-SEP-10 .01 200 12-OCT-10
32 8.875 scratchy dish soap 5000
23-JUN-10 .01 200 12-OCT-10
33 8.875 itchy dish soap 5000
23-MAY-10 1.05 200 12-JUN-10
PRODUCT_ID SALES_TAX NAME EXPIRATIO POTENTIAL_QUANTITY
---------- ---------- -------------------- --------- ------------------
ORDER_DAT POTENTIAL_ORDER_PRICE_PER_ITEM REQUIRED_MIN_QUANTITY EXPECTED_
--------- ------------------------------ --------------------- ---------
34 8.875 palmolive dish soap 7000
09-MAY-10 10.05 200 12-JUN-10
35 8.875 MR clean wood 4500
09-MAY-10 15.05 300 20-JUN-10
36 8.875 coal wood clean 50000
09-JUL-10 100.05 300 20-AUG-10
PRODUCT_ID SALES_TAX NAME EXPIRATIO POTENTIAL_QUANTITY
---------- ---------- -------------------- --------- ------------------
ORDER_DAT POTENTIAL_ORDER_PRICE_PER_ITEM REQUIRED_MIN_QUANTITY EXPECTED_
--------- ------------------------------ --------------------- ---------
37 8.875 shiny wood clean 5000
09-MAR-10 .05 300 20-AUG-10
38 8.875 windex 5000
09-JUL-10 100.05 300 20-AUG-10
39 8.875 windex imitation 5000
09-JUL-10 .05 300 20-AUG-10
PRODUCT_ID SALES_TAX NAME EXPIRATIO POTENTIAL_QUANTITY
---------- ---------- -------------------- --------- ------------------
ORDER_DAT POTENTIAL_ORDER_PRICE_PER_ITEM REQUIRED_MIN_QUANTITY EXPECTED_
--------- ------------------------------ --------------------- ---------
40 8.875 windex store brand 5100
10-JUN-10 .05 300 12-JUN-10
41 8.875 on-fire shampoo 500
10-MAR-09 10.05 300 12-MAY-09
42 8.875 slick-joe shampoo 700
10-MAR-07 110.05 300 12-MAY-09
PRODUCT_ID SALES_TAX NAME EXPIRATIO POTENTIAL_QUANTITY
---------- ---------- -------------------- --------- ------------------
ORDER_DAT POTENTIAL_ORDER_PRICE_PER_ITEM REQUIRED_MIN_QUANTITY EXPECTED_
--------- ------------------------------ --------------------- ---------
43 8.875 store brand shampoo 1700
10-FEB-07 .01 300 20-MAY-09
44 8.875 scottish spring soap 1700
10-APR-07 7.01 300 20-MAY-09
45 8.875 electric toothbrush 100
10-APR-08 77.01 20 20-MAY-08
PRODUCT_ID SALES_TAX NAME EXPIRATIO POTENTIAL_QUANTITY
---------- ---------- -------------------- --------- ------------------
ORDER_DAT POTENTIAL_ORDER_PRICE_PER_ITEM REQUIRED_MIN_QUANTITY EXPECTED_
--------- ------------------------------ --------------------- ---------
46 8.875 electric toothpaste 500
10-JUN-08 177.01 20 20-JUN-08
47 8.875 canned ham 01-JAN-13 1500
10-JUN-09 2.01 20 20-JUN-09
48 8.875 canned happiness 01-JAN-40 1500
10-JUN-04 100.01 20 20-JUN-09
PRODUCT_ID SALES_TAX NAME EXPIRATIO POTENTIAL_QUANTITY
---------- ---------- -------------------- --------- ------------------
ORDER_DAT POTENTIAL_ORDER_PRICE_PER_ITEM REQUIRED_MIN_QUANTITY EXPECTED_
--------- ------------------------------ --------------------- ---------
49 8.875 rover 27-MAR-30 15
10-JUN-06 200.01 2 20-JUL-06
50 8.875 generic cat food 27-MAR-72 300
10-MAY-09 200.01 20 20-JUL-09
51 8.875 wonder bread 15-MAR-72 3000
10-MAY-09 10.01 200 20-JUL-09
PRODUCT_ID SALES_TAX NAME EXPIRATIO POTENTIAL_QUANTITY
---------- ---------- -------------------- --------- ------------------
ORDER_DAT POTENTIAL_ORDER_PRICE_PER_ITEM REQUIRED_MIN_QUANTITY EXPECTED_
--------- ------------------------------ --------------------- ---------
52 8.875 so-so bread 15-JUL-14 3000
10-MAY-09 12.01 200 10-JUN-09
53 8.875 raisin bread 15-DEC-11 3000
10-MAY-09 30.01 200 10-JUN-09
54 8.875 vanilla puffs cereal 15-DEC-11 3000
10-MAY-09 3.1 200 30-JUN-09
PRODUCT_ID SALES_TAX NAME EXPIRATIO POTENTIAL_QUANTITY
---------- ---------- -------------------- --------- ------------------
ORDER_DAT POTENTIAL_ORDER_PRICE_PER_ITEM REQUIRED_MIN_QUANTITY EXPECTED_
--------- ------------------------------ --------------------- ---------
55 8.875 doggy-os cereal 15-AUG-11 3000
10-MAY-09 13.1 200 30-MAY-09
56 8.875 fruity puffs 15-SEP-13 3000
10-MAY-09 26.17 500 29-MAY-09
57 8.875 super duper crunch 12-AUG-13 7000
30-MAY-09 126.17 400 29-JUN-09
PRODUCT_ID SALES_TAX NAME EXPIRATIO POTENTIAL_QUANTITY
---------- ---------- -------------------- --------- ------------------
ORDER_DAT POTENTIAL_ORDER_PRICE_PER_ITEM REQUIRED_MIN_QUANTITY EXPECTED_
--------- ------------------------------ --------------------- ---------
58 8.875 healthy o-s 12-MAR-12 2000
30-MAY-09 826.17 400 15-JUN-09
59 8.875 death by chocolate 28-MAY-12 200
30-MAY-09 26.17 100 27-JUN-09
60 8.875 death-by-strawberry 28-MAR-12 200
30-MAY-09 16.17 100 28-JUN-09
PRODUCT_ID SALES_TAX NAME EXPIRATIO POTENTIAL_QUANTITY
---------- ---------- -------------------- --------- ------------------
ORDER_DAT POTENTIAL_ORDER_PRICE_PER_ITEM REQUIRED_MIN_QUANTITY EXPECTED_
--------- ------------------------------ --------------------- ---------
61 8.875 death by custard 26-MAR-12 300
01-JUN-09 16.17 100 28-JUN-09
62 8.875 jawbreaker 14-MAR-12 300
01-JUN-10 1.17 10 28-JUN-10
63 8.875 crunchy candy X 15-DEC-11 500
11-JUN-10 11.17 100 28-JUN-10
PRODUCT_ID SALES_TAX NAME EXPIRATIO POTENTIAL_QUANTITY
---------- ---------- -------------------- --------- ------------------
ORDER_DAT POTENTIAL_ORDER_PRICE_PER_ITEM REQUIRED_MIN_QUANTITY EXPECTED_
--------- ------------------------------ --------------------- ---------
64 8.875 caramel chews 15-DEC-12 1500
11-JUN-10 11.17 100 17-JUN-10
65 8.875 liquid candy 15-DEC-12 1500
11-JUN-10 31.17 100 18-JUN-10
66 8.875 apple pie 15-DEC-12 500
11-JUN-10 51.17 200 20-JUN-10
PRODUCT_ID SALES_TAX NAME EXPIRATIO POTENTIAL_QUANTITY
---------- ---------- -------------------- --------- ------------------
ORDER_DAT POTENTIAL_ORDER_PRICE_PER_ITEM REQUIRED_MIN_QUANTITY EXPECTED_
--------- ------------------------------ --------------------- ---------
67 8.875 blueberry pie 15-DEC-12 500
11-JUN-10 21.17 200 21-JUN-10
68 8.875 toad cheese pie 15-OCT-12 1500
13-JUN-10 11.1 200 21-JUN-10
69 8.875 cinnamon sticks 15-OCT-12 1500
13-JUN-10 10.1 200 25-JUN-10
PRODUCT_ID SALES_TAX NAME EXPIRATIO POTENTIAL_QUANTITY
---------- ---------- -------------------- --------- ------------------
ORDER_DAT POTENTIAL_ORDER_PRICE_PER_ITEM REQUIRED_MIN_QUANTITY EXPECTED_
--------- ------------------------------ --------------------- ---------
70 8.875 cayanne pepper 15-OCT-15 1700
13-JUN-10 15.3 300 25-JUN-10
71 8.875 cardamom 15-OCT-20 700
13-JUN-10 115.3 100 25-JUN-10
72 8.875 crushed ginger 15-OCT-10 23700
13-JUN-10 12.3 100 25-JUN-10
PRODUCT_ID SALES_TAX NAME EXPIRATIO POTENTIAL_QUANTITY
---------- ---------- -------------------- --------- ------------------
ORDER_DAT POTENTIAL_ORDER_PRICE_PER_ITEM REQUIRED_MIN_QUANTITY EXPECTED_
--------- ------------------------------ --------------------- ---------
73 8.875 ketchup 15-DEC-10 3700
13-OCT-10 2.3 100 25-OCT-10
74 8.875 vinegar 17-DEC-10 700
15-OCT-10 12.3 100 25-OCT-10
75 8.875 mustard 17-DEC-10 800
15-OCT-09 5.7 100 25-OCT-09
PRODUCT_ID SALES_TAX NAME EXPIRATIO POTENTIAL_QUANTITY
---------- ---------- -------------------- --------- ------------------
ORDER_DAT POTENTIAL_ORDER_PRICE_PER_ITEM REQUIRED_MIN_QUANTITY EXPECTED_
--------- ------------------------------ --------------------- ---------
76 8.875 catsup 17-DEC-10 900
15-OCT-09 15.7 100 25-OCT-09
77 10.515 smirnoff vodka 17-DEC-15 11900
15-OCT-09 85.7 2000 25-OCT-10
78 10.515 jack daniels 17-DEC-15 1900
15-OCT-09 185.7 1000 25-OCT-10
PRODUCT_ID SALES_TAX NAME EXPIRATIO POTENTIAL_QUANTITY
---------- ---------- -------------------- --------- ------------------
ORDER_DAT POTENTIAL_ORDER_PRICE_PER_ITEM REQUIRED_MIN_QUANTITY EXPECTED_
--------- ------------------------------ --------------------- ---------
79 10.515 grey goose 30-DEC-15 1500
15-OCT-09 185.7 100 27-OCT-10
80 10.515 gato negro 30-DEC-37 2500
15-OCT-08 23.7 100 27-OCT-10
81 10.515 amstel light 30-DEC-12 2500
15-OCT-08 13.7 100 25-OCT-08
PRODUCT_ID SALES_TAX NAME EXPIRATIO POTENTIAL_QUANTITY
---------- ---------- -------------------- --------- ------------------
ORDER_DAT POTENTIAL_ORDER_PRICE_PER_ITEM REQUIRED_MIN_QUANTITY EXPECTED_
--------- ------------------------------ --------------------- ---------
82 8.875 wakky smoky tabaky 30-DEC-15 1500
15-OCT-08 83.7 100 25-NOV-08
83 16.666 wakky chewy tabaky 30-DEC-16 2700
16-OCT-08 57.7 100 25-NOV-08
84 16.666 marlboro light 30-DEC-16 2700
16-OCT-08 77.7 500 25-NOV-08
PRODUCT_ID SALES_TAX NAME EXPIRATIO POTENTIAL_QUANTITY
---------- ---------- -------------------- --------- ------------------
ORDER_DAT POTENTIAL_ORDER_PRICE_PER_ITEM REQUIRED_MIN_QUANTITY EXPECTED_
--------- ------------------------------ --------------------- ---------
85 8.875 pyrex measuring cup 3000
16-NOV-08 17.7 50 25-NOV-08
86 8.875 alimunum foil 6000
16-NOV-10 7.7 50 30-NOV-10
87 8.875 wax paper 600
16-SEP-10 27.7 50 30-NOV-10
PRODUCT_ID SALES_TAX NAME EXPIRATIO POTENTIAL_QUANTITY
---------- ---------- -------------------- --------- ------------------
ORDER_DAT POTENTIAL_ORDER_PRICE_PER_ITEM REQUIRED_MIN_QUANTITY EXPECTED_
--------- ------------------------------ --------------------- ---------
88 8.875 plastic forks 600
16-SEP-10 .7 50 30-NOV-10
89 8.875 metal forks 1600
12-SEP-10 17.7 50 30-OCT-10
90 8.875 ceramic forks 16
12-SEP-10 117.7 8 05-NOV-10
PRODUCT_ID SALES_TAX NAME EXPIRATIO POTENTIAL_QUANTITY
---------- ---------- -------------------- --------- ------------------
ORDER_DAT POTENTIAL_ORDER_PRICE_PER_ITEM REQUIRED_MIN_QUANTITY EXPECTED_
--------- ------------------------------ --------------------- ---------
91 8.875 big frozen pizza 01-JAN-12 1600
12-SEP-10 17.2 80 05-OCT-10
92 8.875 small frozen pizza 01-MAR-12 1200
12-SEP-10 9.2 80 05-OCT-10
93 8.875 frozen cheese cake 01-MAR-12 1500
12-SEP-10 379.2 80 15-OCT-10
PRODUCT_ID SALES_TAX NAME EXPIRATIO POTENTIAL_QUANTITY
---------- ---------- -------------------- --------- ------------------
ORDER_DAT POTENTIAL_ORDER_PRICE_PER_ITEM REQUIRED_MIN_QUANTITY EXPECTED_
--------- ------------------------------ --------------------- ---------
94 8.875 frozen normal cake 17-MAR-12 11500
12-SEP-10 379.2 80 15-SEP-10
95 8.875 Chocolate Milk 18-OCT-10 1000
12-SEP-10 3.01 40 17-OCT-10
96 8.875 Crunch Cereal 11-DEC-10 1000
12-SEP-10 3.01 40 17-OCT-10
PRODUCT_ID SALES_TAX NAME EXPIRATIO POTENTIAL_QUANTITY
---------- ---------- -------------------- --------- ------------------
ORDER_DAT POTENTIAL_ORDER_PRICE_PER_ITEM REQUIRED_MIN_QUANTITY EXPECTED_
--------- ------------------------------ --------------------- ---------
97 8.875 Dairy Cereal 12-DEC-10 1000
12-SEP-10 3.01 40 17-OCT-10
98 8.875 Sugar Puff 11-DEC-10 1000
12-SEP-10 3.01 40 17-OCT-10
99 8.875 Eclair 18-DEC-10 1000
12-SEP-10 3.01 40 17-OCT-10
PRODUCT_ID SALES_TAX NAME EXPIRATIO POTENTIAL_QUANTITY
---------- ---------- -------------------- --------- ------------------
ORDER_DAT POTENTIAL_ORDER_PRICE_PER_ITEM REQUIRED_MIN_QUANTITY EXPECTED_
--------- ------------------------------ --------------------- ---------
100 8.875 Cruchy Cracker 18-DEC-10 1000
12-SEP-10 3.01 40 17-OCT-10
SQL> SELECT DISTINCT p.name FROM grocery.Belongs_To b INNER JOIN grocery.Product_Batch p ON b.product_id=p.product_id WHERE b.category_name='baked goods';
NAME
--------------------
death by chocolate
death-by-strawberry
death by custard
blueberry pie
frozen cheese cake
so-so bread
apple pie
frozen normal cake
raisin bread
wonder bread
toad cheese pie
Explanation / Answer
SELECT p.name FROM grocery.Belongs_To b INNER JOIN grocery.Product_Batch p
ON b.product_id=p.product_id
WHERE LOWER(b.category_name)='baked goods' AND p.order_date <
cast((now()) as date);
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.