数据库二次测试题目

数据库

1.如何把这样一个表（表名aaa）结构的数据

year	mouth	amount
1991	1	1.1
1991	2	1.2
1991	3	1.3
1991	4	1.4
1992	1	2.1
1992	2	2.2
1993	3	2.3
1994	4	2.4

查出这样的结果：

year	m1	m2	m3	m4
1991	1.1	1.2	1.3	1.4
1992	2.1	2.2	2.3	2.4

答案：
首先按照月份的不同查找amount，然后再按照year分组

select year,
(select amount from aaa m shere month=1 and m.year=aaa.year) as m1,
(select amount from aaa m where month=2 and m.year=aaa.year) as m2,
(select amount from aaa m where month=3 and m.year=aaa.year) as m3,
(select amount from aaa m where month=4 and m.year=aaa.year) as m4,
from aaa group by year;

2.有如下两张表
city表：

CityNo	CityName	StateNo
BJ	北京	（Null）
SH	上海	（Null）
GZ	广州	GD
DL	大连	LN

state表：

StateNo	StateName
GD	广东
LN	辽宁
SD	山东
NMG	内蒙古

得到以下结果：

CityNo	CityName	StateNo	StateName
BJ	北京	（Null）	（Null）
DL	大连	LN	辽宁
GZ	广州	GD	广东
SH	上海	（Null）	（Null）

写出相应的sql语句

答案：

select c.cityNo,c.cityName,c.stateNo,c.stateName
from city c , state s 
where c.stateNo=s.stateNo
group by c.cityNo;

3.现有员工表和部门表

员工表：emp

部门表：dept

1.查询最低工资大于1300的各种工作以及从事的全部员工的人数

select distinct e.job,count(*) from emp e 
where e.sal>1300 group by e.job;
-- 按照job分组，通过count(*)函数对每组进行人数统计

2.查询部门号为20和30的员工名称，部门名称和工资

select e.ename,e.sal,d.dname from emp e,dept d
where (e.deptno in (20,30)) and e.deptno=d.deptno;
-- 查找部门号为20,30的员工

3.查询工资比SMITH员工工资高的所有员工信息

select * from emp 
where sal>(select sal from emp where ename like 'SMITH');
-- 字符串记得加上引号

4.查询所有工种为CLERK的员工的姓名及其部门名称

select ename,dname from emp 
join dept on job like 'CLERK' and emp.deptno=dept.deptno;
-- 

5.查询工资高于公司平均工资的所有员工信息

select * from emp where sal>(select avg(sal) from emp);

6.查询各个部门中的不同工种的最高工资

select max(sal),job,deptno from emp group by deptno,job 
order by deptno,job;
-- 

7.查询10号部门员工以及领导的信息

select * from emp where empno in(select mgr from emp where deptno=10) 
or deptno=10;
-- 查询出所有的部门编号为10的所有员工的领导编号，再通过领导编号与员工编号对应，找到的就是领导的信息，or后边是编号为10的员工信息

8.查询各个部门的人数及平均工资

select deptno,count(*),avg(sal) from emp group by deptno;

9.查询工资为某部门平均工资的员工信息

select * from emp where sal in (select avg(sal) avg_sal from emp group by deptno);
-- 先通过deptno分组求得每个部门的平均工资，然后再用sal比对

10.查询工资高于本部门平均工资的员工信息

-- 与上一题不同，这题是高于本部门，上一题是某部门即可
select emp.* from 
join (select deptno,avg(sal) avg_sal from emp group by deptno) t 
on emp.deptno=t.deptno and sal>avg_sal;
-- 从emp表中按照deptno分组查询平均值记为表t，然后和emp中比对，条件是表t中的deptno=emp表中的deptno且工资高于平均工资

11.查询部门人数大于5的部门的员工信息

select * from emp 
where deptno in 
(select deptno from emp group by deptno having count(*)>5);
-- 按照deptno分组，查找人数＞5的部门编号，然后再根据编号对应找到对应员工信息

12.统计每个部门中各工种的人数与平均工资

select deptno,job,count(emptno),avg(sal) from emp group by deptno,job;
-- 按照部门编号和工作分组，然后找到部门编号对应的工作和平均工资