




版权说明:本文档由用户提供并上传,收益归属内容提供方,若内容存在侵权,请进行举报或认领
文档简介
SQL实验实验41用select语句查询departments和salary表中的所有数据:selectsalary.*,departments.*fromsalary,departments2、查询departments中的departmentid:selectdepartmentidfromdepartmentsgo3、查询salary中的income,outcome:selectincome,outcomefromsalarygo4、查询employees表中的部门号,性别,要用distinct消除重复行:selectdistinct(departmentid),sexfromemployees5、查询月收入高于2000元的员工号码:selectemployeeidfromsalarywhereincome>2000go6、查询1970年以后出生的员工的姓名和住址:selectname,addressfromemployeeswherebirthday>1970go7、查询所有财务部的员工的号码和姓名:selectemployeeid,namefromemployeeswheredepartmentidin(selectdepartmentidfromdepartmentswheredepartmentname='财务部')go8、查询employees员工的姓名,住址和收入水平,2000元以下显示为低收入,2000~3000元显示为中等收入,3000元以上显示为高收入:selectname,address,casewhenincome-outcome<2000then'低收入'whenincome-outcome>3000then'高收入'else'中等收入'endas'收入等级'fromemployees,salarywhereemployees.employeeid=salary.employeeidgo9、计算salary表中员工月收入的评价数:selectavg(income)as'平均收入'fromsalary10、查找employees表中最大的员工号码:selectmax(employeeid)as'最大员工号码'fromemployees11、计算salary表中的所有员工的总支出:selectsum(outcome)as'总支出'fromsalary12、查询财务部雇员的最高实际收入:selectmax(income-outcome)fromsalary,employees,departmentswheresalary.employeeid=employees.employeeidandemployees.departmentid=departments.departmentidanddepartmentname='财务部'go13、查询财务部雇员的最低实际收入:selectmin(income-outcome)fromsalary,employees,departmentswheresalary.employeeid=employees.employeeidandemployees.departmentid=departments.departmentidanddepartmentname='财务部'go14、找出所用地址中含有“中山”的雇员的号码及部门号:selectemployeeid,departmentidfromemployeeswhereaddresslike'%中山%'go15、查找员工号码中倒数第二个数字为0的员工的姓名,地址和学历:selecteducation,address,namefromemployeeswhereemployeeidlike'%0_'go16、使用into字句,由表employees创建“男员工1”表,包括编号和姓名:selectemployeeid,nameinto男员工表fromemployeeswheresex='1'go17、用子查询的方法查找收入在2500元以下的雇员的情况:select*fromemployeeswhereemployeeidin(selectemployeeidfromsalarywhereincome<2500)go18、用子查询的方法查找查找研发部比所有财务部雇员收入都高的雇员的姓名:SELECTNameFROMEmployeesWHEREEmployeeIDIN(SELECTEmployeeIDFROMSalaryWHEREEmployeeIDIN(SELECTEmployeeIdFROMEmployeesWHEREDepartmentIDIN(SELECTDepartmentIDFROMDepartmentsWHEREDepartmentName='研发部'))ANDInCome>ALL(SELECTInComeFROMSalaryWHEREEmployeeIDIN(SELECTEmployeeIdFROMEmployeesWHEREDepartmentIDIN(SELECTDepartmentIDFROMDepartmentsWHEREDepartmentName='财务部'))))19、用子查询的方法查找所有年龄比研发部雇员都大的雇员的姓名:selectnamefromemployeeswhereBirthday<all(selectbirthdayfromemployeeswheredepartmentidin(selectdepartmentidfromdepartmentswheredepartmentname='研发部'))20、查询每个员工的情况及其薪水的情况:selectemployees.*,departments.departmentnamefromemployees,departmentswhereemployees.departmentid=departments.departmentid21、使用内连接方法查找不在财务部工作的所有员工信息:selectemployees.*fromemployeesinnerjoindepartmentsonemployees.departmentid=departments.departmentidwheredepartmentname!='财务部'22、使用外连接方法查找出所有员工的月收入:selectemployees.*,salary.incomefromemployeesjoinsalaryonemployees.employeeid=salary.employeeid23、查找财务部雇员的最高收入:selectmax(income)fromsalarywhereemployeeidin(selectemployeeidfromemployeeswheredepartmentidin(selectdepartmentidfromdepartmentswheredepartmentname='财务部'))24、查询财务部雇员的最高实际收入:selectmax(income-outcome)fromsalarywhereemployeeidin(selectemployeeidfromemployeeswheredepartmentidin(selectdepartmentidfromdepartmentswheredepartmentname='财务部'))25、统计财务部收入在2500元以上的雇员人数:selectcount(employeeid)fromemployeeswheredepartmentidin(selectdepartmentidfromdepartmentswheredepartmentname='财务部')andemployeeidin(selectemployeeidfromsalarywhereincome>2500)26、按部门列出在该部门工作的员工的人数:selectdepartmentid,count(*)as人数fromemployeesgroupbydepartmentid27、按员工的学历分组:selecteducation,count(*)as人数fromemployeesgroupbyeducation28、按员工的工作年份分组,统计年份人数:selectworkyear,count(*)as人数fromemployeesgroupbyworkyear29、按各雇员的情况收入由低到高排列:selectemployees.*,salary.incomefromemployees,salarywhereemployees.employeeid=salary.employeeidorderbyincome30、将员工信息按出生时间从小到大排列:select*fromemployeesorderbybirthday31、在orderby字句中使用子查询,查询员工姓名,性别和工龄信息,要求按实际收入从大到小排列:selectname,sex,workyear,income-outcomefromsalary,employeeswheresalary.employeeid=employees.employeeidorderbyincome-outcomedesc视图部分1、创建view1:Createviewview1asselectemployees.employeeid,name,departmentname,(income-outcome)ascomefromemployees,departments,salarywhereemployees.departmentid=departments.departmentidandemployees.employeeid=salary.employeeid2、查询视图employeeid:3、向视图view1中插入一行数据:insertintoviewlvalues('111111','谎言','1','30000')4、查看视图(没有影响)基本表:实验51、定义一个变量,用于描述YGGL数据库的salary表中000001号员工的实际收入,然后查询该变量:declare@hyintset@hy=(selectincome-outcomefromsalarywhereemployeeid='000001')select@hy2、使用运算符“>”:selectname
fromemployeeswherebirthday>'1974-10-10'3、判断姓名为“王林”的员工实际收入是否高于3000元,如果是则显示“高收入”,否则显示“收入不高于3000”if((selectincome'刘明'刘明'from'刘明'刘明'wheresalary.employeeid==')>3000)selectincomeas'高收入'fromsalary,employeeswheresalary.employeeid==elseselect'收入不高于'4、使用循环输出一个“*”三角形:declare@iintdeclare@jintset@j=20set@i=1while@i<@jbeginprint(space((@j-@i)/2)+replicate('*',@i))set@i=@i+2end4、按部门进行分类,使用if语句实现:Createfunctionhy1(@departmentid1char(3))returnschar(10)asbegindeclare@hy1char(10)if((selectdepartmentidfromdepartmentswhere@departmentid1=departmentid)='1')set@hy1='财务部'if((selectdepartmentidfromdepartmentswhere@departmentid1=departmentid)='2')set@hy1='人力资源部'if((selectdepartmentidfromdepartmentswhere@departmentid1=departmentid)='3')set@hy1='经理办公室'if((selectdepartmentidfromdepartmentswhere@departmentid1=departmentid)='4')set@hy1='研发部'if((selectdepartmentidfromdepartmentswhere@departmentid1=departmentid)='5')set@hy1='市场部'return@hy1endselectemployeeid,name,address,dbo.hy1(departmentid)fromemployeesselectemployeeid,name,address,casedepartmentidwhen1then'财务部'when2then'人力资源部'when3then'经理办公室'when4then'研发部'when5then'市场部'endas部门号fromemployees6、自定义一个函数,计算一个数的阶层createfunctionhy(@hy2int)returnsintasbegindeclare@iintset@i=@hy2declare@jintset@j=1while@i>1beginset@j=@j*@iset@i=@i-1endreturn(@j)enddeclare@hintexec@h=dbo.hy4select@has'jiecheng'7、/*生成随机数*/selectrand()8、/*平方*/selectsquare(12)9、/*求财务部收入最高的员工姓名*/selectmax(name)fromemployeeswhereemployeeidin(selectemployeeidfromsalarywhereemployeeidin(selectemployeeidfromemployeeswheredepartmentidin(selectdepartmentidfromdepartmentswheredepartmentname='财务部')))selectavg(income)as'平均收入'fromsalary/*聚合函数与groupby一起使用*/selectworkyear,count(*)as人数fromemployeesgroupbyworkyear/*将字符组成字符串*/selectchar(123)/*返回字符串左边开始的个字符*/selectleft('abcdef',2)/*返回指定日期时间的天数*/selectday(birthday)fromemployeeswhereemployeeid='010000'/*获取当前时间*/selectgetdate()实验61、创建索引:createuniqueindexhuangyanonemployees(employeeid)2、/*用createindex语句创建主键*/3、重建表employees中employeeid歹U上的索弓丨alterindexhuangyanonemployeesrebuild4、删除索弓:5、创建一个新表,使用一个复合歹作为主键,作为表的约束,并为其命名createtableemployees5(employeeidchar(6)notnull,namechar(5)notnull,sextinyint,educationchar(4),constraintyanprimarykey(employeeid,name))为新表添加一歹:altertableemployees5addaddresschar(10)6、创建新表student,性别只能包含男或女:createtablestudent(号码char(6)notnull,性另别char(2)notnullcheck(性别in('男','女')))7、创建新表:createtableemployees7(学号char(10)notnull,出生日期datetimenotnullcheck(出生日期>'1980-01-01'))8、创建一个规则:9,创建salary2:createtablesalary2(employeeidchar(6)notnullprimarykey,incomefloatnotnull,outcomefloatnotnull,foreignkey(employeeid)referencessalary(employeeid)onupdatecascadeondeletecascade)10、添加一个外键,salary与employees有相关记录,贝U拒绝更新employees:altertablesalaryaddconstraintkc_forforeignkey(employeeid)referencesemployees(employeeid)ondeletenoactiononupdatenoaction实验71、工作年份大于6时,跟换科室到经理办公室(根据员工):CreatePROCUpdateDeptByYear(@EmpIdchar(6))ASBEGINDECLARE@yearintSELECT@year=WorkYearFromEmployeesWHEREEmployeeID=@EmpIdIF(@year>6)UPDATEEmployeesSETDepartmentID='3'WHEREEmployeeID=@EmpIdENDEXECUpdateDeptByYear'020010'SELECT*FROMEmployeesWHEREEmployeeid='020010'2、根据每个员工的学历将收入提高元:CREATEPROCUpdateInComeByEdu@Employeeidchar(6)ASBEGINUPDATESalarySETInCome=InCome+500FROMSalaryLEFTJOINEmployeesONSalary.EmployeeID=Employees.EmployeeIDWHERESalary.Employeeid=@EmployeeidENDEXECUpdateInComeByEdu'020010'SELECT*FROMSalarywhereEmployeeID='020010'3、游标:CREATEPROCEDUREEmployees_biliASBEGINDECLARE@iFLOATDECLARE@jFLOATDECLARE@EducationCHAR(10)DECLAREEmployees_cursorCURSORFORSELECTEducationFROMEmployeesSET@i=0SET@j=0OPENEmployees_cursorFETCHEmployees_cursorINTO@EducationWHILE(@@FETCH_STATUS=0)BEGINIF(@Education!='大专')SET@i=@i+1SET@j=@j+1FETCHEmployees_cursorINTO@EducationENDCLOSEEmployees_cursorSELECT@iAS'本科及以上员工所占员工数'SELECT@jAS'员工总数'SELECT@i/@jAS'本科及以上员工所占比例'CLOSEEmployees_cursorENDEXECEmployees_bili4、使用命令的方式修改存储过程的定义:5、对于YGGL数据库,表Employees的EmployeelD歹U与表Salary的EmployeelD歹U应该满足参照的完整性规则,请用触发器实现两个表的参照完整性:CREATETRIGGERSalary_insertONSalaryFORINSERT,UPDATEASBEGINIF(SELECTEmployeeIDFROMINSERTED)NOTIN(SELECTEmployeeIDFROMEmployees)ROLLBACKENDCREATETRIGGEREmployeesupdateONEmployeesFORUPDATEASBEGINUPDATESalarySETEmployeeID=(SELECTEmployeeIDFROMINSERTED)WHEREEmployeeID=(SELECTEmployeeIDFROMDELETED)ENDCREATETRIGGEREmployeesdeleteONEmployeesFORDELETEASBEGINDELETEFROMSalaryWHEREEmployeeID=(SELECTEmployeeIDFROMDELETED)ENDINSERTINTOSalaryVALUES('000005',2000,1000)UPDATEEmployeesSETEmployeeID='000000'WHEREEmployeeID='990230'DELETEFROMEmployeesWHEREEmployeeID='000000'当修改表Employees时,若将Employees表中员工的工作时间增加1年,则将收入增加500,若增加2年则增加1000,依次增加。若工作时间减少则无变化:CREATETRIGGEREM_WORKYEARONEmployeesAFTERUPDATEASBEGINDECLARE@iINT,@jINTSET@i=(SELECTWorkYearFROMINSERTED)SET@j=(SELECTWorkYearFROMDELETED)IF(@i>@j)UPDATESalarySETInCome=InCome+(@i-@j)*500WHEREEmployeeIDIN(SELECTEmployeeIDFROMINSERTED)ENDUPDATEEmployeesSETWorkYear=7WHEREEmployeeid='990230'SELECT*FROMEmployeesWHEREEmployeeid='990230'创建UPDATE触发器,当Salary表中Income值增加500时,outcome值增加50:CREATETRIGGERSA_INCOMEONSalaryFORUPDATEASBEGINIF((SELECTInComeFROM
温馨提示
- 1. 本站所有资源如无特殊说明,都需要本地电脑安装OFFICE2007和PDF阅读器。图纸软件为CAD,CAXA,PROE,UG,SolidWorks等.压缩文件请下载最新的WinRAR软件解压。
- 2. 本站的文档不包含任何第三方提供的附件图纸等,如果需要附件,请联系上传者。文件的所有权益归上传用户所有。
- 3. 本站RAR压缩包中若带图纸,网页内容里面会有图纸预览,若没有图纸预览就没有图纸。
- 4. 未经权益所有人同意不得将文件中的内容挪作商业或盈利用途。
- 5. 人人文库网仅提供信息存储空间,仅对用户上传内容的表现方式做保护处理,对用户上传分享的文档内容本身不做任何修改或编辑,并不能对任何下载内容负责。
- 6. 下载文件中如有侵权或不适当内容,请与我们联系,我们立即纠正。
- 7. 本站不保证下载资源的准确性、安全性和完整性, 同时也不承担用户因使用这些下载资源对自己和他人造成任何形式的伤害或损失。
最新文档
- 二零二五年度租赁房屋人身安全与社区安全服务体系合同
- 2025年度节庆活动无偿场地使用许可合同
- 二零二五年度金融消费者权益保护合作协议
- 二零二五年度养老护理机构劳动合同终止及护理服务协议
- 2025年度施工现场安全责任认定协议
- 二零二五年度机关单位食堂员工激励与保障合同
- 母公司对子公司2025年度管理费用审核及支付合同
- Unit 3 Writing Home Lesson 17 Danny's Email 同步练习(含答案含听力原文无音频)
- 2025年度餐厅员工劳务及餐饮企业员工绩效管理合同
- 二零二五年度酒店培训投资入股合同
- 江西省南昌市高职单招2023年职业技能真题及答案
- 山西省中小学生转学申请表及说明、电子操作流程
- 2022-2023学年江苏省扬州市普通高校高职单招综合素质测试题(含答案)
- 高端滋补品市场
- 2021年八省联考数学试卷
- DB37T 4242-2020水利工程建设项目代建实施规程
- 学生班级卫生值日表模板下载
- 日产5000t水泥熟料预分解窑窑尾工艺设计说明书
- 劳务派遣服务方案与服务流程图
- qc小组活动记录
- 2022立足岗位秉承工匠精神PPT课件模板
评论
0/150
提交评论