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

1、. 實驗實驗 41.用 select語句查詢departments和 salary 表中的所有數(shù)據(jù)：select salary .*, departments.*fromsalary,departments2、查詢 departments中的 departmentid：select departmentidfrom departmentsgo3、查詢salary 中的income ， outcome ：selectincome ,outcomefromsalarygo.專業(yè)學(xué)習(xí)資料.4、查詢 employees表中的部門號 ，性別 ，要用 distinct消除重復(fù)行 ：select disti

2、nct (departmentid), sexfromemployees5、查詢月收入高于2000 元的員工號碼：select employeeidfrom salarywhere income > 2000go6、查詢 1970 年以后出生的員工的姓名和住址：select name,addressfromemployeeswhere birthday> 1970go.專業(yè)學(xué)習(xí)資料.7、查詢所有財務(wù)部的員工的號碼和姓名：select employeeid,namefromemployeeswhere departmentidin( select departmentidfromde

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

4、s,salary.專業(yè)學(xué)習(xí)資料.where employees.employeeid= salary .employeeidgo9、計算 salary 表中員工月收入的評價數(shù)：select avg (income )as ' 平均收入 ' from salary10 、查找 employees表中最大的員工號碼：select max (employeeid)as '最大員工號碼' fromemployees11 、計算 salary 表中的所有員工的總支出：select sum (outcome ) as'總支出 ' fromsalary12 、

5、查詢財務(wù)部雇員的最高實際收入：select max (income-outcome) from salary,employees ,departmentswhere salary .employeeid= employees.employeeidandemployees .departmentid= departments.departmentidand departmentname= ' 財務(wù)部 'go.專業(yè)學(xué)習(xí)資料.13 、查詢財務(wù)部雇員的最低實際收入：select min (income-outcome) from salary,employees ,department

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

7、ess ,namefromemployeeswhere employeeidlike '%0_'go.專業(yè)學(xué)習(xí)資料.16 、使用 into 字句，由表 employees創(chuàng)建 “男員工 1”表 ，包括編號和姓名：select employeeid,nameinto男員工表fromemployeeswhere sex = '1'go17 、用子查詢的方法查找收入在2500 元以下的雇員的情況：select * fromemployeeswhere employeeidin(select employeeidfrom salarywhereincome < 2

8、500 )go18 、用子查詢的方法查找查找研發(fā)部比所有財務(wù)部雇員收入都高的雇員的姓名：SELECT NameFROM EmployeesWHERE EmployeeIDIN(SELECT EmployeeIDFROM SalaryWHERE EmployeeIDIN(SELECT EmployeeIdFROM EmployeesWHERE DepartmentIDIN(.專業(yè)學(xué)習(xí)資料.SELECT DepartmentIDFROM DepartmentsWHERE DepartmentName= '研發(fā)部 ')AND InCome >ALL(SELECT InComeF

9、ROM SalaryWHERE EmployeeIDIN(SELECT EmployeeIdFROM EmployeesWHERE DepartmentIDIN(SELECT DepartmentIDFROM DepartmentsWHERE DepartmentName= '財務(wù)部 ')19 、 用子查詢的方法查找所有年齡比研發(fā)部雇員都大的雇員的姓名：select namefromemployees.專業(yè)學(xué)習(xí)資料.whereBirthday<all( select birthdayfrom employeeswhere departmentidin(select dep

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

11、epartmentid= departments.departmentidwhere departmentname!= '財務(wù)部 '.專業(yè)學(xué)習(xí)資料.22 、使用外連接方法查找出所有員工的月收入：select employees.*,salary .incomefromemployeesjoin salaryon employees.employeeid= salary .employeeid23 、查找財務(wù)部雇員的最高收入：selectmax (income )from salarywhereemployeeid in(selectemployeeidfromemployees

12、wheredepartmentidin(selectdepartmentidfromdepartmentswheredepartmentname= '財務(wù)部 ')24 、查詢財務(wù)部雇員的最高實際收入：.專業(yè)學(xué)習(xí)資料.select max (income - outcome)fromsalarywhere employeeidin(select employeeidfromemployeeswheredepartmentidin(select departmentidfrom departmentswhere departmentname= '財務(wù)部 ')25 、

13、統(tǒng)計財務(wù)部收入在2500 元以上的雇員人數(shù)：select count (employeeid)fromemployeeswhere departmentidin(select departmentidfrom departmentswhere departmentname= '財務(wù)部 ')and employeeidin(select employeeidfromsalary.專業(yè)學(xué)習(xí)資料.where income > 2500 )26 、按部門列出在該部門工作的員工的人數(shù)：select departmentid,count (*) as 人數(shù)fromemployeesg

14、roupby departmentid27 、按員工的學(xué)歷分組：select education,count (*) as 人數(shù)fromemployeesgroupby education28 、按員工的工作年份分組，統(tǒng)計年份人數(shù)：select workyear,count (*) as 人數(shù)fromemployeesgroupby workyear.專業(yè)學(xué)習(xí)資料.29 、按各雇員的情況收入由低到高排列：selectemployees.* ,salary .incomefromemployees,salarywhereemployees.employeeid = salary .employe

15、eidorderby income30 、將員工信息按出生時間從小到大排列：select *fromemployeesorderby birthday31 、在 order by字句中使用子查詢，查詢員工姓名，性別和工齡信息，要求按實際收入從大到小排列 ：selectname,sex,workyear,income -outcomefromsalary,employeeswheresalary .employeeid = employees .employeeidorderby income -outcomedesc.專業(yè)學(xué)習(xí)資料.視圖部分1、創(chuàng)建 view1 ：Createview view

16、1asselect employees.employeeid,name ,departmentname,(income - outcome) as comefromemployees, departments, salarywhereemployees .departmentid= departments.departmentidandemployees .employeeid= salary .employeeid2、查詢視圖employeeid：3、向視圖 view1 中插入一行數(shù)據(jù)：insertinto view1values ('111111' ,'謊言 

17、9;,'1' ,'30000' ).專業(yè)學(xué)習(xí)資料.4、查看視圖 （沒有影響 ）基本表 ：實驗 51、 定義一個變量 ，用于描述YGGL 數(shù)據(jù)庫的salary 表中 000001 號員工的實際收入，然后查詢該變量 ：declare hyintset hy =( select income - outcomefromsalarywhereemployeeid= '000001' )select hy2、 使用運算符 “> ”：select namefromemployees.專業(yè)學(xué)習(xí)資料.where birthday> '1974

18、-10-10'3、 判斷姓名為 “王林 ”的員工實際收入是否高于3000元，如果是則顯示“高收入 ”，否則顯示“收入不高于3000 ”：if (select incomefrom salary ,employeeswhere salary .employeeid= employees.employeeidand = '劉明 ')> 3000 )select incomeas ' 高收入 'from salary ,employeeswhere salary .employeeid= employees.employee

19、idand = '劉明 'elseselect '收入不高于'4、使用循環(huán)輸出一個“*”三角形 ：declare i intdeclare j intset j = 20set i = 1while i < jbegin.專業(yè)學(xué)習(xí)資料.print (space (j -i )/ 2)+ replicate ('*' ,i )set i = i + 2end4、 按部門進(jìn)行分類，使用 if 語句實現(xiàn) ：Create functionhy1 (departmentid1char (3)returnschar (10

20、)asbegindeclarehy1char (10 )if (selectdepartmentidfromdepartmentswheredepartmentid1= departmentid)= '1' )set hy1 = ' 財務(wù)部 'if (selectdepartmentidfromdepartmentswheredepartmentid1= departmentid)= '2' )set hy1 = '人力資源部 'if (selectdepartmentidfromdepartmentswheredepartmen

21、tid1= departmentid)= '3' )set hy1 = '經(jīng)理辦公室 'if (selectdepartmentidfromdepartmentswheredepartmentid1= departmentid)= '4' )set hy1 ='研發(fā)部 'if (selectdepartmentidfromdepartmentswheredepartmentid1= departmentid)= '5' )set hy1 = '市場部 '.專業(yè)學(xué)習(xí)資料.returnhy1endsel

22、ect employeeid,name ,address ,dbo .hy1 (departmentid) fromemployeesselect employeeid,name ,address ,case departmentidwhen 1 then'財務(wù)部 'when 2 then'人力資源部'when 3 then'經(jīng)理辦公室'when 4 then'研發(fā)部 'when 5 then'市場部 'end as 部門號fromemployees.專業(yè)學(xué)習(xí)資料.6、自定義一個函數(shù)，計算一個數(shù)的階層：create

23、functionhy (hy2 int ) returnsintasbegindeclare i intset i = hy2declare j intset j = 1while i > 1beginset j = j *iset i = i -1endreturn (j )enddeclare h intexec h = dbo .hy 4select has 'jiecheng'7、 /* 生成隨機(jī)數(shù) */select rand ()8、 /* 平方 */.專業(yè)學(xué)習(xí)資料.selectsquare (12 )9、 /* 求財務(wù)部收入最高的員工姓名*/selectmax

24、 (name )fromemployeeswhereemployeeidin(selectemployeeidfromsalarywhereemployeeidin(selectemployeeidfromemployeeswheredepartmentidin(selectdepartmentidfromdepartmentswhere departmentname= '財務(wù)部 ')select avg (income ) as ' 平均收入 'fromsalary/* 聚合函數(shù)與group by一起使用 */select workyear,count (*)

25、 as 人數(shù)fromemployees.專業(yè)學(xué)習(xí)資料.groupby workyear/* 將字符組成字符串*/select char (123 )/* 返回字符串左邊開始的個字符*/select left( 'abcdef' ,2 )/* 返回指定日期時間的天數(shù)*/select day (birthday )fromemployeeswhere employeeid= '010000'/* 獲取當(dāng)前時間*/select getdate ()實驗 61、 創(chuàng)建索引 ：createuniqueindex huangyanon employees(employeei

26、d)2、 /* 用 create index語句創(chuàng)建主鍵*/.專業(yè)學(xué)習(xí)資料.3、重建表employees中 employeeid列上的索引alterindex huangyanon employeesrebuild4、刪除索引 ：5、創(chuàng)建一個新表，使用一個復(fù)合列作為主鍵，作為表的約束，并為其命名：createtable employees5( employeeidchar (6) not null,name char (5) not null,sex tinyint ,educationchar (4),constraintyanprimarykey (employeeid,name )為新表

27、添加一列：altertable employees5add addresschar (10 )6、創(chuàng)建新表 student ，性別只能包含男或女：createtable student(號碼 char (6 ) not null,.專業(yè)學(xué)習(xí)資料.性別 char (2)notnullcheck (性別 in ('男 ','女 ' )7、創(chuàng)建新表 ：createtable employees7(學(xué)號 char (10 ) not null,出生日期 datetimenot nullcheck (出生日期 > '1980-01-01')8、創(chuàng)建一

28、個規(guī)則：9，創(chuàng)建 salary2 ：createtable salary2(employeeidchar (6) not null primarykey ,incomefloatnot null,outcomefloatnot null,foreignkey (employeeid).專業(yè)學(xué)習(xí)資料.referencessalary (employeeid )onupdatecascadeondeletecascade)10 、添加一個外鍵 ， salary與 employees有相關(guān)記錄 ，則拒絕更新employees：alter tablesalaryaddconstraintkc_forf

29、oreign key (employeeid )referencesemployees (employeeid )ondeletenoactiononupdatenoaction實驗 71、 工作年份大于6時，跟換科室到經(jīng)理辦公室(根據(jù)員工 )：CreatePROC UpdateDeptByYear(EmpIdchar (6) )AS.專業(yè)學(xué)習(xí)資料.BEGINDECLARE yearintSELECT year = WorkYearFrom EmployeesWHERE EmployeeID= EmpIdIF(year > 6)UPDATE EmployeesSET Department

30、ID= '3'WHERE EmployeeID= EmpIdENDEXEC UpdateDeptByYear'020010'SELECT * FROM EmployeesWHERE Employeeid= '020010'2、 根據(jù)每個員工的學(xué)歷將收入提高元：CREATE PROC UpdateInComeByEdu Employeeidchar (6)ASBEGINUPDATE SalarySET InCome = InCome + 500FROM SalaryLEFT JOIN EmployeesON Salary .EmployeeID=

31、Employees .EmployeeIDWHERE Salary .Employeeid= Employeeid.專業(yè)學(xué)習(xí)資料.ENDEXEC UpdateInComeByEdu'020010'SELECT * FROM Salarywhere EmployeeID= '020010'3、游標(biāo)：CREATE PROCEDURE Employees_biliASBEGINDECLARE i FLOATDECLARE j FLOATDECLARE EducationCHAR (10)DECLARE Employees_cursorCURSORFOR SELECT

32、EducationFROM EmployeesSET i = 0SET j = 0OPEN Employees_cursorFETCH Employees_cursorINTO EducationWHILE (FETCH_STATUS = 0)BEGINIF(Education!= '大專 ' )SET i = i + 1.專業(yè)學(xué)習(xí)資料.SET j = j+ 1FETCH Employees_cursorINTO EducationENDCLOSE Employees_cursorSELECT i AS'本科及以上員工所占員工數(shù)'SELECT j AS'

33、員工總數(shù) 'SELECT i / j AS'本科及以上員工所占比例'CLOSE Employees_cursorENDEXEC Employees_bili4、使用命令的方式修改存儲過程的定義：.專業(yè)學(xué)習(xí)資料.5、對于 YGGL 數(shù)據(jù)庫 ，表 Employees的 EmployeeID列與表 Salary 的 EmployeeID列應(yīng)該滿足參照的完整性規(guī)則，請用觸發(fā)器實現(xiàn)兩個表的參照完整性：CREATE TRIGGER Salary_insertON SalaryFOR INSERT,UPDATEASBEGINIF(SELECT EmployeeIDFROM INSER

34、TED ) NOT IN( SELECT EmployeeIDFROM Employees)ROLLBACKENDCREATE TRIGGER EmployeesupdateON EmployeesFOR UPDATEASBEGINUPDATE SalarySET EmployeeID=( SELECT EmployeeIDFROM INSERTED ).專業(yè)學(xué)習(xí)資料.WHERE EmployeeID=( SELECT EmployeeIDFROM DELETED )ENDCREATE TRIGGER EmployeesdeleteON EmployeesFOR DELETEASBEGINDE

35、LETE FROM SalaryWHERE EmployeeID=( SELECT EmployeeIDFROM DELETED )ENDINSERT INTO SalaryVALUES ('000005' ,2000 ,1000 )UPDATE EmployeesSET EmployeeID= '000000'WHERE EmployeeID= ' 990230'DELETE FROM EmployeesWHERE EmployeeID= '000000'.專業(yè)學(xué)習(xí)資料.6.當(dāng)修改表 Employees 時，若將 Employe

36、es 表中員工的工作時間增加1 年，則將收入增加500 ，若增加 2 年則增加 1000 ，依次增加 。 若工作時間減少則無變化：CREATE TRIGGER EM_WORKYEAR ON EmployeesAFTER UPDATEASBEGINDECLARE i INT ,j INTSET i =( SELECT WorkYearFROM INSERTED )SET j =( SELECT WorkYearFROM DELETED)IF(i > j)UPDATE SalarySET InCome = InCome +( i - j)* 500WHERE EmployeeIDIN( SE

37、LECT EmployeeIDFROM INSERTED )ENDUPDATE EmployeesSET WorkYear = 7WHERE Employeeid= '990230'SELECT * FROM EmployeesWHERE Employeeid= '990230'.專業(yè)學(xué)習(xí)資料.7.創(chuàng)建 UPDATE 觸發(fā)器 ,當(dāng) Salary 表中 Income值增加500 時， outcome值增加 50 ：CREATE TRIGGER SA_INCOMEON SalaryFOR UPDATEASBEGINIF(SELECT InComeFROM INSERTED )-( SELECT InComeFROM DELETED