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

SELECT          E.ENAME,

                        Е .JOB,

                        E.SAL,

                        S. GRADE,

                        D. DNAME

FROM             EMP Е,

                        SALGRADE S,

                        DEPT D

WHERE          E.DEPTNO = D.DEPTNO

AND                E.SAL BETWEEN S.LOSAL AND S.HISAL

AND                E.JOB != 'CLERK'

ORDER BY     E.SAL DESC;

SELECT          E.ENAME,

                        E.JOB,

                        E.SAL* 12 ANNUAL_SAL,

                        D.DEPTNO,

                        D. DNAME,

                        S. GRADE

FROM             EMP Е, SALGRADE S, DEPT D

WHERE          E.DEPTNO = D.DEPTNO

AND                E.SAL BETWEEN S.LOSAL AND S.HISAL

AND                (E.SAL*12 + NVL(E.COMM,0)= 36000

OR                              E.JOB = 'CLERK')

ORDER BY     E.JOB;

SELECT          E.ENAME EMPLOYEE,

                                    E.HIREDATE,

                                    M.ENAME MANAGER,

                                    M.HIREDATE

FROM             EMP E, EMP M

WHERE          E.MGR = M.EMPNO

AND        E.HIREDATE < M.HIREDATE;

SELECT          DEPTNO, DNAME

FROM             DEPT

MINUS

SELECT          D.DEPTNO, D. DNAME

FROM             DEPT D, EMP E

WHERE          D.DEPTNO = E .DEPTNO

SELECT          JOB, ENAME, SAL

FROM             EMP

WHERE          ( SAL, JOB) IN

                                                      (SELECT             MAX (SAL), JOB

                                                      FROM                 EMP

                                                      GROUP BY          JOB)

ORDER BY    SAL DESC;

SELECT          ENAME, JOB, SAL

FROM             EMP

WHERE          (SAL, JOB) IN

                                         (SELECT               MIN (SAL), JOB

                                          FROM                  EMP

                                          GROUP BY          JOB)

ORDER BY     SAL;

SELECT          DEPTNO, ENAME, HIREDATE

FROM             EMP

WHERE          (HIREDATE, DEPTNO) IN

                                    (SELECT        MAX (HIREDATE), DEPTNO

                                     FROM           EMP

                                     GROUP BY   DEPTNO)

ORDER BY     HIREDATE;

            SELECT         ENAME, SAL SALARY, DEPTNO

FROM             EMP E

WHERE          SAL < (SELECT         AVG (SAL)

                                               FROM             EMP

                                               WHERE          DEPTNO = E .DEPTNO)

ORDER BY     DEPTNO;