《SQL实验实验至实验的答案.docx》由会员分享，可在线阅读，更多相关《SQL实验实验至实验的答案.docx（25页珍藏版）》请在taowenge.com淘文阁网|工程机械CAD图纸|机械工程制图|CAD装配图下载|SolidWorks_CaTia_CAD_UG_PROE_设计图分享下载上搜索。
1、试验试验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 distinct(department
2、id), 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 department
3、name=财务部)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、查找employee
4、s表中最大的员工号码：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 dep
5、artmentname=财务部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 addr
6、ess 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 em
7、ployeeid from salary where incomeALLSELECT InCome FROM Salary WHERE EmployeeID IN SELECT EmployeeId FROM Employees WHERE DepartmentID INSELECT DepartmentID FROM Departments WHERE DepartmentName=财务部19、 用子查询的方法查找全部年龄比研发部雇员都大的雇员的姓名：select name from employees where Birthday2500)26、按部门列出在该部门工作的员工的人数：sele
8、ct 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 employees ,sala
9、ry 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 income-outco
10、me 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 into view1
11、 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 j=20set
13、 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 from employ
14、ees 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)/*返回指定日期时间的天数*/select d
15、ay(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 employees
16、5 ( 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 table empl
17、oyees7(学号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 cascade10、添加一个外键，salary与e
18、mployees有相关记录，则回绝更新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 WHER
19、E 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 JOIN Employe
20、esON 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 SELECT E
21、ducation 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
22、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)ROLLBACKENDCREATE T
23、RIGGER 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)ENDINSER
24、T 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 i INT,j I
25、NTSET 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触发器,当Salary表中
26、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= 504209UPDATE Salary
27、SET 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 GXSTON A_V
28、IEWINSTEAD 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第 25 页