-- ------------------------------ Table structure for bonus-- ----------------------------DROP TABLE IF EXISTS `bonus`;CREATE TABLE `bonus` (`ename` varchar(10) DEFAULT NULL COMMENT '雇员姓名',`job` varchar(9) DEFAULT NULL COMMENT '雇员职位',`sal` decimal(7,2) DEFAULT NULL COMMENT '雇员工资',`comm` decimal(7,2) DEFAULT NULL COMMENT '雇员资金') ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COMMENT='工资表';-- ------------------------------ Records of bonus-- ------------------------------ ------------------------------ Table structure for dept-- ----------------------------DROP TABLE IF EXISTS `dept`;CREATE TABLE `dept` (`deptno` int unsigned NOT NULL AUTO_INCREMENT COMMENT '部门编号',`dname` varchar(15) DEFAULT NULL COMMENT '部门名称',`loc` varchar(50) DEFAULT NULL COMMENT '部门所在位置',PRIMARY KEY (`deptno`)) ENGINE=InnoDB AUTO_INCREMENT=41 DEFAULT CHARSET=utf8mb3 COMMENT='部门表';-- ------------------------------ Records of dept-- ----------------------------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');-- ------------------------------ Table structure for emp-- ----------------------------DROP TABLE IF EXISTS `emp`;CREATE TABLE `emp` (`empno` int unsigned NOT NULL AUTO_INCREMENT COMMENT '雇员编号',`ename` varchar(15) DEFAULT NULL COMMENT '雇员姓名',`job` varchar(10) DEFAULT NULL COMMENT '雇员职位',`mgr` int unsigned DEFAULT NULL COMMENT '雇员对应的领导的编号',`hiredate` date DEFAULT NULL COMMENT '雇员的雇佣日期',`sal` decimal(7,2) DEFAULT NULL COMMENT '雇员的基本工资',`comm` decimal(7,2) DEFAULT NULL COMMENT '奖金',`deptno` int unsigned DEFAULT NULL COMMENT '所在部门',PRIMARY KEY (`empno`),KEY `deptno` (`deptno`),CONSTRAINT `emp_ibfk_1` FOREIGN KEY (`deptno`) REFERENCES `dept` (`deptno`)) ENGINE=InnoDB AUTO_INCREMENT=7935 DEFAULT CHARSET=utf8mb3 COMMENT='雇员表';-- ------------------------------ Records of emp-- ----------------------------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-07-13', '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', '1987-07-13', '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');-- ------------------------------ Table structure for salgrade-- ----------------------------DROP TABLE IF EXISTS `salgrade`;CREATE TABLE `salgrade` (`grade` int unsigned DEFAULT NULL COMMENT '工资等级',`losal` int unsigned DEFAULT NULL COMMENT '此等级的最低工资',`hisal` int unsigned DEFAULT NULL COMMENT '此等级的最高工资') ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COMMENT='工资等级表';-- ------------------------------ Records of salgrade-- ----------------------------INSERT INTO `salgrade` VALUES ('1', '700', '1200');INSERT INTO `salgrade` VALUES ('2', '1201', '1400');INSERT INTO `salgrade` VALUES ('3', '1401', '2000');INSERT INTO `salgrade` VALUES ('4', '2001', '3000');INSERT INTO `salgrade` VALUES ('5', '3001', '9999');
练习题
1.取得每个部门最高薪水的人员名称
2.哪些人的薪水在部门的平均薪水之上
3.取得部门中（所有人的）平均的薪水等级
4.不准用组函数（Max），取得最高薪水（给出两种解决方案）
5.取得平均薪水最高的部门的部门编号（至少给出两种解决方案）
6.取得平均薪水最高的部门的部门名称
7.求平均薪水的等级最低的部门的部门名称
8.取得比普通员工(员工代码没有在mgr字段上出现的)的最高薪水还要高的经理人姓名
9.取得薪水最高的前五名员工
10.取得薪水最高的第六到第十名员工
11.取得最后入职的5名员工
12.取得每个薪水等级有多少员工
13.面试题
S（SNO，SNAME）代表（学号，姓名）
C（CNO，CNAME，CTEACHER）代表（课号，课名，教师）
SC（SNO，CNO，SCGRADE）代表（学号，课号，成绩）
13.1 找出没选过“黎明”老师的所有学生姓名
13.2 列出2门以上（含2门）不及格学生姓名及平均成绩。
13.3 既学过1号课程又学过2号课所有学生的姓名
14.列出所有员工及直接上级的姓名
15.列出受雇日期早于其直接上级的所有员工的编号(empno),姓名(ename),部门名称(dname)
16.列出部门名称和这些部门的员工信息,同时列出那些没有员工的部门
17.列出至少有一个员工的所有部门
18.列出薪金比”SMITH”多的所有员工信息
- 列出所有 “CLERK” 的姓名及其部门名称,部门的人数.
20.列出最低薪金大于1500的各种工作及从事此工作的全部雇员人数
21.列出在部门”SALES”工作的员工的姓名,假定不知道销售部的部门编号
22.列出薪金高于公司平均薪金的所有员工,所在部门,上级领导,雇员的工资等级
23.列出与”SCOTT”从事相同工作的所有员工及部门名称
24.列出薪金等于部门30中员工的薪金的所有员工的姓名和薪金
25.列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金.部门名称
26.列出在每个部门工作的员工数量,平均工资和平均服务期限.
27.列出所有员工的姓名、部门名称和工资
28.列出所有部门的详细信息和人数
29.列出各种工作的最低工资及从事此工作的雇员姓名
30.列出各个部门的MANAGER(经理)的最低薪金
31.列出所有员工的年工资,按年薪从低到高排序
32.查出某个员工的上级主管，并要求这些主管中的薪水超过3000
33.求出部门名称中,带’S’字符的部门，员工的工资合计、部门人数
34.给任职日期超过25年的员工加薪10%
练习题答案
1.取得每个部门最高薪水的人员名称
第一步：按照部门编号分组，对每一组求工资最大值。
SQL> select deptno,max(sal) as maxsal from emp group by deptno;
DEPTNO MAXSAL<br />---------- ----------<br /> 30 2850<br /> 20 3000<br /> 10 5000
第二步：从emp表中找员工的名字，emp.sal=T.maxsal
SQL> select e.ename,t.deptno,t.maxsal from emp e join (select deptno,max(sal) as maxsal from emp group by deptno) t on e.sal=t.maxsal;
ENAME DEPTNO MAXSAL
————— ————— —————
BLAKE 30 2850
FORD 20 3000
SCOTT 20 3000
KING 10 5000
2.哪些人的薪水在部门的平均薪水之上
第一步：每个部门的平均薪水.
SQL> select deptno,avg(sal) as avgsal from emp group by deptno;
DEPTNO AVGSAL<br />---------- ----------<br /> 30 1566.66667<br /> 20 2175<br /> 10 2916.66667
第二步：将上述结果看做临时表T，T表和emp表连接，条件：emp.sal>t.avgsal and emp.deptno=t.deptno
SQL> select e.ename,e.sal,t.deptno,t.avgsal from emp e join (select deptno,avg(sal) as avgsal from emp group by deptno) t on e.sal>t.avgsal and e.deptno=t.deptno;
ENAME SAL DEPTNO AVGSAL
————— ————— ————— —————
BLAKE 2850 30 1566.66667
ALLEN 1600 30 1566.66667
FORD 3000 20 2175
SCOTT 3000 20 2175
JONES 2975 20 2175
KING 5000 10 2916.66667
3.取得部门中（所有人的）平均的薪水等级
（这个问题有争议，1、是所有人等级的平均？这个显然是不对的，2、部门所有人平均薪水的等级，这个显然合理
所有同学们遇到有争议的需求，需要及时沟通才对的
）
第一步：找出每个人的薪水等级。(emp,salgrade)
SQL> select e.ename,e.deptno,s.grade from emp e join salgrade s on e.sal between s.losal and s.hisal;
ENAME DEPTNO GRADE
————— ————— —————
SMITH 20 1
ADAMS 20 1
JONES 20 4
SCOTT 20 4
FORD 20 4
JAMES 30 1
WARD 30 2
MARTIN 30 2
TURNER 30 3
ALLEN 30 3
BLAKE 30 4
MILLER 10 2
CLARK 10 4
KING 10 5
第二步：以上查询结果看做临时表T，按照部门编号分组，求平均的薪水等级。
SQL> select t.deptno,avg(t.grade) as avggrade from (select e.ename,e.deptno,s.grade from emp e join salgrade s on e.sal between s.losal and s.hisal) t group by t.deptno;
DEPTNO AVGGRADE<br />---------- ----------<br /> 30 2.5<br /> 20 2.8<br /> 10 3.66666667
这个答案是平均薪水的等级
SELECT
d.deptno,
d.avgsal,
sg.grade
FROM
(
SELECT
avg(e.sal) AS avgsal,
e.deptno
FROM
emp e
GROUP BY
e.deptno
) d
JOIN salgrade sg ON d.avgsal > sg.losal
AND d.avgsal < sg.hisal
4.不准用组函数（Max），取得最高薪水（给出两种解决方案）
第一种方案：降序排列，取rownum=1
SQL> SELECT * FROM emp e ORDER BY e.SAL desc LIMIT 1;
第二种方案：表的自连接
SQL> select distinct a.sal from emp a join emp b on a.sal<b.sal;
SAL<br />----------<br /> 800<br /> 950<br /> 1100<br /> 1250<br /> 1300<br /> 1500<br /> 1600<br /> 2450<br /> 2850<br /> 2975<br /> 3000
SQL> select e.sal from emp e where sal not in(select distinct a.sal from emp a join emp b on a.sal<b.sal);
SAL<br />----------<br /> 5000
5.取得平均薪水最高的部门的部门编号（至少给出两种解决方案）
分析:前两种可以类似上一题目,另外还可以使用MAX函数
第一种方案：
第一步：取得每个部门的平均薪水
SQL> select deptno,avg(sal) as avgsal from emp group by deptno;
DEPTNO AVGSAL<br />---------- ----------<br /> 30 1566.66667<br /> 20 2175<br /> 10 2916.66667
SQL> select max(avg(sal)) as maxsal from emp group by deptno;
MAXSAL<br />----------<br />2916.66667
SQL> select t1.deptno,t2.maxsal from (select deptno,avg(sal) as avgsal from emp group by deptno) t1 join (select max(avg(sal)) as maxsal from emp group by deptno) t2 on t1.avgsal=t2.maxsal;
DEPTNO MAXSAL<br />---------- ----------<br /> 10 2916.66667
第二种方案：
SQL> select t1.deptno,t2.maxsal from (select deptno,avg(sal) as avgsal from emp
group by deptno) t1 join (select a.avgsal as maxsal from (select avg(sal) as avg
sal from emp group by deptno order by avgsal desc) a where rownum=1) t2 on t1.av
gsal=t2.maxsal;
DEPTNO MAXSAL<br />---------- ----------<br /> 10 2916.66667
6.取得平均薪水最高的部门的部门名称
SQL> select d.dname,t3.maxsal from dept d join (select t1.deptno,t2.maxsal from (select deptno,avg(sal) as avgsal from emp group by deptno) t1 join (select max(avg(sal)) as maxsal from emp group by deptno) t2 on t1.avgsal=t2.maxsal) t3 on d.deptno=t3.deptno;
DNAME MAXSAL
——————— —————
ACCOUNTING 2916.66667
7.求平均薪水的等级最低的部门的部门名称
第一步：求每个部门的平均薪水
SQL> select deptno,avg(sal) as avgsal from emp group by deptno;
DEPTNO AVGSAL<br />---------- ----------<br /> 30 1566.66667<br /> 20 2175<br /> 10 2916.66667
第二步：求每个部门的平均薪水的等级 (T1.avgsal between s.losal and s.hisal)
SQL> select t1.deptno,t1.avgsal,s.grade from (select deptno,avg(sal) as avgsal from emp group by deptno) t1 join salgrade s on t1.avgsal between s.losal and s.hisal;
DEPTNO AVGSAL GRADE
——— ————— —————
30 1566.66667 3
20 2175 4
10 2916.66667 4
SQL> select min(s.grade) as mingrade from (select deptno,avg(sal) as avgsal from emp group by deptno) t1 join salgrade s on t1.avgsal between s.losal and s.hisal;
MINGRADE
—————
3
SQL>select d.dname,t2.avgsal,t2.grade from dept d join (select t1.deptno,t1.avgsal,s.grade from (select deptno,avg(sal) as avgsal from emp group by deptno) t1 join salgrade s on t1.avgsal between s.losal and s.hisal) t2 on d.deptno=t2.deptno where t2.grade = (select min(s.grade) as mingrade from (select deptno,avg(sal) as avgsal from emp group by deptno) t1 join salgrade s on t1.avgsal between s.losal and s.hisal);
DNAME AVGSAL GRADE
——————— ————— —————
SALES 1566.66667 3
8.取得比普通员工(员工代码没有在mgr字段上出现的)的最高薪水还要高的经理人姓名
第一步：取出普通员工的最高薪水
select max(sal) from emp where empno not in(select distinct mgr from emp where mgr is not null);
MAX(SAL)
————-
1600
第二步：比1600高的员工一定是经理.
select ename,sal from emp where sal>(select max(sal) from emp where empno not in(select distinct mgr from emp where mgr is not null));
ENAME SAL
————— —————
JONES 2975
BLAKE 2850
CLARK 2450
SCOTT 3000
KING 5000
FORD 3000
9.取得薪水最高的前五名员工
SQL> select a.ename,a.sal from (select ename,sal from emp order by sal desc) a where rownum<=5;
ENAME SAL
————— —————
KING 5000
SCOTT 3000
FORD 3000
JONES 2975
BLAKE 2850
10.取得薪水最高的第六到第十名员工
select a.ename,a.sal,rownum as r from (select ename,sal from emp order by sal desc) a where rownum<=10;
ENAME SAL R
————— ————— —————
KING 5000 1
SCOTT 3000 2
FORD 3000 3
JONES 2975 4
BLAKE 2850 5
CLARK 2450 6
ALLEN 1600 7
TURNER 1500 8
MILLER 1300 9
WARD 1250 10
select t.ename,t.sal,t.r from (select a.ename,a.sal,rownum as r from (select ename,sal from emp order by sal desc) a where rownum<=10) t where t.r>=6;
ENAME SAL R
————— ————— —————
CLARK 2450 6
ALLEN 1600 7
TURNER 1500 8
MILLER 1300 9
WARD 1250 10
11.取得最后入职的5名员工
按照入职日期降序排列，取前5个.
SQL> select a.ename,to_char(a.hiredate,’YYYY-MM-DD’) as hiredate from (select ename,hiredate from emp order by hiredate desc) a where rownum<=5;
ENAME HIREDATE
————— —————
ADAMS 1987-05-23
SCOTT 1987-04-19
MILLER 1982-01-23
JAMES 1981-12-03
FORD 1981-12-03
12.取得每个薪水等级有多少员工
求每个员工的薪水等级
select e.sal,s.grade from emp e join salgrade s on e.sal between s.losal and s.hisal;
SAL GRADE<br />------- ----------<br /> 800 1<br /> 950 1<br /> 1100 1<br /> 1250 2<br /> 1250 2<br /> 1300 2<br /> 1500 3<br /> 1600 3<br /> 2450 4<br /> 2850 4<br /> 2975 4<br /> 3000 4<br /> 3000 4<br /> 5000 5
SQL> select a.grade,count(a.grade) as gradecount from (select e.sal,s.grade from emp e join salgrade s on e.sal between s.losal and s.hisal) a group by a.grade;
GRADE GRADECOUNT<br />---------- ----------<br /> 1 3<br /> 2 3<br /> 4 5<br /> 5 1<br /> 3 2
13.面试题
S（SNO，SNAME）代表（学号，姓名）
C（CNO，CNAME，CTEACHER）代表（课号，课名，教师）
SC（SNO，CNO，SCGRADE）代表（学号，课号，成绩）
13.1 找出没选过“黎明”老师的所有学生姓名
SELECT SNAME FROM S WHERE SNO NOT IN(SELECT SNO FROM SC WHERE CNO IN(SELECT CNO FROM C WHERE CTEACHER=’黎明’));
13.2 列出2门以上（含2门）不及格学生姓名及平均成绩。
select s.sname,e.avgscore from s join (select c.sno,c.avgscore from (select sno,avg(scgrade) as avgscore from sc group by sno) c join (select b.sno from (select a.sno,count(a.sno) as snocount from (select sno,scgrade from sc where scgrade<60) a group by a.sno) b where b.snocount>=2) d on c.sno=d.sno) e on s.sno=e.sno;
13.3 既学过1号课程又学过2号课所有学生的姓名
select s.sname from s join (select t1.sno from (select sno from sc where cno=1) t1 join (select sno from sc where cno=2) t2 on t1.sno=t2.sno) t on s.sno=t.sno;
14.列出所有员工及直接上级的姓名
emp a (员工表)
emp b (领导表)
员工表.mgr = 领导表.empno
select a.ename 员工,nvl(b.ename,’没有领导’) 领导 from emp a left outer join emp b on a.mgr = b.empno;
员工 领导
————— —————
SMITH FORD
ALLEN BLAKE
WARD BLAKE
JONES KING
MARTIN BLAKE
BLAKE KING
CLARK KING
SCOTT JONES
KING 没有领导
TURNER BLAKE
ADAMS SCOTT
JAMES BLAKE
FORD JONES
MILLER CLARK
15.列出受雇日期早于其直接上级的所有员工的编号(empno),姓名(ename),部门名称(dname)
emp a (员工表)
emp b (领导表)
select
a.empno,a.ename,d.dname
from
emp a
join
emp b
on
a.mgr=b.empno and a.hiredate
dept d
on
a.deptno=d.deptno;
EMPNO ENAME DNAME
——— ————— ——————
7369 SMITH RESEARCH
7499 ALLEN SALES
7521 WARD SALES
7566 JONES RESEARCH
7698 BLAKE SALES
7782 CLARK ACCOUNTING
16.列出部门名称和这些部门的员工信息,同时列出那些没有员工的部门
select e.*,d.dname from emp e right outer join dept d on e.deptno=d.deptno;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO DNAME
——- ————— ————- ————— ——————— ————— ————— ————— ———————
7782 CLARK MANAGER 7839 09-6月 -81 2450 10 ACCOUNTING
7839 KING PRESIDENT 17-11月-81 5000 10 ACCOUNTING
7934 MILLER CLERK 7782 23-1月 -82 1300 10 ACCOUNTING
7566 JONES MANAGER 7839 02-4月 -81 2975 20 RESEARCH
7902 FORD ANALYST 7566 03-12月-81 3000 20 RESEARCH
7876 ADAMS CLERK 7788 23-5月 -87 1100 20 RESEARCH
7369 SMITH CLERK 7902 17-12月-80 800 20 RESEARCH
7788 SCOTT ANALYST 7566 19-4月 -87 3000 20 RESEARCH
7521 WARD SALESMAN 7698 22-2月 -81 1250 500 30 SALES
7844 TURNER SALESMAN 7698 08-9月 -81 1500 0 30 SALES
7499 ALLEN SALESMAN 7698 20-2月 -81 1600 300 30 SALES
7900 JAMES CLERK 7698 03-12月-81 950 30 SALES
7698 BLAKE MANAGER 7839 01-5月 -81 2850 30 SALES
7654 MARTIN SALESMAN 7698 28-9月 -81 1250 1400 30 SALES
OPERATIONS
17.列出至少有一个员工的所有部门
select distinct deptno from emp;
DEPTNO
———
30
20
10
18.列出薪金比”SMITH”多的所有员工信息
select e.* from emp e where e.sal>(select sal from emp where ename=’SMITH’);
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
——— ————— ————- ————— ——————— ————— ————— —————
7499 ALLEN SALESMAN 7698 20-2月 -81 1600 300 30
7521 WARD SALESMAN 7698 22-2月 -81 1250 500 30
7566 JONES MANAGER 7839 02-4月 -81 2975 20
7654 MARTIN SALESMAN 7698 28-9月 -81 1250 1400 30
7698 BLAKE MANAGER 7839 01-5月 -81 2850 30
7782 CLARK MANAGER 7839 09-6月 -81 2450 10
7788 SCOTT ANALYST 7566 19-4月 -87 3000 20
7839 KING PRESIDENT 17-11月-81 5000 10
7844 TURNER SALESMAN 7698 08-9月 -81 1500 0 30
7876 ADAMS CLERK 7788 23-5月 -87 1100 20
7900 JAMES CLERK 7698 03-12月-81 950 30
7902 FORD ANALYST 7566 03-12月-81 3000 20
7934 MILLER CLERK 7782 23-1月 -82 1300 10
- 列出所有 “CLERK” 的姓名及其部门名称,部门的人数.
select deptno,ename from emp where job=’CLERK’;
DEPTNO ENAME
——— ———-
20 SMITH
20 ADAMS
30 JAMES
10 MILLER
select d.dname,t.deptno,t.deptcount from dept d join (select deptno,count(*) as deptcount from emp group by deptno) t on d.deptno=t.deptno;
DNAME DEPTNO DEPTCOUNT
——————— ————— —————
SALES 30 6
RESEARCH 20 5
ACCOUNTING 10 3
select t1.ename,t2.dname,t2.deptcount from (select deptno,ename from emp where job=’CLERK’) t1 join (select d.dname,t.deptno,t.deptcount from dept d join (select deptno,count(*) as deptcount from emp group by deptno) t on d.deptno=t.deptno) t2 on t1.deptno=t2.deptno;
ENAME DNAME DEPTCOUNT
————— ——————— —————
SMITH RESEARCH 5
ADAMS RESEARCH 5
JAMES SALES 6
MILLER ACCOUNTING 3
20.列出最低薪金大于1500的各种工作及从事此工作的全部雇员人数
select job,count(*) as jobcount,min(sal) minsal from emp group by job having min(sal)>1500;
JOB JOBCOUNT MINSAL
————- ————— —————
PRESIDENT 1 5000
MANAGER 3 2450
ANALYST 2 3000
21.列出在部门”SALES”工作的员工的姓名,假定不知道销售部的部门编号
SQL> select ename from emp where deptno=(select deptno from dept where dname=’SALES’);
ENAME
—————
ALLEN
WARD
MARTIN
BLAKE
TURNER
JAMES
22.列出薪金高于公司平均薪金的所有员工,所在部门,上级领导,雇员的工资等级
emp a (员工表)
emp b (领导表)
dept d (部门表)
salgrade s (工资级别表)
select a.ename 员工,a.sal,b.ename 领导,d.dname,s.grade
from emp a left outer join emp b on a.mgr=b.empno join dept d on a.deptno=d.deptno join salgrade s on a.sal between s.losal and s.hisal
where a.sal>(select avg(sal) from emp);
员工 SAL 领导 DNAME GRADE
————— ————— ————— ——————— —————
JONES 2975 KING RESEARCH 4
BLAKE 2850 KING SALES 4
CLARK 2450 KING ACCOUNTING 4
SCOTT 3000 JONES RESEARCH 4
KING 5000 ACCOUNTING 5
FORD 3000 JONES RESEARCH 4
23.列出与”SCOTT”从事相同工作的所有员工及部门名称
select e.ename,d.dname from emp e join dept d on e.deptno=d.deptno
where e.job=(select job from emp where ename=’SCOTT’) and e.ename<>’SCOTT’;
ENAME DNAME
————— ————-
FORD RESEARCH
24.列出薪金等于部门30中员工的薪金的所有员工的姓名和薪金
SQL> select ename,sal,deptno from emp where sal in(select distinct sal from emp where deptno=30);
ENAME SAL
————— —————
ALLEN 1600
MARTIN 1250
WARD 1250
BLAKE 2850
TURNER 1500
JAMES 950
25.列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金.部门名称
select e.ename,e.sal,d.dname from emp e join dept d on e.deptno=d.deptno where sal>(select max(sal) from emp where deptno=30);
ENAME SAL DNAME
————— ————— —————
JONES 2975 RESEARCH
SCOTT 3000 RESEARCH
KING 5000 ACCOUNTING
FORD 3000 RESEARCH
26.列出在每个部门工作的员工数量,平均工资和平均服务期限.
select deptno,count(*),avg(sal),avg((sysdate-hiredate)/365) from emp group by deptno;
DEPTNO COUNT(*) AVG(SAL) AVG((SYSDATE-HIREDATE)/365)
———- ————— ————— —————————————-
30 6 1566.66667 31.0598057
20 5 2175 28.7882075
10 3 2916.66667 30.7611755
27.列出所有员工的姓名、部门名称和工资
select e.ename,d.dname,e.sal from emp e join dept d on e.deptno=d.deptno;
ENAME DNAME SAL
————— ——————— —————
SMITH RESEARCH 800
ALLEN SALES 1600
WARD SALES 1250
JONES RESEARCH 2975
MARTIN SALES 1250
BLAKE SALES 2850
CLARK ACCOUNTING 2450
SCOTT RESEARCH 3000
KING ACCOUNTING 5000
TURNER SALES 1500
ADAMS RESEARCH 1100
JAMES SALES 950
FORD RESEARCH 3000
MILLER ACCOUNTING 1300
28.列出所有部门的详细信息和人数
select deptno,count(*) as deptcount from emp group by deptno;
DEPTNO DEPTCOUNT
——— —————
30 6
20 5
10 3
T(DEPTNO,DEPTCOUNT)
DEPT(DEPTNO,DNAME,LOC)
SQL> select d.,t.deptcount from dept d left join (select deptno,count() as deptcount from emp group by deptno) t on d.deptno=t.deptno;
DEPTNO DNAME LOC DEPTCOUNT<br />---------- -------------- ------------- ----------<br /> 10 ACCOUNTING NEW YORK 3<br /> 20 RESEARCH DALLAS 5<br /> 30 SALES CHICAGO 6<br /> 40 OPERATIONS BOSTON
29.列出各种工作的最低工资及从事此工作的雇员姓名
select job,min(sal) as minsal from emp group by job;
JOB MINSAL
————- —————
CLERK 800
SALESMAN 1250
PRESIDENT 5000
MANAGER 2450
ANALYST 3000
select e.ename,t.job,t.minsal from emp e join (select job,min(sal) as minsal from emp group by job) t on e.job=t.job and e.sal=t.minsal;
ENAME JOB MINSAL
————— ————- —————
SMITH CLERK 800
MARTIN SALESMAN 1250
WARD SALESMAN 1250
KING PRESIDENT 5000
CLARK MANAGER 2450
FORD ANALYST 3000
SCOTT ANALYST 3000
30.列出各个部门的MANAGER(经理)的最低薪金
select sal,deptno from emp where job=’MANAGER’;
SAL DEPTNO
——— —————
2975 20
2850 30
2450 10
select t.deptno,min(t.sal) as minsal from (select sal,deptno from emp where job=’MANAGER’) t group by t.deptno;
DEPTNO MINSAL
——— —————
30 2850
20 2975
10 2450
31.列出所有员工的年工资,按年薪从低到高排序
select ename,(sal+nvl(comm,0))*12 as yearsal from emp order by yearsal asc;
ENAME YEARSAL
————— —————
SMITH 9600
JAMES 11400
ADAMS 13200
MILLER 15600
TURNER 18000
WARD 21000
ALLEN 22800
CLARK 29400
MARTIN 31800
BLAKE 34200
JONES 35700
FORD 36000
SCOTT 36000
KING 60000
32.查出某个员工的上级主管，并要求这些主管中的薪水超过3000
emp a (员工表)
emp b (上级主管表)
select a.ename 员工,b.ename 主管 from emp a join emp b on a.mgr = b.empno where b.sal>3000;
员工 主管
————— ———-
JONES KING
BLAKE KING
CLARK KING
33.求出部门名称中,带’S’字符的部门，员工的工资合计、部门人数
SQL> select deptno,dname from dept where dname like ‘%S%’;
DEPTNO DNAME<br />---------- --------------<br /> 20 RESEARCH<br /> 30 SALES<br /> 40 OPERATIONS
SQL> select deptno,sum(sal) as sumsal,count(*) as deptcount from emp group by deptno;
DEPTNO SUMSAL DEPTCOUNT
——— ————— —————
30 9400 6
20 10875 5
10 8750 3
select t1.dname,t2.sumsal,t2.deptcount from (select deptno,dname from dept where dname like ‘%S%’) t1 left join (select deptno,sum(sal) as sumsal,count(*) as deptcount from emp group by deptno) t2 on t1.deptno=t2.deptno;
DNAME SUMSAL DEPTCOUNT
——————— ————— —————
RESEARCH 10875 5
SALES 9400 6
OPERATIONS
34.给任职日期超过25年的员工加薪10%
create table emp_bak as select * from emp;
update emp_bak set sal=sal*1.1 where months_between(sysdate,hiredate)/12>25;
若有收获，就点个赞吧
0 人点赞
