- 查询所有雇员的姓名及其经理的姓名;
SELECT P1.ENAME AS EMP,P2.ENAME AS MGR,P2.JOB
FROM EMP P1,EMP P2
WHERE P1.MGR=P2.EMPNO AND P2.JOB=’MANDAGER’;
2)
SELECT P1.ENAME AS EMP,P2.ENAME AS MGR,P2.JOB
FROM EMP P1,EMP P2
WHERE P1.MGR=P2.EMPNO(+);
- 查询雇用日期早与其经理的所有雇员的编号、姓名和雇佣日期,以及其经理的编号、姓名和雇佣日期;
- 查询部门名称及其雇员姓名,若有些部门还没有雇员的话只要显示其部门名称即可;
SELECT DNAME, ENAME
FROM DEPT,EMP
WHERE DEPT.DEPTNO = EMP. DEPTNO OR DEPT.DEPTNO IS NOT NULL AND EMP. DEPTNO IS NULL;(不行)
SELECT DNAME, ENAME
FROM DEPT,EMP
WHERE DEPT.DEPTNO = EMP. DEPTNO(+);
- 查询薪金高于部门10中某个雇员薪金的雇员的姓名和薪金;
- 两种理解:
包含10中雇员
SELECT ENAME,SAL
FROM EMP
WHERE SAL>ANY(SELECT SAL
FROM EMP
WHERE DEPTNO=10);
不包含10中雇员
SELECT ENAME,SAL
FROM EMP
WHERE SAL>ANY(SELECT SAL
FROM EMP
WHERE DEPTNO=10)AND
DEPTNO<>10;
- 查询部门10和30的所有雇员的姓名、部门名称和薪金,并将查询结果按部门名称的升序排序;
ORDER BY理解要加强
- 查询薪金处于第四位的雇员的姓名、部门名称、工作和薪金;
SELECT *
FROM (SELECT ENAME,DNAME,JOB,SAL, ROW_NUMBER ()OVER (ORDER BY SAL DESC) AS GRADE
FROM EMP,DEPT
WHERE EMP.DEPTNO=DEPT.DEPTNO)
WHERE GRADE =4;
- 查询每个部门中工资排在前2名的员工信息。
SELECT *
FROM (SELECT *, ROW_NUMBER ()OVER (ORDER BY SAL DESC) AS GRADE
FROM EMP,DEPT
WHERE EMP.DEPTNO=DEPT.DEPTNO
GROUP BY DEPTNO)
WHERE GRADE <=2;
SELECT *
FROM (SELECT EMP.*, RANK ()OVER (PARTITION BY DEPTNO ORDER BY SAL DESC) AS GRADE
FROM EMP)
WHERE GRADE <=2;