SQL实验（实验4⾄实验7的答案）
ＳＱＬ实验
实验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
ployeeid
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
ployeeid and
employees.departmentid=departments.departmentid and departmentname='财务部' go
13、查询财务部雇员的最低实际收⼊：
select min(income-outcome)from salary ,employees,departments
ployeeid 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
sql查询面试题及答案
21、使⽤内连接⽅法查不在财务部⼯作的所有员⼯信息：select employees.* from employees inner join departments on

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。