数据库 多表查询.doc
文本预览下载声明
数据库 多表查询
一、目的与要求
1、掌握Select语句的基本语法；
2、掌握子查询的表示；
3、掌握连接查询的表示。
二、实验内容
使用Select语句进行数据库的多表查询
查找在财务部工作的雇员的情况（分别通过等值连接和子查询实现）；
select employees.*
from employees,departments
where departments.departmentName=财务部
and employees.DepartmentID=departments.departmentID
select *
from employees
where DepartmentID in
(
select departmentID
from departments
where departmentName=财务部
)select employees.*
from salary,employees
where salary.income2500 and salary.employeeID=employees.EmployeeID;
select *
from employees
where EmployeeID in
(
select EmployeeID
from salary
where income2500
)
（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=研发部
)
)
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=财务部
)
)
)
)
（5）查询每个雇员的情况及其薪水的情况；
select employees.*,salary.*
from employees,salary
where employees.EmployeeID=salary.employeeID
（6）使用join连接查询的方法查询名字为“王林”的员工所在的部门；
select departmentname
from departments join employees on departments.departmentID=employees.DepartmentID
where employees.Name=王林
（7）查询财务部收入在2000元以上的雇员姓名及其薪水情况
select employees.Name,salary.income,salary.outcome
from employees,departments,salary
where employees.EmployeeID=salary.employeeID
and employees.DepartmentID=departments.departmentID
and departments.departmentName=财务部
and salary.income 2000
显示全部