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

       COLUMN AVERAGE FORMAT 9999

     SELECT                EMP.EMPNO,

                                   ENAME,

                                   JOB,

                                   SAL,

                                   HIREDATE,

                                   MINIMUM,

                                   MAXIMUM,

                                    AVERAGE

     FROM                    EMP .AGGREGATES AGG

     WHERE                 EMP.DEPTNO = AGG.DEPTNO

     AND                       EMP.EMPNO = &EMPNO;

     SELECT                *

     FROM                    USER_VIEWS

     WHERE                 VIEW_NAME = ' AGGREGATES ';

     CREATE    VIEW MY_DEPT
     AS

     SELECT                EMPNO,
                                   ENAME NAME,
                                   JOB,
                                   HIREDATE,
                                   SAL SALARY,
                                   COMM COMMISSION
     FROM                    EMP
     WHERE                 DEPTNO = 30
     AND                       JOB != 'MANAGERS’;

     SELECT                *
     FROM                    MY_DEPT;

    CREATE VIEW                  JOBS                                     

     AS

    SELECT                             JOB,     

                                               COUNT ( * ) COUNT_JOBS

    FROM                                 EMP                             

    GROUP                              BY JOB;

    COL            ENAME           HEADING 'Name'

    COL            JOB                 HEADING 'Jоb'

    COL            COUNT           HEADING 'Total Employees I With This Job'

    SELECT                             E .ENAME ENAMЕ,

                                               E.JOB JOB,

                                               J .COUNT_JOBS COUNT

    FROM                                 EMP E, JOBS J

    WHERE                              E.JOB = J .JOB;   

CREATE VIEW JOB_STATS
  AS

 SELECT         D.DNAME DEPARTMENT,
                        E .JOB, 
                        COUNT (E.EMPNO) NO_OF_EMPS,
                        AVG (E. SAL) AVG_SAL
  FROM           DEPT D, EMP E
  WHERE        D.DEPTNO = E.DEPTNO(+)
  GROUP        BY D.DNAME, E .JOB;

INSERT          INTO COMPANY_CARS

     VALUES

      ('&CHASSIS_NUMBER',

        ‘&MAKE ',    

        '&MODEL',

         &CUBIC_CAPACITY,

        '&COLOUR',

        '&DATE_OF_REGISTRATION',

        '&REG_NO',

         &EMPNO,

         &DEPTNO,

       ' &COMMENTS ' ) ;

    SAVE INSERT_SQL

      INSERT INTO COMPANY_CARS

      VALUES

      (cc_sequence. nextval,

       ' &MAKE ',

        '&MODEL',

         &CUBIC_CAPACITY,

        '&COLOUR',

        '&DATE_OF_REGISTRATION',

       ' &REG_NO ',

       ' &EMPNO,

         &DEPTNO,

       ' &COMMENTS ');