SELECT * 
FROM LOCATION L , 
    CATEGORY C, 
    EMP E 
WHERE E.EMP_NO BETWEEN 1000 AND 2000 
AND E.CAT_NO = C.CAT_NO 
AND E.LOCN = L.LOCN 

SELECT * 
FROM EMP E , 
LOCATION L , 
    CATEGORY C 
WHERE  E.CAT_NO = C.CAT_NO 
AND E.LOCN = L.LOCN 
AND E.EMP_NO BETWEEN 1000 AND 2000 

SELECT … 
FROM EMP E 
WHERE  SAL > 50000 
AND   JOB = ‘MANAGER' 
AND   25 < (SELECT COUNT(*) FROM EMP 
WHERE MGR=E.EMPNO); 
(高效,执行时间10.6秒) 
SELECT … 
FROM EMP E 
WHERE 25 < (SELECT COUNT(*) FROM EMP 
       WHERE MGR=E.EMPNO) 
AND   SAL > 50000 
AND   JOB = ‘MANAGER'; 

SELECT EMP_NAME , SALARY , GRADE 
   FROM EMP 
   WHERE EMP_NO = 342; 
   SELECT EMP_NAME , SALARY , GRADE 
   FROM EMP 
   WHERE EMP_NO = 291; 

SELECT A.EMP_NAME , A.SALARY , A.GRADE, 
       B.EMP_NAME , B.SALARY , B.GRADE 
   FROM EMP A,EMP B 
   WHERE A.EMP_NO = 342 
   AND  B.EMP_NO = 291; 

DELETE FROM EMP E 
WHERE E.ROWID > (SELECT MIN(X.ROWID) 
          FROM EMP X 
          WHERE X.EMP_NO = E.EMP_NO); 

SELECT TAB_NAME 
      FROM TABLES 
      WHERE TAB_NAME = ( SELECT TAB_NAME 
                 FROM TAB_COLUMNS 
                 WHERE VERSION = 604) 
      AND　DB_VER= ( SELECT DB_VER 
              FROM TAB_COLUMNS 
              WHERE VERSION = 604 

SELECT TAB_NAME 
      FROM TABLES 
      WHERE  (TAB_NAME,DB_VER) 
= ( SELECT TAB_NAME,DB_VER) 
          FROM TAB_COLUMNS 
          WHERE VERSION = 604) 

UPDATE EMP 
      SET EMP_CAT = (SELECT MAX(CATEGORY) FROM EMP_CATEGORIES), 
        SAL_RANGE = (SELECT MAX(SAL_RANGE) FROM EMP_CATEGORIES) 
      WHERE EMP_DEPT = 0020; 

UPDATE EMP 
      SET (EMP_CAT, SAL_RANGE) 
= (SELECT MAX(CATEGORY) , MAX(SAL_RANGE) 
FROM EMP_CATEGORIES) 
      WHERE EMP_DEPT = 0020; 

SELECT * 
FROM EMP (基础表) 
WHERE EMPNO > 0 
AND DEPTNO IN (SELECT DEPTNO 
FROM DEPT 
WHERE LOC = ‘MELB') 

SELECT * 
FROM EMP (基础表) 
WHERE EMPNO > 0 
AND EXISTS (SELECT ‘X' 
FROM DEPT 
WHERE DEPT.DEPTNO = EMP.DEPTNO 
AND LOC = ‘MELB') 

SELECT … 
FROM EMP 
WHERE DEPT_NO NOT IN (SELECT DEPT_NO 
             FROM DEPT 
             WHERE DEPT_CAT='A'); 

SELECT …. 
FROM EMP A,DEPT B 
WHERE A.DEPT_NO = B.DEPT(+) 
AND B.DEPT_NO IS NULL 
AND B.DEPT_CAT(+) = 'A' 

SELECT …. 
FROM EMP E 
WHERE NOT EXISTS (SELECT 'X' 
           FROM DEPT D 
           WHERE D.DEPT_NO = E.DEPT_NO 
           AND DEPT_CAT = 'A'); 

SELECT EXECUTIONS , DISK_READS, BUFFER_GETS, 
     ROUND((BUFFER_GETS-DISK_READS)/BUFFER_GETS,2) Hit_radio, 
     ROUND(DISK_READS/EXECUTIONS,2) Reads_per_run, 
     SQL_TEXT 
FROM  V$SQLAREA 
WHERE  EXECUTIONS>0 
AND   BUFFER_GETS > 0 
AND (BUFFER_GETS-DISK_READS)/BUFFER_GETS < 0.8 
ORDER BY 4 DESC;