set linesize 100;
set pagesize 25;
OEM_sqlplus_input_finished
--
select * from EMPLOYEE;

ID NAME JOB MGRID HDATE SALARY BONUS OUNIT
---------- --------------- --------------- ---------- -------- ---------- ---------- ----------
7369 SMITH CLERK 7902 99/12/17 1100 20
7499 ALLEN SALESMAN 7698 98/02/20 1600 300 30
7521 WARD SALESMAN 7698 00/02/22 1250 500 30
7566 JONES MANAGER 7839 00/04/02 2975 20
7654 MARTIN SALESMAN 7698 99/09/28 1250 1400 30
7698 BLAKE MANAGER 7839 99/05/01 2850 30
7782 CLARK MANAGER 7839 99/06/09 2450 10
7788 SCOTT ANALYST 7566 01/07/13 3000 20
7839 KING BOSS 99/11/17 5000 10
7844 TURNER SALESMAN 7698 99/09/08 1500 0 30
7876 ADAMS CLERK 7788 00/07/13 1100 20
7900 JAMES CLERK 7698 98/12/03 1250 30
7902 FORD ANALYST 7566 99/12/03 3000 20
7934 MILLER CLERK 7782 00/01/23 1300 10

OEM_sqlplus_input_finished
--
select * from OUNITS;

OUNITID OUNAME OULOCATION
---------- --------------- ---------------
10 ACCOUNTING LONDYN
20 ANALYSIS NEW CASTLE
30 SALES DOVER
40 PROCESSING OXFORD

OEM_sqlplus_input_finished
--

14 wierszy zostało wybranych.

OEM_sqlplus_input_finished
--
select * from OUNITS;

OUNITID OUNAME OULOCATION
---------- --------------- ---------------
10 ACCOUNTING LONDYN
20 ANALYSIS NEW CASTLE
30 SALES DOVER
40 PROCESSING OXFORD

OEM_sqlplus_input_finished
--
select NAME, HDATE, 12*SALARY+BONUS from EMPLOYEE;

NAME HDATE 12*SALARY+BONUS
--------------- -------- -------------------
SMITH 99/12/17
ALLEN 98/02/20 19500
WARD 00/02/22 15500
JONES 00/04/02
MARTIN 99/09/28 16400
BLAKE 99/05/01
CLARK 99/06/09
SCOTT 01/07/13
KING 99/11/17
TURNER 99/09/08 18000
ADAMS 00/07/13
JAMES 98/12/03
FORD 99/12/03
MILLER 00/01/23

14 wierszy zostało wybranych.

OEM_sqlplus_input_finished
--
select NAME, HDATE, 12*SALARY+nvl(BONUS,0) from EMPLOYEE;

NAME HDATE 12*SALARY+NVL(BONUS,0)
--------------- -------- --------------------------
SMITH 99/12/17 13200
ALLEN 98/02/20 19500
WARD 00/02/22 15500
JONES 00/04/02 35700
MARTIN 99/09/28 16400
BLAKE 99/05/01 34200
CLARK 99/06/09 29400
SCOTT 01/07/13 36000
KING 99/11/17 60000
TURNER 99/09/08 18000
ADAMS 00/07/13 13200
JAMES 98/12/03 15000
FORD 99/12/03 36000
MILLER 00/01/23 15600

14 wierszy zostało wybranych.

OEM_sqlplus_input_finished
--
select NAME, SALARY from EMPLOYEE;

NAME SALARY
--------------- ----------
SMITH 1100
ALLEN 1600
WARD 1250
JONES 2975
MARTIN 1250
BLAKE 2850
CLARK 2450
SCOTT 3000
KING 5000
TURNER 1500
ADAMS 1100
JAMES 1250
FORD 3000
MILLER 1300

14 wierszy zostało wybranych.

OEM_sqlplus_input_finished
--
select NAME, HDATE from EMPLOYEE where HDATE < '1999/01/01';

NAME HDATE
--------------- --------
ALLEN 98/02/20
JAMES 98/12/03

OEM_sqlplus_input_finished
--
select NAME, months_between(sysdate, HDATE) from EMPLOYEE where JOB = 'SALESMAN';

NAME MONTHS_BETWEEN(SYSDATE,HDATE)
--------------- ----------------------------
ALLEN 135,560617
WARD 111,496101
MARTIN 116,302552
TURNER 116,947714

OEM_sqlplus_input_finished
--
select NAME, months_between(round(sysdate, 'month'), round(HDATE, 'month')) from EMPLOYEE where JOB = 'SALESMAN';

NAME MONTHS_BETWEEN(ROUND(SYSDATE,'MONTH'),ROUND(HDATE,'MONTH'))
--------------- ----------------------------------------------------------
ALLEN 135
WARD 111
MARTIN 116
TURNER 117

OEM_sqlplus_input_finished
--

select NAME, SALARY from EMPLOYEE where SALARY > (select SALARY from EMPLOYEE where NAME = 'MARTIN');

NAME SALARY
--------------- ----------
ALLEN 1600
JONES 2975
BLAKE 2850
CLARK 2450
SCOTT 3000
KING 5000
TURNER 1500
FORD 3000
MILLER 1300

9 wierszy zostało wybranych.

OEM_sqlplus_input_finished
--

select count(*) from EMPLOYEE where odz = 10;
select count(*) from EMPLOYEE where odz = 10
*
BŁĄD w linii 1:
ORA-00904: nieprawidłowa OUNAME kolumny


OEM_sqlplus_input_finished
--

select count(*) from EMPLOYEE where OUNIT = '10';

COUNT(*)
----------
3

OEM_sqlplus_input_finished
--

select JOB, avg(SALARY) from EMPLOYEE where JOB <> 'ANALYST' group by JOB;

JOB AVG(SALARY)
--------------- ------------
SALESMAN 1400
MANAGER 2758,33333
BOSS 5000
CLERK 1187,5

OEM_sqlplus_input_finished
--
select JOB, round(avg(SALARY),2) from EMPLOYEE where JOB <> 'ANALYST' group by JOB;

JOB ROUND(AVG(SALARY),2)
--------------- ---------------------
SALESMAN 1400
MANAGER 2758,33
BOSS 5000
CLERK 1187,5

OEM_sqlplus_input_finished
--
select NAME, OUNIT from EMPLOYEE where OUNIT = (select OUNIT from EMPLOYEE where NAME='SCOTT');

NAME OUNIT
--------------- ----------
SMITH 20
JONES 20
SCOTT 20
ADAMS 20
FORD 20

OEM_sqlplus_input_finished
--

select OULOCATION from OUNITS where OUNITID = (select OUNIT from EMPLOYEE where NAME = 'JAMES');

OULOCATION
---------------
DOVER

OEM_sqlplus_input_finished
--

select NAME, SALARY from EMPLOYEE where OUNIT = (select OUNITID from OUNITS where OULOCATION = 'DOVER');

NAME SALARY
--------------- ----------
ALLEN 1600
WARD 1250
MARTIN 1250
BLAKE 2850
TURNER 1500
JAMES 1250

6 wierszy zostało wybranych.


OEM_sqlplus_input_finished

exit