ora11am_sep23

SQL> Select Job From Emp GROUP BY Job;

JOB
———
CLERK
SALESMAN
PRESIDENT
MANAGER
ANALYST

SQL> ed
Wrote file afiedt.buf

1 Select deptno
2 From Emp
3* GROUP BY deptno
4 /

DEPTNO
———-
30
20
10

SQL> clear screen
SQL> /

DEPTNO
———-
30
20
10

SQL> clear screen
SQL> ed
Wrote file afiedt.buf

1 Select Mgr
2 From Emp
3* GROUP BY Mgr
SQL> /

MGR
———-
7839

7782
7698
7902
7566
7788

7 rows selected.

SQL> ed
Wrote file afiedt.buf

1 Select deptno
2* From Emp
3 /

DEPTNO
———-
20
30
30
20
30
30
10
20
10
30
20
30
20
10

14 rows selected.

SQL> clear screen
SQL> ed
Wrote file afiedt.buf

1 Select unique deptno
2* From Emp
3 /

DEPTNO
———-
30
20
10

SQL> ed
Wrote file afiedt.buf

1 Select unique deptno
2* From Emp
3 /

DEPTNO
———-
30
20
10

SQL> ed
Wrote file afiedt.buf

1 Select unique deptno,count(*) noe
2* From Emp
3 /
Select unique deptno,count(*) noe
*
ERROR at line 1:
ORA-00937: not a single-group group function
SQL>
SQL> ed
Wrote file afiedt.buf

1 Select unique deptno,count(*) noe
2* From Emp
3
SQL> clear screen
SQL> ed
Wrote file afiedt.buf

1 Select deptno,count(*) noe
2 From Emp
3* group by deptno
4 /

DEPTNO NOE
———- ———-
30 6
20 5
10 3

SQL> ed
Wrote file afiedt.buf

1 Select deptno,count(*) noe
2 From Emp
3* group by deptno
4
SQL>
SQL> clear screen
SQL> ed
Wrote file afiedt.buf

1 select JOB,min(sal) LOSAL,max(sal) HISAL,count(*) NOE,sum(sal) TOLSAL
2 from emp
3* group by job
4 /

JOB LOSAL HISAL NOE TOLSAL
——— ———- ———- ———- ———-
CLERK 800 1300 4 4150
SALESMAN 2250 2600 4 9600
PRESIDENT 5000 5000 1 5000
MANAGER 2450 2975 3 8275
ANALYST 3000 3000 2 6000

SQL> ed
Wrote file afiedt.buf

1 select JOB,min(sal) LOSAL,max(sal) HISAL,count(*) NOE,sum(sal) TOLSAL
2 from emp
3 group by job
4* odrer by job
SQL> /
odrer by job
*
ERROR at line 4:
ORA-00933: SQL command not properly ended
SQL> ed
Wrote file afiedt.buf

1 select JOB,min(sal) LOSAL,max(sal) HISAL,count(*) NOE,sum(sal) TOLSAL
2 from emp
3 group by job
4* order by job
SQL> /

JOB LOSAL HISAL NOE TOLSAL
——— ———- ———- ———- ———-
ANALYST 3000 3000 2 6000
CLERK 800 1300 4 4150
MANAGER 2450 2975 3 8275
PRESIDENT 5000 5000 1 5000
SALESMAN 2250 2600 4 9600

SQL> clear screen
SQL> /

JOB LOSAL HISAL NOE TOLSAL
——— ———- ———- ———- ———-
ANALYST 3000 3000 2 6000
CLERK 800 1300 4 4150
MANAGER 2450 2975 3 8275
PRESIDENT 5000 5000 1 5000
SALESMAN 2250 2600 4 9600

SQL> ed
Wrote file afiedt.buf

1 select JOB,min(sal) LOSAL,max(sal) HISAL,count(*) NOE,sum(sal) TOLSAL
2 from emp
3 group by job
4* order by job
SQL>
SQL>
SQL> clear screen
SQL> ed
Wrote file afiedt.buf

1 select to_char(hiredate,’dd’)
2* from emp
SQL> /

TO

17
20
22
02
28
01
09
09
17
08
12
03
03
23

14 rows selected.

SQL> ed
Wrote file afiedt.buf

1 select to_char(hiredate,’DD’)
2* from emp
SQL> /

TO

17
20
22
02
28
01
09
09
17
08
12
03
03
23

14 rows selected.

SQL> ed
Wrote file afiedt.buf

1 select to_char(hiredate,’DDsp’)
2* from emp
SQL> /

TO_CHAR(HIRE
————
SEVENTEEN
TWENTY
TWENTY-TWO
TWO
TWENTY-EIGHT
ONE
NINE
NINE
SEVENTEEN
EIGHT
TWELVE
THREE
THREE
TWENTY-THREE

14 rows selected.

SQL> ed
Wrote file afiedt.buf

1 select to_char(hiredate,’ddsp’)
2* from emp
SQL> /

TO_CHAR(HIRE
————
seventeen
twenty
twenty-two
two
twenty-eight
one
nine
nine
seventeen
eight
twelve
three
three
twenty-three

14 rows selected.

SQL> ed
Wrote file afiedt.buf

1 select to_char(hiredate,’dd’)
2 from emp
3* group by to_char(hiredate,’dd’)
4 /

TO

09
08
17
28
01
12
20
22
02
03
23

11 rows selected.

SQL> clear screen
SQL> /

TO

09
08
17
28
01
12
20
22
02
03
23

11 rows selected.

SQL> ed
Wrote file afiedt.buf

1 select to_char(hiredate,’DD’)
2 from emp
3* group by to_char(hiredate,’dd’)
SQL> /
select to_char(hiredate,’DD’)
*
ERROR at line 1:
ORA-00979: not a GROUP BY expression
SQL> eded
SP2-0042: unknown command “eded” – rest of line ignored.
SQL>
SQL>
SQL> ed
Wrote file afiedt.buf

1 select to_char(hiredate,’YYYY’)
2 from emp
3* group by to_char(hiredate,’YYYY’)
SQL> /

TO_C
—-
1980
1983
1982
1981

SQL> clear screen
SQL> /

TO_C
—-
1980
1983
1982
1981

SQL> ed
Wrote file afiedt.buf

1 select to_char(hiredate,’yyyy’)
2 from emp
3* group by to_char(hiredate,’YYYY’)
SQL> /
select to_char(hiredate,’yyyy’)
*
ERROR at line 1:
ORA-00979: not a GROUP BY expression
SQL>
SQL> ed
Wrote file afiedt.buf

1 select to_char(hiredate,’YEAR’)
2 from emp
3* group by to_char(hiredate,’YYYY’)
4 /
select to_char(hiredate,’YEAR’)
*
ERROR at line 1:
ORA-00979: not a GROUP BY expression
SQL> ed
Wrote file afiedt.buf

1 select to_char(hiredate,’yyyy’)
2 from emp
3* group by to_char(hiredate,’YYYY’)
4 /
select to_char(hiredate,’yyyy’)
*
ERROR at line 1:
ORA-00979: not a GROUP BY expression
SQL> ed
Wrote file afiedt.buf

1 select to_char(hiredate,’Month’)
2 from emp
3 where to_char(hiredate.’MON’)<>’DEC’
4* group by to_char(hiredate,’Month’)
5 /
where to_char(hiredate.’MON’)<>’DEC’
*
ERROR at line 3:
ORA-01747: invalid user.table.column, table.column, or column specification
SQL> ed
Wrote file afiedt.buf

1 select to_char(hiredate,’Month’)
2 from emp
3 where to_char(hiredate,’MON’)<>’DEC’
4* group by to_char(hiredate,’Month’)
SQL> /

TO_CHAR(H
———
September
January
June
February
April
November
May

7 rows selected.

SQL> ed
Wrote file afiedt.buf

1 select to_char(hiredate,’Month’)
2 from emp
3 where to_char(hiredate,’MON’)<>’DEC’
4* group by to_char(hiredate,’Month’)
SQL>
SQL>
SQL> clear screen
SQL> ed
Wrote file afiedt.buf

1 Select Ename,Max(Sal)
2 From Emp
3* Group By Deptno
SQL> /
Select Ename,Max(Sal)
*
ERROR at line 1:
ORA-00979: not a GROUP BY expression
SQL> ed
Wrote file afiedt.buf

1 Select deptno,Max(Sal)
2 From Emp
3* Group By Deptno,job
SQL> /

DEPTNO MAX(SAL)
———- ———-
20 1100
30 2600
20 2975
30 950
10 5000
30 2850
10 1300
10 2450
20 3000

9 rows selected.

SQL> clear screen
SQL> ed
Wrote file afiedt.buf

1 Select deptno,count(*) noe
2 From Emp
3* where count(*)>3
4 /
where count(*)>3
*
ERROR at line 3:
ORA-00934: group function is not allowed here
SQL> ed
Wrote file afiedt.buf

1 Select deptno,count(*) noe
2 From Emp
3 group by deptno
4* having count(*)>3
5 /

DEPTNO NOE
———- ———-
30 6
20 5

SQL> ed
Wrote file afiedt.buf

1 Select empno,deptno
2 From Emp
3 group by empno,deptno
4* having deptno=10
5 /

EMPNO DEPTNO
———- ———-
7782 10
7839 10
7934 10

SQL> clear screen
SQL> /

EMPNO DEPTNO
———- ———-
7782 10
7839 10
7934 10

SQL> ed
Wrote file afiedt.buf

1 Select *
2 From Emp
3 group by *
4* having deptno=10
5 /
group by *
*
ERROR at line 3:
ORA-00936: missing expression
SQL> ed
Wrote file afiedt.buf

1 Select *
2 From Emp
3* where deptno=10
SQL> /

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
———- ———- ——— ———- ——— ———- ———- ———-
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7839 KING PRESIDENT 17-NOV-81 5000 10
7934 MILLER CLERK 7782 23-JAN-82 1300 10

SQL> clear screen
SQL>

ora11am_sep21

select avg(sal)
2 from emp;

AVG(SAL)
———-
2073.21429

SQL> ed
Wrote file afiedt.buf

1* select avg(sal) from emp
2 /

AVG(SAL)
———-
2073.21429

SQL> clear screen
SQL> ed
Wrote file afiedt.buf

1 select avg(unique sal)
2* from emp
3 /

AVG(UNIQUESAL)
————–
2064.58333

SQL> clear screen
SQL> ed
Wrote file afiedt.buf

1 Select Avg(Comm),Avg(DISTINCT Comm)
2* From Emp
3 /

AVG(COMM) AVG(DISTINCTCOMM)
———- —————–
550 550

SQL> clear screen
SQL> ed
Wrote file afiedt.buf

1 select sum(sal),sum(unique sal)
2* from emp
SQL> /

SUM(SAL) SUM(UNIQUESAL)
———- ————–
29025 24775

SQL> clear screen
SQL> ed
Wrote file afiedt.buf

1 select max(sal),max(ename),min(sal),min(ename)
2* from emp
3 /

MAX(SAL) MAX(ENAME) MIN(SAL) MIN(ENAME)
———- ———- ———- ———-
5000 WARD 800 ADAMS

SQL> ed
Wrote file afiedt.buf

1 Select STDDEV(Sal),VARIANCE(Sal)
2* from emp
SQL> /

STDDEV(SAL) VARIANCE(SAL)
———– ————-
1182.50322 1398313.87

SQL> clear screen
SQL> ed
Wrote file afiedt.buf

1 select count(*)
2* from emp
SQL> /

COUNT(*)
———-
14

SQL> select *from emp;

EMPNO ENAME JOB MGR HIREDATE SAL COMM
———- ———- ——— ———- ——— ———- ———-
DEPTNO
———-
7369 SMITH CLERK 7902 17-DEC-80 800
20

7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300
30

7521 WARD SALESMAN 7698 22-FEB-81 1250 500
30

EMPNO ENAME JOB MGR HIREDATE SAL COMM
———- ———- ——— ———- ——— ———- ———-
DEPTNO
———-
7566 JONES MANAGER 7839 02-APR-81 2975
20

7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400
30

7698 BLAKE MANAGER 7839 01-MAY-81 2850
30

EMPNO ENAME JOB MGR HIREDATE SAL COMM
———- ———- ——— ———- ——— ———- ———-
DEPTNO
———-
7782 CLARK MANAGER 7839 09-JUN-81 2450
10

7788 SCOTT ANALYST 7566 09-DEC-82 3000
20

7839 KING PRESIDENT 17-NOV-81 5000
10

EMPNO ENAME JOB MGR HIREDATE SAL COMM
———- ———- ——— ———- ——— ———- ———-
DEPTNO
———-
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0
30

7876 ADAMS CLERK 7788 12-JAN-83 1100
20

7900 JAMES CLERK 7698 03-DEC-81 950
30

EMPNO ENAME JOB MGR HIREDATE SAL COMM
———- ———- ——— ———- ——— ———- ———-
DEPTNO
———-
7902 FORD ANALYST 7566 03-DEC-81 3000
20

7934 MILLER CLERK 7782 23-JAN-82 1300
10

14 rows selected.

SQL> set line 100
SQL> /

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
———- ———- ——— ———- ——— ———- ———- ———-
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 09-DEC-82 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 12-JAN-83 1100 20

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
———- ———- ——— ———- ——— ———- ———- ———-
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10

14 rows selected.

SQL> set pagesize 100
SQL> /

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
———- ———- ——— ———- ——— ———- ———- ———-
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 09-DEC-82 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 12-JAN-83 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10

14 rows selected.

SQL> desc emp
Name Null? Type
—————————————————– ——– ————————————
EMPNO NUMBER(4)
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2)

SQL> insert into emp(empno) values(null);
insert into emp(empno) values(null)
*
ERROR at line 1:
ORA-08002: sequence S1.CURRVAL is not yet defined in this session
SQL> ed
Wrote file afiedt.buf

1* insert into emp(empno) values(null)
SQL>
SQL>
SQL> drop sequence S1;

Sequence dropped.

SQL> insert into emp(empno) values(null)
2 /
insert into emp(empno) values(null)
*
ERROR at line 1:
ORA-02289: sequence does not exist
SQL> drop table emp purge;
drop table emp purge
*
ERROR at line 1:
ORA-02449: unique/primary keys in table referenced by foreign keys
SQL> ed
Wrote file afiedt.buf

1* drop table emp cascade constraint purge
SQL> /

Table dropped.

SQL> CREATE TABLE EMP
2 (EMPNO NUMBER(4) NOT NULL,
3 ENAME VARCHAR2(10),
4 JOB VARCHAR2(9),
5 MGR NUMBER(4),
6 HIREDATE DATE,
7 SAL NUMBER(7, 2),
8 COMM NUMBER(7, 2),
9 DEPTNO NUMBER(2));

Table created.

SQL>
SQL> INSERT INTO EMP VALUES
2 (7369, ‘SMITH’, ‘CLERK’, 7902,
3 TO_DATE(’17-DEC-1980′, ‘DD-MON-YYYY’), 800, NULL, 20);

1 row created.

SQL> INSERT INTO EMP VALUES
2 (7499, ‘ALLEN’, ‘SALESMAN’, 7698,
3 TO_DATE(’20-FEB-1981′, ‘DD-MON-YYYY’), 1600, 300, 30);

1 row created.

SQL> INSERT INTO EMP VALUES
2 (7521, ‘WARD’, ‘SALESMAN’, 7698,
3 TO_DATE(’22-FEB-1981′, ‘DD-MON-YYYY’), 1250, 500, 30);

1 row created.

SQL> INSERT INTO EMP VALUES
2 (7566, ‘JONES’, ‘MANAGER’, 7839,
3 TO_DATE(‘2-APR-1981’, ‘DD-MON-YYYY’), 2975, NULL, 20);

1 row created.

SQL> INSERT INTO EMP VALUES
2 (7654, ‘MARTIN’, ‘SALESMAN’, 7698,
3 TO_DATE(’28-SEP-1981′, ‘DD-MON-YYYY’), 1250, 1400, 30);

1 row created.

SQL> INSERT INTO EMP VALUES
2 (7698, ‘BLAKE’, ‘MANAGER’, 7839,
3 TO_DATE(‘1-MAY-1981’, ‘DD-MON-YYYY’), 2850, NULL, 30);

1 row created.

SQL> INSERT INTO EMP VALUES
2 (7782, ‘CLARK’, ‘MANAGER’, 7839,
3 TO_DATE(‘9-JUN-1981’, ‘DD-MON-YYYY’), 2450, NULL, 10);

1 row created.

SQL> INSERT INTO EMP VALUES
2 (7788, ‘SCOTT’, ‘ANALYST’, 7566,
3 TO_DATE(’09-DEC-1982′, ‘DD-MON-YYYY’), 3000, NULL, 20);

1 row created.

SQL> INSERT INTO EMP VALUES
2 (7839, ‘KING’, ‘PRESIDENT’, NULL,
3 TO_DATE(’17-NOV-1981′, ‘DD-MON-YYYY’), 5000, NULL, 10);

1 row created.

SQL> INSERT INTO EMP VALUES
2 (7844, ‘TURNER’, ‘SALESMAN’, 7698,
3 TO_DATE(‘8-SEP-1981’, ‘DD-MON-YYYY’), 1500, 0, 30);

1 row created.

SQL> INSERT INTO EMP VALUES
2 (7876, ‘ADAMS’, ‘CLERK’, 7788,
3 TO_DATE(’12-JAN-1983′, ‘DD-MON-YYYY’), 1100, NULL, 20);

1 row created.

SQL> INSERT INTO EMP VALUES
2 (7900, ‘JAMES’, ‘CLERK’, 7698,
3 TO_DATE(‘3-DEC-1981’, ‘DD-MON-YYYY’), 950, NULL, 30);

1 row created.

SQL> INSERT INTO EMP VALUES
2 (7902, ‘FORD’, ‘ANALYST’, 7566,
3 TO_DATE(‘3-DEC-1981’, ‘DD-MON-YYYY’), 3000, NULL, 20);

1 row created.

SQL> INSERT INTO EMP VALUES
2 (7934, ‘MILLER’, ‘CLERK’, 7782,
3 TO_DATE(’23-JAN-1982′, ‘DD-MON-YYYY’), 1300, NULL, 10);

1 row created.

SQL> commit;

Commit complete.

SQL> insert into emp(empno) values(null);
insert into emp(empno) values(null)
*
ERROR at line 1:
ORA-01400: cannot insert NULL into (“SYSTEM”.”EMP”.”EMPNO”)
SQL> alter table emp drop constraint
2
SQL>
SQL>
SQL> select constraint_name
2 from user_constraints
3 where table_name=’EMP’;

CONSTRAINT_NAME
—————————————————————————————————-
SYS_C0010292

SQL> alter table emp drop constraint SYS_C0010292;

Table altered.

SQL> insert into emp(empno) values(null);

1 row created.

SQL> select *from emp;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
———- ———- ——— ———- ——— ———- ———- ———-
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 09-DEC-82 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 12-JAN-83 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10

15 rows selected.

SQL> select count(*) from emp;

COUNT(*)
———-
15

SQL>
SQL> ed
Wrote file afiedt.buf

1* select count(empno) from emp
SQL> /

COUNT(EMPNO)
————
14

SQL> clear screen
SQL> ed
Wrote file afiedt.buf

1 select count(comm)
2* from emp
3 /

COUNT(COMM)
———–
4

SQL> clear screen
SQL> ed
Wrote file afiedt.buf

1 select count(comm)
2* from emp
3
SQL>
SQL> clear screen
SQL> ed
Wrote file afiedt.buf

1 select count(empno)
2 from emp
3* where job=’SALESMAN’ and deptno=30
4 /

COUNT(EMPNO)
————
4

SQL> ed
Wrote file afiedt.buf

1 select count(empno)
2 from emp
3* where job=’SALESMAN’ and deptno=30
SQL>
SQL> clear screen
SQL> clear screen
SQL> ed
Wrote file afiedt.buf

1 Select COUNT(Empno),COUNT(DISTINCT MGR)
2* From Emp
3 /

COUNT(EMPNO) COUNT(DISTINCTMGR)
———— ——————
14 6

SQL> ed
Wrote file afiedt.buf

1 Select COUNT(Job),COUNT(DISTINCT MGR)
2 From Emp
3* Where Deptno=20
4 /

COUNT(JOB) COUNT(DISTINCTMGR)
———- ——————
5 4

SQL> clear screen
SQL> ed
Wrote file afiedt.buf

1* select max(1000,2000,3000) from dual
SQL> /
select max(1000,2000,3000) from dual
*
ERROR at line 1:
ORA-00909: invalid number of arguments
SQL> ed
Wrote file afiedt.buf

1* select max(1000) from dual
SQL> /

MAX(1000)
———-
1000

SQL> ed
Wrote file afiedt.buf

1 select sum(100)
2* from emp
3 /

SUM(100)
———-
1500

SQL> rollback;

Rollback complete.

SQL> select sum(100)
2 from emp
3 /

SUM(100)
———-
1400

SQL> ed
Wrote file afiedt.buf

1 select count(100)
2* from emp
3 /

COUNT(100)
———-
14

SQL> ed
Wrote file afiedt.buf

1 select count(100)
2* from emp
SQL>
SQL> ed
Wrote file afiedt.buf

1 select count(empno)
2* from emp
SQL> /

COUNT(EMPNO)
————
14

SQL>
SQL>
SQL> exit

linux_sep20

$cal :it display previous month, current month and next month calendar.
$cal year : It display the given year calendar
Ex $cal 2017
$cal month year :It display the given month calendar only.
$cal 3 2017
$cal -1 display single month
$cal -3 display previous current next month
$cal –s display the current date
$cal –j days one based, number of the day from January 1st to 365 days.
$cal –y total year
#init to change system run levels.
[root@dba ~]# init -v –version
It will display the current version
#init :To change system run levels.
#init 0 :To shut down the system
#init 1 : To bring the system to single user mode
change directories
cd àstands for change directories
This command is used to change directories path.
Syntax
cd [options] directory (or) directories
cd mdir1
cd.. Back to one directory
cd ../../../ àback to three directories
cd / àdirectly comes to root directory.

Removing directories
$ rmdir à stands for removing directories .
This command is used to remove empty directories

sysnatx rmdir [option ] directory
-p parent àremoves directory and its nested.
-v, –verbose output a diagnostic for every directory processed

creating files
File
àFile means collection of information.
àIt has the following two main characteries
1) color black or white
2)symbol -( hyphen )
$cat command
creating file >
displaying file <
appending file >>
cat àstands for concatenates this commands is used to perform the following three tasks.

ora11_spe20

select to_char(sysdate,’HH12′) from dual;

TO

11

SQL> ed
Wrote file afiedt.buf

1* select to_char(sysdate,’HH12 pm’) from dual
SQL> /

TO_CH
—–
11 pm

SQL> /

TO_CH
—–
11 am

SQL> ed
Wrote file afiedt.buf

1* select to_char(sysdate,’HH24′) from dual
SQL> /

TO

11

SQL> ed
Wrote file afiedt.buf

1* select to_char(sysdate,’HH24:mi:ss’) from dual
SQL> /

TO_CHAR(
——–
11:59:50

SQL> ed
Wrote file afiedt.buf

1* select to_char(sysdate,’RM’) from dual
SQL> /

TO_C
—-
IX

SQL> clear screen
SQL> /

TO_C
—-
IX

SQL> ed
Wrote file afiedt.buf

1* select to_char(sysdate,’DD/Month/YYYY’) from dual
SQL> /

TO_CHAR(SYSDATE,’
—————–
19/September/2019

SQL> ed
Wrote file afiedt.buf

1 select ename,hiredate,to_char(hiredate,’DDth’)
2* from emp
3 /

ENAME HIREDATE TO_C
———- ——— —-
SMITH 17-DEC-80 17TH
ALLEN 20-FEB-81 20TH
WARD 22-FEB-81 22ND
JONES 02-APR-81 02ND
MARTIN 28-SEP-81 28TH
BLAKE 01-MAY-81 01ST
CLARK 09-JUN-81 09TH
SCOTT 09-DEC-82 09TH
KING 17-NOV-81 17TH
TURNER 08-SEP-81 08TH
ADAMS 12-JAN-83 12TH

ENAME HIREDATE TO_C
———- ——— —-
JAMES 03-DEC-81 03RD
FORD 03-DEC-81 03RD
MILLER 23-JAN-82 23RD

14 rows selected.

SQL> set pagesize 100
SQL> clear screen
SQL> /

ENAME HIREDATE TO_C
———- ——— —-
SMITH 17-DEC-80 17TH
ALLEN 20-FEB-81 20TH
WARD 22-FEB-81 22ND
JONES 02-APR-81 02ND
MARTIN 28-SEP-81 28TH
BLAKE 01-MAY-81 01ST
CLARK 09-JUN-81 09TH
SCOTT 09-DEC-82 09TH
KING 17-NOV-81 17TH
TURNER 08-SEP-81 08TH
ADAMS 12-JAN-83 12TH
JAMES 03-DEC-81 03RD
FORD 03-DEC-81 03RD
MILLER 23-JAN-82 23RD

14 rows selected.

SQL> ed
Wrote file afiedt.buf

1 select ename,hiredate,to_char(hiredate,’DDsp’)
2* from emp
SQL> /

ENAME HIREDATE TO_CHAR(HIRE
———- ——— ————
SMITH 17-DEC-80 SEVENTEEN
ALLEN 20-FEB-81 TWENTY
WARD 22-FEB-81 TWENTY-TWO
JONES 02-APR-81 TWO
MARTIN 28-SEP-81 TWENTY-EIGHT
BLAKE 01-MAY-81 ONE
CLARK 09-JUN-81 NINE
SCOTT 09-DEC-82 NINE
KING 17-NOV-81 SEVENTEEN
TURNER 08-SEP-81 EIGHT
ADAMS 12-JAN-83 TWELVE
JAMES 03-DEC-81 THREE
FORD 03-DEC-81 THREE
MILLER 23-JAN-82 TWENTY-THREE

14 rows selected.

SQL> ed
Wrote file afiedt.buf

1 select ename,hiredate,to_char(hiredate,’ddsp’)
2* from emp
SQL> /

ENAME HIREDATE TO_CHAR(HIRE
———- ——— ————
SMITH 17-DEC-80 seventeen
ALLEN 20-FEB-81 twenty
WARD 22-FEB-81 twenty-two
JONES 02-APR-81 two
MARTIN 28-SEP-81 twenty-eight
BLAKE 01-MAY-81 one
CLARK 09-JUN-81 nine
SCOTT 09-DEC-82 nine
KING 17-NOV-81 seventeen
TURNER 08-SEP-81 eight
ADAMS 12-JAN-83 twelve
JAMES 03-DEC-81 three
FORD 03-DEC-81 three
MILLER 23-JAN-82 twenty-three

14 rows selected.

SQL> clear screen
SQL> ed
Wrote file afiedt.buf

1 select ename,hiredate,to_char(hiredate,’Day/Month/Year hh:mi:ss pm’)
2* from emp
3 /

ENAME HIREDATE
———- ———
TO_CHAR(HIREDATE,’DAY/MONTH/YEARHH:MI:SSPM’)
————————————————————————–
SMITH 17-DEC-80
Wednesday/December /Nineteen Eighty 12:00:00 am

ALLEN 20-FEB-81
Friday /February /Nineteen Eighty-One 12:00:00 am

WARD 22-FEB-81
Sunday /February /Nineteen Eighty-One 12:00:00 am

JONES 02-APR-81
Thursday /April /Nineteen Eighty-One 12:00:00 am

MARTIN 28-SEP-81
Monday /September/Nineteen Eighty-One 12:00:00 am

BLAKE 01-MAY-81
Friday /May /Nineteen Eighty-One 12:00:00 am

CLARK 09-JUN-81
Tuesday /June /Nineteen Eighty-One 12:00:00 am

SCOTT 09-DEC-82
Thursday /December /Nineteen Eighty-Two 12:00:00 am

KING 17-NOV-81
Tuesday /November /Nineteen Eighty-One 12:00:00 am

TURNER 08-SEP-81
Tuesday /September/Nineteen Eighty-One 12:00:00 am

ADAMS 12-JAN-83
Wednesday/January /Nineteen Eighty-Three 12:00:00 am

JAMES 03-DEC-81
Thursday /December /Nineteen Eighty-One 12:00:00 am

FORD 03-DEC-81
Thursday /December /Nineteen Eighty-One 12:00:00 am

MILLER 23-JAN-82
Saturday /January /Nineteen Eighty-Two 12:00:00 am

14 rows selected.

SQL> ed
Wrote file afiedt.buf

1 select to_char(hiredate,’Day/Month/Year hh:mi:ss pm’)
2* from emp
SQL> /

TO_CHAR(HIREDATE,’DAY/MONTH/YEARHH:MI:SSPM’)
————————————————————————–
Wednesday/December /Nineteen Eighty 12:00:00 am
Friday /February /Nineteen Eighty-One 12:00:00 am
Sunday /February /Nineteen Eighty-One 12:00:00 am
Thursday /April /Nineteen Eighty-One 12:00:00 am
Monday /September/Nineteen Eighty-One 12:00:00 am
Friday /May /Nineteen Eighty-One 12:00:00 am
Tuesday /June /Nineteen Eighty-One 12:00:00 am
Thursday /December /Nineteen Eighty-Two 12:00:00 am
Tuesday /November /Nineteen Eighty-One 12:00:00 am
Tuesday /September/Nineteen Eighty-One 12:00:00 am
Wednesday/January /Nineteen Eighty-Three 12:00:00 am
Thursday /December /Nineteen Eighty-One 12:00:00 am
Thursday /December /Nineteen Eighty-One 12:00:00 am
Saturday /January /Nineteen Eighty-Two 12:00:00 am

14 rows selected.

SQL> clear screen
SQL> /

TO_CHAR(HIREDATE,’DAY/MONTH/YEARHH:MI:SSPM’)
————————————————————————–
Wednesday/December /Nineteen Eighty 12:00:00 am
Friday /February /Nineteen Eighty-One 12:00:00 am
Sunday /February /Nineteen Eighty-One 12:00:00 am
Thursday /April /Nineteen Eighty-One 12:00:00 am
Monday /September/Nineteen Eighty-One 12:00:00 am
Friday /May /Nineteen Eighty-One 12:00:00 am
Tuesday /June /Nineteen Eighty-One 12:00:00 am
Thursday /December /Nineteen Eighty-Two 12:00:00 am
Tuesday /November /Nineteen Eighty-One 12:00:00 am
Tuesday /September/Nineteen Eighty-One 12:00:00 am
Wednesday/January /Nineteen Eighty-Three 12:00:00 am
Thursday /December /Nineteen Eighty-One 12:00:00 am
Thursday /December /Nineteen Eighty-One 12:00:00 am
Saturday /January /Nineteen Eighty-Two 12:00:00 am

14 rows selected.

SQL> ed
Wrote file afiedt.buf

1 select replace(to_char(hiredate,’Day/Month/Year hh:mi:ss pm’),’ ‘)
2* from emp
SQL> /

REPLACE(TO_CHAR(HIREDATE,’DAY/MONTH/YEARHH:MI:SSPM’),”)
————————————————————————–
Wednesday/December/NineteenEighty12:00:00am
Friday/February/NineteenEighty-One12:00:00am
Sunday/February/NineteenEighty-One12:00:00am
Thursday/April/NineteenEighty-One12:00:00am
Monday/September/NineteenEighty-One12:00:00am
Friday/May/NineteenEighty-One12:00:00am
Tuesday/June/NineteenEighty-One12:00:00am
Thursday/December/NineteenEighty-Two12:00:00am
Tuesday/November/NineteenEighty-One12:00:00am
Tuesday/September/NineteenEighty-One12:00:00am
Wednesday/January/NineteenEighty-Three12:00:00am
Thursday/December/NineteenEighty-One12:00:00am
Thursday/December/NineteenEighty-One12:00:00am
Saturday/January/NineteenEighty-Two12:00:00am

14 rows selected.

SQL> ed
Wrote file afiedt.buf

1 select to_char(hiredate,’FMDay/Month/Year hh:mi:ss pm’)
2* from emp
SQL> /

TO_CHAR(HIREDATE,’FMDAY/MONTH/YEARHH:MI:SSPM’)
————————————————————————–
Wednesday/December/Nineteen Eighty 12:0:0 am
Friday/February/Nineteen Eighty-One 12:0:0 am
Sunday/February/Nineteen Eighty-One 12:0:0 am
Thursday/April/Nineteen Eighty-One 12:0:0 am
Monday/September/Nineteen Eighty-One 12:0:0 am
Friday/May/Nineteen Eighty-One 12:0:0 am
Tuesday/June/Nineteen Eighty-One 12:0:0 am
Thursday/December/Nineteen Eighty-Two 12:0:0 am
Tuesday/November/Nineteen Eighty-One 12:0:0 am
Tuesday/September/Nineteen Eighty-One 12:0:0 am
Wednesday/January/Nineteen Eighty-Three 12:0:0 am
Thursday/December/Nineteen Eighty-One 12:0:0 am
Thursday/December/Nineteen Eighty-One 12:0:0 am
Saturday/January/Nineteen Eighty-Two 12:0:0 am

14 rows selected.

SQL> clear screen
SQL> ed
Wrote file afiedt.buf

1* select 100+’USD2,450.57′ from dual
SQL> /
select 100+’USD2,450.57′ from dual
*
ERROR at line 1:
ORA-01722: invalid number
SQL> ed
Wrote file afiedt.buf

1 select 100+to_number(‘USD2,450.57′,’C9G999D99’)
2* from dual
3 /

100+TO_NUMBER(‘USD2,450.57′,’C9G999D99’)
—————————————-
2550.57

SQL> ed
Wrote file afiedt.buf

1 select 100+to_number(‘USD2,450.57′,’C9G999D99′)
2* from dual
SQL>
SQL> clear screen
SQL> select ’10-01-2019′
2
SQL> ed
Wrote file afiedt.buf

1* select ’10-01-2019’+10 from dual
2 /
select ’10-01-2019’+10 from dual
*
ERROR at line 1:
ORA-01722: invalid number
SQL> ed
Wrote file afiedt.buf

1* select to_date(’10-01-2019′)+10 from dual
SQL> /
select to_date(’10-01-2019′)+10 from dual
*
ERROR at line 1:
ORA-01843: not a valid month
SQL> ed
Wrote file afiedt.buf

1* select to_date(’10-jan-2019’)+10 from dual
SQL> /

TO_DATE(‘
———
20-JAN-19

SQL> ed
Wrote file afiedt.buf

1* select ’10-jan-2019’+10 from dual
SQL> /
select ’10-jan-2019’+10 from dual
*
ERROR at line 1:
ORA-01722: invalid number
SQL> ed
Wrote file afiedt.buf

1* select to_date(’10-01-2019′,’dd-mm-yyyy’)+10 from dual
SQL> /

TO_DATE(‘
———
20-JAN-19

SQL> ed
Wrote file afiedt.buf

1* select to_date(’10-01-2019′,’dd-mm-yyyy’)+10 from dual
SQL>
SQL>
SQL> ed
Wrote file afiedt.buf

1* select to_date(’10-01-2019′,’dd-mm-yyyy’)+10 from dual
SQL>
SQL> clear screen
SQL> select add_month(
2
SQL> ed
Wrote file afiedt.buf

1 select add_month(‘1980-dec-17’,1)
2* from dual
3 /
select add_month(‘1980-dec-17’,1)
*
ERROR at line 1:
ORA-00904: “ADD_MONTH”: invalid identifier
SQL> ed
Wrote file afiedt.buf

1 select add_months(‘1980-dec-17’,1)
2* from dual
SQL> /
select add_months(‘1980-dec-17’,1)
*
ERROR at line 1:
ORA-01861: literal does not match format string
SQL> clear screen
SQL> /
select add_months(‘1980-dec-17’,1)
*
ERROR at line 1:
ORA-01861: literal does not match format string
SQL> ed
Wrote file afiedt.buf

1 select add_months(to_date(‘1980-dec-17′,’yyyy-mon-dd’),1)
2* from dual
SQL> /

ADD_MONTH
———
17-JAN-81

SQL> clear screen
SQL> ed
Wrote file afiedt.buf

1 select add_months(to_date(‘1980-dec-17′,’yyyy-mon-dd’),1)
2* from dual
SQL>
SQL>
SQL> clear screen
SQL> ed
Wrote file afiedt.buf

1* select to_date(10,’dd’) from dual
2 /

TO_DATE(1
———
10-SEP-19

SQL> ed
Wrote file afiedt.buf

1* select to_date(32,’dd’) from dual
SQL> /
select to_date(32,’dd’) from dual
*
ERROR at line 1:
ORA-01847: day of month must be between 1 and last day of month
SQL> ed
Wrote file afiedt.buf

1* select to_date(32,’ddd’) from dual
SQL> /

TO_DATE(3
———
01-FEB-19

SQL> ed
Wrote file afiedt.buf

1* select to_date(366,’ddd’) from dual
SQL> /
select to_date(366,’ddd’) from dual
*
ERROR at line 1:
ORA-01848: day of year must be between 1 and 365 (366 for leap year)
SQL> clear screen
SQL> ed
Wrote file afiedt.buf

1* select to_date(1,’w’) from dual
SQL> /
select to_date(1,’w’) from dual
*
ERROR at line 1:
ORA-01820: format code cannot appear in date input format
SQL> clear screen
SQL> ed
Wrote file afiedt.buf

1* select to_char(sysdate,’rrrr’) from dual
2 /

TO_C
—-
2019

SQL> clear screen
SQL> /

TO_C
—-
2019

SQL> ed
Wrote file afiedt.buf

1* select to_char(sysdate,’rrrr’) from dual
2
SQL> /

TO_C
—-
2019

SQL> clear screen
SQL> ed
Wrote file afiedt.buf

1 select ename,comm
2* from emp
SQL> /

ENAME COMM
———- ———-
SMITH
ALLEN 300
WARD 500
JONES
MARTIN 1400
BLAKE
CLARK
SCOTT
KING
TURNER 0
ADAMS
JAMES
FORD
MILLER

14 rows selected.

SQL> ed
Wrote file afiedt.buf

1 select ename,nvl(comm,’Not app’)
2* from emp
SQL> /
select ename,nvl(comm,’Not app’)
*
ERROR at line 1:
ORA-01722: invalid number
SQL> ed
Wrote file afiedt.buf

1 select ename,nvl(to_char(comm),’Not app’)
2* from emp
SQL> /

ENAME NVL(TO_CHAR(COMM),’NOTAPP’)
———- —————————————-
SMITH Not app
ALLEN 300
WARD 500
JONES Not app
MARTIN 1400
BLAKE Not app
CLARK Not app
SCOTT Not app
KING Not app
TURNER 0
ADAMS Not app
JAMES Not app
FORD Not app
MILLER Not app

14 rows selected.

SQL> clear screen
SQL> ed
Wrote file afiedt.buf

1 select to_char(1,’jsp’)
2* from dual
SQL> /
select to_char(1,’jsp’)
*
ERROR at line 1:
ORA-01481: invalid number format model
SQL> ed
Wrote file afiedt.buf

1* select to_char(sysdate,’dd’) fromdual
SQL> /
select to_char(sysdate,’dd’) fromdual
*
ERROR at line 1:
ORA-00923: FROM keyword not found where expected
SQL> ed
Wrote file afiedt.buf

1* select to_char(sysdate,’dd’) from dual
SQL> /

TO

20

SQL> ed
Wrote file afiedt.buf

1 select to_char(sysdate,’ddsp’)
2* from dual
SQL> /

TO_CHAR(SYSD
————
twenty

SQL> ed
Wrote file afiedt.buf

1 select to_char(sysdate,’dddsp’)
2* from dual
SQL> /

TO_CHAR(SYSDATE,’DDDSP’)
—————————
two hundred sixty-three

SQL> ed
Wrote file afiedt.buf

1 select to_char(sysdate,’jsp’)
2* from dual
SQL> /

TO_CHAR(SYSDATE,’JSP’)
——————————————————————————
two million four hundred fifty-eight thousand seven hundred forty-seven

SQL> clear screen
SQL> /

TO_CHAR(SYSDATE,’JSP’)
——————————————————————————
two million four hundred fifty-eight thousand seven hundred forty-seven

SQL> ed
Wrote file afiedt.buf

1 select to_char(1,’jsp’)
2* from dual
SQL> /
select to_char(1,’jsp’)
*
ERROR at line 1:
ORA-01481: invalid number format model
SQL> ed
Wrote file afiedt.buf

1 select to_date(1,’j’)
2* from dual
SQL> /

TO_DATE(1
———
01-JAN-12

SQL> clear screen
SQL> /

TO_DATE(1
———
01-JAN-12

SQL> ed
Wrote file afiedt.buf

1 select to_char(to_date(1,’j’),’dd-mon-yyyy’)
2* from dual
SQL> /

TO_CHAR(TO_
———–
01-jan-4712

SQL> ed
Wrote file afiedt.buf

1 select to_char(to_date(1,’j’),’dd-mon-yyyyAD’)
2* from dual
SQL> /

TO_CHAR(TO_DA
————-
01-jan-4712BC

SQL> ed
Wrote file afiedt.buf

1 select to_char(to_date(&10,’j’),’dd-mon-yyyyAD’)
2* from dual
SQL> /
Enter value for 10: 10
old 1: select to_char(to_date(&10,’j’),’dd-mon-yyyyAD’)
new 1: select to_char(to_date(10,’j’),’dd-mon-yyyyAD’)

TO_CHAR(TO_DA
————-
10-jan-4712BC

SQL> ed
Wrote file afiedt.buf

1 select to_char(to_date(&no,’j’),’dd-mon-yyyyAD’)
2* from dual
SQL> /
Enter value for no: 5373484
old 1: select to_char(to_date(&no,’j’),’dd-mon-yyyyAD’)
new 1: select to_char(to_date(5373484,’j’),’dd-mon-yyyyAD’)

TO_CHAR(TO_DA
————-
31-dec-9999AD

SQL> /
Enter value for no: 5373485
old 1: select to_char(to_date(&no,’j’),’dd-mon-yyyyAD’)
new 1: select to_char(to_date(5373485,’j’),’dd-mon-yyyyAD’)
select to_char(to_date(5373485,’j’),’dd-mon-yyyyAD’)
*
ERROR at line 1:
ORA-01854: julian date must be between 1 and 5373484
SQL> ed
Wrote file afiedt.buf

1 select to_char(to_date(&no,’j’),’j’)
2* from dual
SQL> /
Enter value for no: 100
old 1: select to_char(to_date(&no,’j’),’j’)
new 1: select to_char(to_date(100,’j’),’j’)

TO_CHAR
——-
0000100

SQL> /
Enter value for no: 100000
old 1: select to_char(to_date(&no,’j’),’j’)
new 1: select to_char(to_date(100000,’j’),’j’)

TO_CHAR
——-
0100000

SQL> ed
Wrote file afiedt.buf

1 select to_char(to_date(&no,’j’),’jsp’)
2* from dual
SQL> /
Enter value for no: 1
old 1: select to_char(to_date(&no,’j’),’jsp’)
new 1: select to_char(to_date(1,’j’),’jsp’)

TO_

one

SQL> /
Enter value for no: 2
old 1: select to_char(to_date(&no,’j’),’jsp’)
new 1: select to_char(to_date(2,’j’),’jsp’)

TO_

two

SQL> /
Enter value for no: 12
old 1: select to_char(to_date(&no,’j’),’jsp’)
new 1: select to_char(to_date(12,’j’),’jsp’)

TO_CHA
——
twelve

SQL> ed
Wrote file afiedt.buf

1 select to_char(to_date(&no,’j’),’jsp’)
2* from dual
SQL>
SQL> Select
2 ‘KRISHNA REDDY’ OrgName,
3 Translate(
4 ‘KRISHNA REDDY’,
5 ‘ABCDEFGHIJKLMNOPQRSTUVWXYZ’,
6 ‘1234567890!@#$%^&*()-=_+;,.’)Encryptedname
7 From Dual;

ORGNAME ENCRYPTEDNAME
————- ————-
KRISHNA REDDY !*9(8$1 *544;

SQL> clear screen
SQL> ed
Wrote file afiedt.buf

1 Select
2 Translate( ‘KRISHNA REDDY’,
3 ‘ABCDEFGHIJKLMNOPQRSTUVWXYZ’,
4 ‘1234567890!@#$%^&*()-=_+;,.’)Encryptedname
5* From Dual
SQL> /

ENCRYPTEDNAME
————-
!*9(8$1 *544;

SQL> clear screen
SQL> ed
Wrote file afiedt.buf

1 Select
2 Translate( ‘KRISHNA REDDY’,
3 ‘ABCDEFGHIJKLMNOPQRSTUVWXYZ’,
4 ‘1234567890!@#$%^&*()-=_+;,.’)Encryptedname
5* From Dual
SQL>
SQL> clear screen
SQL> Select ‘!*9(8$1 *544;’ Encryptedname,
2 Translate(
3 ‘!*9(8$1 *544;’,
4 ‘1234567890!@#$%^&*()-=_+;,’,
5 ‘ABCDEFGHIJKLMNOPQRSTUVWXYZ’
6 )Encryptedname
7 From Dual
8 /

ENCRYPTEDNAME ENCRYPTEDNAME
————- ————-
!*9(8$1 *544; KRISHNA REDDY

SQL> ed
Wrote file afiedt.buf

1 Select
2 Translate(‘!*9(8$1 *544;’,
3 ‘1234567890!@#$%^&*()-=_+;,’,
4 ‘ABCDEFGHIJKLMNOPQRSTUVWXYZ’
5 )Encryptedname
6* From Dual
SQL> /

ENCRYPTEDNAME
————-
KRISHNA REDDY

SQL> ed
Wrote file afiedt.buf

1 Select
2 Translate(‘1234’,
3 ‘1234567890!@#$%^&*()-=_+;,’,
4 ‘ABCDEFGHIJKLMNOPQRSTUVWXYZ’
5 )Encryptedname
6* From Dual
SQL> /

ENCR
—-
ABCD

SQL> clear screen
SQL> exit