IT序号网

oracle 基础表 mysql版知识解答

xmjava 2021年06月14日 数据库 146 0
emp 员工表(empno 员工号/ename 员工姓名/job 工作/mgr 上级编号/hiredate 受雇日期/sal 薪金/comm 佣金/deptno 部门编号)

dept 部门表(deptno 部门编号/dname 部门名称/loc 地点)

----------------------------------------------------------emp表-------------------------------------------------------------


   
  1. DROP TABLE IF EXISTS `emp`;
  2. CREATE TABLE `emp` (
  3. `EMPNO` int( 4) NOT NULL,
  4. `ENAME` varchar( 10) DEFAULT NULL,
  5. `JOB` varchar( 9) DEFAULT NULL,
  6. `MGR` int( 4) DEFAULT NULL,
  7. `HIREDATE` date DEFAULT NULL,
  8. `SAL` int( 7) DEFAULT NULL,
  9. `COMM` int( 7) DEFAULT NULL,
  10. `DEPTNO` int( 2) DEFAULT NULL,
  11. PRIMARY KEY ( `EMPNO`),
  12. KEY `FK_DEPTNO` ( `DEPTNO`),
  13. CONSTRAINT `FK_DEPTNO` FOREIGN KEY ( `DEPTNO`) REFERENCES `dept` ( `DEPTNO`)
  14. ) ENGINE= InnoDB DEFAULT CHARSET=utf8;
  15. -- ----------------------------
  16. -- Records of emp
  17. -- ----------------------------
  18. INSERT INTO `emp` VALUES ( '7369', 'SMITH', 'CLERK', '7902', '1980-12-17', '800', null, '20');
  19. INSERT INTO `emp` VALUES ( '7499', 'ALLEN', 'SALESMAN', '7698', '1981-02-20', '1600', '300', '30');
  20. INSERT INTO `emp` VALUES ( '7521', 'WARD', 'SALESMAN', '7698', '1981-02-22', '1250', '500', '30');
  21. INSERT INTO `emp` VALUES ( '7566', 'JONES', 'MANAGER', '7839', '1981-04-02', '2975', null, '20');
  22. INSERT INTO `emp` VALUES ( '7654', 'MARTIN', 'SALESMAN', '7698', '1981-09-28', '1250', '1400', '30');
  23. INSERT INTO `emp` VALUES ( '7698', 'BLAKE', 'MANAGER', '7839', '1981-05-01', '2850', null, '30');
  24. INSERT INTO `emp` VALUES ( '7782', 'CLARK', 'MANAGER', '7839', '1981-06-09', '2450', null, '10');
  25. INSERT INTO `emp` VALUES ( '7788', 'SCOTT', 'ANALYST', '7566', '1987-04-19', '3000', null, '20');
  26. INSERT INTO `emp` VALUES ( '7839', 'KING', 'PRESIDENT', null, '1981-11-17', '5000', null, '10');
  27. INSERT INTO `emp` VALUES ( '7844', 'TURNER', 'SALESMAN', '7698', '1981-09-08', '1500', '0', '30');
  28. INSERT INTO `emp` VALUES ( '7876', 'ADAMS', 'CLERK', '7788', '1987-05-23', '1100', null, '20');
  29. INSERT INTO `emp` VALUES ( '7900', 'JAMES', 'CLERK', '7698', '1981-12-03', '950', null, '30');
  30. INSERT INTO `emp` VALUES ( '7902', 'FORD', 'ANALYST', '7566', '1981-12-03', '3000', null, '20');
  31. INSERT INTO `emp` VALUES ( '7934', 'MILLER', 'CLERK', '7782', '1982-01-23', '1300', null, '10');

--------------------------------------------------------dept表-------------------------------------------------


   
  1. DROP TABLE IF EXISTS `dept`;
  2. CREATE TABLE `dept` (
  3. `DEPTNO` int( 2) NOT NULL,
  4. `DNAME` varchar( 14) DEFAULT NULL,
  5. `LOC` varchar( 13) DEFAULT NULL,
  6. PRIMARY KEY ( `DEPTNO`)
  7. ) ENGINE= InnoDB DEFAULT CHARSET=utf8;
  8. -- ----------------------------
  9. -- Records of dept
  10. -- ----------------------------
  11. INSERT INTO `dept` VALUES ( '10', 'ACCOUNTING', 'NEW YORK');
  12. INSERT INTO `dept` VALUES ( '20', 'RESEARCH', 'DALLAS');
  13. INSERT INTO `dept` VALUES ( '30', 'SALES', 'CHICAGO');
  14. INSERT INTO `dept` VALUES ( '40', 'OPERATIONS', 'BOSTON');

--------------------------------------------salgrade----------------------------------------


   
  1. DROP TABLE IF EXISTS `salgrade`;
  2. CREATE TABLE `salgrade` (
  3. `grade` int( 11) NOT NULL DEFAULT '0',
  4. `losal` int( 11) DEFAULT NULL,
  5. `hisal` int( 11) DEFAULT NULL,
  6. PRIMARY KEY ( `grade`)
  7. ) ENGINE= InnoDB DEFAULT CHARSET=utf8;
  8. -- ----------------------------
  9. -- Records of salgrade
  10. -- ----------------------------
  11. INSERT INTO `salgrade` VALUES ( '1', '700', '1200');
  12. INSERT INTO `salgrade` VALUES ( '2', '1201', '1400');
  13. INSERT INTO `salgrade` VALUES ( '3', '1401', '2000');
  14. INSERT INTO `salgrade` VALUES ( '4', '2001', '3000');
  15. INSERT INTO `salgrade` VALUES ( '5', '3001', '9999');

---------------------------------------------------------------练习-----------------------------------------------------------


   
  1. SELECT * from emp
  2. /*1-数据分组的重要函数: max、min、avg、sum、count*/
  3. SELECT ename,sal FROM emp WHERE sal= ( SELECT max(sal) from emp )
  4. /*2-查询出工资高于平均工资的员工信息*/
  5. SELECT ename,job,sal from emp WHERE SAL>( SELECT avg(sal) from emp)
  6. /*查询出每个部门的平均工资和最高工资;*/
  7. SELECT avg(sal), max(sal),deptno from emp group by deptno
  8. /*查询出每个部门的每种岗位的平均工资和最低工资;*/
  9. SELECT avg(sal), min(sal),DEPTNO,job from emp group by deptno,job
  10. /*查询出平均工资低于2000的部门号和它的平均工资.*/
  11. SELECT avg(sal),deptno FROM emp GROUP BY DEPTNO having avg(sal)< 2000
  12. SELECT avg(sal),deptno FROM emp GROUP BY DEPTNO having avg(sal)> 2000 ORDER BY avg(sal)
  13. /*查询出每个员工的姓名、工资及其工资的级别*/
  14. SELECT e.ename,e.sal,s.grade from emp e,salgrade s WHERE e.sal BETWEEN s.losal AND s.hisal
  15. /*查询出某个员工的上级领导的姓名*/
  16. SELECT e1.ename,e2.ename from emp e1,emp e2 where e1.mgr=e2.empno
  17. /*询出工资比部门编号30的所有员工的工资高的员工的姓名、工资和部门编号*/
  18. SELECT ename,sal,deptno from emp where sal>all ( SELECT sal from emp WHERE deptno= 30)
  19. /*查询出与SMITH的部门和岗位完全相同的所有雇员*/
  20. SELECT * FROM emp WHERE (job,deptno)=( SELECT job ,deptno FROM emp WHERE ename= 'SMITH')
  21. /*查询出高于自己部门平均工资的员工的信息*/
  22. SELECT e1.ename,e1.sal FROM emp e1,( SELECT deptno, avg(sal) mysal FROM emp GROUP BY deptno) e2 WHERE e1.deptno=e2.deptno AND e1.sal>e2.mysal
  23. /*谁挣的钱的最多*/
  24. select ename, sal from emp where sal = ( select max(sal) from emp);
  25. /*求部门人中那些人的薪水最高*/
  26. /*SELECT ename,DEPTNO,max(sal) from emp GROUP BY deptno;*/
  27. select ename, sal,emp.deptno
  28. from emp
  29. join ( select max(sal) max_sal, deptno from emp group by deptno) t
  30. on (emp.sal =t.max_sal and emp.deptno =t.deptno)
  31. SELECT * FROM salgrade
  32. /*部门平均薪水的等级*/
  33. SELECT t.deptno,s.grade FROM salgrade s,
  34. ( SELECT deptno, AVG(sal) avg from emp GROUP BY deptno) t
  35. WHERE t.avg between s.losal and s.hisal;
  36. select deptno,avg_sal,grade from
  37. ( select deptno, avg(sal) avg_sal from emp group by deptno) t
  38. join salgrade s on (t.avg_sal between s.losal and s.hisal);
  39. /*那些人是经理人*/
  40. select ename from emp where empno in ( select distinct mgr from emp);
  41. select distinct d.ename from emp e ,emp d where e.mgr=d.empno
  42. /*不用组函数求最高薪水*/
  43. select distinct sal
  44. from emp
  45. where sal not in
  46. ( select distinct e1.sal from emp e1 join emp e2 on (e1.sal < e2.sal))
  47. /*平均薪水最高的部门编号*/
  48. SELECT deptno , max( avg) FROM
  49. ( SELECT avg(sal) avg,deptno FROM emp GROUP BY deptno)t
  50. /*平均薪水最高的部门名称*/
  51. SELECT * from dept
  52. SELECT d.deptno,d.dname, max( avg) FROM
  53. ( SELECT avg(sal) avg,deptno FROM emp GROUP BY deptno) t,dept d
  54. WHERE t.deptno=d.deptno
  55. /*平均薪水的等级最低的部门名称*/
  56. SELECT t.dname,t.deptno, min(s.grade) grade,t.avg_sal FROM
  57. ( SELECT avg(sal) avg_sal,e.deptno,d.dname FROM emp e,dept d WHERE e.deptno= d.deptno GROUP BY e.deptno)t
  58. join salgrade s on(t.avg_sal between s.losal and s.hisal);
  59. select dname, t1.deptno,grade,avg_sal from
  60. (
  61. select deptno, grade, avg_sal from
  62. ( select deptno, avg(sal) avg_sal from emp group by deptno)t
  63. join salgrade s on(t.avg_sal between s.losal and s.hisal)
  64. )t1
  65. join dept on (t1.deptno=dept.deptno)
  66. where t1.grade=
  67. ( select min(grade) from(
  68. select deptno, grade, avg_sal from
  69. ( select deptno, avg(sal) avg_sal from emp group by deptno)t
  70. join salgrade s on(t.avg_sal between s.losal and s.hisal)
  71. )m
  72. );
  73. /*比普通员工的最高薪水还要高的经理人*/
  74. select ename
  75. from emp
  76. where empno in ( select distinct mgr from emp where mgr is not null) and sal >
  77. ( select max(sal) from emp where empno not in ( select distinct mgr from emp where mgr is not null))



发布评论
IT序号网

微信公众号号:IT虾米 (左侧二维码扫一扫)欢迎添加!

非常抱歉,全站内容审核中...知识解答
你是第一个吃螃蟹的人
发表评论

◎欢迎参与讨论,请在这里发表您的看法、交流您的观点。