ＳＱＬ实验
实验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 where departmentname='研发部' ) ) 20、查询每个员工的情况及其薪水的情况： select employees.*,departments.departmentname from employees,departments where employees.departmentid=departments.departmentid 21、使用内连接方法查找不在财务部工作的所有员工信息： select employees.* from employees inner join departments on employees.departmentid=departments.departmentid where departmentname!='财务部' 22、使用外连接方法查找出所有员工的月收入： select employees.*,salary.income from employees join salary on employees.employeeid=salary.employeeid 23、查找财务部雇员的最高收入： select max(income) from salary where employeeid in (select employeeid from employees where departmentid in (select departmentid from departments where departmentname='财务部' ) ) 24、查询财务部雇员的最高实际收入： select max(income-outcome) from salary where employeeid in (select employeeid from employees where departmentid in (select departmentid from departments where departmentname='财务部' ) ) 25、统计财务部收入在2500元以上的雇员人数： select count(employeeid) from employees where departmentid in (select departmentid from departments where departmentname='财务部') and employeeid in ( select employeeid from salary where income>2500) 26、按部门列出在该部门工作的员工的人数： select departmentid ,count(*) as 人数 from employees group by departmentid 27、按员工的学历分组： select education ,count(*) as 人数
