几个简单sql语句问题，不过是英语版本的，大家帮看看！！在线等！！！

显示全部楼层 · 2011-5-16 06:01:54

1.Replacethequerybelowwithanequivalentquerythatissimpler,doesnotuseUNIONanddoesuseanOUTERJOIN:(2marks)
SELECTCode,COUNT(NbrItemsRequested)ASTotalItems
FROMProductASpINNERJOINSalesOrderProductASsop
ONp.PID=sop.PID
GROUPBYCode
HAVINGCOUNT(NbrItemsRequested)2
UNION
SELECTCode,0
FROMProduct
WHEREPIDNOTIN(SELECTPID
FROMSalesOrderProduct);
2.FormulatethreequeriestolisttheorderNumberofevery“Placed”orderthatincludesarequestforoneormoreCookwareitems.(3marks)
i.OnequerythatusesINtwiceanddoesnotuseEXISTS.
ii.OnequerythatusesEXISTSonceandINonce.
iii.OnequerythatusesEXISTStwiceanddoesnotuseIN.
3.Inyourownwords,describetheresultproducedbythequerybelow.(1.5marks)
SELECTCode
FROMProductASp
WHERENOTEXISTS(SELECT*
FROMCustomerASc
WHEREState=\'QLD\'
ANDEXISTS(SELECT*
FROMSalesOrderASso
WHEREso.CID=c.CID
ANDEXISTS(SELECT*
FROMSalesOrderProductASsop
WHEREsop.SOID=so.SOID
ANDsop.PID=p.PID)));
4.Inyourownwords,describetheresultproducedbythequerybelow.(1.5marks)
SELECTCode
FROMProductASp
WHERENOTEXISTS(SELECT*
FROMCustomerASc
WHEREState=\'QLD\'
ANDNOTEXISTS(SELECT*
FROMSalesOrderASso
WHEREso.CID=c.CID
ANDEXISTS(SELECT*
FROMSalesOrderProductASsop
WHEREsop.SOID=so.SOID
ANDsop.PID=p.PID)));
5.Advanced:FormulateanequivalentquerytotheonegiveninQuestion4above.YourquerymustnotuseEXISTSorIN.(1bonusmark)

千问 · 2011-5-16 06:01:54

可以远程的话联系我！