SQL 查询

一、表: employees(name varchar(30) not null, department varchar(30) not null, primary key(name));

找出员工数>10 的部门。

select department group by deprtment having count(name) > 10;

二、一个是部门表: department(deptno int(8) not null, deptname varchar(30), primary key (deptno));
      一个是员工表:  employee(ename varchar(30) not null, esaraly float(8,2), deptno int(8) not null, primary key(ename), foreign key(deptno) references department(deptno));

找出部门的员工工资比他所在的部门平均工资高的员工信息

mysql> select distinct ename,esaraly,deptname from (select * from employee group
 by deptno having esaraly > avg(esaraly)) as tmp inner join department where tmp
.deptno=department.deptno;