申論題

四、請完成下列 SQL 語法執行的結果。（每小題 10 分，共 20 分） stock 資料表 item_id descript price on_hand M01 PLUMS 1.00 100 M02 APPLES 2.00 200 M03 ORANGES 3.00 300 M04 PEARS 4.00 400 M05 BANANAS 5.00 500 M06 GRAPES 6.00 600 M07 KIWIS 7.00 700 orders 資料表 order_no cust_id item_id quantity 001 AAA M01 10 002 BBB M02 20 003 AAA M03 30 004 CCC M01 40 005 BBB M05 50 006 AAA M04 60 007 AAA M03 70 008 EEE M07 20 009 CCC M01 40 010 BBB M05 60 customers 資料表 cust_id cust_name region phone AAA Alice NE (02)2121-2325 BBB Bill W (04)2129-9825 CCC Cathy NE (03)633-1215 DDD Daren S (02)2621-3838 EEE Elly W (02)3321-1245 FFF Laura NE (04)6215-1245

⑴ select item_id, count(*), sum(quantity) from orders where cust_id in (’AAA’,’CCC’,’EEE’) group by item_id;