MySQL版 emp 数据库

MySQL版 emp 数据库

DROP DATABASE IF EXISTS `emp`;
CREATE DATABASE `emp`;

USE emp;
 
 CREATE TABLE `dept`(
    `deptno` INT(2) NOT NULL, 
    `dname` VARCHAR(14),
    `loc` VARCHAR(13),
    CONSTRAINT pk_dept PRIMARY KEY(deptno)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 
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');

CREATE TABLE `emp` (
    `empno` int(4) NOT NULL PRIMARY KEY,
    `ename` VARCHAR(10),  
    `job` VARCHAR(9),  
    `mgr` int(4),  
    `hiredate` DATE,  
    `sal` float(7,2),  
    `comm` float(7,2),  
    `deptno` int(2),
    CONSTRAINT fk_deptno FOREIGN KEY(deptno) REFERENCES dept(deptno)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 
INSERT INTO EMP VALUES (7369,'SMITH','CLERK',7902,'1980-12-17',800,NULL,20); 
INSERT INTO EMP VALUES (7499,'ALLEN','SALESMAN',7698,'1981-02-20',1600,300,30);
INSERT INTO EMP VALUES (7521,'WARD','SALESMAN',7698,'1981-02-22',1250,500,30); 
INSERT INTO EMP VALUES (7566,'JONES','MANAGER',7839,'1981-04-02',2975,NULL,20); 
INSERT INTO EMP VALUES (7654,'MARTIN','SALESMAN',7698,'1981-09-28',1250,1400,30); 
INSERT INTO EMP VALUES (7698,'BLAKE','MANAGER',7839,'1981-05-01',2850,NULL,30); 
INSERT INTO EMP VALUES (7782,'CLARK','MANAGER',7839,'1981-06-09',2450,NULL,10); 
INSERT INTO EMP VALUES (7788,'SCOTT','ANALYST',7566,'1987-07-13',3000,NULL,20); 
INSERT INTO EMP VALUES (7839,'KING','PRESIDENT',NULL,'1981-11-07',5000,NULL,10); 
INSERT INTO EMP VALUES (7844,'TURNER','SALESMAN',7698,'1981-09-08',1500,0,30); 
INSERT INTO EMP VALUES (7876,'ADAMS','CLERK',7788,'1987-07-13',1100,NULL,20); 
INSERT INTO EMP VALUES (7900,'JAMES','CLERK',7698,'1981-12-03',950,NULL,30); 
INSERT INTO EMP VALUES (7902,'FORD','ANALYST',7566,'1981-12-03',3000,NULL,20); 
INSERT INTO EMP VALUES (7934,'MILLER','CLERK',7782,'1982-01-23',1300,NULL,10);

CREATE TABLE `salgrade` (  
    `grade` int, 
    `losal` int,  
    `hisal` int
) ENGINE=InnoDB DEFAULT CHARSET=utf8; 
 
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);

简单查询练习

--SQL练习训练一
1、 选择部门30中的雇员
2、 检索emp表中的员工姓名、月收入及部门编号
3、 检索emp表中员工姓名、及雇佣时间（雇佣时间按照yyyy-mm-dd显示）
4、 检索emp表中的部门编号及工种，并去掉重复行 
5、 检索emp表中的员工姓名及全年的月收入 
6、 用姓名显示员工姓名，用年收入显示全年月收入。 
7、 检索月收入大于2000的员工姓名及月收入 
8、 检索月收入在1000元到2000元的员工姓名、月收入及雇佣时间  
9、 检索以S开头的员工姓名及月收入
10、检索emp表中月收入是800的或是1250的员工姓名及部门编号 
11、显示在部门20中岗位是CLERK的所有雇员信息 
12、显示工资高于2500或岗位为MANAGER的所有雇员信息 
13、检索emp表中有奖金的员工姓名、月收入及奖金 
14、检索emp表中部门编号是30的员工姓名、月收入及提成，并要求其结果按月收入升序、然后按提成降序显示 
15、列出所有办事员的姓名、编号和部门
16、找出佣金高于薪金的雇员
17、找出部门10中所有经理和部门20中的所有办事员的详细资料
18、找出部门10中所有经理、部门20中所有办事员，既不是经理又不是办事员但其薪金>=2000的所有雇员的详细资料
19、找出收取奖金的雇员的不同工作
20、找出不收取奖金或收取的奖金低于100的雇员 
21、找出各月倒数第三天受雇的所有雇员
22、获取当前日期所在月的最后一天
23、找出早于25年之前受雇的雇员
24、显示正好为6个字符的雇员姓名
25、显示不带有'R'的雇员姓名
26、显示雇员的详细资料，按姓名排序
27、显示雇员姓名，根据其服务年限，将最老的雇员排在最前面 
28、显示所有雇员的姓名、工作和薪金，按工作的降序顺序排序，而工作相同时按薪金升序
29、显示所有雇员的姓名和加入公司的年份和月份，按雇员受雇日所在月排序，将最早年份的项目排在最前面 
30、显示在一个月为30天的情况下所有雇员的日薪金
31、找出在（任何年份的）2月受聘的所有雇员
32、对于每个雇员，显示其加入公司的天数
33、显示姓名字段的任何位置，包含 "A" 的所有雇员的姓名 
34、以年、月和日显示所有雇员的服务年限  
35、选择公司中有奖金 (COMM不为空,且不为0) 的员工姓名，工资和奖金比例,按工资逆排序,奖金比例逆排序.
36、选择公司中没有管理者的员工姓名及job 
37、选择在1987年雇用的员工的姓名和雇用时间 
38、选择在20或10号部门工作的员工姓名和部门号 
39、选择雇用时间在1981-02-01到1981-05-01之间的员工姓名，职位(job)和雇用时间,按从早到晚排序. 
40、选择工资不在5000到12000的员工的姓名和工资 
41、查询员工号为7934的员工的姓名和部门号  
42、查询工资大于1200的员工姓名和工资

复杂查询练习

1.  列出与“SCOTT”从事相同工作的所有员工及部门名称，部门人数。
2.  列出公司各个工资等级雇员的数量、平均工资。
3.  列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金、部门名称。
4.  列出在每个部门工作的员工数量、平均工资和平均服务期限。
5.  列出所有员工的姓名、部门名称和工资。
6.  列出所有部门的详细信息和部门人数。
7.  列出各种工作的最低工资及从事此工作的雇员姓名。
8.  列出各个部门的MANAGER(经理)的最低薪金、姓名、部门名称、部门人数。
9.  列出所有员工的年工资，所在部门名称，按年薪从低到高排序。
10. 查出某个员工的上级主管及所在部门名称，并要求出这些主管中的薪水超过3000
11. 求出部门名称中，带‘S’字符的部门员工的、工资合计、部门人数。
12. 给任职日期超过30年或者在87年雇佣的雇员加薪，加薪原则：10部门增长10%，20部门增长20%，30部门增长30%，依次类推。
13. 列出至少有一个员工的所有部门的信息：
14. 列出薪金比SMITH对的所有员工：
15. 列出所有员工的姓名以及其直接上级的姓名：
16. 列出受雇日期早于其直接上级的所有员工的编号、姓名，部门名称
17. 列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门
18. 列出所有"CLERK(职员)"的姓名以及部门名称，部门的人数
19. 列出最低薪金大于1500的各种工作以及从事此工作的全部雇员人数
20. 列出在部门"SALES"工作的员工的姓名，假定不知道销售部的部门编号
21. 列出薪金高于公司平均薪金的所有员工，所在部门，上级领导，公司的工资等级
22. 列出至少有一个员工的所有部门编号、名称，并统计出这些部门的平均工资、最低工资、最高工资。 
23. 列出薪金比“SMITH”或“ALLEN”多的所有员工的编号、姓名、部门名称、其领导姓名。 
24. 列出所有员工的编号、姓名及其直接上级的编号、姓名，显示的结果按领导年工资的降序排列。 
25. 列出受雇日期早于其直接上级的所有员工的编号、姓名、部门名称、部门位置、部门人数。 
26. 列出部门名称和这些部门的员工信息（数量、平均工资），同时列出那些没有员工的部门。 
27. 列出所有“CLERK”（办事员）的姓名及其部门名称，部门的人数，工资等级。 
28. 列出最低薪金大于1500的各种工作及此从事此工作的全部雇员人数及所在部门名称、位置、平均工资。 
29. 列出在部门“SALES”（销售部）工作的员工的姓名、基本工资、雇佣日期、部门名称，假定不知道销售部的部门编号。 
30. 列出薪金高于公司平均薪金的所有员工，所在部门，上级领导，公司的工资等级。 	
31. 列出与“SCOTT”从事相同工作的所有员工及部门名称，部门人数。
32. 查询dept表的结构
33. 检索emp表，用is a 这个字符串来连接员工姓名和工种两个字段
34. 检索emp表中有提成的员工姓名、月收入及提成。