1、SQL实验实验4至实验7的答案实验实验41.用select 语句查询departments和salary表中的所有数据:select salary.*, departments.* from salary ,departments 2、查询departments 中的departmentid:select departmentid from departments go3、查询 salary中的 income,outcome:select income,outcome from salarygo4、查询employees表中的部门号,性别,要用distinct消除重复行:select dist
2、inct(departmentid), sexfrom employees 5、查询月收入高于2000元的员工号码:select employeeid from salarywhere income2000go6、查询1970年以后出生的员工的姓名和住址:select name ,address from employees where birthday1970go7、查询所有财务部的员工的号码和姓名:select employeeid ,namefrom employeeswhere departmentid in(select departmentid from departments w
3、here departmentname=财务部)go8、查询employees员工的姓名,住址和收入水平,2000元以下显示为低收入,20003000元显示为中等收入,3000元以上显示为高收入:select name ,address,case when income-outcome3000 then 高收入else 中等收入end as 收入等级from employees,salarywhere =go9、计算salary表中员工月收入的评价数:select avg(income)as 平均收入 from salary10、查找employees表中最大的员工号码:select max(
4、employeeid)as 最大员工号码 from employees11、计算salary表中的所有员工的总支出:select sum(outcome) as总支出 from salary12、查询财务部雇员的最高实际收入:select max(income-outcome) from salary ,employees,departmentswhere = and = and departmentname=财务部go13、查询财务部雇员的最低实际收入:select min(income-outcome) from salary ,employees,departmentswhere = a
5、nd = and departmentname=财务部go14、找出所用地址中含有“中山”的雇员的号码及部门号:select employeeid ,departmentid from employeeswhere address like%中山%go15、查找员工号码中倒数第二个数字为0的员工的姓名,地址和学历:select education,address,name from employees where employeeid like%0_go16、使用into字句,由表employees创建“男员工1”表,包括编号和姓名:select employeeid,name into 男员
6、工表from employees where sex=1go17、用子查询的方法查找收入在2500元以下的雇员的情况:select * from employees where employeeid in(select employeeid from salary where incomeALL ( SELECT InCome FROM Salary WHERE EmployeeID IN ( SELECT EmployeeId FROM Employees WHERE DepartmentID IN ( SELECT DepartmentID FROM Departments WHERE D
7、epartmentName=财务部 ) ) )19、 用子查询的方法查找所有年龄比研发部雇员都大的雇员的姓名:select name from employees where Birthday2500)26、按部门列出在该部门工作的员工的人数:select departmentid ,count(*) as 人数from employees group by departmentid27、按员工的学历分组:select education ,count(*) as 人数from employees group by education28、按员工的工作年份分组,统计年份人数:select wo
8、rkyear ,count(*) as 人数from employees group by workyear29、按各雇员的情况收入由低到高排列:select employees.* ,from employees ,salary where =order by income30、将员工信息按出生时间从小到大排列:select *from employees order by birthday31、在order by 字句中使用子查询,查询员工姓名,性别和工龄信息,要求按实际收入从大到小排列:select name ,sex,workyear,income-outcomefrom salary
9、 ,employeeswhere =order by income-outcome desc视图部分1、创建view1:Create view view1 as select ,name,departmentname,(income-outcome) as comefrom employees , departments , salary where = and =2、查询视图employeeid:3、向视图view1中插入一行数据:insert into view1 values(111111,谎言,1,30000)4、查看视图(没有影响)基本表:实验51、 定义一个变量,用于描述YGGL数
10、据库的salary表中000001号员工的实际收入,然后查询该变量:declare hy int set hy=(select income-outcome from salary where employeeid=000001)select hy2、 使用运算符“”:select name from employees where birthday1974-10-103、 判断姓名为“王林”的员工实际收入是否高于3000元,如果是则显示“高收入”,否则显示“收入不高于3000”:if(select income from salary,employees where = and =刘明)30
11、00) select income as 高收入 from salary,employees where = and =刘明else select收入不高于4、使用循环输出一个“*”三角形:declare i int declare j int set j=20set i=1while i1beginset j=j*iset i=i-1endreturn(j)end declare h int exec h= 4select h as jiecheng7、/*生成随机数*/select rand()8、/*平方*/select square(12)9、/*求财务部收入最高的员工姓名*/sele
12、ct 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 一起使用*/select workyear ,count(*
13、) as 人数from employees group by workyear/*将字符组成字符串*/select char(123)/*返回字符串左边开始的个字符*/select left(abcdef,2)/*返回指定日期时间的天数*/select day(birthday)from employees where employeeid=010000/*获取当前时间*/select getdate()实验61、 创建索引:create unique index huangyan on employees(employeeid)2、 /*用create index 语句创建主键*/3、重建表
14、employees中employeeid列上的索引alter index huangyanon employees rebuild4、删除索引:5、创建一个新表,使用一个复合列作为主键,作为表的约束,并为其命名: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 ch
15、ar(10)6、创建新表student,性别只能包含男或女:create table student (号码char(6) not null,性别char(2)not nullcheck(性别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 key,income float not null
16、,outcome float not null,foreign key(employeeid)references salary(employeeid)on update cascadeon delete cascade)10、添加一个外键,salary与employees有相关记录,则拒绝更新employees:alter table salaryadd constraint kc_forforeign key(employeeid)references employees(employeeid)on delete no actionon update no action实验71、 工作年份
17、大于6时,跟换科室到经理办公室(根据员工):Create PROC UpdateDeptByYear(EmpId char(6) )ASBEGINDECLARE year intSELECT year=WorkYear From Employees WHERE EmployeeID=EmpIdIF(year6) UPDATE Employees SET DepartmentID=3 WHERE EmployeeID=EmpIdENDEXEC UpdateDeptByYear 020010SELECT * FROM Employees WHERE Employeeid=0200102、 根据每个
18、员工的学历将收入提高元:CREATE PROC UpdateInComeByEdu Employeeid char(6)ASBEGINUPDATE SalarySET InCome=InCome+500FROM SalaryLEFT JOIN EmployeesON =WHERE =EmployeeidENDEXEC UpdateInComeByEdu 020010SELECT * FROM Salary where EmployeeID=0200103、游标:CREATE PROCEDURE Employees_bili AS BEGIN DECLARE i FLOAT DECLARE j
19、FLOATDECLARE Education CHAR(10)DECLARE Employees_cursor CURSOR FOR SELECT Education FROM Employees SET i=0SET j=0OPEN 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_c
20、ursor SELECT i AS本科及以上员工所占员工数 SELECT j AS员工总数SELECT i/j AS本科及以上员工所占比例CLOSE Employees_cursor END EXEC Employees_bili4、使用命令的方式修改存储过程的定义:5、对于YGGL数据库,表Employees的EmployeeID列与表Salary的EmployeeID列应该满足参照的完整性规则,请用触发器实现两个表的参照完整性:CREATE TRIGGER Salary_insert ON SalaryFOR INSERT,UPDATEASBEGINIF(SELECT EmployeeID
21、 FROM INSERTED) NOT IN(SELECT EmployeeID FROM Employees)ROLLBACKENDCREATE TRIGGER Employeesupdate ON EmployeesFOR UPDATEASBEGINUPDATE SalarySET EmployeeID=(SELECT EmployeeID FROM INSERTED)WHERE EmployeeID=(SELECT EmployeeID FROM DELETED)ENDCREATE TRIGGER Employeesdelete ON EmployeesFOR DELETEASBEGIN
22、DELETE FROM SalaryWHERE EmployeeID=(SELECT EmployeeID FROM DELETED)ENDINSERT INTO SalaryVALUES (000005,2000,1000)UPDATE EmployeesSET EmployeeID=000000WHERE EmployeeID= 990230DELETE FROM EmployeesWHERE EmployeeID=0000006.当修改表Employees时,若将Employees表中员工的工作时间增加1年,则将收入增加500,若增加2年则增加1000,依次增加。若工作时间减少则无变化:CREATE TRIGGE