SQL学习练习题

首先创建相关表，录入信息

/创建部门表/
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
);

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

/插入emp表数据/
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);

/插入salgrade表数据/
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);

SELECT * FROM emp;
SELECT * FROM dept;
SELECT * FROM salgrade;

问题列表

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

SELECT d.* ,z1.cnt
FROM dept d,
			(SELECT count(*) cnt,deptno
			FROM emp
			GROUP BY deptno) z1
WHERE d.deptno = z1.deptno;

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

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

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

SELECT e1.ename '姓名', e2.ename '上级'
FROM emp e1 , emp e2
WHERE e1.mgr = e2.empno

如果这么写会丢失上级为NULL的数据,所以应该换成left join on

SELECT e1.ename '姓名', e2.ename '上级'
FROM emp e1 LEFT JOIN emp e2
ON e1.mgr = e2.empno

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

SELECT e1.empno,e1.ename,d.dname
FROM emp e1 , emp e2 , dept d
WHERE e1.mgr = e2.empno AND e1.hiredate < e2.hiredate AND e1.deptno = d.deptno;

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

SELECT d.dname,d.deptno,e.*
FROM dept d LEFT JOIN emp e
ON d.deptno = e.deptno 
ORDER BY d.deptno

列出所有文员的姓名及其部门名称，部门的人数。注意点：列名必须标注来源

SELECT e.ename,e.job,z2.dname,z2.人数
FROM
(SELECT z1.deptno,z1.dname,count(z1.dname) '人数' 
FROM (SELECT e.ename,d.dname,e.deptno
			FROM emp e LEFT JOIN dept d
			ON e.deptno = d.deptno) z1
GROUP BY dname,deptno) z2 LEFT JOIN emp e
ON e.deptno = z2.deptno
WHERE e.job = '文员'

简洁写法，建立中间表，利用depono分组

SELECT e.ename, d.dname, z.cnt
FROM emp e, (SELECT deptno, COUNT(*) cnt FROM emp GROUP BY deptno) z, dept d
WHERE e.deptno=d.deptno AND z.deptno=d.deptno AND e.job = '文员'

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

SELECT job,count(job) num FROM emp GROUP BY job HAVING min(sal)>15000

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

SELECT e.ename,d.num
FROM emp e,(SELECT deptno num FROM dept WHERE dname = '销售部') d
WHERE e.deptno = d.num

SELECT *
FROM emp
WHERE deptno = (SELECT deptno num FROM dept WHERE dname = '销售部')

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

SELECT e.*,d.dname,e2.ename,g.grade
FROM emp e , dept d ,emp e2 ,  salgrade g
WHERE e.sal > (SELECT avg(sal) FROM emp) AND e.deptno = d.deptno AND e.mgr = e2.empno AND e.sal BETWEEN g.losal AND g.hisal

不使用左右链接的情况下 NULL会缺失

SELECT e.* , d.dname , e2.ename , g.grade 
FROM	
		emp e LEFT JOIN dept d ON e.deptno = d.deptno
		LEFT JOIN emp e2 ON e.mgr = e2.empno
		LEFT JOIN salgrade g ON e.sal BETWEEN g.losal AND g.hisal
WHERE e.sal > (SELECT avg(sal) FROM emp)

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

SELECT e.ename,d.dname
FROM emp e LEFT JOIN dept d
ON e.deptno = d.deptno

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

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

SELECT e.ename , e.sal , d.dname
FROM emp e LEFT JOIN dept d
ON	e.deptno = d.deptno
WHERE e.sal > (SELECT avg(sal) FROM emp WHERE deptno = 30)

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

SELECT deptno , count(deptno) , avg(sal)
FROM emp 
GROUP BY deptno