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

1、SQL实验 实验 4 1. 用 select 语句查询 departments 和 salary 表中的所有数据： select salary .*, departments .* from salary , departments 2、查询 departments 中的 departmentid ： select departmentid from departments go 3 、查询 salary 中的 income ， outcome ： select income , outcome from salary go 4、查询 employees 表中的部门号，性别，要用 distin

2、ct 消除重复行： select distinct ( departmentid ), sex from employees 5、查询月收入高于 2000 元的员工号码： select employeeid from salary where income 2000 go 6、查询 1970 年以后出生的员工的姓名和住址： select name , address from employees where birthday 1970 go 7、查询所有财务部的员工的号码和姓名： select employeeid , name from employees where where depar

3、tmentid in( select departmentid from departments departmentname = 财务部 ) go 20003000 8、查询 employees 员工的姓名，住址和收入水平， 2000 元以下显示为低收入， 元显示为中等收入， 3000 元以上显示为高收入： select name , address , case when income - outcome 3000 then 高收入 else 中等收入 end as 收入等级 from employees , salary where = go 9、计算 salary 表中员工月收入的评价

4、数： select avg ( income ) as 平均收入 from salary 10、查找 employees 表中最大的员工号码： select max( employeeid ) as 最大员工号码 from employees 11、计算 salary 表中的所有员工的总支出： select sum( outcome ) as 总支出 from salary 12、查询财务部雇员的最高实际收入： select max( income-outcome ) from salary , employees , departments where go = and = and depa

5、rtmentname = 财务部 13、查询财务部雇员的最低实际收入： select min ( income-outcome ) from salary , employees , departments where go = and = and departmentname = 财务部 14、找出所用地址中含有“中山”的雇员的号码及部门号： select employeeid , departmentid from employees where address like % 中山 % go 15、查找员工号码中倒数第二个数字为0 的员工的姓名，地址和学历： select educatio

6、n , address , name from employees where employeeid like %0_ go 16、使用 into 字句，由表 employees 创建“男员工 1”表，包括编号和姓名： select employeeid , name into 男员工表 from employees where sex =1 go 17、用子查询的方法查找收入在2500 元以下的雇员的情况： select * from employees where employeeid in ( select employeeid from salary where income ALL

7、( SELECT InCome FROM Salary WHEREEmployeeID IN ( SELECT EmployeeId FROMEmployees WHEREDepartmentID IN ( SELECT DepartmentID FROM Departments WHEREDepartmentName = 财务部 ) ) ) ) 19、用子查询的方法查找所有年龄比研发部雇员都大的雇员的姓名： select name from employees where Birthday 2500 ) 26、按部门列出在该部门工作的员工的人数： select departmentid ,

8、count (*) as 人数 from employees group by departmentid 27、按员工的学历分组： select education , count (*) as 人数 from employees group by education 28、按员工的工作年份分组，统计年份人数： select workyear , count (*) as 人数 from employees group by workyear 29、按各雇员的情况收入由低到高排列：select employees .* , from employees , salary where = ord

9、er by income 30、将员工信息按出生时间从小到大排列： select * from employees order by birthday 31、在 order by 字句中使用子查询，查询员工姓名，性别和工龄信息，要求按实际收入从 大到小排列： select name , sex , workyear , income - outcome from salary , employees where = order by income - outcome desc 视图部分 1、创建 view1 ： Create view view1 as select , name, depar

10、tmentname ,( income - outcome ) as come from employees , departments , salary where = and = 2、查询视图 employeeid ： 3、向视图 view1 中插入一行数据： 谎言 , 1 , 30000 ) insert into view1 values （ 111111 4、查看视图（没有影响）基本表： 实验 5 1、 定义一个变量，用于描述 YGGL 数据库的 查询该变量： declare hy int set hy =( select income - outcome from salary w

11、here employeeid =000001 ) select hy 2、 使用运算符“ ”： select name salary 表中 000001 号员工的实际收入，然后 from employees where birthday 1974-10-10 3000 元，如果是则显示“高收入” ，否则 3、判断姓名为“王林”的员工实际收入是否高于 显示“收入不高于 3000”： if ( select income from salary , employees where = and = 刘明 ) 3000 )select income as 高收入 from salary , empl

12、oyees where = and = 刘明 else select 收入不高于 4、使用循环输出一个“ * ”三角形： declare i int declare j int set j =20 set i =1 while i 1 begin set j =j* i set i =i- 1 end return ( j) end declare h int exec h= 4 select h as jiecheng 7、/* 生成随机数 */ select rand () 8 、 /* 平方 */ select square ( 12) 9 、/* 求财务部收入最高的员工姓名*/ sel

13、ect max( name) from employees where employeeid in ( select employeeid from salary where employeeid in ( select employeeid from employees where departmentid in ( select departmentid from departments where departmentname = 财务部 ) select avg ( income ) as 平均收入 from salary /* 聚合函数与 group by 一起使用 */ selec

14、t workyear, count (*) as 人数 from employees group by workyear /* 将字符组成字符串 */ select char ( 123 ) /* 返回字符串左边开始的个字符 */ selectleft( abcdef , 2) /* 返回指定日期时间的天数 */ selectday ( birthday) from employees where employeeid =010000 /* 获取当前时间 */ select getdate () 实验 6 1、创建索引： create unique index huangyan on empl

15、oyees ( employeeid ) 2 、 /* 用 create index 语句创建主键 */ 3 、重建表 employees 中 employeeid 列上的索引 alter index huangyan on employees rebuild 4 、删除索引： 5 、创建一个新表，使用一个复合列作为主键，作为表的约束，并为其命名： create table employees5 ( employeeid char ( 6) not null, name char ( 5) not null, sex tinyint , education char ( 4), constra

16、int yan primary key ( employeeid , name ) ) 为新表添加一列： alter table employees5 add address char ( 10 ) 6 、创建新表 student ，性别只能包含男或女： create table student ( 号码 char ( 6) not null, 性别 char ( 2)not null check (性别 in (男,女) ) 7 、创建新表： create table employees7 ( 学号 char ( 10 ) not null, 出生日期 datetime not null c

17、heck ( 出生日期 1980-01-01) employees = EmpId ) 8 、创建一个规则： 9 ，创建 salary2 ： create table salary2 ( employeeid char ( 6) not null primary key income float not null, outcome float not null, foreign key ( employeeid ) references salary ( employeeid ) on update cascade on delete cascade ) 10 、添加一个外键， salary

18、与 employees 有相关记录，则拒绝更新 alter table salary add constraint kc_for foreign key ( employeeid ) references employees ( employeeid ) on delete no action on update no action 实验 7 1、工作年份大于 6时，跟换科室到经理办公室 ( 根据员工 )： Create PROC UpdateDeptByYear ( EmpId char ( 6) ) AS BEGIN DECLARE year int SELECT year = WorkY

19、ear From Employees WHEREEmployeeID IF ( year 6) UPDATE Employees SET DepartmentID =3 WHEREEmployeeID = EmpId END EXEC UpdateDeptByYear 020010 SELECT * FROM EmployeesWHEREEmployeeid =020010 2、根据每个员工的学历将收入提高元： CREATE PROC UpdateInComeByEdu Employeeid char ( 6) AS BEGIN UPDATE Salary SET InCome = InCom

20、e +500 FROM Salary LEFT JOIN Employees ON = WHERE = Employeeid END EXEC UpdateInComeByEdu 020010 SELECT * FROM Salarywhere EmployeeID = 020010 3、游标： CREATE PROCEDURE Employees_bili AS BEGIN DECLARE i FLOAT DECLARE j FLOAT DECLARE Education CHAR( 10) DECLARE Employees_cursor CURSOR FOR SELECT Educati

21、onFROM Employees SET i =0 SET j =0 OPEN Employees_cursor FETCH Employees_cursor INTO Education WHILE ( FETCH_STATUS=0) BEGIN IF ( Education != 大专 ) SET i =i+1 SET j =j+1 FETCH Employees_cursor INTO Education END CLOSE Employees_cursor SELECT i AS 本科及以上员工所占员工数 SELECT j AS 员工总数 SELECT i / j AS 本科及以上员工

22、所占比例 CLOSE Employees_cursor END EXEC Employees_bili 4、使用命令的方式修改存储过程的定义： 列应 5 、对于 YGGL 数据库，表 Employees 的 EmployeeID 列与表 Salary 的 EmployeeID 该满足参照的完整性规则，请用触发器实现两个表的参照完整性： CREATE TRIGGER Salary_insertON Salary FOR INSERT , UPDATE AS BEGIN IF ( SELECT EmployeeIDFROM INSERTED ) NOT IN( SELECT EmployeeID

23、FROM Employees ) ROLLBACK END ON Employees CREATE TRIGGER Employeesupdate FOR UPDATE AS BEGIN UPDATE Salary SET EmployeeID =( SELECT EmployeeID FROMINSERTED ) WHEREEmployeeID =( SELECT EmployeeID FROM DELETED ) END CREATE TRIGGER EmployeesdeleteON Employees FOR DELETE AS BEGIN DELETE FROM Salary WHE

24、REEmployeeID =( SELECT EmployeeIDFROM DELETED ) END INSERT INTO Salary VALUES ( 000005 , 2000 , 1000 ) UPDATE Employees SET EmployeeID =000000 WHEREEmployeeID = 990230 DELETE FROM Employees WHEREEmployeeID =000000 6. 当修改表 Employees 时，若将 Employees 表中员工的工作时间增加 1 年，则将收入增加 500 ，若增加 2 年则增加 1000 ，依次增加。若工作

25、时间减少则无变化： CREATE TRIGGER EM_WORKYEAR ON Employees AFTER UPDATE AS BEGIN FROM INSERTED ) FROM DELETED ) DECLARE i INT , j INT SET i =( SELECT WorkYear SET j =( SELECT WorkYear IF ( ij) UPDATE Salary SET InCome = InCome +( i- j)* 500 WHEREEmployeeID IN( SELECT EmployeeID FROM INSERTED ) END UPDATE Emp

26、loyees SET WorkYear =7 WHEREEmployeeid =990230 SELECT * FROM Employees WHEREEmployeeid =990230 7. 创建 UPDATE 触发器 , 当 Salary 表中 Income 值增加 500 时， outcome 值增加 50 ： CREATE TRIGGER SA_INCOME ON Salary FOR UPDATE AS BEGIN IF ( SELECT InCome FROM INSERTED )-( SELECT InCome FROMDELETED )= 500 ) UPDATE Salary SET OutCome =OutCome +50 WHEREEmployeeID =( SELECT EmployeeID FROM INSERTED ) END SELECT INCOME , OUTCOME FROM SALARY WHEREEMPLOYEEID = 504209 UPDATE Salary SET InCome = InCome +500 WHEREEmployeeID =