SQLSERVER2008实用教程实验参考答案(实验4)

SELECT * FROM Employees WHERE DepartmentID =(

SELECT DepartmentID FROM Departments WHERE DepartmentName='财务部');

2. 用子查询的方法查找所有收入在2500以下的雇员的情况

--分析:员工表和收入表通过EmployeeID进行关联

SELECT * FROM Employees WHERE EmployeeID IN ( SELECT EmployeeID FROM Salary WHERE InCome<2500);

3. 查找财务部年龄不低于研发部雇员年龄的雇员姓名

--分析:雇员表和部门表通过DepartmentID进行关联 --1)先找到研发部的部门编号

--2)再通过部门编号找到研发部的雇员年龄

--3)再找到财务部的部门编号,并通过部门编号查找雇员姓名,条件是年龄不低于所有研发部雇员年龄

SELECT Name FROM Employees WHERE DepartmentID in (

SELECT DepartmentID FROM Departments WHERE DepartmentName='财务部') AND Birthday !> ALL(

SELECT Birthday FROM Employees WHERE DepartmentID in(

SELECT DepartmentID FROM Departments WHERE DepartmentName='研发部'));

4. 用子查询的方法查找研发部比所有财务部雇员收入都高的雇员的姓名

--1)查找财务部雇员的收入

--2)查找研发部雇员的编号,条件是收入比所有财务部雇员收入都高 --3)通过编号找到雇员姓名

SELECT Name FROM Employees WHERE EmployeeID IN ( SELECT EmployeeID FROM Salary WHERE EmployeeID IN( SELECT EmployeeID FROM Employees WHERE DepartmentID =(

SELECT DepartmentID FROM Departments WHERE DepartmentName='研发部')) AND InCome > ALL(

SELECT InCome FROM Salary WHERE EmployeeID IN(

SELECT EmployeeID FROM Employees WHERE DepartmentID =(

SELECT DepartmentID FROM Departments WHERE DepartmentName='财务部'))));

5. 查找比所有财务部的雇员收入都高的雇员的姓名

--分析:1)查找所有财务部的雇员收入

--2)查找其他雇员编号,条件是收入比所有财务部的雇员收入都高 --3)通过编号找到姓名

SELECT Name FROM Employees WHERE EmployeeID IN( SELECT EmployeeID FROM Salary WHERE InCome>ALL( SELECT InCome FROM Salary Where EmployeeID IN (

SELECT EmployeeID FROM Employees Where DepartmentID =(

SELECT DepartmentID FROM Departments WHERE DepartmentName='财务部'))));

6. 用子查询的方法查找所有年龄比研发部雇员年龄都大的雇员的姓名

--分析:年龄都大等价于生日都小 --1)找到所有研发部雇员的生日

--2)找到其他部门雇员的姓名,条件是生日比研发部的所有雇员的生日都小 SELECT Name FROM Employees WHERE Birthday < ALL ( SELECT Birthday FROM Employees WHERE DepartmentID IN (

SELECT DepartmentID FROM Departments WHERE DepartmentName = '研发部'));

三、连接查询的使用

1. 查询每个雇员的情况及薪水的情况

SELECT a.*,b.*

FROM Employees a,Salary b WHERE a.EmployeeID=b.EmployeeID;

2. 查询每个雇员的情况及其工作部门的情况

SELECT a.*,b.*

FROM Employees a,Departments b WHERE a.DepartmentID=b.DepartmentID;

3. 使用内连接的方法查询名字为“王林”的雇员所在的部门

SELECT b.DepartmentName FROM Departments b INNER JOIN Employees a

ON a.DepartmentID=b.DepartmentID Where a.Name='王林';

4. 使用内连接的方法查找出不在财务部工作的所有雇员信息

SELECT a.* FROM Employees a INNER JOIN Departments b

ON a.DepartmentID=b.DepartmentID WHERE b.DepartmentName!='财务部';

5. 使用外连接方法查找出所有员工的月收入

SELECT * FROM Employees a LEFT OUTER JOIN Salary b ON a.EmployeeID=b.EmployeeID;

6. 查找财务部收入在2000元以上的雇员姓名及其薪水详情

SELECT a.*,b.InCome FROM Employees a INNER JOIN Salary b

ON a.EmployeeID=b.EmployeeID INNER JOIN Departments c

ON a.DepartmentID=c.DepartmentID

WHERE b.InCome>2000 AND c.DepartmentName='财务部';

7. 查询研发部在1976年以前出生的雇员姓名及其薪水详请

SELECT a.*,b.InCome FROM Employees a INNER JOIN Salary b

ON a.EmployeeID=b.EmployeeID INNER JOIN Departments c

ON a.DepartmentID=c.DepartmentID

WHERE c.DepartmentName='研发部' AND a.Birthday>'1976'

四、聚合函数的使用

1. 求财务部雇员的平均收入

SELECT AVG(a.InCome) FROM Salary a INNER JOIN Employees b

ON a.EmployeeID=b.EmployeeID INNER JOIN Departments c

ON c.DepartmentID=b.DepartmentID WHERE c.DepartmentName='财务部';

2. 查询财务部雇员的最高和最低收入

SELECT MIN(a.InCome),MAX(a.InCome) FROM Salary a INNER JOIN Employees b ON a.EmployeeID=b.EmployeeID INNER JOIN Departments c

ON c.DepartmentID=b.DepartmentID WHERE c.DepartmentName='财务部';

3. 求财务部雇员的平均实际收入

SELECT AVG(a.InCome-a.OutCome) FROM Salary a INNER JOIN Employees b ON a.EmployeeID=b.EmployeeID INNER 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 JOIN Employees b ON a.EmployeeID=b.EmployeeID INNER JOIN Departments c

ON c.DepartmentID=b.DepartmentID WHERE c.DepartmentName='财务部';

5. 求财务部雇员的总人数

SELECT COUNT(a.EmployeeID) FROM Employees a INNER JOIN Departments b

ON a.DepartmentID=b.DepartmentID WHERE b.DepartmentName='财务部';

6. 统计财务部收入在2500元以上的雇员人数

SELECT COUNT(a.EmployeeID) FROM Employees a INNER JOIN Departments b

ON a.DepartmentID=b.DepartmentID INNER JOIN Salary c

ON c.EmployeeID=A.EmployeeID

WHERE 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.EmployeeID) FROM Employees a

INNER JOIN Departments b

ON a.DepartmentID=b.DepartmentID GROUP BY b.DepartmentName;

--使用多表查询方法,GROUP BY后面的字段,必须出现在SELECT语句要查询的字段中。 SELECT b.DepartmentName,COUNT(a.EmployeeID) FROM Employees a,Departments b

WHERE a.DepartmentID=b.DepartmentID GROUP BY b.DepartmentName;

3. 按员工的学历分组，排列出本科、大专、硕士的人数

SELECT a.Education,COUNT(a.EmployeeID) FROM Employees a GROUP BY a.Education;

4. 查找员工数超过2的部门名称和雇员数量

SELECT b.DepartmentName,COUNT(a.EmployeeID) FROM Employees a,Departments b

WHERE a.DepartmentID=b.DepartmentID GROUP BY b.DepartmentName HAVING COUNT(a.EmployeeID)>2;

5. 按员工的工作年份分组，统计各个工作年份的人数，例如工作1年的多少人，工作2年的多少人

SELECT a.WorkYear,COUNT(a.EmployeeID) FROM Employees a GROUP BY a.WorkYear;

6. 将雇员的情况按收入由低到高排列

SELECT a.*,b.InCome FROM Employees a,Salary b WHERE a.EmployeeID=b.EmployeeID ORDER BY b.InCome ASC;

7. 将员工信息按出生时间从小到大排列

SELECT * FROM Employees ORDER BY Birthday;

8. 在ORDER BY 子句中使用子查询，查询员工姓名、性别和工龄信息，要求按实际收入从大到小排列

SELECT a.Name,a.Sex,a.WorkYear,b.InCome-b.OutCome FROM Employees a,Salary b

WHERE a.EmployeeID=b.EmployeeID ORDER BY b.InCome-b.OutCome DESC;

六、视图的使用 1. 创建视图

（1）在数据库YGGL上创建视图Departments_View，视图包含Department表的全部列

USE YGGL GO

CREATE VIEW Departments_View

AS SELECT * FROM Departments;

（2）创建视图Employees_Departments_View，视图包含员工号码、姓名、所在部门名称