SQL存儲過程試題及答案

1、-寫存儲過程及調用存儲過程/*1.寫出創(chuàng)建分數(shù)存儲過程用于計算某門課程成績最高分、最低分、平均分，參數(shù)課程號。*/-2.寫出統(tǒng)計某門課選人數(shù)的存儲過程，輸入?yún)?shù)課程號，輸出參數(shù)人數(shù)。/*3.創(chuàng)建存儲過程，要求根據(jù)學生姓名查看學生的籍貫.（要求:在存儲過程里定義兩個參數(shù)，第一個接收由調用程序指定的輸入值（學生姓名），第二個參數(shù)用于將該值返回調用程序）*/*4.程序員工資表：ProWage字段名稱數(shù)據(jù)類型說明IDint自動編號，主鍵PNameChar(10)程序員姓名Wageint工資創(chuàng)建一個存儲過程，對程序員的工資進行分析，月薪1500到10000不等，如果有百分之五十的人薪水不到2000元，給

2、所有人加薪，每次加100,再進行分析，直到有一半以上的人大于2000元為止，存儲過程執(zhí)行完后，最終加了多少錢？例如：如果有百分之五十的人薪水不到2000,給所有人加薪，每次加100元，直到有一半以上的人工資大于2000元，調用存儲過程后的結果如圖：一共加薪二5加元加薪后的程序員H資列表,ir1春鳥£002百iaoo3李四19004二月S藍笑2980請編寫T-SQL來實現(xiàn)如下功能：查詢是否有一半程序員的工資在2200、3000、3500、4000、5000或6000元之上，如果不到分別每次給每個程序員加薪100元，至之一半程序員的工資達到2200,3000,3500,4000,5000

3、或6000元。/*5.編寫一個存儲過程PR_GET_PASS_RATE統(tǒng)計某門課程的及格率，其傳入?yún)?shù)是課程號P_CNO,傳出參數(shù)是該課程成績的及格率P_PASSRATE,及格率的格式形如：86.56%。6 .創(chuàng)建觸發(fā)器T_1,功能是當向數(shù)據(jù)表學生添加記錄時，顯示學生的信息。createtriggerT_1on學生afterinsertasselect*from學生insertinto學生values（'3001','李四'，'男','計本10'）7 .創(chuàng)建觸發(fā)器T_2,功能是當向數(shù)據(jù)表班級添加、修改和刪除記錄時，顯示學生的信息。

4、createtriggerT_2on學生afterinsert,update,deleteasselect*from學生8 .創(chuàng)建觸發(fā)器T_3,功能是當修改班級班號，同步更新學生表的班號。9 .創(chuàng)建觸發(fā)器T_4,功能是當刪除學生表的記錄時，同步刪除選課表中的選課信息。createtriggerT_4on學生afterdeleteasdeclarexhchar(10)select*卜=學號fromdeleteddeletefrom選課where學號=xhdeletefrom學生where學號='3002'/*1.寫出創(chuàng)建分數(shù)存儲過程用于計算某門課程成績最高分、最低分、平均分,參數(shù)

5、課程號。*/usexsglgocreateprocedureaa(程名nchar(16)asbeginselect課程號，最高分=max(成績，最低分=min(成績),平均分=avg(成績)from成績where課程號=課程名groupby課程號endgoexecuteaa'001'- -2.寫出統(tǒng)計某門課選人數(shù)的存儲過程，輸入?yún)?shù)課程號，輸出參數(shù)人數(shù)。usexsglgocreateprocedurebb課程號char(10),人數(shù)intoutputasbeginselect人數(shù)=(selectcount(課程號from成績where課程號=課程號)enddeclare人數(shù)in

6、t,課程號char(4)set課程號='002'execbb課程號,人數(shù)outputprint'課程號為'+課程號+'的人數(shù)：'+cast(人數(shù)aschar(2)/*3.創(chuàng)建存儲過程,要求根據(jù)學生姓名查看學生的籍貫.(要求:在存儲過程里定義兩個參數(shù),第一個接收由調用程序指定的輸入值(學生姓名),第二個參數(shù)用于將該值返回調用程序)*/usexsglgocreateprocedure8(姓名char(10),籍貫char(10)output)asbeginselect籍貫=(select籍貫from學生where姓名=姓名)enddeclare姓名c

7、har(10),籍貫char(10)set姓名='廖小小'execcc姓名,籍貫outputprint'學生'+姓名+'的籍貫是：'+籍貫USEWageGOCREATETABLEProWage-程序員工資表(IDintidentity(1,1)primarykey,-工資編號PNameCHAR(10)NOTNULL,-程序員姓名WageintNOTNULL-工資)GO- -1、創(chuàng)建存儲過程-ifexists(select*fromsysobjectswherename='Sum_wage')dropprocedureSum_wag

8、eGOcreateprocedureSum_wagePWageint,AWageint,totalintaswhile(1=1)beginif(selectcount(*)fromProWage)>2*(selectcount(*)fromProWagewhereWage>=PWage)updateProWagesettotal=total+AWage,Wage=Wage+AWageelsebreakendprint'一共加薪：'+convert(varchar,total)+'元'print'加薪后的程序員工資列表：select*fromP

9、roWage- -調用存儲過程1-execSum_wagePWage=2000,AWage=100,total=0execSum_wagePWage=2200,AWage=100,total=0execSum_wagePWage=3000,AWage=100,total=0execSum_wagePWage=4000,AWage=100,total=0execSum_wagePWage=5000,AWage=100,total=0execSum_wagePWage=6000,AWage=100,total=05：編寫一個存儲過程PR_GET_PASS_RATE統(tǒng)計某門課程的及格率，其傳入?yún)?shù)是

10、課程號P_CNO,傳出參數(shù)是該課程成績的及格率P_PASSRATE,及格率的格式形如：86.56%。createorreplaceprocedurePR_passrate_count(p_cnochar,P_PASSRATEoutchar)asv_passcntint;v_totalcntint;begin-先求及格人數(shù)selectcount(*)intov_passcntfromscwherecno=p_cnoandgrade>=60;-若及格人數(shù)為0則給出結果ifv_passcnt=0thenP_PASSRATE:='0%'elseselectcount(*)into

11、v_totalcntfromscwherecno=p_cno;P_PASSRATE:=to_char(round(100*v_passcnt/v_totalcnt,2)|'%'endif;end;SQL實驗實驗41.用select語句查詢departments和salary表中的所有數(shù)據(jù):selectsalary.*,departmentsfromsalary,departmentsJ結果_j消息EmployeelDIncomeCXitComeDepartmentIDDepartmentMameNotefl3MIM1財務部NUZ2"oSoii)201981財篝部rJU

12、LL3M0182W683501財務哥NULL4102201316983185651財富部rJULL5am1001財莠部NULLG1OB9S1溺998261521財多部NULL72507口795B1財勢部NULL2、查詢departments中的departmentid:selectdepartmentidfromdepartmentsgo3、查詢salary中的income,outcomeselectincome,outcomefromsalarygo1irieomeouteoin41，慟2"i=BilliiviiiiimmiiBiii!29019By244763350431G9.8

13、S185.65520CO1004、查詢employees表中的部門號，性另1J,要用distinct消除重復行:selectdistinct(departmentid),sexfromemployees5、查詢月收入高于2000元的員工號碼:selectemployeeidfromsalarywhereincome>2000go6、查詢1970年以后出生的員工的姓名和住址:selectname,addressfromemployeeswherebirthday>1970go二I結果3消息name：address1住容華j北京東路1gN2主向容四牌樓噩3劉明虎距蹄同作74朱俊牌樓替黑

14、口施5里石兵解放踴摑14Q3林濤中山北路續(xù)站7李民物翔蹺國土33葉凡北京西路37-523現(xiàn)朝儆7、查詢所有財務部的員工的號碼和姓名:selectemployeeid,namefromemployeeswheredepartmentidin(selectdepartmentidfromdepartmentswheredepartmentname='財務部')gotwa*小|iqjmaemployeeidneameTiQ1000Q_'何容華2'oSoid-'-壬向容8、查詢employees員工的姓名，住址和收入水平，2000元以下顯示為低收入，200030

15、00元顯示為中等收入，3000元以上顯示為高收入：selectname,address,casewhenincome-outcome<2000then'低收入'whenincome-outcome>3000then'高收入'else'中等收入'endas'收入等級'fromemployees,salarywhereemployees.employeeid=salary.employeeidgo一Tsvnameaddress收入等如1王向容:四牌樓中等收入2劉明虎距跖1見2卬等收入3朱僮牌樓巷低收入4張石兵解散路3412

16、W中等收入5林濤中山北路?435中等收入£李玉堤熱和路2好3高收入7_葉兄北京i於工52中等收入I土方俏知f%個衿I9、計算salary表中員工月收入的評價數(shù)：selectavg(income)as'平均U入'fromsalary平均收入一aaiBiiiaiii(,ni112920J0727272727j10、查找employees表中最大的員工號碼：selectmax(employeeid)as'最大員工號碼'fromemployees平均收入Iill-hllianiHllnailllt«1113aaiIiA11111IlliMil、1j2

17、32030727272727|salary11、計算salary表中的所有員工的總支出：selectsum(outcome)as'總支出'from14聲支出_irimw"'wmaiinirC12、查詢財務部雇員的最高實際收入：selectmax(income-outcome)fromsalary,employees,departmentswheresalary.employeeid=employees.employeeidandemployees.departmentid=departments.departmentidanddepartmentname=&#

18、39;財務部goII比藥坨4-AllII-SI.bJIII、1I27E213、查詢財務部雇員的最低實際收入：selectmin(income-outcome)fromsalary,employees,departmentswheresalary.employeeid=employees.employeeidandemployees.departmentid=departments.departmentidanddepartmentname='財務部go14、找出所用地址中含有“中山”的雇員的號碼及部門號:selectemployeeid,departmentidfromemployee

19、swhereaddresslike%中山goI-bJ-Li5'|Jgg|employeeiddepartmentidi酉麗£.一?3raniiiTmiim'iiirE15、查找員工號碼中倒數(shù)第二個數(shù)字為0的員工的姓名，地址和學歷:selecteducation,address,namefromemployeeswhereemployeeidlike%0_'go看必叮前0naddressname1,祠北京東路1小2伍君華2»":jiiim-iim-nim曲成距路WK2劉明I碩士牌窿巷54*106朱俊4鈉解能路341-2G3張石兵16、使用in

20、to字句，由表employees創(chuàng)建“男員工1”表，包括編號和姓名:selectemployeeid,nameinto男員工表fromemployeeswheresex='1'go行行愛影響17、用子查詢的方法查找收入在2500元以下的雇員的情況:select*fromemployeeswhereemployeeidin(selectemployeeidfromsalarywhereincome<2500)go£np|n沙"門WiffiFHHSrten日KhWwkTurAddmsPv1rwhijmisirg：i口才mpnflJ1朱僮穎上136&

21、09-Zfi(».C0.W.Q(H12牌樓蕾第106MTDSSr518、用子查詢的方法查找查找研發(fā)部比所有財務部雇員收入都高的雇員的姓名：SELECTNameFROMEmployeesWHEREEmployeeIDIN(SELECTEmployeeIDFROMSalaryWHEREEmployeeIDIN(SELECTEmployeeIdFROMEmployeesWHEREDepartmentIDIN(SELECTDepartmentIDFROMDepartmentsWHEREDepartmentName='研發(fā)部')ANDInCome>ALL(SELECTIn

22、ComeFROMSalaryWHEREEmployeeIDIN(SELECTEmployeeIdFROMEmployeesWHEREDepartmentIDIN(SELECTDepartmentIDFROMDepartmentsWHEREDepartmentName='財務部')Meme1至寶，EF，I”，T19、用子查詢的方法查找所有年齡比研發(fā)部雇員都大的雇員的姓名：selectnamefromemployeeswhereBirthday<all(selectbirthdayfromemployeeswheredepartmentidin(selectdepartmen

23、tidfromdepartmentswheredepartmentname='研發(fā)部)n3rr號>Si：iIlliSi俳倭i20、查詢每個員工的情況及其薪水的情況：selectemployees.*,departments.departmentnamefromemployees,departmentswhereemployees.departmentid=departments.departmentid營似甘的ID出發(fā)L5S曲Add版sFpMufw1州JBO:像石學和1雌0M&NLM5091miiui1時麗2曲加TW俱J.iMMmouotw.oeo12工心睢1陶勢器3H明

24、切WTlCHaOttMOO.OOB1JMt跖口比£市場郎41022W熏他5H:19KW2BWWM.0QC12WWP5市場倒5111006串，11宰魄&第1匕駒wsfewia5市場苧fi21DETS收大事13774M2HtoDft(B0a01£.患i346n«i技理;心手7前25耕中士出和1曲如gHKlQOG3K1E59BTd21、使用內連接方法查找不在財務部工作的所有員工信息:selectemployees.*fromemployeesinnerjoindepartmentsonemployees.departmentid=departments.depa

25、rtmentidwheredepartmentname!='財務部EiiJoiselEWa-TteBril.VjrkYecA無器時FlKrwtJ二七王：孝力口1102201劃西鈿is7in)-isnoMaaooa1面瞪1附2£3盛糕065Tz1QZ2D3A":阻DO皿gOOO1卿？巷堤IMS23B1/5p11TDW理石兵*191001D:aD.LLU1184&G34IB5Ti21DC78林有犬告怡TTtU怔M的歸.or。i1中山北吊”哈i3026gG季ME以1蛉aMDGM.DM13熱加黯?07ES7G55S14W5i葉凡鈉1573-1-1SX'lOZ

26、Z.DCCi起二隆工路工不登E33J3MI13的02式I劉朝_isw-wranomocLDKi_吻EW再兜7322、使用外連接方法查找出所有員工的月收入:selectemployees.*,salary.incomefromemployeesjoinsalaryonemployees.employeeid=salary.employeeid邑軍1號人。ManeEa心工nBittdajM.Addt&si.PhcneMjmber2&_a：jf&rtJDincome1：血1口1壬向春13B2S1Z4EDafflr®.1Z曲曜KHHOB羽用2的1IZ3&-21

27、G2XI1則1軌4小憎眥MOOMfi11空西a3士拈E至6外的8s3ID22K菱按13t5G2BDthDftm.OOO12理怦看上£羽&4mB8l75zsrn111006主石兵lS74-lMlMD03CiDK11%*341屯S2£fl7.0l5210673弗1977-HCiJ&XDX12中山北流亂差加?13632G&3630S66李玉電1救州，附DOJOCKK1工5&7E5SS143Sa0.7|7配B；湖葉凡1s7SI1-1EQDtXDX'1£4葺據(jù)H133B1.SE23、查找財務部雇員的最高收入:selectmax(inc

28、ome)fromsalarywhereemployeeidin(selectemployeeidfromemployeeswheredepartmentidin(selectdepartmentidfromdepartmentswheredepartmentname='財務部)疣列名）24、查詢財務部雇員的最高實際收入：selectmax(income-outcome)fromsalarywhereemployeeidin(selectemployeeidfromemployeeswheredepartmentidin(selectdepartmentidfromdepartments

29、wheredepartmentname='財務部)25、統(tǒng)計財務部收入在2500元以上的雇員人數(shù):selectcount(employeeid)fromemployeeswheredepartmentidin(selectdepartmentidfromdepartmentswheredepartmentname='財務部')andemployeeidin(selectemployeeidfromsalary26、按部門列出在該部門工作的員工的人數(shù)：selectdepartmentid,count(*)as人數(shù)fromemployeesgroupbydepartment

30、iddepartmentid人數(shù)1X233k1327、按員工的學歷分組：selecteducation,count(*)as人數(shù)fromemployeesgroupbyeducation28、按員工的工作年份分組，統(tǒng)計年份人數(shù)：selectworkyear,count(*)as人數(shù)fromemployeesgroupbyworkyearworkyear人數(shù)22333247153129、按各雇員的情況收入由低到高排列:selectemployees.*,salary.incomefromemployees,salarywhereemployees.employeeid=salary.employ

31、eeidorderbyincomeEpiplayfrlD訕1M570mPhen.N5Hh£pmr1傳電"3650123OC-M.OCOCC12I特f7347陷175ZDBO2r-'I-安£序ttrorooogo1153507JI3內漳HEM皿BMOO00012中皿SMS習啊*3422OC1Q子限782-124)9DC-MWOCC12皿協(xié)打抄1戰(zhàn)印箱用1ZS&C5制£二招葉凡1mli-IgmHhOQ頌1g北333Dtgffll33131就靜現(xiàn)明口另*1中日gpkdoux13E36C50£5:n鱷弱7州厘s李玉地DO燦:OCOK13

32、430、將員工信息按出生時間從小到大排列:select*fromemployeesorderbybirthdayEmpogellZtJar-eEiIli匚db口riEtit七wSexA=d-«sP-Dre-Ntn!-Dqna-lire-tlDI未俊23tlX:K.3C-L12牌堞蒼52工E8-TMS175;之宏我T96B2flDOffi：Da1a58765951.<h劉典1383co的的54.moos至石耳1總成整斗1立的5月01C00D布言單97622COX:K.X"217JT上慶我埼1X-2如1型11s2WAf977-aMJ2DBK：D：12中山內力S&4

33、35B34fi733fi37葉凡973-11-aI21X:tX；.X-：ig上京古混廠里923033C'1331、在orderby字句中使用子查詢，查詢員工姓名，性別和工齡信息，要求按實際收入從大到小排列：selectname,sex,workyear,income-outcomefromsalary,employeeswheresalary.employeeid=employees.employeeidorderbyincome-outcomedescname3*比列名：1李玉旭j13J37U52劉明1329342a3葉凡182332m4王向容112芯25張石兵112M741G122

34、4797朱窿121380視圖部分1、創(chuàng)建viewl:Createviewviewlasselectemployees.employeeid,name,departmentname,(income-outcome)ascomefromemployees,departments,salarywhereemployees.departmentid=departments.departmentidandemployees.employeeid=salary.employeeidLj梢息命令已成功兄成.2、查詢視圖employeeid:n一emplDiyeeidniimaminif1 D2W102后汨一

35、31022084111006521W78630256673037593、向視圖view1中插入一行數(shù)據(jù)：insertintoview1values('111111'，謊言'，'1','30000')消息448,級別16,狀態(tài)L,第二行現(xiàn)引恚函皴不可更新，因河修點4、查看視圖（沒有影響）基本表:emplaeeidnamedepartmentnamecome1!020010!王同容財勢部27622102201劉明市場部298423310220A朱健市場部19304llltXK張石兵市場部2507.4352107fl林濤經(jīng)理辦公室23630擊時

36、李玉“就段部337057308739葉凡經(jīng)理辦公室29329及nidaglD高收入pkiiiitaanilnntinim儂居iSanKBdjcaorBrhdW2柏色arAddircePhcnEl<jTiiHrCoaranrtlD1巧苕¥軍科l97M'12e.D0:DDKC-3C1rJ叵本蹈1t)d2艮史2因112后Fi!bn!irn，/"amm(Koaio至向若強士m2曲聞:時加M)Q12EJ035I1J1022M劃里聿科1B72-1-3-1E.&：.GOX'C"3C13上江”1二？g提吟色54.1Q22&S采俄125.3-1

37、06B44kE!175£iirx&關王兵考科1974-1M1聞僮HMMO11解能毫3*4例口知冥1金Eg210678林忠戈與1977-£4.2200:DD'K&K12中山:ti82*空53671363730256ft軍三出主因19G&gaDOO：g0Q0001j拄知骷20輸總F煙14&30S7S9葉凡聿網(wǎng)1973-11-1£DC.COXCOt1&1響西跑工：23q制8!不料1小弟DlLU克bOL11L0tiSBEG£213：3實驗51、定義一個變量，用于描述查詢該變量：declarehyintsethy=(

38、selectincomefromsalarywhereemployeeidYGGL數(shù)據(jù)庫的salary表中000001號員工的實際收入,-outcome='000001')然后2、使用運算符“>”：selectnamefromemployeeswherebirthday>'1974-10-10'Iname;p，r，E，EUT1 l便燮打2 王同容3 林濤w葉凡5朝朝3、判斷姓名為“王林”的員工實際收入是否高于3000元，如果是則顯示“高收入”顯示“收入不高于3000”：if(selectincomefromsalary,employees劉明wher

39、esalary.employeeid=')>3000)selectincomeas'高收入'fromsalary,employees劉明wheresalary.employeeid=elseselect'收入不高于'4、使用循環(huán)輸出一個“*”三角形：declareiintdeclarejintsetj=20seti=1whilei<jbeginprint(space(j-i)/2)+replica

40、te('*',i)seti=i+2end4*#J444上-X-4A上Jr44事*Ab匕&5/晶普、右、i4上1占AA134上事人髭1缶1XJt縣上占工上事人晶上山上上上H4、按部門進行分類，使用if語句實現(xiàn)：Createfunctionhy1(departmentid1char(3)returnschar(10)asbegindeclarehy1char(10)if(selectdepartmentiddepartmentid1=departmentidsethy1='財務部'if(selectdepartmentiddepartmentid1=depa

41、rtmentidsethy1='人力資源部if(selectdepartmentiddepartmentid1=departmentidsethy1='經(jīng)理辦公室'if(selectdepartmentiddepartmentid1=departmentidsethy1='研發(fā)部if(selectdepartmentiddepartmentid1=departmentidsethy1='市場部'returnhy1fromdepartmentswhere)='1')fromdepartmentswhere)='2')

42、fromdepartmentswhere)='3')fromdepartmentswhere)='4')fromdepartmentswhere)='5')endselectemployeeid,name,address,dbo.hy1(departmentid)fromemployeesemployeetdnameaddress比列旬110000任容華北京家路1DQ2r娜募玩一一2(321X)70王阿容四牌樓1WTI四財務部3102201劉明虎距路1MK2市場部41022Q8朱俊牌樓巷5-106市場部51110D6張石兵解放跖341割3市場部6

43、210678林濤中山北路24-35經(jīng)理辦公室7J3025S李玉娟熱和需2口38研發(fā)部330B759葉凡北京西路工52經(jīng)理辦公室9駟0230劉朝武漢履理辦公室selectemployeeid,name,addresscasedepartmentidwhen1then'財務部when2then'人力資源部when3then'經(jīng)理辦公室when4then'研發(fā)部when5then'市場部endas部門號fromemployeesemrilay&Eidnameaddress骸門號1010000伍容華北京東路1的2明多部_202OT10壬向容四犍樓1tHM

44、c唱財易部,3102201劉明虎距路1%2市場部4朱俊睥摟桂4X06市場部15111006張石兵解放路34/-203市場部e210673林濤中山1噴&2435經(jīng)理辦於室73025tS李正岷施和路研發(fā)部8M8753葉凡北京西踴£7-52鮮理辦公室19990234)劉朝武漢經(jīng)理辦公室6、自定義一個函數(shù)，計算一個數(shù)的階層：createfunctionhy(hy2int)returnsintasbegindeclareiintseti=hy2declarejintsetj=1whilei>1beginsetj=j*iseti=i-1endreturn(j)enddeclareh

45、intexech=dbo.hy4selecthas'jiecheng'jiediengliTHUTHI，1惚j7、/*生成隨機數(shù)*/selectrand()8、/*平方*/selectsquare(12)9、/*求財務部收入最高的員工姓名*/selectmax(name)fromemployeeswhereemployeeidin(selectemployeeidfromsalarywhereemployeeidin(selectemployeeidfromemployeeswheredepartmentidin(selectdepartmentidfromdepartment

46、swheredepartmentname='財務部')fromsalary/*聚合函數(shù)與groupby一起使用*/selectworkyear,count(*)as人數(shù)fromemployeesgroupbyworkyear/*將字符組成字符串*/selectchar(123)/*返回字符串左邊開始的個字符*/selectleft('abcdef',2)/*返回指定日期時間的天數(shù)*/selectday(birthday)fromemployeeswhereemployeeid='010000'/*獲取當前時間*/selectgetdate()實驗

47、61、創(chuàng)建索引：createuniqueindexhuangyanonemployees(employeeid)2、/*用createindex語句創(chuàng)建主鍵*/3、重建表employees中employeeid列上的索引alterindexhuangyanonemployeesrebuild4、刪除索引：匕工QF匕口目ngTan口匚七陽各<_j消息|命令已成功完成.5、創(chuàng)建一個新表，使用一個復合列作為主鍵，作為表的約束，并為其命名：createtableemployees5(employeeidchar(6)notnull,namechar(5)notnull,sextinyint,ed

48、ucationchar(4),constraintyanprimarykey(employeeid,name)為新表添加一列：altertableemployees5addaddresschar(10)6、創(chuàng)建新表student，性別只能包含男或女：createtablestudent(號碼char(6)notnull,性別char(2)notnullcheck(性別in(男，女)7、創(chuàng)建新表：createtableemployees7(學號char(10)notnull,出生日期datetimenotnullcheck(出生日期>'1980-01-01')8、創(chuàng)建一個規(guī)

49、則：createrule111as&listliXe1'execspbin<lruLe11111r'employee.workyearcreatetabsalaxySenjploye&idchar6njtn_llprimarykeyF±ncom£floatnotnull,out?？诙縡laeL口。匕匚口二二,icreignkeyeraployeeid_J消息|F輔則綁定到康麗:9,創(chuàng)建salary2:createtablesalary2(employeeidchar(6)notnullprimarykey,incomefloatnot

50、null,outcomefloatnotnull,foreignkey(employeeid)referencessalary(employeeid)onupdatecascadeondeletecascade)10、添加一個外鍵，salary與employees有相關記錄，則拒絕更新employeesaltertablesalaryaddconstraintkc_forforeignkey(employeeid)referencesemployees(employeeid)ondeletenoactiononupdatenoaction像加一力夕健與叼1uyw睛梯諄,貝悅版新=F-*8我有做

51、出笨*：-=：_；='，一:；.：5'；：.-：L匕門：fareigien7lcyeeireferttcesstrplovetsl*nrloveeidi"一-".H.">'.邕電表工F3一中組LC。，喇上的舌引Valterivjexs_lC3.內型"：曰p二uyeeldret-d.111消目消息,，斯耳，胡頹。，第二F,-.一,_MIERMHLE否句后TCSEraKKEY分束”匚不一明臾，式產(chǎn)臾發(fā)生預據(jù)庫""i=L，表"dhi一己舊1二3亡1=：51匚nlLEm'EnlDyeeE'

52、;K實驗71、工作年份大于6時，跟換科室到經(jīng)理辦公室(根據(jù)員工)：CreatePROCUpdateDeptByYear(EmpIdchar(6)ASBEGINDECLAREyearintSELECTyear=WorkYearFromEmployeesWHEREEmployeeID=EmpIdIF(year>6)UPDATEEmployeesSETDepartmentID='3'WHEREEmployeeID=EmpIdENDEXECUpdateDeptByYear'020010'SELECT*FROMEmployeesWHEREEmployeeid='020010'Eirdoyee0NarnEdjatixKfc3ySacWmYearAcdrsssPncrtTJjrbpr馨前naiD廠而而?王解虹13fl四掰00忸(0001I匹用瞿KMM解S3792J6I12、根據(jù)每個員工的學歷將收入提