数据库解题思路

select * from departments;
select * from employees;
select * from salary;

-- 1.列出总人数大于4的部门号和总人数
-- 表：employees
-- 条件：各部门的总人数大于4（聚合函数，又要做为条件来过滤）
-- 分组：detptid
-- 排序:
-- 子查询：
-- 显示：部门号,总人数

-- 1.基本数据：
select deptid from employees group by deptid
-- 2.条件过滤
select deptid from employees group by deptid HAVING count(*) >4
-- 3.显示内容
select deptid, count(*) from employees group by deptid HAVING count(*) >4

-- 2.列出开发部和测试部的职工号、姓名
-- 表：departments,employees (连接点：deptid）
-- 条件：开发部，测试部 (departments.deptname)
-- 分组：
-- 排序:
-- 子查询：
-- 显示：职工号、姓名

-- 1.基本数据：
select * from departments a inner join employees b on a.deptid=b.deptid
-- 2.条件过滤
select * from departments a inner join employees b on a.deptid=b.deptid
where a.deptname in ('开发部','测试部')
-- 3.显示内容
select b.empid,b.empname from departments a inner join employees b on a.deptid=b.deptid
where a.deptname in ('开发部','测试部')

-- 3）求出各部门党员的人数，要求显示部门名称
-- 表：departments,employees (连接点：deptid）
-- 条件：党员 (having employees.politicalstatus  = '党员')
-- 分组：部门名称(deptname), politicalstatus
-- 排序:
-- 子查询：
-- 显示：部门名称,党员,人数

-- 1.基本数据：
select a.deptid from departments a inner join employees b on a.deptid=b.deptid
-- 2.条件过滤
select a.deptid,b.politicalstatus from departments a inner join employees b on a.deptid=b.deptid
group by a.deptid,b.politicalstatus having b.politicalstatus = '党员'

select a.deptname,b.politicalstatus from departments a 
inner join employees b on a.deptid=b.deptid
where b.politicalstatus = '党员' 
GROUP BY a.deptname
-- 3.显示内容
select a.deptname,b.politicalstatus,count(*) from departments a inner join employees b on a.deptid=b.deptid
group by a.deptid,b.politicalstatus having b.politicalstatus = '党员'

select a.deptname,b.politicalstatus,count(*) from departments a 
inner join employees b on a.deptid=b.deptid
where b.politicalstatus = '党员' 
GROUP BY a.deptname,b.politicalstatus


-- 4.列出市场部的所有女职工的姓名和政治面貌
-- 表：departments,employees (连接点：deptid）
-- 条件：市场部 (departments.deptname)，女职工(sex)
-- 分组：
-- 排序:
-- 子查询：
-- 显示：姓名,政治面貌

-- 1.基本数据：
select * from departments a inner join employees b on a.deptid=b.deptid
-- 2.条件过滤
select * from departments a inner join employees b on a.deptid=b.deptid
where a.deptname = '市场部' and b.sex = '女'
-- 3.显示内容
select b.empname,b.politicalstatus from departments a inner join employees b on a.deptid=b.deptid
where a.deptname = '市场部' and b.sex = '女'

-- 5.显示所有职工的姓名、部门名和工资数
-- 表：departments,employees (连接点：deptid），salary
-- 条件：
-- 分组：
-- 排序:
-- 子查询：
-- 显示：姓名、部门名和工资数

-- 1.基本数据：
select * from departments a 
inner join employees b on a.deptid=b.deptid
inner join salary c on b.empid = c.empid
-- 2.条件过滤

-- 3.显示内容
select b.empname,a.deptname,c.salary from departments a 
inner join employees b on a.deptid=b.deptid
inner join salary c on b.empid = c.empid

-- 6.显示各部门名和该部门的职工平均工资
-- 表：departments,employees (连接点：deptid），salary
-- 条件：
-- 分组：部门名
-- 排序:
-- 子查询：
-- 显示：部门名，平均工资

-- 1.基本数据：
select a.deptname from departments a 
inner join employees b on a.deptid=b.deptid
inner join salary c on b.empid = c.empid
group by a.deptname
-- 2.条件过滤

-- 3.显示内容
select a.deptname,avg(c.salary) from departments a 
inner join employees b on a.deptid=b.deptid
inner join salary c on b.empid = c.empid
group by a.deptname

-- 7.显示工资最高的前3名职工的职工号和姓名
-- 表：employees (连接点：deptid），salary
-- 条件：
-- 分组：
-- 排序: order by salary desc
-- 子查询：
-- 显示：职工号，姓名

-- 1.基本数据：
select * from employees b
inner join salary c on b.empid = c.empid
order by salary desc
-- 2.条件过滤
select * from employees b
inner join salary c on b.empid = c.empid
order by salary desc limit 0,3
-- 3.显示内容
select b.empid,b.empname from employees b
inner join salary c on b.empid = c.empid
order by salary desc limit 0,3

-- 8.列出工资在1000－2000之间的所有职工姓名
-- 表：employees (连接点：deptid），salary
-- 条件：工资在1000－2000
-- 分组：
-- 排序: 
-- 子查询：
-- 显示：职工姓名

-- 1.基本数据：
select * from employees b
inner join salary c on b.empid = c.empid
-- 2.条件过滤
select * from employees b
inner join salary c on b.empid = c.empid
where c.salary between 1000 and 2000;
-- 3.显示内容
select b.empname from employees b
inner join salary c on b.empid = c.empid
where c.salary between 1000 and 2000;

-- 9.列出工资比王昭君高的员工
-- 表：employees (连接点：deptid），salary
-- 条件：工资(salary)比王昭君高
-- 分组：
-- 排序: 
-- 子查询：王昭君的工资
-- 显示：员工信息

-- 1.基本数据：
select * from employees b
inner join salary c on b.empid = c.empid
-- 2.条件过滤
select * from employees b
inner join salary c on b.empid = c.empid
where c.salary >(select salary from salary where empid =
(select empid from employees where empname='王昭君'))
-- 3.显示内容
select * from employees b
inner join salary c on b.empid = c.empid
where c.salary >(select salary from salary where empid =
(select empid from employees where empname='王昭君'))


-- 10.列出每个部门中工资小于本部门平均工资的员工信息
-- 表：departments,employees (连接点：deptid），salary
-- 条件：各部门中工资（salary）小于本部门平均工资(表中没有的数据--造数据）
-- 分组：部门id(deptid)
-- 排序: 
-- 子查询：本部门平均工资
-- 显示：员工信息

-- 1.基本数据：
select * from departments a 
join employees b on a.deptid = b.deptid
join salary c on c.empid=b.empid
join 
(select a.deptname,avg(c.salary) avg_salary from departments a 
join employees b on a.deptid = b.deptid
join salary c on c.empid=b.empid
group by a.deptname) d on d.deptname=a.deptname
-- 2.条件过滤
select * from departments a 
join employees b on a.deptid = b.deptid
join salary c on c.empid=b.empid
join 
(select a.deptname,avg(c.salary) avg_salary from departments a 
join employees b on a.deptid = b.deptid
join salary c on c.empid=b.empid
group by a.deptname) d on d.deptname=a.deptname
where c.salary < d.avg_salary 
-- 3.显示内容
select b.*,c.salary,d.avg_salary from departments a 
join employees b on a.deptid = b.deptid
join salary c on c.empid=b.empid
join 
(select a.deptname,avg(c.salary) avg_salary from departments a 
join employees b on a.deptid = b.deptid
join salary c on c.empid=b.empid
group by a.deptname) d on d.deptname=a.deptname
where c.salary < d.avg_salary 
#数据库相关面试常考题汇总##旷视面经#