【正文】 values(2201,1,2,39。39。) INSERT INTO RoomStatusvalues(2,39。不空閑39。,178) INSERT INTO RoomStatusvalues(0,39。已預訂39。,20) INSERT INTO RoomStatusvalues(1,39?？臻e39。,99)INSERT INTO RoomTypevalues(4,39。商務套房39。,80,400)INSERT INTO RoomTypevalues(1,39。單人房39。,40,120)INSERT INTO RoomTypevalues(2,39。標準間39。,100,150)INSERT INTO RoomTypevalues(5,39?？偨y(tǒng)套房39。,2,1888)INSERT INTO RoomTypevalues(3,39。豪華間39。,60,270)INSERT INTO LoginInfovalues(39。A000000739。,39。WWWNDX39。,0)INSERT INTO LoginInfovalues(39。B000000139。,39。12345639。,1)INSERT INTO ReInfovalues(39。C000000139。,8204,39。20110705 09:06:0739。,39。20110710 09:06:0739。)INSERT INTO ReInfovalues(39。C000000239。,5203,39。20110704 19:06:0739。,39。20110710 09:06:0739。)INSERT INTO ReInfovalues(39。C000000339。,2222,39。20110709 09:06:0739。,39。20110713 19:06:0739。)INSERT INTO ReInfovalues(39。C000000439。,2202,39。20110709 09:06:0739。,39。20110713 19:06:0739。)修改數(shù)據(jù)USE MC酒店前臺管理系統(tǒng)GO客戶調(diào)房間update ReInfoset RoomNo=2201where ClientID=39。C000000439?？蛻羧胱⌒薷姆块g狀態(tài)update RoomInfoset StatusNo=2where RoomNo=2202將客戶的消費減去200元，但因為之后設置了一個保護安全性的觸發(fā)器所以這個應該執(zhí)行不了update ClientInfoset ClientConsume=ClientConsume200where ClientID=39。C000000439。刪除數(shù)據(jù)客戶退房delete from ReInfowhere ClientID=39。C000000439。刪除客戶信息delete from ClientInfowhere ClientName=39。馬冰潔39。七. 利用TSQL命令進行數(shù)據(jù)的檢索和統(tǒng)計根據(jù)自己設計的數(shù)據(jù)庫和輸入的數(shù)據(jù)，寫出至少10個查詢要求及對應的SQL查詢命令，應該包含如下功能：投影和選擇；多表連接；分組統(tǒng)計與HAVING；子查詢；查詢結果保存。USE MC酒店前臺管理系統(tǒng)GoSELECT ClientName from ClientInfoSELECT RoomNo from RoomInfo where StatusNo=1SELECT ,RoomNo,ClientName,ClientSex,ClientRemarks from ClientInfo,ReInfo where = select ClientSex,Count(ClientSex) as 人數(shù)from ClientInfo group by ClientSexSELECT TypeName,AVG(ClientConsume) AS 平均收入from RoomType,ClientInfo,ReInfo,RoomInfowhere = and = and =Group by TypeName Having AVG(ClientConsume)1000SELECT TypeNo from RoomInfo GROUP BY TypeNo HAVING COUNT(*)2SELECT RoomNo from RoomInfo WHERE TypeNo IN(SELECT TypeNo from RoomInfo GROUP BY TypeNo HAVING COUNT(*)2)SELECT * from RoomType where Price(SELECT AVG(Price) from RoomType)SELECT ClientName from ClientInfo where ClientSex=39。男39。 AND ClientAdress LIKE 39。中國北京市%39。select RoomNo,StatusNo,RoomRemarksfrom RoomInfowhere TypeNo=(select TypeNo from RoomType where TypeName=39。商務套房39。)八. 利用SQL命令創(chuàng)建視圖對象根據(jù)開發(fā)的系統(tǒng)的需要，至少設計三個視圖：基于單表的、包含多表連接的、包含統(tǒng)計運算的。use MC酒店前臺管理系統(tǒng)gocreate view clientVIPinfoasselect ClientID,ClientName,ClientSex,ClientCertificateID,ClientBirthday,ClientPhone,ClientConsumefrom ClientInfowhere ClientConsume2000select * from clientVIPinfocreate view clientmaninfoasselect ClientID,ClientName,ClientSex,ClientCertificateID,ClientBirthday,ClientPhonefrom ClientInfowhere ClientSex=39。男39。 from ClientInfoselect * from clientmaninfocreate view roomreinfoasselect ,ClientName,ClientSex,ClientCertificateID,ClientBirthday,ClientPhone,RoomNo,LiveDate,OutDatefrom ClientInfo,ReInfowhere RoomNo=2202and=create view freeroomasselect ,RoomRemarksfrom RoomStatus,RoomInfo,RoomTypewhere RoomStatus=39?？臻e39。 and =and = select * from freeroomcreate view zxfasselect ,SUM(ClientConsume) as SumConsumefrom ClientInfo,ReInfo,RoomInfo,RoomTypewhere =and = and =group by TypeNameselect * from zxf利用SQL對視圖進行查詢。select TypeName,SumConsumefrom zxfwhere SumConsume10000select ClientIDfrom clientmaninfowhere ClientName=39。林靜39。select RoomNofrom freeroomwhere TypeName=39。商務套房39。九. 對視圖進行插入、刪除、修改數(shù)據(jù)操作。體會視圖與表的異同。對視圖進行插入數(shù)據(jù)insert into clientmaninfovalues(39。C000001139。,39。Jason39。,39。男39。,39。42333319880704007839。,39。1988070439。,39。12345678939。)insert into clientVIPinfovalues(39。C000001239。,39。Freja　Black39。,39。女39。,39。42333319880604007839。,39。1988060439。,39。12345678939。,5000)修改數(shù)據(jù)update clientVIPinfoset ClientConsume=ClientConsume+200where ClientSex=39。男39。beforeAfterupdate clientmaninfoset ClientName=39。池賢宇39。where ClientID=39。C000000539。beforeAfterupdate freeroomset TypeName=39。海景套房39。where