SQL实验实验至实验的答案

1、实验实验 41. 用 select 语句查询 departments 和 salary 表中的所有数据： select salary .*, departments .* from salary , departments2、查询 departments 中的 departmentid ： select departmentid from departments go3 、查询 salary 中的 income ， outcome ： select income , outcome from salary go4、查询 employees 表中的部门号，性别，要用 distinct 消除重复行

2、： select distinct ( departmentid ), sexfrom employees5、查询月收入高于 2000 元的员工号码： select employeeid from salary where income >2000go6、查询 1970 年以后出生的员工的姓名和住址： select name , addressfrom employeeswhere birthday >1970go7、查询所有财务部的员工的号码和姓名： select employeeid , name from employeeswhere20003000where departm

3、entid in( select departmentid from departments departmentname =' 财务部 ' )go8、查询 employees 员工的姓名，住址和收入水平， 2000 元以下显示为低收入， 元显示为中等收入， 3000 元以上显示为高收入：selectname , address ,casewhenincome - outcome <2000then '低收入 'whenincome - outcome >3000then '高收入 'else' 中等收入 'endas

4、 ' 收入等级fromemployees ,salarywhereemployees. employeeid= salary. employeeidgo9、计算 salary 表中员工月收入的评价数：select avg ( income ) as ' 平均收入 ' from salary10、查找 employees 表中最大的员工号码：select max( employeeid ) as ' 最大员工号码 ' from employees11、计算 salary 表中的所有员工的总支出：select sum( outcome ) as '

5、总支出 ' from salary12、查询财务部雇员的最高实际收入：select max( income-outcome ) from salary , employees , departmentswhere salary . employeeid = employees . employeeid andemployees . departmentid =departments . departmentid and departmentname =' 财 务部'go13、查询财务部雇员的最低实际收入：select min ( income-outcome ) fro

6、m salary , employees , departments where salary . employeeid = employees . employeeid andemployees . departmentid =departments . departmentid and departmentname =' 财 务部'go14、找出所用地址中含有“中山”的雇员的号码及部门号：select employeeid , departmentidfrom employeeswhere address like '% 中山 %' go15、查找员工号码中

7、倒数第二个数字为0 的员工的姓名，地址和学历：select education , address , namefrom employeeswhere employeeid like '%0_' go16、使用 into 字句，由表 employees 创建“男员工 1”表，包括编号和姓名： select employeeid , nameinto 男员工表 from employees where sex ='1' go17、用子查询的方法查找收入在2500 元以下的雇员的情况：select * from employees where employeeid

8、in ( select employeeid from salary where income <2500 ) go18、用子查询的方法查找查找研发部比所有财务部雇员收入都高的雇员的姓名：SELECT Name FROM EmployeesWHERE EmployeeIDIN(SELECT EmployeeID FROM SalaryWHERE EmployeeIDIN(SELECT EmployeeIdFROM EmployeesWHERE DepartmentIDIN(SELECT DepartmentIDFROM DepartmentsWHERE DepartmentName=&#

9、39; 研发部 ')AND InCome >ALL(SELECT InCome FROM SalaryWHERE EmployeeIDIN(SELECT EmployeeIdFROM EmployeesWHERE DepartmentID IN(SELECT DepartmentID FROM Departments WHERE DepartmentName=' 财务部 ')19、 用子查询的方法查找所有年龄比研发部雇员都大的雇员的姓名： select namefrom employeeswhere Birthday <allselectbirthdayfr

10、omemployeeswheredepartmentid in( selectdepartmentidfromdepartmentswheredepartmentname(=' 研发部 ')20、查询每个员工的情况及其薪水的情况：select employees .*, departments. departmentnamefrom employees , departments where employees . departmentid= departments. departmentidselectemployees .*21、使用内连接方法查找不在财务部工作的所有员工信

11、息：from employees inner join departments on employees . departmentid =departments . departmentid where departmentname != ' 财务部 ' 22、使用外连接方法查找出所有员工的月收入：selectemployees .*, salary . incomefrom employees join salary on employees 23、查找财务部雇员的最高收入： select max( income )from salarywhere employeeid in

12、( select employeeidfrom employeeswhere departmentid in( select departmentidfrom departmentswhere departmentname =' 财务部 ')24、查询财务部雇员的最高实际收入：select max( income - outcome )from salarywhere employeeid in( select employeeidfrom employeeswhere departmentid in( select departmentidfrom departmentswh

13、ere departmentname =' 财务部 ')25、统计财务部收入在 2500 元以上的雇员人数： select count ( employeeid ). employeeid=salary. employeeidfrom employees where departmentid ( select departmentid where departmentname and employeeid in ( select employeeid from salary where income >2500 )infrom departments=' 财务部

14、' )26、按部门列出在该部门工作的员工的人数： select departmentid , count (*) as 人数 from employees group by departmentid27、按员工的学历分组：select education , count (*) as 人数 from employeesgroup by education28、按员工的工作年份分组，统计年份人数：select workyear , count (*) as 人数 from employeesgroup by workyear29、按各雇员的情况收入由低到高排列：select employ

15、ees .* , salary . incomefrom employees , salarywhere employees . employeeid = salary . employeeidorder by income30、将员工信息按出生时间从小到大排列：select * from employees order by birthday31、在 order by 字句中使用子查询，查询员工姓名，性别和工龄信息，要求按实际收入从 大到小排列：select name , sex , workyear , income - outcomefrom salary , employeeswher

16、e salary . employeeid = employees . employeeidorder by income - outcome desc视图部分1、创建 view1 ：Create view view1asasselect employees . employeeid , name, departmentname ,( income - outcome ) comefrom employees , departments , salaryandwhere employees . departmentid = departments . departmentid employee

17、s . employeeid = salary . employeeid2、查询视图 employeeid ：3、向视图 view1 中插入一行数据：insert into view1 values ( '111111' , ' 谎言 ' , '1' , '30000' )4、查看视图(没有影响)基本表：实验 51、 定义一个变量，用于描述 查询该变量： declare hy int set hy =( select income from salary where employeeid select hy2、 使用运算符“ &g

18、t;”： select nameYGGL 数据库的 salary 表中 000001 号员工的实际收入，- outcome然后='000001' )from employeeswhere birthday >'1974-10-10'3、判断姓名为“王林”的员工实际收入是否高于3000 元，如果是则显示“高收入” ，否则显示“收入不高于 3000”：if ( select incomefromsalary ,employeeswheresalary .employeeid = employees. employeeidandemployees. name =

19、' 刘明' )> 3000 )selectincomeas ' 高收入 'fromsalary ,employeeswheresalary .employeeid = employees. employeeidandemployees. name =' 刘明 'elseselect ' 收入不高于 '4、使用循环输出一个“ * ”三角形： declare i int declare j int set j =20 set i =1 while i <j beginprint ( space ( j- i)/ 2)+ r

20、eplicate ( '*' , i) set i =i+2end4、 按部门进行分类，使用 if 语句实现：Create function hy1 ( departmentid1 char ( 3) returns char ( 10) asbegindeclare hy1 char ( 10)if ( selectdepartmentidfrom departmentswheredepartmentid1=departmentid)= '1' )set hy1 ='财务部 'if ( selectdepartmentidfrom depart

21、mentswheredepartmentid1=departmentid)= '2' )set hy1 =' 人力资源部if ( selectdepartmentidfrom departmentswheredepartmentid1=departmentid)= '3' )set hy1 = '经理办公室 'if ( selectdepartmentidfrom departmentswheredepartmentid1=departmentid)= '4' )set hy1 =' 研发部 'if ( s

22、electdepartmentidfrom departmentswheredepartmentid1=departmentid)= '5' )set hy1 =' 市场部 ' return hy1endemployeesselect employeeid, name , address , dbo . hy1 ( departmentid ) fromselect employeeid, name , address ,case departmentidwhen1then' 财务部 'when2then' 人力资源部when3then&

23、#39; 经理办公室when4then' 研发部 'when5then' 市场部 'endas部门号from employees6、自定义一个函数，计算一个数的阶层： create function hy ( hy2 int ) returns int as begin declare i int set i =hy2 declare j int set j =1 while i >1 begin set j =j* i set i =i- 1 end return ( j) end declare h int exec h = dbo . hy 4 se

24、lect h as 'jiecheng'7、/* 生成随机数 */ select rand ()8 、 /* 平方 */ select square ( 12)9 、/* 求财务部收入最高的员工姓名*/select max( name) from employees where employeeid in ( select employeeid from salary where employeeid in ( select employeeid from employees where departmentid in ( select departmentidfrom dep

25、artmentswhere departmentname =' 财务部 ' ) ) select avg ( income ) as ' 平均收入 ' from salary/* 聚合函数与 group by 一起使用 */ select workyear, count (*) as 人数from employees group by workyear/* 将字符组成字符串 */ select char ( 123 ) /* 返回字符串左边开始的个字符 */ select left( 'abcdef' , 2) /* 返回指定日期时间的天数 */

26、 select day ( birthday ) from employees where employeeid ='010000' /* 获取当前时间 */ select getdate () 实验 61、 创建索引： create unique index huangyan on employees ( employeeid )2 、 /* 用 create index语句创建主键 */3 、重建表 employees 中 employeeid 列上的索引 alter index huangyan on employees rebuild4 、删除索引：5 、创建一个新表，

27、使用一个复合列作为主键，作为表的约束，并为其命名： create table employees5( employeeid char ( 6) not null, name char ( 5) not null, sex tinyint , education char ( 4), constraint yan primary key ( employeeid , name ) ) 为新表添加一列： alter table employees5 add address char ( 10 )6 、创建新表 student ，性别只能包含男或女： create table student( 号码

28、 char ( 6) not null,性别 char ( 2)not null check (性别in ( ' 男' , ' 女' ) )7 、创建新表：create table employees7( 学号 char ( 10 ) not null, 出生日期 datetime not nullcheck ( 出生日期 >'1980-01-01' ) )8 、创建一个规则：9 ，创建 salary2 ： create table salary2( employeeid char ( 6) not null primary income

29、float not null, outcome float not null, foreign key ( employeeid ) references salary ( employeeid ) on update cascade on deletecascadekey)10 、添加一个外键， salary 与 employees 有相关记录，则拒绝更新 alter table salaryadd constraint kc_for foreign key ( employeeid ) references employees ( employeeid ) on delete no act

30、ion on update no action实验 7employees1、 工作年份大于 6时，跟换科室到经理办公室 ( 根据员工 )：Create PROC UpdateDeptByYear( EmpId char ( 6) )ASBEGINDECLARE year intSELECT year = WorkYear From EmployeesWHERE EmployeeIDIF ( year >6)UPDATE EmployeesSET DepartmentID ='3'WHERE EmployeeID = EmpIdENDEXEC UpdateDeptByYea

31、r '020010'SELECT * FROM EmployeesWHERE Employeeid='020010'2、 根据每个员工的学历将收入提高元：= EmpIdchar ( 6)CREATE PROC UpdateInComeByEdu EmployeeidASBEGINUPDATE SalarySET InCome = InCome +500FROM SalaryLEFT JOIN EmployeesON Salary . EmployeeID = Employees . EmployeeIDWHERE Salary . Employeeid = Em

32、ployeeidENDEXEC UpdateInComeByEdu '020010'SELECT * FROM Salarywhere EmployeeID = '020010'3、游标：CREATE PROCEDURE Employees_biliASBEGINDECLARE i FLOATDECLARE j FLOATDECLARE Education CHAR( 10)DECLARE Employees_cursor CURSORFOR SELECT EducationFROM EmployeesSET i =0SET j =0OPEN Employees

33、_cursorFETCH Employees_cursor INTO EducationWHILE ( FETCH_STATUS=0)BEGINIF ( Education != ' 大专 ' )SET i =i+1SET j =j+1FETCH Employees_cursor INTO EducationENDCLOSE Employees_cursorSELECT i AS' 本科及以上员工所占员工数 'SELECT j AS' 员工总数 'SELECT i / j AS' 本科及以上员工所占比例 'CLOSE Employ

34、ees_cursorENDEXEC Employees_bili4、使用命令的方式修改存储过程的定义：列应5 、对于 YGGL 数据库，表 Employees 的 EmployeeID 列与表 Salary 的 EmployeeID 该满足参照的完整性规则，请用触发器实现两个表的参照完整性：CREATE TRIGGER Salary_insertON SalaryFOR INSERT , UPDATEASBEGINIF ( SELECT EmployeeIDFROM INSERTED ) NOT IN( SELECT EmployeeIDEmployees )ROLLBACKENDCREATE

35、 TRIGGER EmployeesupdateON EmployeesFOR UPDATEASBEGINUPDATE SalarySET EmployeeID =( SELECT EmployeeIDFROM INSERTED )WHERE EmployeeID =( SELECT EmployeeIDFROM DELETED )ENDCREATE TRIGGER EmployeesdeleteON EmployeesFOR DELETEASBEGINDELETE FROM SalaryWHERE EmployeeID =( SELECT EmployeeID FROM DELETED )E

36、NDINSERT INTO SalaryVALUES ( '000005' , 2000 , 1000 )UPDATE EmployeesSET EmployeeID ='000000'WHERE EmployeeID =' 990230'DELETE FROM EmployeesWHERE EmployeeID ='000000'6. 当修改表 Employees 时，若将 Employees 表中员工的工作时间增加 1 年，500 ，若增加 2 年则增加 1000 ，依次增加。若工作时间减少则无变化：CREATE TRIGGE

37、R EM_WORKYEAR ON EmployeesAFTER UPDATEFROM则将收入增加ASBEGINDECLARE i INT , j INT SET i =( SELECT WorkYear SET j =( SELECT WorkYearFROM INSERTED )FROM DELETED )IF ( i>j)UPDATE SalarySET InCome = InCome +( i- j)* 500FROM INSERTED )WHERE EmployeeIDIN( SELECT EmployeeIDENDUPDATE EmployeesSET WorkYear =7WHERE Employeeid ='990230'SELECT * FROM EmployeesWHERE Employeeid='990230'7. 创建 UPDATE 触发器 , 当 Salary 表中 Income 值增加 500 时， outcome 值增加 50 ： CREATE TRIGGER SA_INCOME ON SalaryFOR UPDATEASBEGINIF ( SELECT InCome FROM INSERTED )-( SELECT