建表语句：
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;
