-- 1. 返回拥有员工的部门名、部门号。
SELECT d.dname, d.deptno FROM dept d, emp e WHERE d.DEPTNO=e.DEPTNO GROUP BY d.DEPTNO;
SELECT DISTINCT d.dname, d.deptno FROM dept d, emp e WHERE d.DEPTNO=e.DEPTNO;
-- 2. 工资水平多于smith的员工信息。
SELECT * FROM emp WHERE sal > (SELECT sal FROM emp WHERE ename='SMITH');
-- 3. 返回员工和所属经理的姓名。
SELECT e1.ENAME, e2.ENAME AS '所属经理' FROM emp e1, emp e2 WHERE e1.MGR=e2.EMPNO;
SELECT e1.ENAME, e2.ENAME AS '所属经理' FROM emp e1 LEFT OUTER JOIN emp e2 ON e1.MGR=e2.EMPNO;
-- 3.1 返回每个部门的部门经理姓名，及其部门员工
SELECT deptno,ename FROM emp WHERE job='MANAGER';
SELECT e.ENAME,m.ename FROM emp e, (SELECT deptno,ename FROM emp WHERE job='MANAGER') m WHERE e.DEPTNO=m.deptno AND e.ename != m.ename;
-- 4. 返回雇员的雇佣日期早于其经理雇佣日期的员工及其经理姓名
SELECT e1.ENAME,e2.ENAME FROM emp e1,emp e2 WHERE e1.MGR=e2.EMPNO AND e1.HIREDATE<e2.HIREDATE;
-- 5. 返回员工姓名及其所在的部门名称。
SELECT e.ENAME,d.DNAME FROM emp e, dept d WHERE e.DEPTNO = d.DEPTNO;
-- 6. 返回从事clerk工作的员工姓名和所在部门名称。
SELECT e.ENAME,d.DNAME FROM emp e, dept d WHERE e.DEPTNO = d.DEPTNO AND e.JOB='CLERK';
-- 7. 返回部门号及其本部门的最低工资。
SELECT deptno,MIN(sal) FROM emp GROUP BY deptno;
-- 8. 返回销售部(sales)所有员工的姓名。
-- 9. 返回工资水平多于平均工资的员工。
SELECT * FROM emp WHERE sal > (SELECT AVG(sal) FROM emp);
-- 10. 返回与SCOTT从事相同工作的员工。
SELECT job FROM emp WHERE ename='SCOTT';
SELECT * FROM emp WHERE job=(SELECT job FROM emp WHERE ename='SCOTT') AND ename != 'SCOTT';
-- 11. 返回与30部门员工工资水平相同的员工姓名与工资。
SELECT sal FROM emp WHERE deptno=30;
SELECT ename,sal FROM emp WHERE sal IN (SELECT sal FROM emp WHERE deptno=30);
-- 12. 返回工资高于30部门所有员工工资水平的员工信息。
SELECT MAX(sal) FROM emp WHERE deptno=30;
SELECT * FROM emp WHERE sal > (SELECT MAX(sal) FROM emp WHERE deptno=30);
SELECT * FROM emp WHERE sal > ALL(SELECT sal FROM emp WHERE deptno=30);
-- 13. 返回门号、部门名、部门所在位置及其每个部门的员工总数。
SELECT d.*, COUNT(*) FROM dept d, emp e WHERE d.DEPTNO=e.DEPTNO GROUP BY e.DEPTNO;
-- 14. 返回员工的姓名、所在部门名及其工资。
SELECT e.ename, d.dname, e.SAL FROM emp e, dept d WHERE e.DEPTNO=d.DEPTNO;
-- 15. 返回员工的详细信息。(包括部门名)
SELECT e.*, d.dname FROM emp e, dept d WHERE e.DEPTNO=d.DEPTNo;
-- 16. 返回员工工作及其从事此工作的最低工资。
SELECT job,MIN(sal) FROM emp GROUP BY job;
-- 17. 计算出员工的年薪，并且以年薪排序。
SELECT ename, sal*12 AS 'ySalary' FROM emp ORDER BY ySalary;
-- 18. 返回工资处于第四级别的员工的姓名。
SELECT e.ename,e.SAL FROM emp e, salgrade s WHERE s.GRADE=4 AND e.SAL BETWEEN s.LOSAL AND s.HISAL;
SELECT losal FROM salgrade WHERE grade=4;
SELECT hisal FROM salgrade WHERE grade=4;
SELECT ename FROM emp WHERE sal BETWEEN (SELECT losal FROM salgrade WHERE grade=4) AND (SELECT hisal FROM salgrade WHERE grade=4);
-- 19. 返回工资为二等级的职员名字、部门所在地、和二等级的最低工资和最高工资
SELECT e.ename, d.loc, s.losal, s.hisal FROM emp e, dept d, salgrade s
WHERE e.DEPTNO=d.DEPTNO AND s.GRADE=2
AND e.SAL>=s.LOSAL AND e.SAL<=s.HISAL;
-- 20. 工资等级大于smith的员工信息。
# A：smith的工资
SELECT sal FROM emp WHERE ename='SMITH';
# B：得到smith的工资等级
SELECT grade FROM salgrade WHERE losal<=(SELECT sal FROM emp WHERE ename='SMITH') AND hisal>(SELECT sal FROM emp WHERE ename='SMITH');
# C: smith工资等级的最高工资
SELECT hisal FROM salgrade WHERE grade=(SELECT grade FROM salgrade WHERE losal<=(SELECT sal FROM emp WHERE ename='SMITH') AND hisal>(SELECT sal FROM emp WHERE ename='SMITH'));
# C：得到员工信息，条件 工资 > smith工资等级的最高工资
SELECT * FROM emp WHERE sal > (SELECT hisal FROM salgrade WHERE grade=(SELECT grade FROM salgrade WHERE losal<=(SELECT sal FROM emp WHERE ename='SMITH') AND hisal>(SELECT sal FROM emp WHERE ename='SMITH')));
-- 20. 工资等级大于smith的员工信息。
SELECT e.* FROM emp e WHERE sal > (SELECT s.hisal FROM salgrade s, emp e WHERE e.ENAME='SMITH' AND e.SAL>=s.LOSAL AND e.SAL<=s.HISAL);
SELECT s.hisal FROM salgrade s, emp e
WHERE e.ENAME='SMITH'
AND e.SAL>=s.LOSAL AND e.SAL<=s.HISAL;
|
|
收藏
淘帖
踩
