????-->??Ч??

SELECT deptno?? AVG( sal )
FROM   emp
WHERE  deptno = 20
GROUP BY deptno;

scott@CNMMBO> SELECT deptno?? AVG( sal )
  2  FROM   emp
  3  WHERE  deptno = 20
  4  GROUP BY deptno;

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          2  consistent gets
          0  physical reads
          0  redo size
        583  bytes sent via SQL*Net to client
        492  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

????11??С??????????

????-->???????????SQL????У????????????????

????-->??Ч??

SELECT *
FROM   employees
WHERE  department_id = (SELECT department_id
                        FROM   departments
                        WHERE  department_name = 'Marketing')
       AND manager_id = (SELECT manager_id
                         FROM   departments
                         WHERE  department_name = 'Marketing');

????-->??Ч??

SELECT *
FROM   employees
WHERE  ( department_id?? manager_id ) = (SELECT department_id?? manager_id
                                        FROM   departments
                                        WHERE  department_name = 'Marketing')

????-->?????????е?????

????-->??Ч??

UPDATE employees
SET    job_id = ( SELECT MAX( job_id ) FROM jobs )?? salary = ( SELECT AVG( min_salary ) FROM jobs )
WHERE  department_id = 10;

????-->??Ч??

UPDATE employees
SET    ( job_id?? salary ) = ( SELECT MAX( job_id )?? AVG( min_salary ) FROM jobs )
WHERE  department_id = 10;

????12?????????

????-->??????????????????????????????????????????????Щ????????????????????

????13????EXISTS???IN

???????Щ?????????????У????????????????????????????????????????.??????????￡????EXISTS????NOT EXISTS??????????????Ч???

????-->??Ч??

SELECT *
FROM   emp
WHERE  sal > 1000
       AND deptno IN (SELECT deptno
                      FROM   dept
                      WHERE  loc = 'DALLAS')

????-->??Ч??

SELECT *
FROM   emp
WHERE  empno > 1000
       AND EXISTS
              (SELECT 1
               FROM   dept
               WHERE  deptno = emp.deptno AND loc = 'DALLAS')

????14????NOT EXISTS???NOT IN

???????????У?NOT IN???????????????????????????????????NOT IN??????Ч???????????????е???????????????????????????Σ?????????д?????????OUTTER JOIN????????NOT EXISTS

????-->??Ч??

SELECT *
FROM   emp
WHERE  deptno NOT IN (SELECT deptno
                       FROM   dept
                       WHERE  loc = 'DALLAS');

????-->??Ч??

SELECT e.*
FROM   emp e
WHERE  NOT EXISTS
          (SELECT 1
           FROM   dept
           WHERE  deptno = e.deptno AND loc = 'DALLAS');

????-->??Ч???????????????Ч??????????????????loc????????????????dept???????????loc?д?????????????????????Ч??

SELECT e.*
FROM   emp e LEFT JOIN dept d ON e.deptno = d.deptno
WHERE  d.loc <> 'DALLAS'