SQLSERVER实用教程实验参考答案实验4.docx

1、SQLSERVER实用教程实验参考答案实验4实验4数据库的查询和视图-、SELECT语句的基本使用1.查询Employees表中所有数据2.查询Employees表中指定字段数据3.查询Employees表中的部门号和性别，要求使用Distinct消除重复行4.使用WHERE子句查询表中指定的数据 查询编号为*000001 的雇员的地址和电话查询月 收入高于2000元的员工号码 查询1970年以后出生的员工的姓名和住址5.使用AS子句为表中字段指定别名查询Employees表中女雇员的地址和电话，并将列标题显示为地址和电话查询Employees表中男雇员的姓名和出生日期，并将列标题显示为姓名和

2、出生日期6.使用使用CASE子句查询Employees表中员工的姓名和性别，要求Sex值为1时显示“男”，为0时显示“女” 查询Employees表中员工的姓名、住址和收入水平，2000元以下显示为低收入，2000-3000 地显示为中等收入3000元以上显示为高收入。7.使用SELECT语句进行简单计算计算每个雇员的实际收入8.使用内置函数获得员工总数计算Salary表中员工月收入的平均数 获得Employees表中最大的员工号码 计算Salary表中所 有员工的总支出查询财务部雇员的最高和最低实际收入9.模糊查询找出所有姓王的雇员的部门号 找出所有地址中含有“中山”的雇员的号码及部门号 找

3、出员工号 码中倒数第二个数字为 0的员工的姓名、地址和学历10.Between “And 和Or的使用找出收入在2000-3000元之间的雇员编号找出部门为“ T 或“ 2”的雇员的编号11使用INTO子句，由源表创建新表由表Salary创建“ SalaryNew表，要求包括编号和收入，选择收入在1500元以上的雇员 由表Employees创建“ EmployeesNew 表，要求包括编号和姓名，选择所有男员工二、子查询的使用1.查找在财务部工作的雇员情况2.用子查询的方法查找所有收入在2500以下的雇员的情况3.查找财务部年龄不低于硏发部雇员年龄的雇员姓名4.用子查询的方法查找研发部比所有财

4、务部雇员收入都高的雇员的姓名5.查找比所有财务部的雇员收入都高的雇员的姓名6.用子查询的方法查找所有年龄比研发部雇员年龄都大的雇员的姓名 三、连接查询的使用1.查询每个雇员的情况及薪水的情况2.查询每个雇员的情况及其工作部门的情况3.使用内连接的方法查询名字为“王林”的雇员所在的部门4.使用内连接的方法查找出不在财务部工作的所有雇员信息5.使用外连接方法查找出所有员工的月收入6.查找财务部收入在2000元以上的雇员姓名及其薪水详情7.查询研发部在1976年以前出生的雇员姓名及其薪水详请四、 聚合函数的使用1.求财务部雇员的平均收入2.查询财务部雇员的最高和最低收入3.求财务部雇员的平均实际收入

5、4.查询财务部雇员的最高和最低实际收入5.求财务部雇员的总人数6.统计财务部收入在2500元以上的雇员人数五、 GROUP BY、ORDER BY子句的使用1.查找Employees表中男性和女性的人数2.按部门列出在该部门工作的员工的人数3.按员工的学历分组，排列出本科、大专、硕士的人数4.查找员工数超过2的部门名称和雇员数量5.按员工的工作年份分组，统计各个工作年份的人数，例如工作1年的多少人，工作2年的 多少人6.将雇员的情况按收入由低到高排列7.将员工信息按出生时间从小到大排列8.在ORDER BY子句中使用子查询，查询员工姓名、性别和工龄信息，要求按实际收入从 大 到小排列 六、视图

6、的使用1 -创建视图(1 )在数据库YGGL上创建视图Departments_View，视图包含Department表的全部列(2 )创建视图Employees_Departments_View，视图包含员工号码、姓名、所在部门名称(3 )创建视图Employees_Salary_View，视图包含员工号码、姓名和实际收入三列2.查询视图从视图Employees_Salary_View中查询出姓名为“王林”的员工的实际收入3.更新视图(1 )向视图Departments_View中添加一条记录(广告部，广告业务)执行完 命令后,分别查看Departments_View和Department表中

7、发生的变化(2)尝试向 Employees_Departments_View中添加一条记录，看看会发生什么情况(3)尝试向 Employees_Salary_View中添加一条记录，看看会发生什么情况(4)将视图Departments_View中，部门号为6,的部门名称修改为生产车间(5)删除视图 Departments_View中最新增加的的一条记录4.删除视图 Employees_Departments_View5.在界面工具中操作视图一、SELECT语句的基本使用1.查询Employees表中所有数据SELECT * FROM Employees ;2.查询Employees表中指定字段

8、数据SELECT EmployeelD , Name, DepartmentID FROM Employees ;3.查询Employees表中的部门号和性别，要求使用Distinct消除重复行SELECT Distinct DepartmentID , Sex FROM Employees ;4.使用WHERE子句查询表中指定的数据 查询编号为000001 的雇员的地址和电话Select Address , PhoneNumber FROM Employees WHERE EmployeelD = 000001;查询月收入高于2000元的员工号码SELECT EmployeelD FROM

9、Salary WHERE InCome 2000 ;查询1970年以后出生的员工的姓名和住址SELECT Name,Address FROM Employees WHERE YEAR(Birthday),1970,;SELECT Name, Address FROM Employees WHERE Birthday 1970：5.使用AS子句为表中字段指定别名查询Employees表中女雇员的地址和电话，并将列标题显示为地址和电话SELECT Address AS 地址，PhoneNumber AS 电话 FROM Employees ;查询Employees表中男雇员的姓名和出生日期，并将列

10、标题显示为姓名和出生日期SELECT Name AS 姓名，Birthday AS 出生日期 FROM Employees WHERE Sex =1;6使用使用CASE子句查询Employees表中员工的姓名和性别，要求Sex值为1时显示“男”，为0时显示“女” SELECT Name AS 姓名，CASEWHEN Sex =1 THEN * 男WHEN Sex =0 THEN* 女 *ENDAS性别FROM Employees ;查询Employees表中员工的姓名、住址和收入水平，2000元以下显示为低收入，2000-3000 地显示为中等收入，3000元以上显示为高收入。SELECTa.

11、 Name AS 姓名，a. Address as 住址，CASEWHEN b. InCome 3000 THEN 高收入ENDAS收入水平FROM Employees a, Salary bWHERE a. EmployeelD =b. EmployeelD ;7.使用SELECT语句进行简单计算在Salary表中，计算每个雇员的实际收入，并显示雇员编号和实际收入SELECT EmployeelD AS 雇员编号，InCome OutCome AS 实际收入 FROM Salary ;8.使用聚合函数 获得员工总数SELECT COUNTS) FROM Employees ;计算Salary

12、表中员工月收入的平均数SELECT AVG( InCome ) AS 平均收入 FROM Salary ;获得Employees表中最大的员工号码SELECT MAX( EmployeelD ) AS 雇员编号 FROM Employees ;计算Salary表中所有员工的总支出SELECT SUM( OutCome ) AS总支岀FROM Salary ;查询财务部雇员的最高和最低实际收入SELECT MAX( InCome OutCome ) AS 最高收入，MIN (InCome OutCome ) AS 最低收入 FROM Salary ;9模糊查询找出所有姓王的雇员的部门号SELEC

13、T DepartmentID AS 部门号 FROM Employees WHERE Name like * 王呀;找出所有地址中含有“中Lh”的雇员的编号及部门号SELECT EmployeelD AS 雇员编号，DepartmentID AS 部门号 FROM Employees WHERE Address like % 中山，找出雇员编号中倒数第二个数字为0雇员的编号的姓名、地址和学历SELECT EmployeelD AS 雇员编号，Name AS 姓名,Address AS 地址 Education AS 学历 FROM Employees WHERE SUBSTRING ( Emp

14、loyeelD ,5, 1)= O ;10. BetweenAnd和Or的使用找出收入在2000-3000元之间的雇员编号SELECT EmployeelD , InCome FROM Salary WHERE InCome BETWEEN 2000 AND 3000 ;找出部门为“ T或“ 2”的雇员的编号SELECT EmployeelD , DepartmentID FROM Employees WHERE DepartmentID =1 OR DepartmentID =2*;11使用INTO子句，由源表创建新表由表Salary创建“ SalaryNew表，要求包括编号和收入，选择收入

15、在1500元以上的雇员SELECT EmployeelD , InComeINTO SalaryNewFROM SalaryWHERE InCome 1500 ;由表Employees创建“ EmployeesNew 表，要求包括编号和姓名，选择所有男员工SELECT EmployeelD , NameINTO EmployeesNewFROM EmployeesWHERE Sex=1;二、子查询的使用1.查找在财务部工作的雇员情况-分析：雇员表和部门表通过DepartmentID进行尖联-1)在Departments表中查找财务部的编号-2）在Employees表中查找部门的雇员情况SELE

16、CT * FROM Employees WHERE DepartmentID =(SELECT DepartmentID FROM Departments WHERE DepartmentName =财务部);2.用子查询的方法查找所有收入在2500以下的雇员的情况分析：员工表和收入表通过EmployeelD进行矢联SELECT * FROM Employees WHERE EmployeelD IN (SELECT EmployeelD FROM Salary WHERE InCome ALL(SELECT Birthday FROM Employees WHEREDepartmentID

17、in(SELECT DepartmentID FROM Departments WHERE DepartmentName =研发部);4.用子查询的方法查找研发部比所有财务部雇员收入都高的雇员的姓名巒财务部雇员的收入_.2)查找研发部雇员的编号，条件是收入比所有财务部雇员收入都_3)高通过编号找到雇员姓名SELECT Name FROM Employees WHERE EmployeelD IN (SELECT EmployeelDSELECT EmployeelDFROM Salary WHERE EmployeelD IN( FROMEmployees WHERE DepartmentID

18、 =(SELECT DepartmentID AND InCome ALL(SELECT InCome FROM Salary WHERE EmployeelD IN(SELECT EmployeelD FROM Employees WHERE DepartmentID =( SELECTDepartmentID FROM Departments WHERE DepartmentName 5.查找比所有财务 =财务部)；部的雇员收入都高的雇员的姓名分析：1)查找所有财务部_夂立py斤如亠 ,条件是收入比所有财务部的雇员收入都咼-2)查找其他雇员编号6.用子查询的方法查找所有年龄比研发部雇员年龄

19、都大的雇员的姓名分析:年龄都大等价于生曰都小-1)找到所有研发部雇员的生日-2)找到其他部门雇员的姓名，条件是生日比研发部的所有雇员的生日都小SELECT Name FROMEmployees WHERE Birthday 2000 AND c. DepartmentName =财务部;7.查询研发部在1976年以前出生的雇员姓名及其薪水详请SELECT a.*, b. InCome FROMEmployees a INNER JOIN Salary bON a. EmployeelD =b. EmployeelDINNER JOIN Departments cON a. Department

20、ID =c. DepartmentIDWHERE c. DepartmentName =研发部AND a. Birthday 1976四、 聚合函数的使用1.求财务部雇员的平均收入 SELECT AVG( a. InCome ) FROM Salary a INNER JOIN Employees bON a. EmployeelD =b. EmployeelDINNER JOIN Departments cON c. DepartmentID =b. DepartmentID WHERE c. DepartmentName =财务部;2.查询财务部雇员的最高和最低收入SELECT MIN (

21、 a. InCome ), MAX( a. InCome ) FROM Salary aINNER JOIN Employees bON a. EmployeelD =b. EmployeelDINNER JOIN Departments cON c. DepartmentID =b. DepartmentID WHERE c. DepartmentName =财务部；3.求财务部雇员的平均实际收入SELECT AVG( a. InCome a. OutCome ) FROM Salary aINNER JOIN Employees b ON a. EmployeelD =b. Employe

22、elDINNER JOIN Departments c ON c. DepartmentID =b. DepartmentID WHERE c. DepartmentName =财务部 .4.查询财务部雇员的最高和最低实际收入SELECT MIN( a. InCome a. OutCome ), MAX( a. InCome a. OutCome ) FROM Salary a INNER JOINEmployees bON a. EmployeelD =b. EmployeelDINNER JOIN Departments cON c. DepartmentID =b. Department

23、ID WHERE c. DepartmentName =财务部；5.求财务部雇员的总人数SELECT COUNT( a. EmployeelD ) FROM Employees aINNER JOIN Departments bON a. DepartmentID =b. DepartmentID WHERE b. DepartmentName =财务部;6.统计财务部收入在2500元以上的雇员人数SELECT COUNT( a. EmployeelD ) FROM Employees aINNER JOIN Departments bON a. DepartmentID =b. Depart

24、mentIDINNER JOIN Salary cON c. EmployeelD =A. EmployeelDWHERE c. InCome 2500 AND b. DepartmentName =财务部;五、GROUP BY ORDER BY子句的使用1.查找Employees表中男性和女性的人数SELECT Sex , COUNT( Sex ) FROM Employees GROUP BY Sex ;2.按部门列出在该部门工作的员工的人数-使用内连接的方法SELECT b. DepartmentName , COUNT( a. EmployeelD ) FROM Employees a

25、INNER JOIN Departments bON a. DepartmentID =b. DepartmentID GROUP BY b. DepartmentName ;-使用多表查询方法,GROUP BY后面的字段，必须出现在SELECT语句要查询的字段中。SELECT b. DepartmentName , COUNT( a. EmployeelD ) FROM Employees a, Departments bWHERE a. DepartmentID =b. DepartmentIDGROUP BY b. DepartmentName ;3.按员工的学历分组，排列出本科、大专、

26、硕士的人数 SELECT a. Education , COUNT( a. EmployeelD ) FROM Employees a GROUP BY a. Education ;4.查找员工数超过2的部门名称和雇员数量SELECT b. DepartmentName , COUNT( a. EmployeelD ) FROM Employeesa, Departments bWHERE a. DepartmentID =b. Department!DGROUP BY b. DepartmentNameHAVING COUNT( a. EmployeelD ) 2;5.按员工的工作年份分组，统计各个工作年份的人数，例如工作1年的多少人，工作2年的 多少人SELECT a. WorkYear, COUNT( a. EmployeelD ) FROM Employees aGROUP BY a. WorkYear;6.将雇员的情况按收入由低到高排列SELECT a.*, b. InCome FROM Employees a, Salary bWHERE a. EmployeelD =b. EmployeelD ORDER BY b. InCome ASC;7.将员工信息按出生时间从小到大排列SELECT * FROM Employees O