SQL實(shí)驗(yàn)(實(shí)驗(yàn)4至實(shí)驗(yàn)7的答案)

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

2、nct (departmentid), sexfromemployees5、查詢(xún)?cè)率杖敫哂?000 元的員工號(hào)碼：select employeeidfrom salarywhere income > 2000go6、查詢(xún) 1970 年以后出生的員工的姓名和住址：select name,addressfromemployeeswhere birthday> 1970go.專(zhuān)業(yè)學(xué)習(xí)資料.7、查詢(xún)所有財(cái)務(wù)部的員工的號(hào)碼和姓名：select employeeid,namefromemployeeswhere departmentidin( select departmentidfromde

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

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

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

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

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

8、500 )go18 、用子查詢(xún)的方法查找查找研發(fā)部比所有財(cái)務(wù)部雇員收入都高的雇員的姓名：SELECT NameFROM EmployeesWHERE EmployeeIDIN(SELECT EmployeeIDFROM SalaryWHERE EmployeeIDIN(SELECT EmployeeIdFROM EmployeesWHERE DepartmentIDIN(.專(zhuān)業(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= '財(cái)務(wù)部 ')19 、 用子查詢(xún)的方法查找所有年齡比研發(fā)部雇員都大的雇員的姓名：select namefromemployees.專(zhuān)業(yè)學(xué)習(xí)資料.whereBirthday<all( select birthdayfrom employeeswhere departmentidin(select dep

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

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

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

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

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

15、eidorderby income30 、將員工信息按出生時(shí)間從小到大排列：select *fromemployeesorderby birthday31 、在 order by字句中使用子查詢(xún)，查詢(xún)員工姓名，性別和工齡信息，要求按實(shí)際收入從大到小排列 ：selectname,sex,workyear,income -outcomefromsalary,employeeswheresalary .employeeid = employees .employeeidorderby income -outcomedesc.專(zhuān)業(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、查詢(xún)視圖employeeid：3、向視圖 view1 中插入一行數(shù)據(jù)：insertinto view1values ('111111' ,'謊言 

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

18、-10-10'3、 判斷姓名為 “王林 ”的員工實(shí)際收入是否高于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)輸出一個(gè)“*”三角形 ：declare i intdeclare j intset j = 20set i = 1while i < jbegin.專(zhuān)業(yè)學(xué)習(xí)資料.print (space (j -i )/ 2)+ replicate ('*' ,i )set i = i + 2end4、 按部門(mén)進(jìn)行分類(lèi)，使用 if 語(yǔ)句實(shí)現(xiàn) ：Create functionhy1 (departmentid1char (3)returnschar (10

20、)asbegindeclarehy1char (10 )if (selectdepartmentidfromdepartmentswheredepartmentid1= departmentid)= '1' )set hy1 = ' 財(cái)務(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 = '市場(chǎng)部 '.專(zhuān)業(yè)學(xué)習(xí)資料.returnhy1endsel

22、ect employeeid,name ,address ,dbo .hy1 (departmentid) fromemployeesselect employeeid,name ,address ,case departmentidwhen 1 then'財(cái)務(wù)部 'when 2 then'人力資源部'when 3 then'經(jīng)理辦公室'when 4 then'研發(fā)部 'when 5 then'市場(chǎng)部 'end as 部門(mén)號(hào)fromemployees.專(zhuān)業(yè)學(xué)習(xí)資料.6、自定義一個(gè)函數(shù)，計(jì)算一個(gè)數(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、 /* 平方 */.專(zhuān)業(yè)學(xué)習(xí)資料.selectsquare (12 )9、 /* 求財(cái)務(wù)部收入最高的員工姓名*/selectmax

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

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

26、d)2、 /* 用 create index語(yǔ)句創(chuàng)建主鍵*/.專(zhuān)業(yè)學(xué)習(xí)資料.3、重建表employees中 employeeid列上的索引alterindex huangyanon employeesrebuild4、刪除索引 ：5、創(chuàng)建一個(gè)新表，使用一個(gè)復(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(號(hào)碼 char (6 ) not null,.專(zhuān)業(yè)學(xué)習(xí)資料.性別 char (2)notnullcheck (性別 in ('男 ','女 ' )7、創(chuàng)建新表 ：createtable employees7(學(xué)號(hào) char (10 ) not null,出生日期 datetimenot nullcheck (出生日期 > '1980-01-01')8、創(chuàng)建一

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

29、oreign key (employeeid )referencesemployees (employeeid )ondeletenoactiononupdatenoaction實(shí)驗(yàn) 71、 工作年份大于6時(shí)，跟換科室到經(jīng)理辦公室(根據(jù)員工 )：CreatePROC UpdateDeptByYear(EmpIdchar (6) )AS.專(zhuān)業(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ù)每個(gè)員工的學(xué)歷將收入提高元：CREATE PROC UpdateInComeByEdu Employeeidchar (6)ASBEGINUPDATE SalarySET InCome = InCome + 500FROM SalaryLEFT JOIN EmployeesON Salary .EmployeeID=

31、Employees .EmployeeIDWHERE Salary .Employeeid= Employeeid.專(zhuān)業(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!= '大專(zhuān) ' )SET i = i + 1.專(zhuān)業(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、使用命令的方式修改存儲(chǔ)過(guò)程的定義：.專(zhuān)業(yè)學(xué)習(xí)資料.5、對(duì)于 YGGL 數(shù)據(jù)庫(kù) ，表 Employees的 EmployeeID列與表 Salary 的 EmployeeID列應(yīng)該滿足參照的完整性規(guī)則，請(qǐng)用觸發(fā)器實(shí)現(xiàn)兩個(gè)表的參照完整性：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 ).專(zhuān)業(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'.專(zhuān)業(yè)學(xué)習(xí)資料.6.當(dāng)修改表 Employees 時(shí)，若將 Employe

36、es 表中員工的工作時(shí)間增加1 年，則將收入增加500 ，若增加 2 年則增加 1000 ，依次增加 。 若工作時(shí)間減少則無(wú)變化：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'.專(zhuān)業(yè)學(xué)習(xí)資料.7.創(chuàng)建 UPDATE 觸發(fā)器 ,當(dāng) Salary 表中 Income值增加500 時(shí)， outcome值增加 50 ：CREATE TRIGGER SA_INCOMEON SalaryFOR UPDATEASBEGINIF(SELECT InComeFROM INSERTED )-( SELECT InComeFROM DELETED