# Решения практических упражнений дисциплины "Теория экономических информационных систем", страница 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) ;

## Результат

Server: Msg 170, Level 15, State 1, Line 110

Line 110: Incorrect syntax near 'DEFINE'.

Server: Msg 170, Level 15, State 1, Line 112

Line 112: Incorrect syntax near '&'.

Server: Msg 170, Level 15, State 1, Line 115

Line 115: Incorrect syntax near '&'.

Дополнительные упражнения

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'));

## Результат

Server: Msg 195, Level 15, State 10, Line 110

'TO_CHAR' is not a recognized function name.

Server: Msg 130, Level 15, State 1, Line 115

Cannot perform an aggregate function on an expression containing an aggregate or a subquery.

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);

## Результат

Без ошибок

Решения упражнений: урок 9

DEPTNO, EMPNO, ENAME, JOB, SAL

FROM             EMP

CONNECT BY PRIOR EMPNO = MGR

## Результат

Server: Msg 195, Level 15, State 10, Line 110

'LPAD' is not a recognized function name.

Решения упражнений: урок 10

set echo off

set feed off

set pages 40

set lines 62

set term off

column today new value today1

select to_char(sysdate, 'dd/mm/yy') today from sys.dual

/

ttitle     left 'Номер страницы: ' sql.pno –

right 'Дата выдачи: ' today1 skip2 –