SQL 数据查询语句

《数据库系统 》     实验报告

实验名称 SQL 数据查询语句 实验地点 实验楼423 实验日期

一、实验目的及要求

1. 掌握SELECT 语句的基本语法和查询条件表示方法
2. 掌握GROUP BY 和ORDER BY 子句的作用和使用方法
3. 掌握连接查询和子查询的使用方法

二、实验环境
Windows 10 SQLSERVER 2008

三、实验内容
实验任务一：
1.对上节建立的表输入数据：
Departments 表：

Employee 表：

Salary表信息：

实验任务二：
（1） 查询每个雇员的所有信息

select *
from Employee
（2）查询每个雇员的地址和电话

select EmployeeID,Address,PhoneNumber
from Employee
（3）查询EmployeeID 为000001 的雇员的地址和电话。

select EmployeeID,Address,PhoneNumber
from Employee
WHERE EmployeeID=‘000001’
（4） 查询女雇员地址和电话，并用AS 子句将结果中各列的标题分别指定为“地址” 和
“电话”。

select EmployeeID,Address as’地址’,PhoneNumber as’电话’
from Employee
WHERE sex=‘true’
（5）计算每个雇员的实际收入。

select EmployeeID,实际收入=Income-OutCome
from Salary
（6）找出所有姓王的雇员的部门号。

select EmployeeID
from Employee
WHERE Name like ‘王%’
（7）找出所有地址中含有“中山”的雇员的号码和部门号。

select EmployeeID,PhoneNumber
from Employee
WHERE Address like ‘%中山%’
实验任务三：
（1）查询每个雇员的情况及工资情况（工资=Income - Outcome）

select *,Income-OutCome AS ‘工资’
from Employee,Salary
where Employee.EmployeeID = Salary.EmployeeID
（2）查询财务部工资在2200 元以上的雇员姓名及工资情况

select Name,Income-OutCome as ‘工资’
from Employee,Salary
where Employee.EmployeeID=Salary.EmployeeID and Employee.EmployeeID in
(
select EmployeeID
from Salary
where Income-OutCome>=2200 and EmployeeID in
(
select EmployeeID
from Employee
where DepartmentID in
(
select DepartmentID
from Departments
where DepartmentName=‘财务部’
)
)
)
（3）查询研发部在1966 年以前出生的雇员姓名及其工资详情

select Name,Income-OutCome as ‘工资’
from Employee,Salary
where Employee.EmployeeID=Salary.EmployeeID and Employee.EmployeeID in
(
select EmployeeID
from Salary
where EmployeeID in
(
select EmployeeID
from Employee
where Birthday<1966 and DepartmentID in
(
select DepartmentID
from Departments
where DepartmentName=‘研发部’
)
)
)
（4）查询人力资源部雇员的最高和最低工资

select min(Income-OutCome) as ‘最低工资’,max(Income-OutCome) as ‘最高工资’
from Salary
where EmployeeID in
(
select EmployeeID
from Employee
where DepartmentID in
(
select DepartmentID
from Departments
where DepartmentName=‘人力资源部’
)
)
（5）将各雇员的情况按工资由低到高排列

select Employee.*
from Employee,Salary
where Employee.EmployeeID=Salary.EmployeeID
order by Income asc
（6）求各部门的雇员数

select Departments.DepartmentID, count(*)
from Employee,Departments
where Employee.DepartmentID=Departments.DepartmentID
group by Departments.DepartmentID
（7）找出所有在财务部和人力资源部工作的雇员的编号

select Employee.EmployeeID
from Employee,Departments
where Employee.DepartmentID = Departments.DepartmentID
and (Departments.DepartmentName = ‘财务部’
or Departments.DepartmentName= ‘人力资源部’)
（8）统计人力资源部工资在2500 以上雇员的人数

select count(EmployeeID)as ‘人数’
from Salary
where Income-OutCome>=2500 and EmployeeID in
(
select EmployeeID
from Employee
where DepartmentID in
(
select DepartmentID
from Departments
where DepartmentName=‘人力资源部’
)
)
（9）求财务部雇员的总人数

select count(Employee.EmployeeID) as ‘总人数’
from Employee
where DepartmentID in
(
select DepartmentID
from Departments
where DepartmentName=‘人力资源部’
)
（10）求财务部雇员的平均工资

(
select Employee.EmployeeID
from Employee
where DepartmentID in
(
select DepartmentID
from Departments
where DepartmentName=‘财务部’
)
)
（11）查找比所有财务部的雇员工资都高的雇员的姓名

select Name
from Employee,Salary
where Employee.EmployeeID=Salary.EmployeeID and (Income - OutCome)>
(
select max(Income-OutCome)
from Salary
where EmployeeID in
(
select Employee.EmployeeID
from Employee
where DepartmentID in
(
select DepartmentID
from Departments
where DepartmentName=‘财务部’
)
)
)
（12）查找财务部年龄不低于研发部所有雇员年龄的雇员的姓名

select Name
from Departments ,Employee
where Departments.DepartmentID=Employee.DepartmentID and DepartmentName=‘财务部’
and (2019-Employee.Birthday) >=
(
select max(2019-Employee.Birthday)
from Employee
where DepartmentID in
(
select DepartmentID
from Departments
where DepartmentName=‘研发部’
)
)
（13）查找在财务部工作的雇员的情况

select *
from Employee
where DepartmentID in
(
select DepartmentID
from Departments
where DepartmentName=‘财务部’
)
四、实验总结
通过本次实验，更好的理解了SQL语句的用法，这次实验将很多的SQL语句都运用，可以使我在实验中不断进行复习，在练习中巩固，使我印象更深刻。