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

1、实验一1-1.查询员工的姓名、职务和薪水select employeename,headship,salary from employeex刊 0r4«r儿1 mselect eniloycenflm, department, salary frc» <»plcy«« ««depaitmerrfuuryan 15000802g科2000012coooo4ml业铮制410000n 511000085«imnnnr?0科2(0000m250000n smw址參料25000010豪代彷公立200000tii1200

2、00a«ikh2x)000h» 0 m)82) qr“r8 00 00 00 15 ft图1-12. 查询名字中含有“有限”的客户姓名和所在地 select customername, addressfrom customerwhere customername like 有限0s59 0r4«r. qverys. xfelect cuatoroernam, ekldress7from customrwhere cuscomrn<m>e like* *有限z «» 4曲cwtomcfnameaddrets1i公司天1市2公朋天*市3

3、联含tteeww公司上海市4万达垓协有公5天 itib5idkttmv 公旬0$-99 (b o it1) “ c52) orbrm 00 00 00 5 fj图1-23. 查询出姓“张”并且姓名的最后一个字为“梅”的员工。select *from employeewhere employeename like '张梅，06or4“ qb«rjr5. msmlecc fro» employee vb<r« «nloyeencude lxke 张梅.efvctoywnwne texbfthdeyhr«0mhmdshp如1 12000

4、02«nsfisnnxn ooooooooo13eo74o5ok200503*28000000000业务财<fus2400002 txmm*n»f197&1106000000000|5«p!5hx 大 jmwnuu200&1i+50000000$-59 ® 0 rm) “ e») orderob 00 00 00 2 fl图1-34. 查询住址中含有上海或南昌的女员工，并显示其姓名、所属部门、职称、住址，其中性 别用“男”和“女”显示select employeename,departmenta

5、addressisnull (convert (char (10) , birthdmy, 120),'不详 1)出生期，case sex when then '男.when 1f'then 1女1end as性别from employeewhere (address like 上海'or address like 南昌$') and sex=1f1413cltctir««h c. rvert cbar 10 bictbday.uo 不"出生日wl c«m mb vtettjj* tlh冃31bwismi*-r&#

6、171;2"吊"umirmosir3阳人 tdl'gjr4m対訂wimi宸$ihh1p24m5< «d4l»w1vzkxojr图1-45. 查询出职务为“职员”或职务为“科长”的女员工的信息select *from employeewhere (headship'职员，or headship='科长')and sex='f'£匕 5 b r t二二一 30,4amsselect fro« employeewhere h«adship '职员 beacuhip 科长

7、fnd »”"fisti j maiotpoymno皿机8mteciorwmbiyv-1 £ 205002w叩 6 00 00 00 000136o7mko1g20028000000000业务料240000(20k003f1973xxmk 000000 0001wj7305o25200md28 000000 000iww2(0000£20)5004f1%70105000000 00015907205134200m>28000000000410000(20x005“豪m1973c»o3 000000 00015co7kb243200m&am

8、p;28000000000业痔科180000(20x002hwf197>1m1000000000上市东大啊138078054(1200g-11 280000000001e«m2sooooe 2008003wrf3734571000000000101115807905578200x228000000000iww250000(20)7001mmm197kb12000000000开血1净hull2007428 00 00 00 000ikjm25000012(09002kftm1s724bu 000000 000nuu20*0228 000000000270000e 2006003x

9、mxf1972w5 000000 000m6九3叭nuu20(»02 28(»0000000业鼻段31000002006004f19721&中山trqnuu2000(52800000000034000006-5 8 0 m) “ g2) orskb 00 00 0010图1-56. 选取编号不在“c20050001”和“c20050004”的客户编号、客户名称、客户地址。select *from customerwhere customerno not in (,c20050001, /c200500041)06*59. 0r4«r

10、. q««r5.ftvselect from costomervhere customerno not m (ec2oo5oool 1# *020050004*<cmtomnocmtometnametcfephofwaddreis却1|c2oo5o»1tt-tt份有hi公司0223568021天it市2200122c2oo5om2兴曜jr份有限公it市2203013c200500030102121000:tj?(市1080014c20060j01大 mki4i0101168152北京市1008035c20060j02021 4568

11、451上海市2101006c20070j01i®市电j前制书心0791 44121523300467c20070j02世界技#开发公司0214564512上海市2102308c20070j03万事达股份有隈公司0224533141天it市2204009c20080j01ilfltjr份吉限公司0105421585北京市100800图1-6图1-67. 在订单明细表ordermaster屮挑出销售金额大于等于5000元的订单。 update ordermaster set ordersum=sum2from ordermaster a, (select orderno, sum (qu

12、antity，p:rice) sum2from orderdetailgroup by orderno)bwhere a.orderno=b.ordernoselect *from ordermasterwhere ordersum>=,5000,*»-9y»k1 ' j 一 “ 06-59. order. . . qaerr5. sqlbselect 1£roro ordermaster where ordersuxn > soool1<ocdernocuttomernotdecnoor decdateorders umnvoiceho

13、1(200801090002c20050004e2005003200801-0900000000083100010000000022200802190001c20050001e20050032008412-19 oqoooq 0001323200100000000432008050900010203002e200800220084js09000000000107560010000000094200806120001c20050001e200500220084k-120000000005705 6010000000105200901010001c20050001e200500220090101

14、0000000005790401000000011pb201206070001c20050004e200500320120607000000000813q001000000012图1-78. 选取订单金额最高的前10%的订单数据select top 10 percent *from orderdetail order by price desc» 7矽于匕5占p7 2圏906-91.order.qu«ry5. sql< 3#要2结里j mfiocdetnoptoducino quantty price12006012008061200012008050900013 2

15、012060700014 200802130001p200s0001 3p20060003 8p20060003 4120000120d00120q00200802190001p20080002 3110000200801090002p20080002noaooselect top 10 percent from orderdetail order by price desca i-89. 计算一共销售了儿种商品select count (distinct product no) =s 种类 from orderdeta衰-db«. ord«rd«ttil 衰 d

16、bo. 0rd«rl<st«r 06-87. 0r4«iselect ：ount distinct productno as 种类from orderdetail图1-910计算orderdetail表中每种商品的销售数量、平均价格和总销售量金额，并且依据悄售金 额山大到小输出。select productno 商品乖中类，coun七(*) quntityavg (price)平均价 格，sum (quantity*price)金额from orderdetdil group by productnoorder by 金额 desc舉-or“rsq.il |

17、曲血删卅»4 0r4omr 060 ord”owt5" 心孑 xcount ()quant icy,avg (price)平均价怙，平曲口*1(p20060003|30000001)200002p2008000231100 0000009900003p200800014890 000000woo 004p20cs00035300 000000g30000%4700000000<®000gp2o0m0127006000004203 go7p200e000333000000003(0000p2002000is2560000002b16003p2007000242

18、0000000028000010p2007q004322000000013200011p20050001480700000u14 0012p200600053100600000150900图 1-1011. 按客户编号统计每个客户2008年2月的订单总金额。selec t cus to memo, ordersumfrom ordermasterwhere year(orderdate)=2008 and month(orderdate)=27 sr *小匕f 7 7矗】3亠近吾<sore qqmkw-w (w4«v twfyl «<1« my cu*

19、tomrno.orcterswi fro* ortrbasttrwhere y<«< (ok<>erc>ate» -2006 aad mmiii order&ate ! j «*<*ranmrv> «*««4n»4k1 c20050001 ivoo2 cw0002 4291«)q eitemubikfi. s) 0i4«图 1-1112. 统计至少销售了 10件以上的商品编号和销售数量。 select productno 商品编号，quantity 商品数

20、廿from orderdewhere quantity>=10o 丄""a厅 7 s z a 7 sv 、淳；襄 dbo orderdettil 06-96. order. queryl. slt 06-96 scor select productno商品编号/quantity 商品数目 from orderdetailwhere quantity 10蘇j maw&iw wfl»b1 ip2ocbbo6r 112 p20050003 10图 1-1213. 统计在业务科工作且在1973年或1967年出生的员工人数和平均工资select count(

21、*)人数,avg(salary)平均工资from employeewhere department=,业务科，and (year(birthday)=1973 or year(birthday)=1967)ml爸j2鱼二 刮匚潭潭w06-70. score qutry2 sql 06-70. order. . . qoerl. .1mleec coum(w) aft/ *vg (salary 平均工资from employeevhere department 业务科 and (year (birthday) 1973 or year (birthday) 1967：结果a®平均工费1

22、7| 2642 857142图 1-13实验二1. 找出同一天进入公司工作的员工 select distinet a.employeenoa.employeenamea.hiredate from employee a,employee b where a.employeeno!=b.employeeno and a.hiredate=b.hiredate空 “；¥ z t q c 】匚 2 "丘 _06-t)gry2os-to.orr. . .qweryl.msselect distinct dezoployeeno, a.employeuneude ahxi:edcit

23、& frero employee employee b«.eroployeeno b.employ<«no and hiredate bhiredate|enpioyeenoecrptoyeewartehtfedate1£2006002200503-280000000002e 2005003张na20058280000000003e 2005004制b20058280000000004£2005005m3k20058280000000005£200800220080228000000000se 2006003sws20060228

24、000000000图2-12. 查找与“陈诗杰”在同一个单位工作的员工姓名、性别、部门和职务 select aemployeename,a.sex,adepartment,aheadship from employee a,employee bwhere a. department=b department and b empl.oyeen3me='陈诗杰妙 7" w rjmb 凰 g 二二谁吃06-70. scort. qu«r/206-70. order. query 1. sqlt asselect &croployeenaroerad.dep&

25、;irtroent,a.headship:i 1az e:np loyee where a.departmentab.department and benployeenaxne"结臬：maemptojeenametexdepartmentheadshp1 i 自袪i m1财务科科长2 丽杰m财务科3 张小f财务科合计3. 在employee表中查询薪水超过员工平均薪水的员工信息select *from employee awhere a.salary>(select avg(b.salary) from employee b)2 7 v牛也刍口二二潭潭孑(*t0. $«

26、;r« quots. ,ql 06-70.0r4«r q««ryl “ sk *<lecc froib biployee ahere lary select r/q b.salary from employee b ««fwctojwcnoerptoyeenametexb<tmeuletechonehreditedepartmenthdshpmtary1e2006001m196mv 京韦.“sim13817w5008200s4j24kooooooooowm5800802e2omxm鼻！1f1967

27、41x600000000015907205134200m&2800000000014长4100003e2006001m13651101 000000000m 韦山花1co1360770535220(k<sj(i0q(x1000主任4030 004e2008001时杰m196&okkoooooooooiiw!慑大学 12w33mxnull200w2-06000000000mm3200005e20(x004f19721013000000000hi韦中山til号null200b462800 0000 000340000ge2008005架hlf197oh4k 000000 00

28、0席城韦阳关大iilgnuu200&11n500000图234. 查找有销售记录的客户编号名称和订单总额select a.customerno.customername.ordernoumfquantity*price) ordersum from customer a,ordermaster bzorderdetail cwhere a.customerno=b.customerno and b.orderno=c.orderno group by a.customerno,a.customername,b.ordernox)>/u u 厂“二。匚o

29、二二06-70 sc©r< zry2 “】06-70 0r4»r . qweryl.mlselect cuatoaerno,customernane,b.orderno,3quantityprice ordersunf roat cuscosaer a, order raster b,orderdetail cwhere a.customerno b customerno b orderno c.orderno group by &customerno. cuscocdcrnanxf b.orderno ««cwtomeiuocutiome

30、vnameordwnoovdedum1c2oo5oo)!tx-wawm 公司200001090001310350?ctfnww五一用厦2008010900028310003200900)1公司2008010900031005 304c200500j1公司20060219000113232005c200700k潼界技术开发公司20080?! 900024291 306c200700c遒界技术开发公司 五一砂2008030100011663 so7c2oo5oom2008030200011161 408c200700j3万事坦叙材用公m2008030900013847009c20060cb2联合贱

31、份哥»公司2000050900011075600100200600)1扳一腋份丽公司2008061200015705 so11000500)1扳一收份有公司 五一用厦20090101000157904012c200500m201206070001813qoo5. 查询没有订购商品的客户编号和客户名称select a customerno,customernamefrom customer awhere acustomerno not in (select customerno from ordermaster )7- “ 匕 56 tt 乜障du m 二血06-70. scrt. q

32、u«ry2 iql* 06-70. order. . . queryl. sql 搞要select d customerno / customernaroefrom customer awhere a.customerrno not in iselect customerno from ordermasteri c2oo7ooo1南昌市电脑硏制中补j结果d消息cudometno (customername| c»»50002 |畑嚴份吉限公司 !c20050003 '上海生则究立c2006000大地?5城图2-56. 使用子查询查找32m dram的销售情

33、况 要求显示相应的销售员的姓名、性别、销售口期 销售数量和金额其中性别用“男”和“女”表示select employeenarr.ez case sexwhen 1m1 then 1男 1when 1f1 then '女'end as sex, b.o rderdate, c . quant i ty 销售数量,c qu 吕 nt i ty*c .price 金额 from employee a,ordermaster b, orderdetail cwhere a . employeeno=b sajlerno and b. orderno=c . crderno and c

34、 . productno in (select ductnofrom ordermaster d,orderdetail e,product fwhere d.orderno=eorderno and productname=132m dram')*70 s<w« qutry2 «<!06-t0 or4«r. . qveryl.select enployeenftm.case sexwhen *r* then 另' vhen f1 then 女.nd at sex, b. order date, c. quant icy 俐

35、flj 数畳 u quant ltyc. price 金额f roen uoployte «r orderketscer b, orderdetall cvbet a cwployccnob.sclcrno and bordcrno uorcrno ,r.d u pro<lucno xn < oclcc f - prolucno from orderneuscer d, orderdetttll «( product f where d.ordernoce.orderno and productnase * 32r drah*texordnditemffir11

36、女2008<n090000000005403502女200w18 000080005403503 h冷片男20088010000000008645 go4 iwt女20(»4»0200000&000216140图2-67. 查询0rdermaster表中订单金额最高的订单号及订单金额select orderno,sum(quantity*price) ordersumfrom orderdetailgroup by ordernohaving sum(quantity*price)=(select max(ordersum)from (select order

37、no,sum(quantity*price) ordersum from orderdetailgroup by orderno)b)"v y9nt 乂黑vj二二 l孑06-70. score. query2 sql* 06-70. order. . queryl. sql* 孺要select orderno,sum(quantity*price ordersum from orderdetail group by ordernohaving sum(quantity*price)=(select max(ordersum)from (select orderno,sure(quan

38、tityprice) ordersum from orderdetail group by orderno)b)结果_j消息ordernoocdetsum12(x1 爲订 13232.008在订单主表中查询订单金额大于“e2005002业务员在2008-1-9这天所接的任一张订单的 金额”的所有订单信息。select *from ordermasterwhere ordersum>any(select ordersum from ordermaster where salerno=,e2005002, and orderdate=,2008-l-9,)os-to score06-70 o

39、r4«r . qweryl.hsselect from ordermastervmre ordersum ny (select ordersum from order master where salerno 1 e2oosoo2 * cm order dace 200-1-9'ocdedocuhcmerno mfefnoordefo«ieorde«$5 nvoceno1 200ffl109000fc2oo5ooo1e20050022008010900000000031035010000000012 20001090002c2oo5ooo4e 200500

40、32008 0103 0000 00 0008j10 0010000000023 20002130001c2oo5ooo1£20050032008219000000000132320010000000044 2008jz190002c20070002e200800220084j2-1900 0000 0004291 8010000000055 2008d3010001c20070002e2007001200886 00 0000 00016696010000000066 200603020001c20050004e2008003zoowmdoqoqooooo1161401000000

41、0077 20m0hw妙 c20c>7ci：o3af$ c3 09 00 0000 o» ：«47 co 10000000018 c20ci524005 09 y ri? iyi ：iti0000000099 200608120001c20050001e2oo5oo22008如2000000 000570560100000001010 200901010001c20050001e2oo5oo220094)1 <n 00 0000 000579040100000001111 201206070001c20050004e2oo5oo32012807000000000

42、813000100000001212 201206070002c20070002e200900220120g07 000000 000ss78 001000000013图2-89.查询单价高于400元的商品编号 商品名称 订货数量和订货单价。select ductnductnamesumfb.quantityjit货数量，b.pricefrom product a,orderdetail bwhere ductprice>400 and ductno=ductnogroup by ductno,ductname,b.p

43、rice06-70. score. . . qu«ry2. sqlt 06-70. order. . . qwylselect & productnoz a pxroductname, sum >b quantity丿订货数 price from product a,orderdetail bwhere & productpr ice 400 and b productno a .piroductnogroup by a. productno, a pi:oductnaine,b .price1 irambopoz wfi示882 而丽st pent5$校770

44、0.0010890 003p20w0003龙基777ft疑平旻示磊4900 00p2og3p20®0003啖泰克sl-k8an4ge板 pentiumloocptj9161100 001200 00图2-910. 分别使用左外连接、右外连接、完整外部连接查询单价高于400元的商品编号、商品名 称、订货数量和订货单价并分析比较检索的结果。select ductnductname.sumfb.quantity)订货数量b.pricefrom product a left outer join orderdetail b on ductprice>400

45、 andb. productno=ductnogroup by ductno,ductname,b.priceselect ductno,ductname,sum(b.quantity)it货数量，b.pricefrom product a right outer join orderdetail b on ductprice>400 and ductno=ductnogroup by ductnductname.priceselect ductnductnameumf

46、b.quantityjij'ssb.pricefrom product a full outer join orderdetail b on ductprice>400 and ductno=ductnogroup by ductno,ductname,b.price127aoxord«ftx $qlqu«rylt|- m a fxoduccmo. a. pro<lucut<m. b.quantity 订货数 b .price trca fxoduct a * f < .<«o

47、rordetall b on .productpric«>400 q ducxo produculo«rcp by . produccno. a productn<m b a productno produatnm«. b quantity it黛毅. to prict"s froduet a *:ord«rd«ta&l b m pxod-jccfxic« 400 -】b protoctmo-a psoduccno” protfuetno productham.b pric«”x&qu

48、ot; a productmo,ductlum. b.quantity 仃贷数 b.price"oa product full;r oxo«rd«caxl b on .rxoducprice?40 b.pxo4ucxductxo9teur protfuculo. productham b mc |3"kduaw aodg&motjmtt prtot20050001 vmd4ua4nuu?20050003huu1nuu1dnuukxiotoooicast*nuuixxmoojnuu91gddrnuu12000002nuupo

49、auanopoaxtnamepmmu | maiit)5001nuuhixitc60c1nuukjux1070inuuhju1$100006nuuha115100w图 2-1011使用左外连接查找每个客户的客户编号、名称、订货日期、订单金额、其屮订货日期不 显示时间口期格式为yyyy-mm-dd 按客户编号排序 同一客户再按订单金额降序排序输出。select a . customerno 客户编号，customsrneme 客户名称，convert (char (10) , orderdate, 120)销售日期，ordersum 销售金额from ordermaster a left out

50、er join customer b on(a.customerno=b custcmerno)order by a.customerno,ordersum desc127aoxxero虫lquery人# mvxxc< cuitomrn©csf mm 富戶名矗：c . omroatw. 120 u日金sicrc* oraerbatcer a :ic«r .fi ewxmmrx-b «u««4mrfm«» a mli户mmin1q00w014.(h"绚tt-iw 细im;ooi000*0001040)qoowo

51、c884qomoooiu-«wefldnasoo>ooosqoowc-t6-r1domama11)000$6-mrmmttww7icjqqkmx联8»c2qqtwottwhfwwttw ew鼻4a1i010cwww»nocqocottttrufwiiw11hmsooeooo)*700u1)qootooola"胡ns瓯12.查找每个员工的销售记录 要求显示销售员的编号、姓名、性别、商品名称、数量、单 价、金额和销售日期 其中性别使用“男”和“女”表示 日期使用yyyy-mm-dd格式显示。select a employeeno,aemployeena

52、me,case sexwhen 1f1 then 1女 1when'm1then 1男 1end sex,ductneme,dquantity,d price,d qu吕ntity*d price 金 额，orderdate=convert(char(10) , orderdate,120)from employee ar product bz ordermaster c,orderdetail dwhere a employeeno=c.salerno and ductno=ductno andc o:rderno=d. orderno号 mfcroee

53、 sql serwrstudiot j 0 «4 w 3"协 omwm ae for the last ride.2丄 出83亠 ms j&>&» ifb(y ay tide9i m dmmmbbwm<o). 9iutmlorffm fqlqutfyl 珂.wtexe enlo*/«c*o*c cri88 8 m hro63 y ° 2 201nm0图 2-12u0m0c2 uomoo umoqu00kc2uoosoc2 vocaoo umottu0mx2*tjr »wvw>rjt etlfflfr )9卄jr meh星 fr wttw ff93旳 mo 00«39ocm.700 00u00 moo )008m00 00moo moo too« mw ivvmm nnrcr£&d *«s.b preduecsam s.qu&atxty d 9price 金h.ohsxdaefr«i ewloy«« e. vroikt b.0r4«i>ut* o