Решения практических упражнений дисциплины "Теория экономических информационных систем", страница 5

            SELECT         DEPTNO, DNAME

FROM             DEPT D         

WHERE          NOT EXISTS

                                          (SELECT 'что-нибудь'

                                           FROM    EMP

                                           WHERE   DEPTNO=D. DEPTNO ) ;

DEFINE          REM = SAL*12+NVL(COMM,0)

SELECT          DEPTNO,SUM(&REM) COMPENSATION

FROM             EMP

GROUP BY     DEPTNO

HAVING          SUM(&REM) = (SELECT       MAX(SUM(&REM))

                                                              FROM          EMP

                                                              GROUP BY DEPTNO) ;

            SELECT         ENAME, SAL

FROM             EMP E

WHERE          3 > (SELECT COUNT (*)

                                           FROM     EMP

                                           WHERE E.SAL < SAL);

            SELECT         TO_CHAR(HIREDATE,'YYYY') YEAR,

                                    COUNT (EMPNO) NUMBER_OF_EMPS

            FROM             EMP

            GROUP BY   TO_CHAR(HIREDATE,'YYYY')

            HAVING          COUNT (EMPNO) =

                                                (SELECT        MAX (COUNT (EMPNO))

                                                 FROM           EMP

                                                 GROUP BY   TO_CHAR(HIREDATE,'YYYY'));

            SELECT         E.ENAME ENAME,

                                    E.SAL SALARY,

                                    E.DEPTNO DEPTNO,

                                    AVG (A. SAL) DEPT_AVG

FROM             EMP A, EMP E

WHERE          E.DEPTNO = A. DEPTNO

AND                E.SAL < (SELECT      AVG (SAL)

                                                   FROM         EMP

                                                   WHERE      DEPTNO = E.DEPTNO)

            GROUP BY   E.ENAME, E.SAL, E.DEPTNO

            ORDER BY     DEPTNO;

            SELECT         ENAME,

                                   HIREDATE,

                                   ' * ' MAXDATA

            FROM             EMP

            WHERE          HIREDATE = (SELECT          MAX (HIREDATE)

                                                           FROM             EMP)

            UNION

            SELECT         ENAME,

                                    HIREDATE,

                                    ‘  ‘

            FROM             EMP

            WHERE          HIREDATE <> (SELECT       MAX (HIREDATE)

                                                              FROM          EMP);

            SELECT         LPAD (EMPNO, LEVEL*4) EMPLOYEE_NUMBER,

                                   DEPTNO, EMPNO, ENAME, JOB, SAL

            FROM             EMP

CONNECT BY PRIOR EMPNO = MGR

START WITH MGR IS NULL;