SQL实验(实验4至实验7的答案)

1、专业学习资料实验41.用 select 语句查询 departments 和 salary 表中的所有数据select salary .*, departments .*from salary ,departments2、查询 departments中的 departmentidselect departmentidfrom departmentsgo|出结未口渭息_departrnentdTiil22334AFs53、查询salary中的in come,outcome select in come outcome fromsalary goincomeoutcome3040j 一.15021

2、9832447. &B35043169 &3185,6552030100SQL实验123456Empl0yeelDInconeOutCbmeDepartment ID Department NameM000130401501财务部02001029601981财务部02D0132447 683501财务BJ1022013169.B8135 651财务部10220S2080WO1财务部T08991335938281.521财务部1110062587.0179.5B1财铸部NoteNULLULLNULLNULLNULLNULLNULL专业学习资料4、查询 employees 表中的部

3、门号，性别，要用 distinet 消除重复行：select distinet (departmentid ), sexfrom employees5、查询月收入高于 2000 元的员工号码select employeeid from salarywhere in come 2000gor _E结果消息employeeid1L. .2Q2001D3Q200184102201510220Sb108991111006210673卜S02566io3037595042096、查询 1970 年以后出生的员工的姓名和住址select name ,addressfrom employeeswhere b

4、irthday 1970 goO结果屆消息nameaddress1北京东路2王向容四牌楼iwoe专业学习资料3刘明虎距100-24朱俊牌楼巷731滋5张石兵解放路3442閃G林涛中山北路2357李玉琨热和路20期8叶凡北京西K 37-529刘朝武祝7、查询所有财务部的员工的号码和姓名select employeeid ,name from employeeswhere departme ntidin (select departme ntidfrom departme ntswheredepartme ntn ame=财务部)goI_I| q IHAAempkiyeeid narnemainB

5、I1 OTIXKJO：伍容华202OT10王向容8、查询 employees员工的姓名，住址和收入水平，2000 元以下显示为低收入20003000 元显示为中等收入 ，3000 元以上显示为高收入 ：select n ame ,address ,casewhe n in come -outcome 3000 the n 高收入else 中等收入end as 收入等级from employees salary专业学习资料where employees .employeeid = salary .employeeid gori *nameaddress收入等级J_四牌楼IORIOS中等收入2刘明

6、虏距路1弘2中等收入Ta朱f愛牌楼巷外106低收入4张石兵中等收入5林涛中山北路*35中等收入6李土氓热和路2W3高收入71叶几泊土北京西ESJ-7 52理敕水貯1中等收入9、计算 salary 表中员工月收入的评价数select avg (income )as 平均收入from salary1平均收入2330.30727272727 10、查找 employees 表中最大的员工号码无列名|iiiriiiiriiiriiniriiirn1? 2762I_ Jselect sum (outcome ) as总支岀from salary总支出U- 1983.03 j12、查询财务部雇员的最高实际

7、收入select max (income-outcome ) from salary ,employees ,departmentswhere salary .employeeid = employees .employeeid andemployees .departmentid =departments .departmentid and departmentname go=财务部专业学习资料13、查询财务部雇员的最低实际收入：select min (income-outcome) from salary employees ,departmentswhere salary .employ

8、eeid = employees .employeeid andselect employeeid,departmentidfrom employees where address like %中山%goemplo) ),Eeiddepartmentjd1 _210678L_ _315、查找员工号码中倒数第二个数字为0 的员工的姓名，地址和学历select education address ,name from employeeswhere employeeid like %0_gooeducationaddressname1-1本科i北尿东路1*2伍容华本科虎距路1皿2刘明3碘士牌楼巷31

9、06朱俊本科张石兵16、使用 into 字句，由表 employees 创建男员工 1 表，包括编号和姓名select employeeid ,nameinto男员工表from employees14、找出所用地址中含有中山”的雇员的号码及部门号=财务部专业学习资料where sex= 1 go9行受彫响17、用子查询的方法查找收入在2500 元以下的雇员的情况select * from employeeswhere employeeid in(select employeeid from salary where in come ALL(SELECTInCome FROM SalaryWHE

10、RE EmployeeID IN(SELECT EmployeeId FROM EmployeesWHERE Departme ntID IN(SELECT DepartmentIDFROM DepartmentsWHERE DepartmentName =财务部)Name1 i李玉琨i-丿19、用子查询的方法查找所有年龄比研发部雇员都大的雇员的姓名select namewhere Birthday 2500 )无列名)1i1iBIT 26、按部门列出在该部门工作的员工的人数select departmentid ,count (*) as人数from employeesgroup by de

11、partmentiddepartmentid人数J 123334145327、按员工的学历分组select education,count (*) as人数from employeesgroup by educationIpeducation ! Hl if人数f1译科夭专1IP硕士228、按员工的工作年份分组，统计年份人数select workyear ,count (*) as人数from employeesgroup by workyear1workyeari i人数|222333247158129、按各雇员的情况收入由低到高排列select employees .* salary .i

12、ncome专业学习资料from employees ,salary where employees .employeeid = salary .employeeid order by in comeErrpkjyccFDNameBrthdnySexWorktwrfti one Number DcpwtmcnilDiriwnft1朱僵顶士12牌SE-3-106847DBflt752nea2111006峯石兵1974JWlOT:QttOO.DOO11S45G341S531577-04-(12 M：i.00.CG012中山寸胸沏-39&347333200402OT1D壬冋容ISB2-12-O

13、EHXJ:OttQD.DOD 1210-0-IDS837S236115叶凡ISTg-ll-IBOOWOOCOO18:l师西K-r7-5380830133131.98W22A1剤明1972-1tHaffl:D(k00.Cm1383D6SQ5J163.8B7U邂默19WW-20MWMOOO13曲顽如547659114J5S0730、将员工信息按出生时间从小到大排列select * from employees order by birthday&7ipbwlDNameEcsbcation&nhdaySecWwfcYearAddiBssftmeNumtwrD-DartniiBntlD

14、1訂d涵19652 00:00 ( 00012畑直建车字1068470881752血瞒李玉琨1968.09-20t:0ft.0001工热和跻2mM765991431Q2201刘明1972-101# 00：0a：WDD01$虎距K1DO2836Q66DB541110M张石兵197idCMiT 00:00：関.000118456341855Q1D0OT活瘩华197603-3 00:00 00 00017北京东100-283321121121O67S1977-M-02 00:00:00:00012中山北路2W5831&73詰27308759叶几197B-11-1S00：0a：H)D01B北京

15、西跻3工畀&3308901331、在 order by 字句中使用子查询，查询员工姓名，性别和工龄信息，要求按实际收入从 大到小排列select name ,sex,workyear ,income -outcomefrom salary ,employees where salary .employeeid = employees .employeeid order by in come -outcome desc专业学习资料namesexwarkyear6E列名）1：李玉眾|133370.52刘明1329B4233叶凡182932.9斗王向容1227625张石兵112507.43&

16、amp;122479朱俊121980视图部分1、创建 viewl :Create view viewl asselect employees .employeeid ,name ,departmentname ,(income -outcome ) as come from employees ,departme nts, salarywhereemployees .departmentid = departments .departmentidandemployees .employeeid =salary .employeeidJj消息命令已应功完成.2、查询视图 employeeidem

17、ployeeidraiaiam1020010210220131022034111006521067863D25G67308759insert into view1 values (111111，谎言,1 ,30000)消息级别 视图或函数rviewl3、向视图 view1 中插入一行数据专业学习资料4、查看视图（没有影响）基本表：empbiseidnaiTiedepartmentnamecomehlQ20010王向容财务部27G21 2102201刘明市场部2984.23310220fi朱俊市炀部19804111W张石兵市场部2507.435210678袜涛经理办公室2479G3025李玉眾硏

18、发部3370.57308759叶凡经理办公室2933.9EmployBBIDNameEdueafranBtrthdaySBKWcwicYear.AddnassPhone NumberDepaitmentlD-197WJ3-2B 00:00 WW011北100-2833313211202001D唄士1982-12-09 00:00:00.(X1012EWKH)-1DB837323611102201刘明T9f72皿蚀OOgOfkOBO12庇生賂IOC炷呂跖礙讯541Q22D3朱宦碩土1523 Hl:QO：OQ(X)Q1 2W3-1D6B47Wgi755111006张石兵T97斗H血IT 00:00

19、:00.00011WJ5S33 34-1-203 84534165右21067B1&77Q2M):(M:OO.MCIi1中山北K24J58144733633O256E李玉匪l%B4i2D D0:M:0O.K13败礴2I&35S7659S14翊759197B-11-1g00:00：H018北京833CS901399023019640909 OO:OO:M.JO11854452133实验5查询该变量declare hy int set hy =( select in come -outcomefrom salarywhere employeeid =000001)select hy

20、2、使用运算符select name from employeeswhere birthday 1974-10-10name1:伍豁j2王向容34叶凡1、定义一个变量，用于描述 YGGL 数据库的salary表中，然后专业学习资料5刘朝3、 判断姓名为王林”的员工实际收入是否高于3000 元，如果是则显示高收入”，否则显 示收入不高于 3000 ”：if (select in comefrom salary employeeswhere salary .employeeid = employees .employeeidand employees .name =刘明) 3000 )select

21、 in come as 高收入from salary ,employeeswhere salary .employeeid = employees .employeeidand employees .name =刘明elseselect收入不高于Il i高收入711 3169.SS !i4、 使用循环输出一个*”三角形：declare i intdeclare j intset j = 20set i = 1while i 7-52经理办公室9990230刘朝经理办公室6、自定义一个函数，计算一个数的阶层create function hy(hy2 int)returns asbegindec

22、lare i intset i = hy2declare j intset j = 1while i 1beginint专业学习资料set j = j *iset i = i -1endreturn (j)enddeclare h intexec h = dbo .hy 4select h as jiechengjiecheng inmruHisimniriwi ”7、/*生成随机数*/select rand () 8、/*平方*/from employees专业学习资料select square (12)9、/*求财务部收入最高的员工姓名*/select max (name)from emp

23、loyeeswhere employeeid in(select employeeidfrom salarywhere employeeid in(select employeeidfrom employeeswhere departmentid in(select departmentidfrom departmentswhere departmentname=财务咅E )氐列名)I1至丙吞“haminniKmiBm !1980-01-01)8、 创建一个规则：create rule 11.1a.sLike1；C-20 xec sp bindrule 111r* employ皀 mm-w工t

24、y亡且工u 上eate table aalarySeraployeeid chfii - netr.ill primary k皀y.” income float noi;,outcome float nat foreign key employeeid6)UPDATE EmployeesSET Departme ntID =3WHERE EmployeeID = EmpIdENDEXEC UpdateDeptByYear 020010SELECT* FROM Employees WHERE Employeeid = 020010BipbyeelDMameEducationBrthdaSexV/

25、orkYearAddicssPhoneHinterDepartmentlD咖王鯛碩士1932J2屈DCQO:W.QDO12IM-IDSS379236112、根据每个员工的学历将收入提高元CREATE PROC UpdateInComeByEdu Employeeidchar (6)ASBEGINUPDATE SalarySET In Come = In Come + 500FROM SalaryLEFT JOIN EmployeesON Salary .EmployeeID = Employees .EmployeeIDENDEXEC Updatel nComeByEdu 020010专业学习

26、资料SELECT* FROM Salary where EmployeelDEmployeelDIncomeOutCome1 I 02D0W346Q15S3、游标：CREATE PROCEDURE Employees_biliASBEGINDECLARE i FLOATDECLARE j FLOATDECLARE Education CHAR (10)DECLARE Employees_cursor CURSORFOR SELECTEducation FROM EmployeesSET i =0SET j =0OPEN Employees_cursorFETCH Employees_curso

27、r INTO EducationWHILE (FETCH_STATUS = 0)BEGINIF(Education !=大专)SET i = i + 1SET j =j+1FETCH Employees_cursor INTO EducationENDCLOSE Employees_cursor=020010专业学习资料SELECTi AS本科及以上员工所占员工数SELECTj AS员工总数SELECT i/j AS本科及以上员工所占比例CLOSE Employees_cursorENDEXEC Employees_bili本科及以上员工及以上员工所占员工数11S11员工总数|e 1r” 1E

28、KI LIIT1円E11本科风以上员工所占比例丨D.SmS8SBS8SBS894、使用命令的方式修改存储过程的定义专业学习资料L性sL系odbdbdbabdb- dbgl娴 磁 存JJnc3!slJ视耶聯ass讯 田ss国严fflLJ口口曰新逹存储过程型）修改执疔存摘过程迢）编结存储过程阳本为查看依赖其系赖其系（V）重命名（M）刷新屈性5、对于YGGL数据库，表Employees的EmployeelD列与表Salary的EmployeelD列应该满足参照的完整性规则，请用触发器实现两个表的参照完整性：CREATE TRIGGER Salarynsert ON SalaryFOR INSERT,

29、UPDATEASBEGINIF(SELECT EmployeeID FROM INSERTED) NOT IN(SELECTEmployeeID FROM Employees )ROLLBACKENDCREATE TRIGGER Employeesupdate ON EmployeesFOR UPDATEASBEGINUPDATE SalarySET EmployeeID =( SELECT EmployeeID FROM INSERTED)WHERE EmployeeID = 000000专业学习资料专业学习资料WHERE EmployeelD =( SELECT EmployeelD FR

30、OM DELETED)ENDCREATE TRIGGER Employeesdelete ON EmployeesFOR DELETEASBEGINDELETE FROM SalaryWHERE EmployeeID =( SELECT EmployeeID FROM DELETED)ENDINSERT INTO SalaryVALUES (000005 ,2000,1000)消息盹，级别状态第1行 事务往触发器中结束.批处理已中止.UPDATE EmployeesSET EmployeeID = 000000WHERE EmployeeID = 990230j)UPDATE SalarySE

31、T In Come = In Come +(i-j)*500WHERE EmployeeID IN(SELECT EmployeeID FROM INSERTED)ENDUPDATE EmployeesSET WorkYear =7WHERE Employeeid = 990230SELECT* FROM Employees WHERE Employeeid = 990230500，若增加2年则增加1000,依次增加若工作时间减少则无变化SELECT INCOME ,OUTCOME专业学习资料看弓 為SSI 1-tt Oi S 1?T列Bffl履黠駛就沁H mi輛麟定的觑甦朕.该馳西此冲憨4于賤库阴砧*仙伍咖碍别 齣己紐.7.创建UPDATE触发器，当Salary表中In come值增加500时，outcome值增加50:CREATE TRIGGER SA_INCOME ON SalaryFOR UPDATEASBEGINIF(SELECTInCome FROM INSERTED )-(SELECT InCome FROM DELETED)= 500 )UPDATE SalarySET OutCome = OutCome +50WHERE EmployeeID =( SELECT EmployeeID FROM INSERTED )ENDSELEC