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

《SQL实验(实验4至实验7的答案).doc》由会员分享，可在线阅读，更多相关《SQL实验(实验4至实验7的答案).doc（21页珍藏版）》请在得力文库 - 分享文档赚钱的网站上搜索。

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 income2000go6、查询1970年以后出生的员工的姓名和住址：select name ,address from employees where birthday1970go7、查询所有财务部的员工的号码和姓名：select employeeid ,namefrom employeeswhere departmentid in(select departmentid from departments where departmen

3、tname=财务部)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 salary10、查找employe

4、es表中最大的员工号码：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.departmentid and de

5、partmentname=财务部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 employeeswhere add

6、ress 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 employeeid in(select e

7、mployeeid 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 Birthday2500)26、按部门列出在该部门工作的员工

8、的人数：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.* ,salary.incomefrom employe

9、es ,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.employeeidorder by inco

10、me-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、向视图view1中插入一行数据：insert in

11、to 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、 判断姓名为“王林”的员工实际收入是否高于3000元，如果是则显示“高收入”，否

12、则显示“收入不高于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 int declare j int set

13、 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 (select employeeid fro

14、m 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(abcdef,2)/*返回指定日期时间的天数*/

15、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、创建一个新表，使用一个复合列作为主键，作为表的约束，并为其命名：create table e

16、mployees5 ( 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 (男,女)7、创建新表：create ta

17、ble 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 delete cascade)10、添加一个外键

18、，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 year=WorkYear From Emplo

19、yees 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+500FROM SalaryLEFT JOI

20、N 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 Employees_cursor CURSOR FOR

21、 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本科及以上员工所

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

23、DCREATE TRIGGER Employeesupdate ON EmployeesFOR UPDATEASBEGINUPDATE SalarySET EmployeeID=(SELECT EmployeeID FROM INSERTED)WHERE EmployeeID=(SELECT EmployeeID FROM DELETED)ENDCREATE TRIGGER Employeesdelete ON EmployeesFOR DELETEASBEGINDELETE FROM SalaryWHERE EmployeeID=(SELECT EmployeeID FROM DELETED

24、)ENDINSERT INTO SalaryVALUES (000005,2000,1000)UPDATE EmployeesSET EmployeeID=000000WHERE EmployeeID= 990230DELETE FROM EmployeesWHERE EmployeeID=0000006.当修改表Employees时，若将Employees表中员工的工作时间增加1年，则将收入增加500，若增加2年则增加1000，依次增加。若工作时间减少则无变化：CREATE TRIGGER EM_WORKYEAR ON EmployeesAFTER UPDATEASBEGINDECLARE

25、i INT,j INTSET i=(SELECT WorkYear FROM INSERTED)SET j=(SELECT WorkYear FROM DELETED)IF(ij)UPDATE SalarySET InCome=InCome+(i-j)*500WHERE EmployeeID IN(SELECT EmployeeID FROM INSERTED)ENDUPDATE Employees SET WorkYear=7WHERE Employeeid=990230SELECT * FROM Employees WHERE Employeeid=9902307.创建UPDATE触发器,

26、当Salary表中Income值增加500时，outcome值增加50：CREATE TRIGGER SA_INCOME ON SalaryFOR UPDATEASBEGINIF(SELECT InCome FROM INSERTED)-(SELECT InCome FROM DELETED)=500)UPDATE SalarySET OutCome=OutCome+50WHERE EmployeeID=(SELECT EmployeeID FROM INSERTED)ENDSELECT INCOME,OUTCOMEFROM SALARYWHERE EMPLOYEEID= 504209UPDA

27、TE SalarySET InCome=InCome+500WHERE EmployeeID= 504209SELECT INCOME,OUTCOMEFROM SALARYWHERE EMPLOYEEID=5042098.创建INSTEAD OF触发器，实现向不可更新视图插入数据：CREATE VIEW A_VIEWASSELECT Employees.EmployeeID,Name,WorkYear,InCome,OutComeFROM Employees,SalaryWHERE Employees.EmployeeID=Salary.EmployeeIDGOCREATE TRIGGER G

28、XSTON A_VIEWINSTEAD OF INSERTASBEGINDECLARE EI CHAR(6),NAME CHAR(10),WY TINYINT,IC FLOAT,OC FLOATSELECT EI=EmployeeID,NAME=Name,WY=WorkYear,IC=InCome,OC=OutCome FROM insertedINSERT INTO Employees(EmployeeID,Name,WorkYear) VALUES(EI,NAME,WY)INSERT INTO Salary VALUES(EI,IC,OC)ENDINSERT INTO A_VIEWVALUES(000011,小黄,3,2000,1500)SELECT * FROM A_VIEWWHERE EmployeeID=000011SELECT * FROM EmployeesWHERE EmployeeID=000011SELECT * FROM SalaryWHERE EmployeeID=0000119.创建DDL触发器,当删除数据库时，提示“无法删除”并回滚删除操作CREATE TRIGGER T_DELETEON ALL SERVERAFTER DROP_DATABASEASPRINT不能删除该表ROLLBACK TRANSACTIONGODROP DATABASE YGGL