SQL实验实验4至实验7的答案.docx

1、SQL实验实验4至实验7的答案实验实验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 dis

2、tinct(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

3、where departmentname=财务部)go8、查询employees员工的姓名，住址和收入水平，2000元以下显示为低收入，20003000元显示为中等收入，3000元以上显示为高收入：select name ,address,case when income-outcome3000 then 高收入else 中等收入end as 收入等级from employees,salarywhere employees.employeeid=salary.employeeidgo9、计算salary表中员工月收入的评价数：select avg(income)as 平均收入 from sal

4、ary10、查找employees表中最大的员工号码：select max(employeeid)as 最大员工号码 from employees11、计算salary表中的所有员工的总支出：select sum(outcome) as总支出 from salary12、查询财务部雇员的最高实际收入：select max(income-outcome) from salary ,employees,departmentswhere salary.employeeid=employees.employeeid and employees.departmentid=departments.depa

5、rtmentid and departmentname=财务部go13、查询财务部雇员的最低实际收入：select min(income-outcome) from salary ,employees,departmentswhere salary.employeeid=employees.employeeid and employees.departmentid=departments.departmentid and departmentname=财务部go14、找出所用地址中含有“中山”的雇员的号码及部门号：select employeeid ,departmentid from emp

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

7、eid 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 DepartmentName=财务部 ) ) )19、 用子查询的方法查找所有年龄比研发部雇员都大的雇员的姓名：select name from employees where Birthday

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

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

10、mployeeidorder by income-outcome desc视图部分1、创建view1：Create view view1 as select employees.employeeid,name,departmentname,(income-outcome) as comefrom employees , departments , salary where employees.departmentid=departments.departmentid and employees.employeeid=salary.employeeid2、查询视图employeeid：3、向视图

11、view1中插入一行数据：insert into view1 values(111111,谎言,1,30000)4、查看视图（没有影响）基本表：实验51、 定义一个变量，用于描述YGGL数据库的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、 判断姓名为“王林”的员工实际收入是

12、否高于3000元，如果是则显示“高收入”，否则显示“收入不高于3000”：if(select income from salary,employees where salary.employeeid=employees.employeeid and employees.name=刘明)3000) select income as 高收入 from salary,employees where salary.employeeid=employees.employeeid and employees.name=刘明else select收入不高于4、使用循环输出一个“*”三角形：declare i

13、 int declare j int set j=20set i=1while i1beginset j=j*iset i=i-1endreturn(j)end declare h int exec h=dbo.hy 4select h as jiecheng7、/*生成随机数*/select rand()8、/*平方*/select square(12)9、/*求财务部收入最高的员工姓名*/select max(name) from employees where employeeid in(select employeeid from salary where employeeid in

14、(select employeeid from employees where departmentid in (select departmentid from departments where departmentname=财务部)select avg(income) as 平均收入from salary/*聚合函数与group by 一起使用*/select workyear ,count(*) as 人数from employees group by workyear/*将字符组成字符串*/select char(123)/*返回字符串左边开始的个字符*/select left(ab

15、cdef,2)/*返回指定日期时间的天数*/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 huangyanon employees rebuild4、删除索引：5、创建一个新表，使用一个复合列作为主键，作为表的约

16、束，并为其命名：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 (号码char(6) not null,性别char(2)not nullcheck(性别in

17、 (男,女)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,outcome float not null,foreign key(employeeid)references salary(employeeid)on update cascadeon de

18、lete cascade)10、添加一个外键，salary与employees有相关记录，则拒绝更新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 ye

19、ar=WorkYear From Employees WHERE EmployeeID=EmpIdIF(year6) UPDATE Employees SET DepartmentID=3 WHERE EmployeeID=EmpIdENDEXEC UpdateDeptByYear 020010SELECT * FROM Employees WHERE Employeeid=0200102、 根据每个员工的学历将收入提高元：CREATE PROC UpdateInComeByEdu Employeeid char(6)ASBEGINUPDATE SalarySET InCome=InCome+

20、500FROM SalaryLEFT JOIN EmployeesON Salary.EmployeeID=Employees.EmployeeIDWHERE Salary.Employeeid=EmployeeidENDEXEC UpdateInComeByEdu 020010SELECT * FROM Salary where EmployeeID=0200103、游标：CREATE PROCEDURE Employees_bili AS BEGIN DECLARE i FLOAT DECLARE j FLOATDECLARE Education CHAR(10)DECLARE Emplo

21、yees_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 AS本科及以上员工所占员工数 SELECT j AS员工总数SELECT i/j AS本科及以上员工所占比例CLOSE Employees_cursor END EXEC Employees_bili4、使用命令的方式修改存储过程的定义：5、对于YGGL数据库，