SQL Server实用教程数据库的查询由刀豆文库小编整理，希望给你工作、学习、生活带来方便，猜你可能喜欢“sqlserver数据库教程”。
《SQL Server实用教程》教案
实验5 数据库的查询
授课教师： 课时：2学时
实验目的 掌握子查询的表示 掌握连接查询的表示 掌握数据汇总的方法
实验重点
连接查询的表示 数据汇总的方法
实验难点
连接查询的表示
实验内容与步骤
1、数据汇总
(1)求财务部雇员的总人数。
（2）统计财务部收入在2000元以上女雇员的人数。
2、GROUP BY，ORDER BY子句的使用
1)统计出部门人数超过2人的部门名称。
2)按员工的学历分组，排列出本科、大专和硕士的人数。3)统计每个部门每种学历的人数和总人数。4)查出每个部门平均工资和人数。5)求各部门的雇员数。
6)统计各部门收入在2000元以上雇员的人数。7)将各雇员的情况按收入由低到高排列。8)将各雇员的情况按出生时间先后排列。 实验小结（要求学生写）
(1)求财务部雇员的总人数。use yggl go select DepartmentName,count(*)人数 from dbo.Employees,dbo.Departments where dbo.Employees.DepartmentID=dbo.Departments.DepartmentID and dbo.Departments.DepartmentName='财务部' group by DepartmentName（）统计财务部收入在元以上女雇员的人数。use yggl go select DepartmentName,count(*)from dbo.Employees,dbo.Departments,dbo.Salary where dbo.Employees.DepartmentID=dbo.Departments.DepartmentID and dbo.Salary.EmployeeID=dbo.Employees.EmployeeID and dbo.Departments.DepartmentName='财务部' and dbo.Salary.InCome>=2000 and dbo.Employees.sex='false' group by DepartmentName 1)统计出部门人数超过人的部门名称。select DepartmentName,count(*)from dbo.Departments,dbo.Employees where dbo.Employees.DepartmentID=dbo.Departments.DepartmentID group by DepartmentName having count(name)>2 2)按员工的学历分组，排列出本科、大专和硕士的人数。select Education,count(*)from dbo.Employees group by Education 3)统计每个部门每种学历的人数和总人数。
select DepartmentName,Education,count(*)人数 from dbo.Departments,dbo.Employees where dbo.Employees.DepartmentID=dbo.Departments.DepartmentID group by DepartmentName,Education with rollup 4)查出每个部门平均工资和人数。
select DepartmentName,avg(InCome),count(*)from dbo.Departments,dbo.Employees,dbo.Salary where dbo.Employees.DepartmentID=dbo.Departments.DepartmentID and dbo.Salary.EmployeeID=dbo.Employees.EmployeeID group by DepartmentName 5)求各部门的雇员数。
select DepartmentName,count(*)from dbo.Departments,dbo.Employees
where dbo.Employees.DepartmentID=dbo.Departments.DepartmentID group by DepartmentName 6)统计各部门收入在元以上雇员的人数。select DepartmentName,count(*)from dbo.Employees,dbo.Departments,dbo.Salary where dbo.Employees.DepartmentID=dbo.Departments.DepartmentID and dbo.Salary.EmployeeID=dbo.Employees.EmployeeID and dbo.Salary.InCome>=2000 group by DepartmentName 7)将各雇员的情况按收入由低到高排列。select * from dbo.Employees,dbo.Salary where dbo.Salary.EmployeeID=dbo.Employees.EmployeeID order by InCome 8)将各雇员的情况按出生时间先后排列。select * from dbo.Employees,dbo.Salary where dbo.Salary.EmployeeID=dbo.Employees.EmployeeID order by Birthday
