SQL实验实验至实验的答案

1、实验实验41.用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 salarygo4、查询employees表中的部门号，性别，要用distinct消除重复行：select distinct(departmen

2、tid), sexfrom employees 5、查询月收入高于2000元的员工号码：select employeeid from salarywhere income>2000go6、查询1970年以后出生的员工的姓名和住址：select name ,address from employees where birthday>1970go7、查询所有财务部的员工的号码和姓名：select employeeid ,namefrom employeeswhere departmentid in(select departmentid from departments where d

3、epartmentname='财务部')go8、查询employees员工的姓名，住址和收入水平，2000元以下显示为低收入，20003000元显示为中等收入，3000元以上显示为高收入：select name ,address,case when income-outcome<2000 then '低收入'when income-outcome>3000 then '高收入'else '中等收入'end as '收入等级'from employees,salarywhere employees.emp

4、loyeeid=salary.employeeidgo9、计算salary表中员工月收入的评价数：select avg(income)as '平均收入' from salary10、查找employees表中最大的员工号码：select max(employeeid)as '最大员工号码' from employees11、计算salary表中的所有员工的总支出：select sum(outcome) as'总支出' from salary12、查询财务部雇员的最高实际收入：select max(income-outcome) from sala

5、ry ,employees,departmentswhere salary.employeeid=employees.employeeid and employees.departmentid=departments.departmentid and departmentname='财务部'go13、查询财务部雇员的最低实际收入：select min(income-outcome) from salary ,employees,departmentswhere salary.employeeid=employees.employeeid and employees.depart

6、mentid=departments.departmentid and departmentname='财务部'go14、找出所用地址中含有“中山”的雇员的号码及部门号：select employeeid ,departmentid from employeeswhere address like'%中山%'go15、查找员工号码中倒数第二个数字为0的员工的姓名，地址和学历：select education,address,name from employees where employeeid like'%0_'go16、使用into字句，由表

7、employees创建“男员工1”表，包括编号和姓名：select employeeid,name into 男员工表from employees where sex='1'go17、用子查询的方法查找收入在2500元以下的雇员的情况：select * from employees where employeeid in(select employeeid from salary where income<2500)go18、用子查询的方法查找查找研发部比所有财务部雇员收入都高的雇员的姓名：SELECT Name FROM Employees WHERE Employee

8、ID IN (SELECT EmployeeID FROM Salary WHERE EmployeeID IN ( SELECT EmployeeId FROM Employees WHERE DepartmentID IN (SELECT DepartmentID FROM Departments WHERE DepartmentName='研发部' )AND InCome>ALL(SELECT InCome FROM Salary WHERE EmployeeID IN ( SELECT EmployeeId FROM Employees WHERE Departm

9、entID IN (SELECT DepartmentID FROM Departments WHERE DepartmentName='财务部' )19、 用子查询的方法查找所有年龄比研发部雇员都大的雇员的姓名：select name from employees where Birthday<all ( select birthday from employees where departmentid in (select departmentid from departments where departmentname='研发部' )20、查询每个

10、员工的情况及其薪水的情况： select employees.*,departments.departmentname from employees,departments where employees.departmentid=departments.departmentid21、使用内连接方法查找不在财务部工作的所有员工信息：select employees.* from employees inner join departments on employees.departmentid=departments.departmentidwhere departmentname!='

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

12、rtmentname='财务部')24、查询财务部雇员的最高实际收入：select max(income-outcome)from salary where employeeid in(select employeeid from employees where departmentid in (select departmentid from departments where departmentname='财务部')25、统计财务部收入在2500元以上的雇员人数：select count(employeeid)from employees where de

13、partmentid in(select departmentid from departments where departmentname='财务部')and employeeid in(select employeeid from salary where income>2500)26、按部门列出在该部门工作的员工的人数：select departmentid ,count(*) as 人数from employees group by departmentid27、按员工的学历分组：select education ,count(*) as 人数from empl

14、oyees group by education28、按员工的工作年份分组，统计年份人数：select workyear ,count(*) as 人数from employees group by workyear29、按各雇员的情况收入由低到高排列：select employees.* ,salary.incomefrom employees ,salary where employees.employeeid=salary.employeeidorder by income30、将员工信息按出生时间从小到大排列：select *from employees order by birthd

15、ay31、在order by 字句中使用子查询，查询员工姓名，性别和工龄信息，要求按实际收入从大到小排列：select name ,sex,workyear,income-outcomefrom salary ,employeeswhere salary.employeeid=employees.employeeidorder by income-outcome desc视图部分1、创建view1：Create view view1 as select employees.employeeid,name,departmentname,(income-outcome) as comefrom e

16、mployees , departments , salary where employees.departmentid=departments.departmentid and employees.employeeid=salary.employeeid2、查询视图employeeid：3、向视图view1中插入一行数据：insert into view1 values('111111','谎言','1','30000')4、查看视图（没有影响）基本表：实验51、 定义一个变量，用于描述YGGL数据库的salary表中00000

17、1号员工的实际收入，然后查询该变量：declare hy int set hy=(select income-outcome from salary where employeeid='000001')select hy2、 使用运算符“>”：select name from employees where birthday>'1974-10-10'3、 判断姓名为“王林”的员工实际收入是否高于3000元，如果是则显示“高收入”，否则显示“收入不高于3000”：if(select income from salary,employees where

18、salary.employeeid=employees.employeeid and ='刘明')>3000) select income as '高收入' from salary,employees where salary.employeeid=employees.employeeid and ='刘明'else select'收入不高于'4、使用循环输出一个“*”三角形：declare i int declare j int set j=20set i=1whil

19、e i<jbegin print(space(j-i)/2)+replicate('*',i)set i=i+2end4、 按部门进行分类，使用if语句实现：Create function hy1(departmentid1 char(3)returns char(10) as begin declare hy1 char(10)if(select departmentid from departments where departmentid1=departmentid)='1') set hy1='财务部'if(select depar

20、tmentid from departments where departmentid1=departmentid)='2')set hy1='人力资源部'if(select departmentid from departments where departmentid1=departmentid)='3')set hy1= '经理办公室'if(select departmentid from departments where departmentid1=departmentid)='4') set hy1=

21、'研发部'if(select departmentid from departments where departmentid1=departmentid)='5')set hy1='市场部'return hy1endselect employeeid,name,address,dbo.hy1(departmentid) from employeesselect employeeid,name,address,case departmentidwhen 1 then '财务部'when 2 then '人力资源部'

22、when 3 then '经理办公室'when 4 then '研发部'when 5 then '市场部'end as 部门号from employees6、自定义一个函数，计算一个数的阶层：create function hy(hy2 int) returns intas begin declare i int set i=hy2declare j int set j=1while i>1beginset j=j*iset i=i-1endreturn(j)end declare h int exec h=dbo.hy 4select h

23、 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 departmentid from departments where departme

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

25、;010000'/*获取当前时间*/select getdate()实验61、 创建索引：create unique index huangyan on employees(employeeid)2、 /*用create index 语句创建主键*/3、重建表employees中employeeid列上的索引alter index huangyanon employees rebuild4、删除索引：5、创建一个新表，使用一个复合列作为主键，作为表的约束，并为其命名：create table employees5 ( employeeid char(6) not null,name c

26、har(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 (号码char(6) not null,性别char(2)not nullcheck(性别in ('男','女')7、创建新表：create table employees7(学号char(10)

27、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,outcome float not null,foreign key(employeeid)references salary(employeeid)on update cascadeon delete cascade)10、添加一个外键，salary与empl

28、oyees有相关记录，则拒绝更新employees：alter table salaryadd constraint kc_forforeign key(employeeid)references employees(employeeid)on delete no actionon update no action实验71、 工作年份大于6时，跟换科室到经理办公室(根据员工)：Create PROC UpdateDeptByYear(EmpId char(6) )ASBEGINDECLARE year intSELECT year=WorkYear From Employees WHERE E

29、mployeeID=EmpIdIF(year>6) UPDATE Employees SET DepartmentID='3' WHERE EmployeeID=EmpIdENDEXEC UpdateDeptByYear '020010'SELECT * FROM Employees WHERE Employeeid='020010'2、 根据每个员工的学历将收入提高元：CREATE PROC UpdateInComeByEdu Employeeid char(6)ASBEGINUPDATE SalarySET InCome=InCome+

30、500FROM SalaryLEFT JOIN EmployeesON Salary.EmployeeID=Employees.EmployeeIDWHERE Salary.Employeeid=EmployeeidENDEXEC UpdateInComeByEdu '020010'SELECT * FROM Salary where EmployeeID='020010'3、游标：CREATE PROCEDURE Employees_bili AS BEGIN DECLARE i FLOAT DECLARE j FLOATDECLARE Education C

31、HAR(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_cursor SELECT i

32、 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(S

33、ELECT EmployeeID 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 EmployeesF

34、OR DELETEASBEGINDELETE FROM SalaryWHERE EmployeeID=(SELECT EmployeeID FROM DELETED)ENDINSERT INTO SalaryVALUES ('000005',2000,1000)UPDATE EmployeesSET EmployeeID='000000'WHERE EmployeeID=' 990230'DELETE FROM EmployeesWHERE EmployeeID='000000'6.当修改表Employees时，若将Employe

35、es表中员工的工作时间增加1年，则将收入增加500，若增加2年则增加1000，依次增加。若工作时间减少则无变化：CREATE TRIGGER EM_WORKYEAR ON EmployeesAFTER UPDATEASBEGINDECLARE i INT,j INTSET i=(SELECT WorkYear FROM INSERTED)SET j=(SELECT WorkYear FROM DELETED)IF(i>j)UPDATE SalarySET InCome=InCome+(i-j)*500WHERE EmployeeID IN(SELECT EmployeeID FROM INSERTED)ENDUPDATE Employees SET WorkYear=7WHERE Employeeid='990230'SELECT * FROM Employees WHERE Employeeid='990230'7.创建UPDATE触发器,当Salary表中Income值增加500时，outcome值增加50：CREATE TRIGGER SA_INCOME ON SalaryFOR UPDATEASBEGINIF(SELECT