建表语句：
CREATE TABLE employee (
id int(11) NOT NULL AUTO_INCREMENT,
name varchar(150) NOT NULL DEFAULT '',
dept varchar(150) NOT NULL DEFAULT '',
salary int(11) NOT NULL DEFAULT '0',
edlevel int(11) NOT NULL DEFAULT '0',
hiredate varchar(150) NOT NULL DEFAULT '',
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
插入数据：
INSERT INTO employee (id, name, dept, salary, edlevel, hiredate)
VALUES
(1,'张三','开发部',2000,3,'2009-10-11'),
(2,'李四','开发部',2500,3,'2009-10-01'),
(3,'王五','设计部',2600,5,'2010-10-12'),
(4,'王六','设计部',2300,4,'2010-10-03'),
(5,'马七','设计部',2100,4,'2010-10-06'),
(6,'赵八','销售部',3000,5,'2010-10-05'),
(7,'钱九','销售部',3100,7,'2010-10-07'),
(8,'孙十','销售部',3500,7,'2010-10-06');
1、列出工资高于本部门平均工资的员工姓名、工资、部门名称;
select a.name,a.dept,a.salary from employee a where a.salary > (select avg(b.salary) from employee b where a.dept=b.dept);
2、列出各部门中工资高于本部门平均工资的员工数和部门名称;
select dept,count(*) num from employee a where a.salary > (select avg(b.salary) from employee b where a.dept=b.dept) group by a.dept;
二、
CREATE TABLE stuscore (
id int(11) NOT NULL AUTO_INCREMENT,
name varchar(50) NOT NULL DEFAULT '',
class varchar(50) NOT NULL DEFAULT '',
course varchar(50) NOT NULL DEFAULT '',
score int(11) NOT NULL DEFAULT '0',
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO stuscore (id, name, class, course, score)
VALUES
(1,'小明','1班','高数',81),
(2,'小明','1班','数据结构',88),
(3,'小明','1班','操作系统',65),
(4,'小红','1班','数据结构',55),
(5,'小丁','2班','高数',90),
(6,'小样','3班','高数',70),
(7,'小样','3班','数据结构',78),
(8,'小红','1班','高数',67),
(9,'小丁','2班','操作系统',87),
(10,'小红','1班','操作系统',52);
1、列出课程得分小于课程平均分的记录;
select a.* from stuscore a ,(select course as a_course,avg(score) as a_score from stuscore group by course) as avg_score
where a.course=avg_score.a_course and a.score < avg_score.a_score ;
2、筛选出每门课程都比对应课程平均分低的同学（悬而未决）
三、
CREATE TABLE provincet (
id int(11) NOT NULL AUTO_INCREMENT,
province varchar(50) NOT NULL DEFAULT '',
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO provincet (id, province)
VALUES
(1,'广东'),
(2,'湖南'),
(3,'湖北');
CREATE TABLE city (
id int(11) NOT NULL AUTO_INCREMENT,
pid int(11) NOT NULL DEFAULT '0',
city varchar(50) NOT NULL DEFAULT '',
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO city (id, pid, city)
VALUES
(1,1,'广州'),
(2,1,'深圳'),
(3,1,'惠州'),
(4,2,'长沙'),
(5,3,'武汉');
1、统计每个省份有几个城市，显示字段：省份id、省份名、城市个数
select p.id,p.province,count(pid) num from provincet p left join city c on p.id = c.pid group by pid;