Mysql_习题（公司员工）

DROP TABLE
IF
EXISTS EMP;
DROP TABLE
IF
EXISTS DEPT;
DROP TABLE
IF
EXISTS SALGRADE;
CREATE TABLE DEPT ( DEPTNO INT ( 2 ) NOT NULL, DNAME VARCHAR ( 14 ), LOC VARCHAR ( 13 ), PRIMARY KEY ( DEPTNO ) );
CREATE TABLE EMP (
EMPNO INT ( 4 ) NOT NULL,
ENAME VARCHAR ( 10 ),
JOB VARCHAR ( 9 ),
MGR INT ( 4 ),
HIREDATE DATE DEFAULT NULL,
SAL DOUBLE ( 7, 2 ),
COMM DOUBLE ( 7, 2 ),
PRIMARY KEY ( EMPNO ),
DEPTNO INT ( 2 )
);
CREATE TABLE SALGRADE ( GRADE INT, LOSAL INT, HISAL INT );
INSERT INTO DEPT ( DEPTNO, DNAME, LOC )
VALUES
( 10, ‘ACCOUNTING’, ‘NEW YORK’ );
INSERT INTO DEPT ( DEPTNO, DNAME, LOC )
VALUES
( 20, ‘RESEARCH’, ‘DALLAS’ );
INSERT INTO DEPT ( DEPTNO, DNAME, LOC )
VALUES
( 30, ‘SALES’, ‘CHICAGO’ );
INSERT INTO DEPT ( DEPTNO, DNAME, LOC )
VALUES
( 40, ‘OPERATIONS’, ‘BOSTON’ );
COMMIT;
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO )
VALUES
( 7369, ‘SMITH’, ‘CLERK’, 7902, ‘1980-12-17’, 800, NULL, 20 );
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO )
VALUES
( 7499, ‘ALLEN’, ‘SALESMAN’, 7698, ‘1981-02-20’, 1600, 300, 30 );
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO )
VALUES
( 7521, ‘WARD’, ‘SALESMAN’, 7698, ‘1981-02-22’, 1250, 500, 30 );
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO )
VALUES
( 7566, ‘JONES’, ‘MANAGER’, 7839, ‘1981-04-02’, 2975, NULL, 20 );
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO )
VALUES
( 7654, ‘MARTIN’, ‘SALESMAN’, 7698, ‘1981-09-28’, 1250, 1400, 30 );
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO )
VALUES
( 7698, ‘BLAKE’, ‘MANAGER’, 7839, ‘1981-05-01’, 2850, NULL, 30 );
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO )
VALUES
( 7782, ‘CLARK’, ‘MANAGER’, 7839, ‘1981-06-09’, 2450, NULL, 10 );
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO )
VALUES
( 7788, ‘SCOTT’, ‘ANALYST’, 7566, ‘1987-04-19’, 3000, NULL, 20 );
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO )
VALUES
( 7839, ‘KING’, ‘PRESIDENT’, NULL, ‘1981-11-17’, 5000, NULL, 10 );
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO )
VALUES
( 7844, ‘TURNER’, ‘SALESMAN’, 7698, ‘1981-09-08’, 1500, 0, 30 );
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO )
VALUES
( 7876, ‘ADAMS’, ‘CLERK’, 7788, ‘1987-05-23’, 1100, NULL, 20 );
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO )
VALUES
( 7900, ‘JAMES’, ‘CLERK’, 7698, ‘1981-12-03’, 950, NULL, 30 );
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO )
VALUES
( 7902, ‘FORD’, ‘ANALYST’, 7566, ‘1981-12-03’, 3000, NULL, 20 );
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO )
VALUES
( 7934, ‘MILLER’, ‘CLERK’, 7782, ‘1982-01-23’, 1300, NULL, 10 );
COMMIT;
INSERT INTO SALGRADE ( GRADE, LOSAL, HISAL )
VALUES
( 1, 700, 1200 );
INSERT INTO SALGRADE ( GRADE, LOSAL, HISAL )
VALUES
( 2, 1201, 1400 );
INSERT INTO SALGRADE ( GRADE, LOSAL, HISAL )
VALUES
( 3, 1401, 2000 );
INSERT INTO SALGRADE ( GRADE, LOSAL, HISAL )
VALUES
( 4, 2001, 3000 );
INSERT INTO SALGRADE ( GRADE, LOSAL, HISAL )
VALUES
( 5, 3001, 9999 );
COMMIT;
– 1. 取得每个部门最高薪水的人员名称
– 2. 哪些人的薪水在部门平均薪水之上
– 3. 取得部门中（所有人的）平均薪水等级
– 4. 不准用组函数（MAX），取得最高薪水（给出两种方案）
– 5. 取得平均薪水最高的部门的部门编号
– 6. 取得平均薪水最高的部门的名称
– 7. 求平均薪水的等级最低的部门的部门名称
– 8. 取得比普通员工（员工代码没有在mgr上出现的）的最高薪水还要高的经理人姓名
– 9. 取得薪水最高的前五名员工
– 10. 取得薪水最高的第六到第十的员工
– 11. 取得最后入职的5名员工
– 12. 取得每个薪水等级有多少员工
– 13.
/*有三个表s（学生表）、c（课程表）、sc（学生选课表）

S（SNO,SNAME）代表 （学号，姓名）
C（CNO,CNAME,CTEACHER）代表（课号，课名，教师）
SC（SNO,CNO,SCFRADE）代表（学号，课号，成绩）
问题

找出没选过“黎明”老师的所有学生姓名
列出2门以上（含两门）不及格学生姓名及平均成绩
即学过1号课程又学过2号课程所有学生的姓名 /
CREATE TABLE s ( sno INT ( 4 ) PRIMARY KEY AUTO_INCREMENT, sname VARCHAR ( 32 ) );
INSERT INTO s ( sname )
VALUES
( ‘zhangsan’ );
INSERT INTO s ( sname )
VALUES
( ‘lisi’ );
INSERT INTO s ( sname )
VALUES
( ‘wangwu’ );
INSERT INTO s ( sname )
VALUES
( ‘zhaoliu’ );
CREATE TABLE c ( cno INT ( 4 ) PRIMARY KEY AUTO_INCREMENT, cname VARCHAR ( 32 ), cteacher VARCHAR ( 32 ) );
INSERT INTO c ( cname, cteacher )
VALUES
( ‘Java’, ‘吴老师’ );
INSERT INTO c ( cname, cteacher )
VALUES
( ‘C++’, ‘王老师’ );
INSERT INTO c ( cname, cteacher )
VALUES
( ‘C##’, ‘张老师’ );
INSERT INTO c ( cname, cteacher )
VALUES
( ‘MySQL’, ‘郭老师’ );
INSERT INTO c ( cname, cteacher )
VALUES
( ‘Oracle’, ‘黎明’ );
CREATE TABLE sc (
sno INT ( 4 ),
cno INT ( 4 ),
scgrade DOUBLE ( 3, 1 ),
CONSTRAINT sc_sno_cno_pk PRIMARY KEY ( sno, cno ),
CONSTRAINT sc_sno_fk FOREIGN KEY ( sno ) REFERENCES s ( sno ),
CONSTRAINT sc_cno_fk FOREIGN KEY ( cno ) REFERENCES c ( cno )
);
INSERT INTO sc ( sno, cno, scgrade )
VALUES
( 1, 1, 30 );
INSERT INTO sc ( sno, cno, scgrade )
VALUES
( 1, 2, 50 );
INSERT INTO sc ( sno, cno, scgrade )
VALUES
( 1, 3, 80 );
INSERT INTO sc ( sno, cno, scgrade )
VALUES
( 1, 4, 90 );
INSERT INTO sc ( sno, cno, scgrade )
VALUES
( 1, 5, 70 );
INSERT INTO sc ( sno, cno, scgrade )
VALUES
( 2, 2, 80 );
INSERT INTO sc ( sno, cno, scgrade )
VALUES
( 2, 3, 50 );
INSERT INTO sc ( sno, cno, scgrade )
VALUES
( 2, 4, 70 );
INSERT INTO sc ( sno, cno, scgrade )
VALUES
( 2, 5, 80 );
INSERT INTO sc ( sno, cno, scgrade )
VALUES
( 3, 1, 60 );
INSERT INTO sc ( sno, cno, scgrade )
VALUES
( 3, 2, 70 );
INSERT INTO sc ( sno, cno, scgrade )
VALUES
( 3, 3, 80 );
INSERT INTO sc ( sno, cno, scgrade )
VALUES
( 1, 3, 50 );
INSERT INTO sc ( sno, cno, scgrade )
VALUES
( 1, 4, 80 );-- 13.1 找出没选过“黎明”老师的所有学生姓名
– 13.2 列出2门以上（含两门）不及格学生姓名及平均成绩
– 13.3 即学过1号课程又学过2号课程所有学生的姓名
– 14. 列出所有员工及领导的名字
– 15. 列出受雇日期早于其直接上级的所有员工编号、姓名、部门名称
– 16. 列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门
– 17. 列出至少有5个员工的所有部门
– 18. 列出薪水比’SMITH’多的所有员工信息
– 19. 列出所有’CLERK’（办事员）的姓名及其部门名称，部门人数
– 20. 列出最低薪水大于1500的各种工作及从事此工作的全部雇员人数 ,求出每种工作岗位的最低薪水
– 21. 列出在部门’SALES’<销售部>工作的员工姓名，假定不知道销售部门的部门编号
– 22. 列出薪金高于公司平均薪水的所有员工，所在部门，上级领导，雇员的工资等级
– 23. 列出与’SCOTT’从事相同工作的所有员工及部门名称
– 24. 列出薪金等于部门30中员工的薪金的其他员工的姓名和薪金
– 25. 列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金、部门名称
/ 26. 列出在每个部门工作的员工数量、平均工资和平均服务期限
to_days(日期类型) ->获取天数
获取数据库的系统当前时间的函数是：now()
*/-- 27. 列出所有员工的姓名、部门名称和工资
– 28. 列出所有部门的详细信息和人数
– 29. 列出各工作的最低工资及从事此工作的雇员姓名
– 30. 列出各个部门MANAGER的最低薪金
– 31. 列出所有员工的年工资，按年薪从低到高排序
– 32. 求出员工领导的薪水超过3000的员工名称和领导名称
– 33. 求部门名称中带’S’字符的部门员工的工资合计、部门人数
– 34. 给任职日期超过30年的员工加薪10%
CREATE TABLE emp_bak AS SELECT
*
FROM
emp;
SELECT
*
FROM
emp_bak;