数据库sql语句操作题目

(1) 取得每个部门最高薪水的人员名称：

(2)哪些人的薪水在部门平均薪水之上：

1.创建部门表：

DROP TABLE IF EXISTS `dept`;
CREATE TABLE `dept` (
  `DEPTNO` int(2) NOT NULL COMMENT '部门编号',
  `DNAME` varchar(14) DEFAULT NULL COMMENT '部门名称',
  `LOC` varchar(13) DEFAULT NULL COMMENT '位置',
  PRIMARY KEY (`DEPTNO`)
) ENGINE=InnoDB;

插入数据：

INSERT INTO `dept` VALUES ('10', 'ACCOUNTING', 'NEW YORK');
INSERT INTO `dept` VALUES ('20', 'RESEARCH', 'DALLAS');
INSERT INTO `dept` VALUES ('30', 'SALES', 'CHICAGO');
INSERT INTO `dept` VALUES ('40', 'OPERATIONS', 'BOSTON');

2：创建员工表

DROP TABLE IF EXISTS `emp`;
CREATE TABLE `emp` (
  `EMPNO` int(4) NOT NULL COMMENT '员工编号',
  `ENAME` varchar(10) DEFAULT NULL COMMENT '员工姓名',
  `JOB` varchar(9) DEFAULT NULL COMMENT '工作岗位',
  `MGR` int(4) DEFAULT NULL COMMENT '上级经理',
  `HIREDATE` date DEFAULT NULL,
  `SAL` double(7,2) DEFAULT NULL,
  `COMM` double(7,2) DEFAULT NULL,
  `DEPTNO` int(2) DEFAULT NULL,
  PRIMARY KEY (`EMPNO`),
  KEY `DEPTNO` (`DEPTNO`),
  KEY `SAL` (`SAL`),
  CONSTRAINT `emp_ibfk_1` FOREIGN KEY (`DEPTNO`) REFERENCES `dept` (`DEPTNO`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='员工编号';

 插入数据：

INSERT INTO `emp` VALUES ('7369', 'SMITH', 'CLERK', '7902', '1980-12-17', '800.00', null, '20');
INSERT INTO `emp` VALUES ('7499', 'ALLEN', 'SALESMAN', '7698', '1981-02-20', '1600.00', '300.00', '30');
INSERT INTO `emp` VALUES ('7521', 'WARD', 'SALESMAN', '7698', '1981-02-22', '1250.00', '500.00', '30');
INSERT INTO `emp` VALUES ('7566', 'JONES', 'MANAGER', '7839', '1981-04-02', '2975.00', null, '20');
INSERT INTO `emp` VALUES ('7654', 'MARTIN', 'SALESMAN', '7698', '1981-09-28', '1250.00', '1400.00', '30');
INSERT INTO `emp` VALUES ('7698', 'BLAKE', 'MANAGER', '7839', '1981-05-01', '2850.00', null, '30');
INSERT INTO `emp` VALUES ('7782', 'CLARK', 'MANAGER', '7839', '1981-06-09', '2450.00', null, '10');
INSERT INTO `emp` VALUES ('7788', 'SCOTT', 'ANALYST', '7566', '1987-04-19', '3000.00', null, '20');
INSERT INTO `emp` VALUES ('7839', 'KING', 'PRESIDENT', null, '1981-11-17', '5000.00', null, '10');
INSERT INTO `emp` VALUES ('7844', 'TURNER', 'SALESMAN', '7698', '1981-09-08', '1500.00', '0.00', '30');
INSERT INTO `emp` VALUES ('7876', 'ADAMS', 'CLERK', '7788', '1981-05-23', '1100.00', null, '20');
INSERT INTO `emp` VALUES ('7900', 'JAMES', 'CLERK', '7698', '1981-12-03', '950.00', null, '30');
INSERT INTO `emp` VALUES ('7902', 'FORD', 'ANALYST', '7566', '1981-12-03', '3000.00', null, '20');
INSERT INTO `emp` VALUES ('7934', 'MILLER', 'CLERK', '7782', '1982-01-23', '1300.00', null, '10');

查看数据： 

(1) 取得每个部门最高薪水的人员名称：

 第1步：求出每个部门的最高薪水(根据部门编号进行分组求出最大值)

select e.DEPTNO,max(e.SAL) as maxSAL from emp as e group by e.DEPTNO;

 

第2步：将以上查询结果当为一个临时表t(DEPTNO,maxSAL)，然后将t表和emp表进行连接，连接条件是DEPTNO，最后的where条件是：员工薪水等于部门最高薪水

select e.DEPTNO,e.ENAME,t.maxSAL,e.SAL from t inner join emp as e on t.DEPTNO=e.DEPTNO
where t.maxSAl=e.SAL;

将t替换为第一步的语句：

select e.DEPTNO,e.ENAME,t.maxSAL,e.SAL from 
(select e.DEPTNO,max(e.SAL) as maxSAL from emp as e group by e.DEPTNO) as t 
inner join emp as e on t.DEPTNO=e.DEPTNO 
where t.maxSAl=e.SAL;

 

(2)哪些人的薪水在部门平均薪水之上：

第1步：先求出每个部门的平均薪水：

select e.DEPTNO,avg(e.SAL) as avgSAL from emp as e group by e.DEPTNO;

第2步：将以上结果作为临时表t(DEPTNO,avgSAL) ,然后与员工表进行连接，最后的where条件是员工得到薪水大于部门的平均薪水：

select t.DEPTNO,e.ENAME from t
inner join emp e on t.DEPTNO=e.DEPTNO
where e.SAL>t.avgSAL

然后将t替换为第1步中的语句：

select t.DEPTNO,e.ENAME from 
(select e.DEPTNO,avg(e.SAL) as avgSAL from emp as e group by e.DEPTNO) as t
inner join emp e on t.DEPTNO=e.DEPTNO
where e.SAL>t.avgSAL;