sql练习转载(https://blog.csdn.net/fy_java1995/article/details/80305172)

#创建部门表
CREATE TABLE dept( 
deptno INT PRIMARY KEY, 
dname VARCHAR(50), 
loc VARCHAR(50) 
);

#创建雇员表 
CREATE TABLE emp( 
empno INT PRIMARY KEY, 
ename VARCHAR(50), 
job VARCHAR(50), 
mgr INT, 
hiredate DATE, 
sal DECIMAL(7,2), 
COMM DECIMAL(7,2), 
deptno INT, 
CONSTRAINT fk_emp FOREIGN KEY(mgr) REFERENCES emp(empno) 
);

#创建工资等级表
CREATE TABLE salgrade( 
grade INT PRIMARY KEY, 
losal INT, 
hisal INT 
);

INSERT INTO dept VALUES (10, '教研部', '北京'); 
INSERT INTO dept VALUES (20, '学工部', '上海'); 
INSERT INTO dept VALUES (30, '销售部', '广州'); 
INSERT INTO dept VALUES (40, '财务部', '武汉');

INSERT INTO salgrade VALUES (1, 7000, 12000); 
INSERT INTO salgrade VALUES (2, 12010, 14000); 
INSERT INTO salgrade VALUES (3, 14010, 20000); 
INSERT INTO salgrade VALUES (4, 20010, 30000); 
INSERT INTO salgrade VALUES (5, 30010, 99990);

INSERT INTO emp VALUES (1009, '曾阿牛', '董事长', NULL, '2001-11-17', 50000, NULL, 10); 
INSERT INTO emp VALUES (1004, '刘备', '经理', 1009, '2001-04-02', 29750, NULL, 20); 
INSERT INTO emp VALUES (1006, '关羽', '经理', 1009, '2001-05-01', 28500, NULL, 30); 
INSERT INTO emp VALUES (1007, '张飞', '经理', 1009, '2001-09-01', 24500, NULL, 10); 
INSERT INTO emp VALUES (1008, '诸葛亮', '分析师', 1004, '2007-04-19', 30000, NULL, 20); 
INSERT INTO emp VALUES (1013, '庞统', '分析师', 1004, '2001-12-03', 30000, NULL, 20); 
INSERT INTO emp VALUES (1002, '黛绮丝', '销售员', 1006, '2001-02-20', 16000, 3000, 30); 
INSERT INTO emp VALUES (1003, '殷天正', '销售员', 1006, '2001-02-22', 12500, 5000, 30); 
INSERT INTO emp VALUES (1005, '谢逊', '销售员', 1006, '2001-09-28', 12500, 14000, 30); 
INSERT INTO emp VALUES (1010, '韦一笑', '销售员', 1006, '2001-09-08', 15000, 0, 30); 
INSERT INTO emp VALUES (1012, '程普', '文员', 1006, '2001-12-03', 9500, NULL, 30); 
INSERT INTO emp VALUES (1014, '黄盖', '文员', 1007, '2002-01-23', 13000, NULL, 10); 
INSERT INTO emp VALUES (1011, '周泰', '文员', 1008, '2007-05-23', 11000, NULL, 20); 
INSERT INTO emp VALUES (1001, '甘宁', '文员', 1013, '2000-12-17', 8000, NULL, 20);
 

 

 

下面练习题中设计四个表。分别为：

dept表

emp表

salgrade表

tbyear表

1. 查出至少有一个员工的部门。显示部门编号、部门名称、部门位置、部门人数。

SELECT * FROM(SELECT d.deptno,d.dname,d.loc,(SELECT COUNT(e.ename) FROM emp e
WHERE e.deptno=d.deptno
GROUP BY e.deptno
) AS total FROM dept d) t WHERE t.total>1

或

SELECT d.deptno,d.dname,d.loc,e1.`count(*)`
FROM dept d INNER JOIN (SELECT deptno,COUNT(*) FROM emp GROUP BY deptno) e1
ON d.deptno=e1.deptno;

2. 列出薪金比关羽高的所有员工。

1. SELECT *

2. FROM emp e

3. WHERE e.sal>(SELECT sal FROM emp WHERE ename='关羽')

 

3. 列出所有员工的姓名及其直接上级的姓名。

SELECT e.ename,(SELECT e2.ename from emp e2 WHERE e2.empno=e.mgr) mgrname FROM emp e;

SELECT e1.ename,e2.ename 上级
FROM emp e1 LEFT OUTER JOIN emp e2
ON e1.mgr=e2.empno;
 

4. 列出受雇日期早于直接上级的所有员工的编号、姓名、部门名称。

-- SELECT e.hiredate,e.empno,e.ename,(SELECT d.dname from dept d WHERE e.deptno=d.deptno) deptname FROM emp e 
-- WHERE e.hiredate<(SELECT e2.hiredate from emp e2 WHERE e2.empno=e.mgr)

SELECT  e1.empno,e1.ename,d.dname
FROM emp e1 LEFT OUTER JOIN emp e2 ON e1.mgr=e2.empno  
LEFT OUTER JOIN dept d ON e1.deptno=d.deptno
WHERE e1.hiredate<e2.hiredate

 

5. 列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门。

SELECT d.dname,e.*

FROM dept d LEFT OUTER JOIN emp e

ON d.deptno=e.deptno;

1. 

 

6. 列出所有文员的姓名及其部门名称，部门的人数。

SELECT e.ename,d.dname,z.`count(*)`

FROM emp e INNER JOIN dept d ON e.deptno=d.deptno

INNER JOIN (SELECT deptno,COUNT(*) FROM emp GROUP BY deptno)z ON z.deptno=d.deptno

WHERE e.job='文员'

7. 列出最低薪金大于15000的各种工作及从事此工作的员工人数。

SELECT job,COUNT(*)

FROM emp

GROUP BY job

HAVING MIN(sal)>15000

8. 列出在销售部工作的员工的姓名，假定不知道销售部的部门编号。

SELECT e1.ename

FROM emp e1 INNER JOIN dept d

ON e1.deptno=d.deptno

WHERE d.dname='销售部'

9. 列出薪金高于公司平均薪金的所有员工信息，所在部门名称，上级领导，工资等级。

SELECT e1.*,e2.ename 上级,d.dname 部门名称,sal.`grade`

FROM emp e1 LEFT OUTER JOIN emp e2 ON e1.mgr=e2.empno

LEFT OUTER JOIN dept d ON e1.deptno=d.deptno

LEFT OUTER JOIN salgrade sal ON e1.sal BETWEEN losal AND hisal

WHERE e1.`sal`>(SELECT AVG(sal) FROM emp)

10.列出与庞统从事相同工作的所有员工及部门名称。

SELECT e.ename,d.dname

FROM emp e INNER JOIN dept d

ON e.deptno=d.deptno

WHERE e.job=(SELECT job FROM emp WHERE ename='庞统')

11.列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金、部门名称。

SELECT e.ename,e.sal,d.dname

FROM emp e LEFT OUTER JOIN dept d

ON e.deptno=d.deptno

WHERE e.sal>ALL(SELECT sal FROM emp WHERE deptno=30)

12.列出每个部门的员工数量、平均工资。

SELECT d.dname,e1.*

FROM (SELECT e.deptno,COUNT(*),AVG(sal) FROM emp e GROUP BY e.deptno)e1 INNER JOIN dept d

ON e1.deptno=d.deptno;

13.查出年份、利润、年度增长比

1. SELECT tb1.*,IFNULL(CONCAT((tb1.zz-tb2.zz)/tb2.zz*100,'%'),0) 年度增长比

2. FROM tbyear tb1 LEFT OUTER JOIN tbyear tb2

3. ON tb1.`year`=tb2.`year`+1;