5.txt [一](ppt 249) 在資料表ord中找出客戶JOCKSPORTS曾下過的訂單如下 +-------+------------+--------+---------+ | ordid | orderdate | custid | total | +-------+------------+--------+---------+ | 606 | 1986-07-14 | 100 | 3.40 | | 609 | 1986-08-01 | 100 | 97.50 | | 620 | 1987-03-12 | 100 | 4450.00 | | 621 | 1987-03-15 | 100 | 730.00 | +-------+------------+--------+---------+ 4 rows in set (0.03 sec) ANS: select ordid, orderdate, custid, total from ord where custid = ( select custid from customer where name = 'JOCKSPORTS' ); [二](ppt 249) 在資料表item中找出曾經訂過產品 'ACE TENNIS NET' 的訂單如下 +-------+--------+------+-------------+ | ordid | prodid | qty | actualprice | +-------+--------+------+-------------+ | 604 | 100890 | 3 | 58.00 | | 605 | 100890 | 5 | 58.00 | | 609 | 100890 | 1 | 50.00 | | 610 | 100890 | 1 | 58.00 | | 616 | 100890 | 2 | 58.00 | | 617 | 100890 | 500 | 58.00 | +-------+--------+------+-------------+ 6 rows in set (0.00 sec) Ans: select ordid, prodid, qty, actualprice from item where prodid= ( select prodid from product where descrip = 'ACE TENNIS NET'
) ;
================================================================================ (三)(ppt 273) 將emp資料表中comm欄位的值是NULL的改為0(零) update emp set comm = 0 where comm is null (四)在emp資料表中將research部門的員工調薪5% update emp set sal=sal*1.05 where deptno = ( select deptno
from dept where dname='research' )
=================================================================================== (五)(ppt 278) 在ORD資料表中，將訂單編號'620'的訂單刪除(請觀察會產生何種錯誤訊息) delete from ORD where ordid=620 /* 訊息 547，層級 16，狀態 0，行 1 DELETE 陳述式與 REFERENCE 條件約束 "FK__ITEM__ORDID__1ED998B2" 衝突。衝突發生在資料庫 "sample"，資料表 "dbo.ITEM", column 'ORDID'。 陳述式已經結束。 */ 第 1 頁
5.txt (六)在ITEM資料表中，將訂單編號'620'的訂購記錄刪除 delete from ITEM where ordid=620 (七)重新執行第一題 delete from ORD where ordid=620 (八)在ITEM資料表中，將客戶編號'106'的訂購記錄刪除 delete from ITEM where ORDID =any ( select ordid
from ord where custid=106 )
第 2 頁