ＳＱＬ实验
实验4
1.用select 语句查询departments和salary表中的所有数据：
select salary.*, departments.*
from salary ,departments
2、查询departments 中的departmentid：
select departmentid from departments
go
3、查询 salary中的 income，outcome：
select income,outcome from salary
go
4、查询employees表中的部门号，性别，要用distinct消除重复行：select distinct(departmentid), sex
from employees
5、查询月收入高于2000元的员工号码：
select employeeid from salary
where income>2000
go
6、查询1970年以后出生的员工的姓名和住址：
select name,address
from employees
where birthday>1970
go
7、查询所有财务部的员工的号码和姓名：
select employeeid ,name
from employees
where departmentid in(select departmentid from departments where
departmentname='财务部')
go
8、查询employees员工的姓名，住址和收入水平，2000元以下显示为低收入，2000~3000元显示为中等收入，3000元以上显示为高收入：
select name,address,
case
when income-outcome<2000 then'低收入'
when income-outcome>3000 then'高收入'
else'中等收入'
end as'收入等级'
from employees,salary
where employees.employeeid=salary.employeeid
go
9、计算salary表中员工月收入的评价数：
select avg(income)as'平均收入'from salary
10、查找employees表中最大的员工号码：
select max(employeeid)as'最大员工号码'from employees
11、计算salary表中的所有员工的总支出：
select sum(outcome)as'总支出'from salary
12、查询财务部雇员的最高实际收入：
select max(income-outcome)from salary ,employees,departments
where salary.employeeid=employees.employeeid and
employees.departmentid=departments.departmentid and departmentname='财
务部'
go
13、查询财务部雇员的最低实际收入：
select min(income-outcome)from salary ,employees,departments
where salary.employeeid=employees.employeeid and
employees.departmentid=departments.departmentid and departmentname='财务部'
go
14、找出所用地址中含有“中山”的雇员的号码及部门号：
select employeeid ,departmentid
from employees
where address like'%中山%'
go
15、查找员工号码中倒数第二个数字为0的员工的姓名，地址和学历：
select education,address,name
from employees
where employeeid like'%0_'
go
16、使用into字句，由表employees创建“男员工1”表，包括编号和姓名：
select employeeid,name
into男员工表
from employees
where sex='1'
go
17、用子查询的方法查找收入在2500元以下的雇员的情况：
select*from employees
where employeeid in
(select employeeid from salary where income<2500)
go
18、用子查询的方法查找查找研发部比所有财务部雇员收入都高的雇员的姓名：SELECT Name FROM Employees WHERE EmployeeID IN
(
SELECT EmployeeID FROM Salary
WHERE EmployeeID IN
(
SELECT EmployeeId FROM Employees
WHERE DepartmentID IN
(
SELECT DepartmentID FROM Departments
WHERE DepartmentName='研发部'
)
)
AND InCome>ALL
(
SELECT InCome FROM Salary
WHERE EmployeeID IN
(
SELECT EmployeeId FROM Employees
WHERE DepartmentID IN
(
SELECT DepartmentID FROM Departments
WHERE DepartmentName='财务部'
)
)
)
)
19、用子查询的方法查找所有年龄比研发部雇员都大的雇员的姓名：
select name
from employees
where Birthday(select birthday
from employees
where departmentid in
(select departmentid
from departments
