SQL實驗(實驗4至實驗7的答案)

1、【精品文檔】如有侵權(quán)，請聯(lián)系網(wǎng)站刪除，僅供學(xué)習(xí)與交流SQL實驗(實驗4至實驗7的答案).精品文檔.實驗實驗41.用select 語句查詢departments和salary表中的所有數(shù)據(jù)：select salary.*, departments.* from salary ,departments 2、查詢departments 中的departmentid：select departmentid from departments go3、查詢 salary中的 income，outcome：select income,outcome from salarygo4、查詢employees表中的

2、部門號，性別，要用distinct消除重復(fù)行：select distinct(departmentid), sexfrom employees 5、查詢月收入高于2000元的員工號碼：select employeeid from salarywhere income>2000go6、查詢1970年以后出生的員工的姓名和住址：select name ,address from employees where birthday>1970go7、查詢所有財務(wù)部的員工的號碼和姓名：select employeeid ,namefrom employeeswhere departmentid

3、 in(select departmentid from departments where departmentname='財務(wù)部')go8、查詢employees員工的姓名，住址和收入水平，2000元以下顯示為低收入，20003000元顯示為中等收入，3000元以上顯示為高收入：select name ,address,case when income-outcome<2000 then '低收入'when income-outcome>3000 then '高收入'else '中等收入'end as '收

4、入等級'from employees,salarywhere employees.employeeid=salary.employeeidgo9、計算salary表中員工月收入的評價數(shù)：select avg(income)as '平均收入' from salary10、查找employees表中最大的員工號碼：select max(employeeid)as '最大員工號碼' from employees11、計算salary表中的所有員工的總支出：select sum(outcome) as'總支出' from salary12、查詢財

5、務(wù)部雇員的最高實際收入：select max(income-outcome) from salary ,employees,departmentswhere salary.employeeid=employees.employeeid and employees.departmentid=departments.departmentid and departmentname='財務(wù)部'go13、查詢財務(wù)部雇員的最低實際收入：select min(income-outcome) from salary ,employees,departmentswhere salary.empl

6、oyeeid=employees.employeeid and employees.departmentid=departments.departmentid and departmentname='財務(wù)部'go14、找出所用地址中含有“中山”的雇員的號碼及部門號：select employeeid ,departmentid from employeeswhere address like'%中山%'go15、查找員工號碼中倒數(shù)第二個數(shù)字為0的員工的姓名，地址和學(xué)歷：select education,address,name from employees wh

7、ere employeeid like'%0_'go16、使用into字句，由表employees創(chuàng)建“男員工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、用子查詢的方法查找查找研發(fā)部比所有財務(wù)部雇員收入都高

8、的雇員的姓名：SELECT Name FROM Employees WHERE EmployeeID IN SELECT EmployeeID FROM Salary WHERE EmployeeID IN SELECT EmployeeId FROM Employees WHERE DepartmentID INSELECT DepartmentID FROM Departments WHERE DepartmentName='研發(fā)部'AND InCome>ALLSELECT InCome FROM Salary WHERE EmployeeID IN SELECT E

9、mployeeId FROM Employees WHERE DepartmentID INSELECT DepartmentID FROM Departments WHERE DepartmentName='財務(wù)部'19、 用子查詢的方法查找所有年齡比研發(fā)部雇員都大的雇員的姓名：select name from employees where Birthday<all ( select birthday from employees where departmentid in (select departmentid from departments where dep

10、artmentname='研發(fā)部'20、查詢每個員工的情況及其薪水的情況： select employees.*,departments.departmentname from employees,departments where employees.departmentid=departments.departmentid21、使用內(nèi)連接方法查找不在財務(wù)部工作的所有員工信息：select employees.* from employees inner join departments on employees.departmentid=departments.depart

11、mentidwhere departmentname!='財務(wù)部'22、使用外連接方法查找出所有員工的月收入：select employees.*,salary.incomefrom employees join salary on employees.employeeid=salary.employeeid23、查找財務(wù)部雇員的最高收入：select max(income)from salary where employeeid in(select employeeid from employees where departmentid in (select departme

12、ntid from departments where departmentname='財務(wù)部'24、查詢財務(wù)部雇員的最高實際收入：select max(income-outcome)from salary where employeeid in(select employeeid from employees where departmentid in (select departmentid from departments where departmentname='財務(wù)部'25、統(tǒng)計財務(wù)部收入在2500元以上的雇員人數(shù)：select count(empl

13、oyeeid)from employees where departmentid in(select departmentid from departments where departmentname='財務(wù)部')and employeeid inselect employeeid from salary where income>2500)26、按部門列出在該部門工作的員工的人數(shù)：select departmentid ,count(*) as 人數(shù)from employees group by departmentid27、按員工的學(xué)歷分組：select educa

14、tion ,count(*) as 人數(shù)from employees group by education28、按員工的工作年份分組，統(tǒng)計年份人數(shù)：select workyear ,count(*) as 人數(shù)from employees group by workyear29、按各雇員的情況收入由低到高排列：select employees.* ,salary.incomefrom employees ,salary where employees.employeeid=salary.employeeidorder by income30、將員工信息按出生時間從小到大排列：select *f

15、rom employees order by birthday31、在order by 字句中使用子查詢，查詢員工姓名，性別和工齡信息，要求按實際收入從大到小排列：select name ,sex,workyear,income-outcomefrom salary ,employeeswhere salary.employeeid=employees.employeeidorder by income-outcome desc視圖部分1、創(chuàng)建view1：Create view view1 as select employees.employeeid,name,departmentname,(

16、income-outcome) as comefrom employees , departments , salary where employees.departmentid=departments.departmentid and employees.employeeid=salary.employeeid2、查詢視圖employeeid：3、向視圖view1中插入一行數(shù)據(jù)：insert into view1 values('111111','謊言','1','30000')4、查看視圖（沒有影響）基本表：實驗51、 定義一

17、個變量，用于描述YGGL數(shù)據(jù)庫的salary表中000001號員工的實際收入，然后查詢該變量：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

18、 from salary,employees where salary.employeeid=employees.employeeid and ='劉明')>3000) select income as '高收入' from salary,employees where salary.employeeid=employees.employeeid and ='劉明'else select'收入不高于'4、使用循環(huán)輸出一個“*”三角形：declare i int decl

19、are j int set j=20set i=1while i<jbegin print(space(j-i)/2)+replicate('*',i)set i=i+2end4、 按部門進(jìn)行分類，使用if語句實現(xiàn)：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

20、='財務(wù)部'if(select departmentid from departments where departmentid1=departmentid)='2')set hy1='人力資源部'if(select departmentid from departments where departmentid1=departmentid)='3')set hy1= '經(jīng)理辦公室'if(select departmentid from departments where departmentid1=departm

21、entid)='4') set hy1= '研發(fā)部'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 '財務(wù)部

22、9;when 2 then '人力資源部'when 3 then '經(jīng)理辦公室'when 4 then '研發(fā)部'when 5 then '市場部'end as 部門號from employees6、自定義一個函數(shù)，計算一個數(shù)的階層：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

23、h int exec h=dbo.hy 4select h as 'jiecheng'7、/*生成隨機數(shù)*/select rand()8、/*平方*/select square(12)9、/*求財務(wù)部收入最高的員工姓名*/select max(name) from employees where employeeid in(select employeeid from salary where employeeid in (select employeeid from employees where departmentid in (select departmentid fr

24、om departments where departmentname='財務(wù)部')select avg(income) as '平均收入'from salary/*聚合函數(shù)與group by 一起使用*/select workyear ,count(*) as 人數(shù)from employees group by workyear/*將字符組成字符串*/select char(123)/*返回字符串左邊開始的個字符*/select left('abcdef',2)/*返回指定日期時間的天數(shù)*/select day(birthday)from em

25、ployees where employeeid='010000'/*獲取當(dāng)前時間*/select getdate()實驗61、 創(chuàng)建索引：create unique index huangyan on employees(employeeid)2、 /*用create index 語句創(chuàng)建主鍵*/3、重建表employees中employeeid列上的索引alter index huangyanon employees rebuild4、刪除索引：5、創(chuàng)建一個新表，使用一個復(fù)合列作為主鍵，作為表的約束，并為其命名：create table employees5 ( emplo

26、yeeid 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、創(chuàng)建新表student，性別只能包含男或女：create table student (號碼char(6) not null,性別char(2)not nullcheck(性別in ('男','女')7、創(chuàng)建新表：create

27、 table employees7(學(xué)號char(10) not null,出生日期datetime not nullcheck(出生日期>'1980-01-01')8、創(chuàng)建一個規(guī)則：9，創(chuàng)建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

28、cascade10、添加一個外鍵，salary與employees有相關(guān)記錄，則拒絕更新employees：alter table salaryadd constraint kc_forforeign key(employeeid)references employees(employeeid)on delete no actionon update no action實驗71、 工作年份大于6時，跟換科室到經(jīng)理辦公室(根據(jù)員工)：Create PROC UpdateDeptByYear(EmpId char(6) )ASBEGINDECLARE year intSELECT year=Wor

29、kYear From Employees WHERE EmployeeID=EmpIdIF(year>6) UPDATE Employees SET DepartmentID='3' WHERE EmployeeID=EmpIdENDEXEC UpdateDeptByYear '020010'SELECT * FROM Employees WHERE Employeeid='020010'2、 根據(jù)每個員工的學(xué)歷將收入提高元：CREATE PROC UpdateInComeByEdu Employeeid char(6)ASBEGINUPD

30、ATE SalarySET InCome=InCome+500FROM SalaryLEFT JOIN EmployeesON Salary.EmployeeID=Employees.EmployeeIDWHERE Salary.Employeeid=EmployeeidENDEXEC UpdateInComeByEdu '020010'SELECT * FROM Salary where EmployeeID='020010'3、游標(biāo)：CREATE PROCEDURE Employees_bili AS BEGIN DECLARE i FLOAT DECLAR

31、E j 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 CLO

32、SE Employees_cursor SELECT i AS'本科及以上員工所占員工數(shù)' SELECT j AS'員工總數(shù)'SELECT i/j AS'本科及以上員工所占比例'CLOSE Employees_cursor END EXEC Employees_bili4、使用命令的方式修改存儲過程的定義：5、對于YGGL數(shù)據(jù)庫，表Employees的EmployeeID列與表Salary的EmployeeID列應(yīng)該滿足參照的完整性規(guī)則，請用觸發(fā)器實現(xiàn)兩個表的參照完整性：CREATE TRIGGER Salary_insert ON Salary

33、FOR INSERT,UPDATEASBEGINIF(SELECT 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 E

34、mployeesdelete ON EmployeesFOR 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

35、9;6.當(dāng)修改表Employees時，若將Employees表中員工的工作時間增加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.創(chuàng)建UPDATE觸發(fā)器,當(dāng)Salary表中Income值增加500時，outcome值增加50：CREATE TRIGGER SA_INCOME ON SalaryFOR UPDATEASBEGINIF(SEL