数据库系统原理与设计第二版试验一至试验三

1、实验一1-1查询员工的姓名、职务和嶄水select employeeName, headship, salary from employee06-SS. 0r4r.j5. 1* XiXJlect.O-p5rt.mnt-.3oloryXuc employeeVg J Mn4oMNTa4CMdwr4卄41RM53(0 21L#U24 COM3% cam4址杯ai(O(B50科lacam6a)(0(7砒岐科xca(n811別$25 aim3旻俯11却25CO (BID办公丈3Jca(n1132(00)1211W2? com-W (3 0 MWI s CO (Vtart (M OT ()5 U图M2.

2、 查询名字中含有“有限”的客户姓需和所在地select CustomerName, addressfrom Customerwhere CustomerName like 有限婷图1-23. 査询岀姓“张”并且姓名的最后一个字为“梅”的员工。select *from employeewhere employee立me like 张梅“灶乙L ：咚王醞d_ _二 J00-59.Drier. . .Qery5.sa 痺 aeLect rfxcm crplayaa UhQCQ QfiplO/OfifCOBQ HXu 卡丁构 Jj maCMabacthwDya1EXCEOCF19?31imCO0ftK

3、)CO3上 帀北牌轩1XU74O5O1E2OD5XB(nCO0a(KO阿2401032EXCKOX197011.000:090HILL；OX11 18(MOOa(KO灯务轲&+50n(K)立闻匚琰也v&qs&9 o.oiot) “(52) Qcitfie (n：oo：(n 2 tj图1-34. 查询住址中含有上海或南昌的女员工，并显示其姓名、所属部门、职称、住址，其中性 别用“男”和女”显示SELECT employeeName, department, address,isnull (convert (char (10), birthday, 120),不详)出生日期,case sex wh

4、en Mthen 男when F then女end as性别 from employeewhere (address like 上海幫 or address like 南昌 ) and sex=，F*X 仇04Q/ 讥 MSfLWT mpUraJuM .daartmnt ddrr .ici.*(r ch*rii0i.Mrikp.U0i 和”岀生Btl. caam Ntwn 势.=* n，女x z性别fronp】ojMMUice 0劝00. waxew : 、剧、 on mx-t 图1-4M H u上斫好tit濟 羽11必出CWM itzuelecv Xtotti QupLavQQhere hM

5、tlsbiy-P.JJ or beMslupi科长 cns |wjW1姑長J?nDW*5_-r0c5wMrrgeWw?WwtcreihnsO 办F137114)1 CC (DEC OX上溥市如侥眄1XOr4CfiO16ns-awBrnoaccaM)业跻24m CDE20*CCCofFl/H06m8CCaX上怨巾1370025imowBmaaccoccit%科260 CO3)曲临fi7(n-O5tti.aiox兩昌币A-isniw1(613135r-2eOftttlOIt务科4153败（6MgwmoiwgiKo2WWW6 010ft WOWRJ5160KOE20磁WiFi9?i2-imoaroo

6、&wow1K&Wft2MC0E3)(0XMF1 刃 2(615 (OS waxWifiJUI施删NULL沏 &K6(noa(ttoa)IHI531WC0E3teXM斑.F1972.10-13 ma) cc ax丙R市中山克1号HULL2fM&a5-28moftaioa业銅34OTCOi3E4己氏IMtE0&-M ds o itr “ fta、 D/darDi co tn oo io图1-56. 选取编号不在“C20050001”和“C20050004”的客户编号、客户名称、客户地址。Select *from Customerwhere CustomerNo not in ( C20050001

7、 ,C20050004)3 72叩匕门彳 r t也m二二沪歹弋/ 06-59. tI|wy5.扳|select Tfroari cusiomsrvhere custotrerKo non m (C30050001 920050004：结果七3常氏cuitorrrfJocuattrreftJarft*telqhoneadders3P1C2005CC01统一於诊有用公司02235021天1”市22CO122 05IX&於12股份总阳么、司Q 戈 35&452丟渔市22013测5皿上海生物因恋010 210北京市10?(014C2O06C(Cn0101168152北臣市10(00506応脈合股份百甩

8、公可C0 45幽印上縛币215CD6C2O0?c(n雨岂帀835研制中吕币331X4$?02畑世界扌杯开友公司021 碗512上縛币213“8C3J07(XO3万爭达谀份郴也虑市22MOJ9C3J08(XCn81朋份有舷司0】654215巧北点市10CCOJ图1-6图1-67. 在订单明细表Ordermaster中挑出销售金额大于等于5000元的订单。 update ordermaster set ordersum=sum2from ordermaster a. (select orderno, sum quantityprice sum2

9、from orderdetailgroup by orderno bwhere a.orderno二b. ordernoSelect *From ordermasterWhere ordersum=? 5000*-3 a -x t xsj06-59. Order. . 0ery5.股s已丄ecc *fcaw ordermsstecvherc orc?ctr3iiro-500o| 23结呆.J梢总匚ordeiMoojstameiNosahsMooidsDaleoidsSunnmc&lo1：2O3EO1 CO2 :C2O3 划 04E2CO5CC32001-0900:0):00.(X08310 0

10、0IC03C03C022anaisiaDCoiC2O3CHE2CC5CO3200802-1900:0X0.OJ13232 COICO3CO3CO43EO3EO5CO3CO1CEO3EO302E2CO3CO22008050900:03 CO COD107KCOICO3CO3CO9aZO3EIE133(D1匚 zmsaicnE2CO5CO2aooMErizooajcoan570560lanancnossojsoioioixnC2O3SOJCHE2CC6CO22)0$01I1 O0；OJ CO COD579040IC0JC03CH16201 血070101C2O3TOD4E2CC6CO3纫 2437

11、00:8 CO COJ813000ICO3CO3C12图1-78. 选取订单金额最髙的前10弔的订单数据SELECT TOP 10 PERCENT *from orderdetail order by price DESC22血7否牛U、乜M田宇：06-9r 0r4er.PuerjrS. sqI* 徐2FSELECT TOP 10 PTRCHOT *Xroxti orderdeLftll orcJer by ptclce DE3C结耒ccdaHoccoductNoqurfelji12O3$3G133O31P20C6C0CT131200EO2203$350S3031P20C6C0C3812i3O

12、 CO3201336070031P20L$C0LG4j 1230 CO :42000321 射 001P2OC6CO(B41330 CO52O39321SJO3IPEDC8CO0231100 CO62(0801 Q9Q0J2psocecoce411QOCO图1-89. 计算共销售了几种商品SELECT COUNT (DISTINCT productno)as 种类from orderDeta- dba. 0rdrD at ail f S - dba. OrderflasterOr lei一SELTCT rriNTiMSTrNCT product no) as 种类 fcoro orderDet

13、ai 1. UL-二1詰提1 J消息1：15 :图1-910. il算orderDetail衣中每种商品的销售数量、平均价格和总销售量金额，并且依据销售金额 由大到小输出。SELECT productno 商品种类,count (*) quantity, avg (price)平均价格,sum (quant ity*price) 金额 from orderDetail group by productno order by 金额 desc: 一上y js-O-tTT. Ord . Qa*rF3SELKT produccna f!count(pzic|fror ouerZf门. jroup by

14、 pxodtno by 全汝 ctesd1 SjMWM 31210(0)X022UCOCOXWW0)3pzoKtmi oxdwcmtvctvkara “； ccdrlt -2005 andordarPata 28誉gtancfi a Mt) w ainiw灯除r豪舎除乳产sK电农这式0須g含:m H 2 O. - T._壮尺&弓(3八-心ft，:rd鼻 Z几【6、图 1-1112. 统计至少销售了 10件以上的商品编号和销售数量。 select productno商品编号，quantity商晶数目from orderdetail where quantity=10g b 亠-j- 5 左行#曰

15、器畛忆A/? Q匚衣-dbo. OrdcrDetil U6-96. Order. . Q-cryl. sql 06-6.Sccj elect ptroductno 両品编号 Quantity 荷品数目 rorA ordetrdetQiLwhere quant ity-10结杲Lj ?faeBO?a1 fpaiiKoior； n2 p2C*ffiD03 10图 1-1213. 统计在业务科工作且在1973年或1967年出生的员工人数和平均工资 select count (*)人数,avgCsalarO 平均工资from Employeewhere department二业务科and (year

16、(birthday) =1973 or year (birthday) =1967)严兰空 u犒n嗚貝 珀關I QSTD Score. QaeryZ., 05-70 0r4er. Qeryl. sql* 擔姜I select counvi*) A/V9 isalary) 平均工资Xcoiti Erop loyeewhere degrtmsnt:- 1 业务科1 and yrrai birthday；! -1973 or j-er: :birthdayi -1967图 1-13实验二1. 找出同一天进入公司工作的员工select distinct a. employeeNo, a. employ

17、eeName, a. hireDatefrom Employee a, Employee bwhere a. employeeNo!=b. employeeNo and a. hireDate=b.hireDate讥 a - / C : ;j 一 - 25 .0&-70. Scox. LQwry2./0r4r. . . Qryl.|_select distinct a. eniplayeeio, a. employeeiiaxoe. . biceDatu froco Enployee Eirployee b vnece a.employeeNo ewpLoyeeKo a.hirepace o

18、hlrePacj诉| j俏qanptcvMim?hiePd11:E3H60Q2* S85seiecc q exop love el-iorfis r g 3 ex, g .cimpaTUFemm/a万f.ip 卡，*：=rroro ntiplovee a, ntip丄口包包 匕 匚 . .： ；匸；where & .dep&ri;rfetjt=b. depar cinenLandto7etopLoyeSjaroe=*第栄內消息:OTokccM.rcdcporlrcnlbiodSHp1；M2陈诗我M财务科出纳3至小梧F会计图2-2223. 在employee表中查询薪水超过员工平均薪水的员工信息

19、 select *from Employee a where a.salary(select avg(b.salary) from Employee b)w込性r二二汽亡.0fr-70.Qar gl fselect:from En-ploTee b二曲 2 Mcrrtttfloon 也心tathdkrcDwhONfiV*cioai)?匕目矗H1S(H-150QO)CiM1l7O)5iXeDa2-06 co a)oia财务科2长saBfio2E2C050di-F17-01-0500COCC(n)祠市八一丈曲1590720513d纸60刃旅0创w.on磁4ioaoo3EZOTKC1MIll-OIO

20、OCOIXKTOrt2rt#djSir1预E3农2OJa28COCC(n.W3工E40(00042X9X1Ni9aoi-asoQCoa)(n)江妙燈士知25笛NULL血外20&cos man财隽科22(HOO5F1572-1O-D0Q)OJ：0iW8?5中山坊1号NULL20冷血28013.或034KL006E3XK05斛捲F1W11-05DQOT0Ora)移如市用关丈知吨MULL2TOn-18COCC(D.TO会i+3）（noo图2-34. 査找有销售记录的客户编号名称和订单总额select a. customerNo, a. customerName, b orderMo, sum (qu

21、ant ity*pr ice) orderSum from Customer a, OrderMaster b, OrderDetail cwhere a. customerNo=b customerNo and b orderNo二c orderNogroup by a. customerNo, a. customerName, b orderNo“ j 升忆饷厂怛4砌Q二2谨迄r06-70.Scor* .xl 06-70. 0r4r Qwaryl. x= FROM Customer aWHEPE a. ctistoroerNo NOT IM SELECT c?us cower No FRO

22、K OrderIaster )彳结果二消启customerNo cuonscN areFcajiejjiel兴is股份有恆公司?、巨违j方亍丿上海生物胡究室) C3DBD3J1 大地商城r:razoi南吕市电跪胡制中心图2-56. 使用子査询查找32MDRAM的销售情况 要求显示相应的销售员的姓名、性别、销售日期销售数量和金额其中性别用男”和“女”表示select employeeName, case sexwhen W then 男when F then 女end as sex, b. orderDate, c. quantity 销售数a, c. quantity*c. price 金额f

23、rom Employee a.OrderMaster b,OrderDetail cwhere a. emploeeNob. salerNo and b. orderNo=c.orderNo and c. productNo in (selectf productNofrom OrderMaster d. OrderDetail e.Product fwhere d. orderNo=e orderNo and productName= 32M DRAM*)22y忆坊厂鱼色虧10二孙g計CfeTO Sctr* .fyiuxyZ m06-70 Ordr nryl . xl* JJ|曳select

24、 ctnployeeNeo-, case sexuten chen 男”b=n F* then 立end as sex, b. or6*rDate, c. quantityc. quantlty c. price 主额from Etoployee a. Order Mast ec bOrduuDeSxl cvheve a. ettip loyeeo-b oaletfMo sjxd b. orcterKo-o ot: ci er Ko asd duotNo Ujiselcoc 1. praduocNo irorn OttSerKaater d.OrderPeDSiLL e,Produc

25、e 1 vnere d. orderKo-e. orderKo an ci productNaaBe- 1 .3 DRAJ5)wr(ioyeeJ3nvseio 册 Dare角*1立血旳心0?呛0X85z刻復3：C?01QCOro：OOC03532a)9CGany (select orderSum from OrderMaster where salerNoY E2005002 and orderDate= 2008-l-9,)D&-?0 Score .Quryote 3006-1-9 ；ctiilorrfiiWoordaiSunkc/ocMo1:20)801 (W:帧5师2(05X22iXe0

26、103000：r.(0 W：iIWXOOW22CT801OC0(ECO)5(DME285X03reoiBooa?cooxi6310COlorcoore32CC0O21OD1C2C5TO1E2TOCO33Xe02-1900X602-1300atCOJ4291.60l：C0：65;8oiooai2moi202030! 000M 血1GG9C0iorcfl(DXG:a)8axcooGiC2O)5(IJM2(DKi32dX03400 and b productNo=a productNo group by a. productNo, aproductName, b.price22select a. p

27、roduccNo, a. productMsroe surr ib. quant ity) 口贷数至/ b. price Horn Produce a. OrdetDecail tovherc a. puoductPr ice400 :aid b productNductNogroup bv si productNo, a produetHMe. b puice结卑J消扃productNoproduct omc订货数price1P2cceccce用显翅7700.032P2CCGCCC1PcntiunZ 後10990.033pzcceccco龙圣?FT纯平显不爲4900.034P2

28、CCECCC2硕泰克SL-K&4NRLi後91100.035P2CCECCC3Penliuml OOCPU161200.03图2-910. 分别使用左外连接、右外连接、完整外部连接查询单价高于400元的商品编号、商品爼 称、订货数量和订货单价并分析比较检索的结果。select a. productNo, a. productName, sum(b quantity) i丁货数量,b pricefrom Product a left outer join OrderDetail b on & productPrice400 and b productNoa productNogroup by &

29、productNo, aproductName, b.priceselect a. productNo, a. productName, sum(b quantity) l货数量,b pricefrom Product a right outer join OrderDetail b on a. productPrice400 and b productNo=a productNogroup by a. productNo, aproductName, b.priceselect a. productNo, a. productName, sum(b quantity) iT货数量,b pri

30、cefrom Product a full outer join OrderDetail b on & productPrice400 and b productNo=a productNogroup by a. productNo, aproductame, b.priceSOQ丄Ordtrg rQLQtfdCtXo, .F：cduc oa a pioduccexicoOO ano b prooij-a prcduccKo G2Q by * - prodcXo. . psoducXaxa, b. pric*9leot a prootXo o produocMajrje b订赏

31、数量 b a“eco Product a rlghc aucas joinb 8 -&duuuP2r,uaOO abdb progotWn pxoduccKo G2丰 by a. prodcXo, . pscclucXaxa, b.pr&dctXc, . pscductKav . .： b订货故上.p=：ufrca Projot a full outer join Oxo*rD*wii b oc a pxoduccPxicexOO ar b pr8Rt&-4 prcduocMo G2行 by a. prcdctXo. . FsaducXa&a, b. price |23订曲pxa1

32、P20QSO00!UM DRAMMALMAIP2Q0WQ03MAXMAX1P2OOE0XHMAtMAL120550005ueMALM.U.F2OX1XI01MALP4XLiP2DO6OTJ2MALM.U.?P20P0001tiCCftMALP31rsuAuu545CAMXLMXLSaPApnxLcsHniTftfi1KULLMALMOOtNUUW-U.UOOIHULLMALKP1NULLPMX1X001NUUM.U.图 2-1011使用左外连接查找每个客户的客户编号、名称、订货日期、订单金额、其中订货日期不 显示时间日期格式为yyyy-mm-dd 按客户编号排序同一客户再按订单金额降序排序输

33、出。select a. customerno 客户编号,customername 客户名称.convert char 10). orderdate. 120 销售日期.ordersum销售金额from ordermaster a left outer join customer b on a. customerno二b. customerno)order by a. customerno. ordersum desc7w “ 旧匕 丁 =创saz -UT-aaLCfdDO 闪guaryW *cuiymi 容户宫户各称“ .cdrdas,：；、；镐書田诵色纟?rt3ft - 1 cusw*c t

34、 cn a oswex cswbce，ortr try cmtrTe ,crdrn arcMt3UMM1C2X5CGJ1XS1IIS； 302W4I钉4C3XCO3131E58lCDMfflMH-WBxwuaxc$H-BB1XJ /C20WWX五1H14I:C3XOCXCucx1cncxou4M：*3t)5郴冷MW湘W1133*W?rA3：2312C3XXCX0JXCO45X4TWucxccmiIMH12査找每个员工的销售记录 要求显示销售员的编号、姓名、性别、商品名称、数量.单 价.金额和销售日期 其中性别使用“男”和“女”表示 日期使用yyyy-mm-dd格式显示。select a. em

35、ployeeNo. a. employeeName、case sexwhen* F* then 女when* W then,男End sex. b productName d. quantity、 d. price, d. quantity*d. price 金额.orderDate=convert char 10:, orderDate.120from Employee a,Product b. OrderMaster c.OrderDetail dwhere a. employee。二c salerNo and b. productNo=d. productNo andc.orderNo二

36、d orderNo怙 MenMASQLSOTMrMmgmvtaufoT Mft. HM m rwn VM kftv*Uh MOM m for the IMt ride.M wJMiMM 二 aQQd -JA7.m C3 tMW : ; “ Z tiJU Q io, d ga“wd pri* 金直 artteyUbt* ./! Ktfca ZglQfzt t Fxcic b GrlrxBosvcs p-OrsOst-oil d Mbs cpl/aeSto-c.MrtCd rQ b.p;oa=%5to-d.pxDdu:tXw r4 c.ard-cl.DZOriCdYdiQ*t UOi ide 12

37、7A1Al 0Je 35g Ktco Mean td AM9 U lookD |j田yc9 |J ScorcDSX39* Zj r4：* sqt. 5nf nl?iE*d? xnfflT3 51ttWsW-x*s ，1ST AO 1 e I MW; S0， 呦 l.A.020 “TXT M1UXMi:t3ZXC2知1mmAE20WX?AM!iluxg-3)7mvaKOWX?mmUX8哆IXKALU1)u1% JllMR CAW 曲吃袖 iinrl isxxse* IS1RW & BKTE6 nxM cm ex 治 ttiW mac aac “ 1 3W1M 112 ac lXM EusxnXOKKJl仙图 2-1213.查询16MDRAM的销售情况要求显示相应的销售员的姓名、性别、销售日期、销售数量和 金额、其中性别用“男”