SQL语句面试问答（二）

二、多表查询面试题

create table departments (
deptid int(10) primary key, 
deptname varchar(20) not null -- 部门名称
)；
insert into departments values ('1001','市场部')；
insert into departments values ('1002','测试部')；
insert into departments values ('1003','开发部')；

create table employees (
	empid int(10) primary key, 
	empname varchar(20) not null,-- 姓名
	sex varchar(4) default null, -- 性别
	deptid int(20) default null,-- 部门编号
	jobs varchar(20) default null, -- 岗位
	politicalstatus varchar(20) default null,-- 政治面貌
	leader int(10) default null
)；

insert into employees values ('1','王一','女','1003','开发','群众','9')；
insert into employees values ('2','朱二','男','1003','开发经理','群众',null)；
insert into employees values ('3','张三','男','1002','测试','团员','4')；
insert into employees values ('4','白四','男','1002','测试经理','党员',null)；
insert into employees values ('5','杨五','女','1002','测试','党员','4');
insert into employees values ('6','孙六','女','1001','市场','党员','12');
insert into employees values ('7','赵七','男','1001','市场','团员','12');
insert into employees values ('8','小八','女','1002','测试','群众','4');
insert into employees values ('9','久久','男','1003','开发','党员','9');
insert into employees values ('10','史十','女','1003','开发','团员','9')；
insert into employees values ('11','李十一','男','1002','测试','团员','4')；
insert into employees values ('12','孙十二','男','1001','市场经理','党员',null)；

create table salary (
	sid int(10) primary key, 
	empid int(10) not nul1, 
	salary int(10) not null-- 工资
)；

insert into salary values ('1','7','2100');
insert into salary values ('2','6','2000')；
insert into salary values ('3','12','5000')；
insert into salary values ('4','9','1999')；
insert into salary values ('5','10','1900');
insert into salary values ('6','1','3000');
insert into salary values ('7','2','5500');
insert into salary values ('8','5','2000')；
insert into salary values ('9','3','1500')；
insert into salary values ('10','8','4000')；
insert into salary values ('11','11','2600')；
insert into salary values ('12','4','5300')；

1.列出总人数大于4的部门号和总人数

select deptid， count(*) from employees group by deptid having count(*)>4

2.列出开发部和测试部的职工号，姓名

select e.empid,d.deptname 
from employees as e 
inner join deptnames as d on e.empid=d.deptid 
where d.department in ('开发部','测试部')

3.求出各部门党员的人数，要求显示部门名称

select d.deptname , count(*)
from employees as e 
inner join departments as d on e.empid=d.deptid 
where e.politicalstatus='党员' group by e.deptid

4.列出市场部的所有女职工的姓名和政治面貌

select e.empname，e.politicalstatus 
from employees as e 
inner join departments as d on e.empid=d.deptid
where e.sex='女' and d.deptname='市场部'

5.显示所有职工的姓名，部门名和工资数

select e.empname,d.deptname,s.salary
from employees as e 
inner join departments as d on e.empid=d.deptid
inner join salary as s on e.empid=s.empid

6.显示各部门名和该部门的职工平均工资

select d.deptname, avg(s.salary) from departments as d 
inner join employees as e on e.empid=d.deptid
inner join salary as s on e.empid=s.empid 
group by d.deptname

7.显示工资最高的前3名职工的职工号和姓名

select e.empid,e.empname,s.salary from employees as e
inner join salary as s on e.empid=s.empid
order by s.salary desc limit 3

8.列出工资在1000-2000之间的所有职工姓名

select e.empname,s.salary from salary as s
inner join employees as e on e.empid=s.empid
where s.salary between 1000 and 2000

9.列出工资比王一高的员工

select * from employees as e
inner join salary as s on e.empid=s.empid
where s.salary > (
select s.salary from employees as e 
inner join salary as s on e.empid=s.empid 
where e.empname='王一')