数据库部分面试一

1、用两种方式根据部门号从高到底，工资从低到高列出每个员工的信息。

employee:eid,ename,salary,deptid;

select * from employee order by deptid desc,salary asc;

2、列出各个部门中工资高于本部门的平均工资的员工数和部门号，并按部门号排序。

创建表：

mysql>create table employee921(id int primary key auto_increment,name varchar(50),salary bigint,deptid int);

插入实验数据：

mysql>insert into employee921 values(null,'zs',1000,1),(null,'ls',1100,1),(null,'ww',1100,1),(null,'zl',900,1),(null,'zl',1100,2),(null,'zl',900,2),(null,'zl',1000,2),(null,'zl',1100,2);

编写sql语句：

(1)select avg(salary) from employy921 group by deptid;

(2)mysql>select employee921.id,employee921.name,employee921.salary,employee921.deptid tid from employee921 where salary>(select avg(salary) from employy921 where  deptid=tid);

效率低的一个语句，仅供学习参考使用（在group by之后不能使用where,只能使用having,在group by之前可以使用where,即表示过滤后的结果分组）：

mysql>select employee921.id,employee921.name,employee921.salary,employee921.deptid tid from employee921 where salary>(select avg(salary) from employee921 group by deptid having deptid = tid);

(1)select count(*),tid from (

              select employee921.id,employee921.name,

                        employee921.salary,employee921.deptid tid

              from           

                 employee921 where salary > (select avg(salary) from employee921 where deptid = tid)

) as t group by tid;