SQL> SELECT ENAME,SAL,NVL2(COMM,100,'SMITH') FROM EMP;
SELECT ENAME,SAL,NVL2(COMM,100,'SMITH') FROM EMP
*
ERROR at line 1:
ORA-01722: invalid number
SQL> SELECT ENAME,SAL,NVL2(COMM,'A','SMITH') FROM EMP;
ENAME SAL NVL2(
--------------- ---------- -----
SMITH 800 SMITH
ALLEN 1600 A
WARD 1250 A
JONES 2975 SMITH
MARTIN 1250 A
BLAKE 2850 SMITH
CLARK 2450 SMITH
SCOTT 3000 SMITH
KING 5000 SMITH
TURNER 1500 A
ADAMS 1100 SMITH
JAMES 950 SMITH
FORD 3000 SMITH
MILLER 1300 SMITH
14 rows selected.
SQL> SELECT ENAME,SAL,NVL2(COMM,'A',56) FROM EMP;
ENAME SAL NV
--------------- ---------- --
SMITH 800 56
ALLEN 1600 A
WARD 1250 A
JONES 2975 56
MARTIN 1250 A
BLAKE 2850 56
CLARK 2450 56
SCOTT 3000 56
KING 5000 56
TURNER 1500 A
ADAMS 1100 56
JAMES 950 56
FORD 3000 56
MILLER 1300 56
14 rows selected.
SQL> SELECT ENAME,SAL,NVL2(COMM,100,'SMITH') FROM EMP;
SELECT ENAME,SAL,NVL2(COMM,100,'SMITH') FROM EMP
*
ERROR at line 1:
ORA-01722: invalid number
SQL> SELECT ENAME,SAL+NVL2(COMM,100,1000) FROM EMP;
ENAME SAL+NVL2(COMM,100,1000)
--------------- -----------------------
SMITH 1800
ALLEN 1700
WARD 1350
JONES 3975
MARTIN 1350
BLAKE 3850
CLARK 3450
SCOTT 4000
KING 6000
TURNER 1600
ADAMS 2100
JAMES 1950
FORD 4000
MILLER 2300
14 rows selected.
SQL> SELECT ENAME,SAL,COMM,NVL2(COMM,SAL,1000) FROM EMP;
ENAME SAL COMM NVL2(COMM,SAL,1000)
--------------- ---------- ---------- -------------------
SMITH 800 1000
ALLEN 1600 300 1600
WARD 1250 500 1250
JONES 2975 1000
MARTIN 1250 1400 1250
BLAKE 2850 1000
CLARK 2450 1000
SCOTT 3000 1000
KING 5000 1000
TURNER 1500 0 1500
ADAMS 1100 1000
JAMES 950 1000
FORD 3000 1000
MILLER 1300 1000
14 rows selected.
SQL> SELECT ENAME,SAL,COMM,NVL2(COMM,SAL+COMM,1000) FROM EMP;
ENAME SAL COMM NVL2(COMM,SAL+COMM,1000)
--------------- ---------- ---------- ------------------------
SMITH 800 1000
ALLEN 1600 300 1900
WARD 1250 500 1750
JONES 2975 1000
MARTIN 1250 1400 2650
BLAKE 2850 1000
CLARK 2450 1000
SCOTT 3000 1000
KING 5000 1000
TURNER 1500 0 1500
ADAMS 1100 1000
JAMES 950 1000
FORD 3000 1000
MILLER 1300 1000
14 rows selected.
SQL> SELECT ENAME,SAL,COMM,NVL2(COMM,SAL+COMM,'SMITH') FROM EMP;
SELECT ENAME,SAL,COMM,NVL2(COMM,SAL+COMM,'SMITH') FROM EMP
*
ERROR at line 1:
ORA-01722: invalid number
SQL> SELECT ENAME,SAL,COMM,NVL2(COMM,SAL+COMM,SAL) FROM EMP;
ENAME SAL COMM NVL2(COMM,SAL+COMM,SAL)
--------------- ---------- ---------- -----------------------
SMITH 800 800
ALLEN 1600 300 1900
WARD 1250 500 1750
JONES 2975 2975
MARTIN 1250 1400 2650
BLAKE 2850 2850
CLARK 2450 2450
SCOTT 3000 3000
KING 5000 5000
TURNER 1500 0 1500
ADAMS 1100 1100
JAMES 950 950
FORD 3000 3000
MILLER 1300 1300
14 rows selected.
SQL> SELECT ENAME,SAL,COMM,NVL2(COMM,COMM+500,100) FROM EMP;
ENAME SAL COMM NVL2(COMM,COMM+500,100)
--------------- ---------- ---------- -----------------------
SMITH 800 100
ALLEN 1600 300 800
WARD 1250 500 1000
JONES 2975 100
MARTIN 1250 1400 1900
BLAKE 2850 100
CLARK 2450 100
SCOTT 3000 100
KING 5000 100
TURNER 1500 0 500
ADAMS 1100 100
JAMES 950 100
FORD 3000 100
MILLER 1300 100
14 rows selected.
SQL> CREATE TABLE TEST(NAME VARCHAR2(10),NO NUMBER(2)};
CREATE TABLE TEST(NAME VARCHAR2(10),NO NUMBER(2)}
*
ERROR at line 1:
ORA-00911: invalid character
SQL> CREATE TABLE TEST(NAME VARCHAR2(10),NO NUMBER(2));
Table created.
SQL> DESC TEST
Name Null? Type
----------------------------------------------------------------------------------------------------------------- -------- ----------------------------------------------------------------------------
NAME VARCHAR2(10)
NO NUMBER(2)
SQL> SET LINE 50
SQL> SET LINE 50
SQL> DESC TEST
Name Null? Type
----------------------- -------- ----------------
NAME VARCHAR2(10)
NO NUMBER(2)
SQL> INSERT INTO TEST VALUES(NVL((SELECT MAX(NO)FROM TEST),1000)+1,'&NAME');
Enter value for name: SMITH
old 1: INSERT INTO TEST VALUES(NVL((SELECT MAX(NO)FROM TEST),1000)+1,'&NAME')
new 1: INSERT INTO TEST VALUES(NVL((SELECT MAX(NO)FROM TEST),1000)+1,'SMITH')
INSERT INTO TEST VALUES(NVL((SELECT MAX(NO)FROM TEST),1000)+1,'SMITH')
*
ERROR at line 1:
ORA-01722: invalid number
SQL> set pagesize 200
SQL> select nvl(comm,0) from emp;
NVL(COMM,0)
-----------
0
300
500
0
1400
0
0
0
0
0
0
0
0
0
14 rows selected.
SQL> select nvl(1400,0)
2 from emp
3 /
NVL(1400,0)
-----------
1400
1400
1400
1400
1400
1400
1400
1400
1400
1400
1400
1400
1400
1400
14 rows selected.
SQL> cl scr
SQL> select *from duel;
ENAME DEPTNO
--------------- ----------
ALLEN 30
12
SQL> select ename,deptno,comm,nvl2(comm,comm+500,100)
2 from emp;
ENAME DEPTNO COMM NVL2(COMM,COMM+500,100)
--------------- ---------- ---------- -----------------------
SMITH 20 100
ALLEN 30 300 800
WARD 30 500 1000
JONES 20 100
MARTIN 30 1400 1900
BLAKE 30 100
CLARK 10 100
SCOTT 20 100
KING 10 100
TURNER 30 0 500
ADAMS 20 100
JAMES 30 100
FORD 20 100
MILLER 10 100
14 rows selected.
SQL> select ename,deptno,comm,nvl2(comm,'abc',100)
2 from emp;
ENAME DEPTNO COMM NVL
--------------- ---------- ---------- ---
SMITH 20 100
ALLEN 30 300 abc
WARD 30 500 abc
JONES 20 100
MARTIN 30 1400 abc
BLAKE 30 100
CLARK 10 100
SCOTT 20 100
KING 10 100
TURNER 30 0 abc
ADAMS 20 100
JAMES 30 100
FORD 20 100
MILLER 10 100
14 rows selected.
SQL> select ename,deptno,comm,nvl2(comm,'abc','XYZ')
2 from emp;
ENAME DEPTNO COMM NVL
--------------- ---------- ---------- ---
SMITH 20 XYZ
ALLEN 30 300 abc
WARD 30 500 abc
JONES 20 XYZ
MARTIN 30 1400 abc
BLAKE 30 XYZ
CLARK 10 XYZ
SCOTT 20 XYZ
KING 10 XYZ
TURNER 30 0 abc
ADAMS 20 XYZ
JAMES 30 XYZ
FORD 20 XYZ
MILLER 10 XYZ
14 rows selected.
SQL> select ename,deptno,comm,nvl2(comm,100,'XYZ')
2 from emp;
select ename,deptno,comm,nvl2(comm,100,'XYZ')
*
ERROR at line 1:
ORA-01722: invalid number
SQL> spool off
SQL> clscr
SP2-0042: unknown command "clscr" - rest of line ignored.
SQL> cl scr
SQL> select nullif(100,200)
2 from emp;
NULLIF(100,200)
---------------
100
100
100
100
100
100
100
100
100
100
100
100
100
100
14 rows selected.
SQL> select nullif(100,100)
2 from emp;
NULLIF(100,100)
---------------
14 rows selected.
SQL> select nullif(100,smith)
2 from emp;
select nullif(100,smith)
*
ERROR at line 1:
ORA-00904: "SMITH": invalid identifier
SQL> select nullif(smith,smith)
2 from emp;
select nullif(smith,smith)
*
ERROR at line 1:
ORA-00904: "SMITH": invalid identifier
SQL> select nullif(100,'smith')
2 from emp;
select nullif(100,'smith')
*
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected NUMBER got CHAR
SQL> select nullif('smith','smith')
2 from emp;
NULLI
-----
14 rows selected.
SQL> select nullif('smith',100)
2 from emp;
select nullif('smith',100)
*
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected CHAR got NUMBER
SQL> select nullif(smith,smith)
2 from emp;
select nullif(smith,smith)
*
ERROR at line 1:
ORA-00904: "SMITH": invalid identifier
SQL> select nullif(smith,ford)
2 from emp;
select nullif(smith,ford)
*
ERROR at line 1:
ORA-00904: "FORD": invalid identifier
SQL> select nullif('smith','ford')
2 from emp;
NULLI
-----
smith
smith
smith
smith
smith
smith
smith
smith
smith
smith
smith
smith
smith
smith
14 rows selected.
SQL> select nullif('100','ford')
2 from emp;
NUL
---
100
100
100
100
100
100
100
100
100
100
100
100
100
100
14 rows selected.
SQL> select nullif('smith','100')
2 from emp;
NULLI
-----
smith
smith
smith
smith
smith
smith
smith
smith
smith
smith
smith
smith
smith
smith
14 rows selected.
SQL> select nullif(null,'smith')
2 from duel;
select nullif(null,'smith')
*
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected - got CHAR
SQL> select nullif(100,'smith')
2 from duel;
select nullif(100,'smith')
*
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected NUMBER got CHAR
SQL> select nullif(null,100)
2 from duel;
select nullif(null,100)
*
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected - got CHAR
SQL> select nullif('smith','100')
2 /
select nullif('smith','100')
*
ERROR at line 1:
ORA-00923: FROM keyword not found where expected
SQL> spool off
SQL> select coalesce(100,500,200) from emp;
COALESCE(100,500,200)
---------------------
100
100
100
100
100
100
100
100
100
100
100
100
100
100
14 rows selected.
SQL> select coalesce(null,500,200) from emp;
COALESCE(NULL,500,200)
----------------------
500
500
500
500
500
500
500
500
500
500
500
500
500
500
14 rows selected.
SQL> select coalesce(null,null,200) from emp;
COALESCE(NULL,NULL,200)
-----------------------
200
200
200
200
200
200
200
200
200
200
200
200
200
200
14 rows selected.
SQL> select coalesce(null,null,null) from emp;
C
-
14 rows selected.
SQL> select coalesce(comm,sal,10) from emp;
COALESCE(COMM,SAL,10)
---------------------
800
300
500
2975
1400
2850
2450
3000
5000
0
1100
950
3000
1300
14 rows selected.
SQL> select coalesce(comm,mgr,10) from emp;
COALESCE(COMM,MGR,10)
---------------------
7902
300
500
7839
1400
7839
7839
7566
10
0
7788
7698
7566
7782
14 rows selected.
SQL> select coalesce(comm,mgr,qwe) from emp;
select coalesce(comm,mgr,qwe) from emp
*
ERROR at line 1:
ORA-00904: "QWE": invalid identifier
SQL> select coalesce(comm,mgr,9999) from emp;
COALESCE(COMM,MGR,9999)
-----------------------
7902
300
500
7839
1400
7839
7839
7566
9999
0
7788
7698
7566
7782
14 rows selected.
SQL> select coalesce(comm,mgr,10) from emp;
COALESCE(COMM,MGR,10)
---------------------
7902
300
500
7839
1400
7839
7839
7566
10
0
7788
7698
7566
7782
14 rows selected.
SQL> desc test
ERROR:
ORA-04043: object test does not exist
SQL> select * from test;
select * from test
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> create table test(no,name);
create table test(no,name)
*
ERROR at line 1:
ORA-02263: need to specify the datatype for this column
SQL> create table test(no number(4),name varchar2(15));
Table created.
SQL> desc test
Name Null? Type
----------------------------------------------------------------------------------------------------------------- -------- ----------------------------------------------------------------------------
NO NUMBER(4)
NAME VARCHAR2(15)
SQL> insert into test values(nvl((select max(no) from test),100)+1,'&name');
Enter value for name: qwe
old 1: insert into test values(nvl((select max(no) from test),100)+1,'&name')
new 1: insert into test values(nvl((select max(no) from test),100)+1,'qwe')
1 row created.
SQL> /
Enter value for name: asd
old 1: insert into test values(nvl((select max(no) from test),100)+1,'&name')
new 1: insert into test values(nvl((select max(no) from test),100)+1,'asd')
1 row created.
SQL> /
Enter value for name: asdwe
old 1: insert into test values(nvl((select max(no) from test),100)+1,'&name')
new 1: insert into test values(nvl((select max(no) from test),100)+1,'asdwe')
1 row created.
SQL> /
Enter value for name: wewe
old 1: insert into test values(nvl((select max(no) from test),100)+1,'&name')
new 1: insert into test values(nvl((select max(no) from test),100)+1,'wewe')
1 row created.
SQL> select * from test;
NO NAME
---------- ---------------
101 qwe
102 asd
103 asdwe
104 wewe
SQL> drop table test;
Table dropped.
SQL> drop table test;
drop table test
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> commit;
Commit complete.
SQL> create table empl(lname varchar2(35),salary number(8,2),comm number(5,2));
Table created.
SQL> desc empl
Name Null? Type
----------------------------------------------------------------------------------------------------------------- -------- ----------------------------------------------------------------------------
LNAME VARCHAR2(35)
SALARY NUMBER(8,2)
COMM NUMBER(5,2)
SQL> select ename "EMPLOYEE NAME",sal "SALARY" from emp;
EMPLOYEE NAME SALARY
--------------- ----------
SMITH 800
ALLEN 1600
WARD 1250
JONES 2975
MARTIN 1250
BLAKE 2850
CLARK 2450
SCOTT 3000
KING 5000
TURNER 1500
ADAMS 1100
JAMES 950
FORD 3000
MILLER 1300
14 rows selected.
SQL> select ename "EMPLOYEE NAME",sal+(10*.25) "RESULT" from emp;
EMPLOYEE NAME RESULT
--------------- ----------
SMITH 802.5
ALLEN 1602.5
WARD 1252.5
JONES 2977.5
MARTIN 1252.5
BLAKE 2852.5
CLARK 2452.5
SCOTT 3002.5
KING 5002.5
TURNER 1502.5
ADAMS 1102.5
JAMES 952.5
FORD 3002.5
MILLER 1302.5
14 rows selected.
SQL> select empno||ename from emp;
EMPNO||ENAME
-------------------------------------------------------
7369SMITH
7499ALLEN
7521WARD
7566JONES
7654MARTIN
7698BLAKE
7782CLARK
7788SCOTT
7839KING
7844TURNER
7876ADAMS
7900JAMES
7902FORD
7934MILLER
14 rows selected.
SQL> select empno||ename 'qw' from emp;
select empno||ename 'qw' from emp
*
ERROR at line 1:
ORA-00923: FROM keyword not found where expected
SQL> select empno||ename qw from emp;
QW
-------------------------------------------------------
7369SMITH
7499ALLEN
7521WARD
7566JONES
7654MARTIN
7698BLAKE
7782CLARK
7788SCOTT
7839KING
7844TURNER
7876ADAMS
7900JAMES
7902FORD
7934MILLER
14 rows selected.
SQL> select 'The Basic Salary of'|| ename 'is Rs' ||sal from emp;
select 'The Basic Salary of'|| ename 'is Rs' ||sal from emp
*
ERROR at line 1:
ORA-00923: FROM keyword not found where expected
SQL> select 'The Basic Salary of'|| ename|| 'is Rs' ||sal from emp;
'THEBASICSALARYOF'||ENAME||'ISRS'||SAL
-------------------------------------------------------------------------------
The Basic Salary ofSMITHis Rs800
The Basic Salary ofALLENis Rs1600
The Basic Salary ofWARDis Rs1250
The Basic Salary ofJONESis Rs2975
The Basic Salary ofMARTINis Rs1250
The Basic Salary ofBLAKEis Rs2850
The Basic Salary ofCLARKis Rs2450
The Basic Salary ofSCOTTis Rs3000
The Basic Salary ofKINGis Rs5000
The Basic Salary ofTURNERis Rs1500
The Basic Salary ofADAMSis Rs1100
The Basic Salary ofJAMESis Rs950
The Basic Salary ofFORDis Rs3000
The Basic Salary ofMILLERis Rs1300
14 rows selected.
SQL> select 'The Basic Salary of '||ename||' is Rs ' ||sal from emp;
'THEBASICSALARYOF'||ENAME||'ISRS'||SAL
----------------------------------------------------------------------------------
The Basic Salary of SMITH is Rs 800
The Basic Salary of ALLEN is Rs 1600
The Basic Salary of WARD is Rs 1250
The Basic Salary of JONES is Rs 2975
The Basic Salary of MARTIN is Rs 1250
The Basic Salary of BLAKE is Rs 2850
The Basic Salary of CLARK is Rs 2450
The Basic Salary of SCOTT is Rs 3000
The Basic Salary of KING is Rs 5000
The Basic Salary of TURNER is Rs 1500
The Basic Salary of ADAMS is Rs 1100
The Basic Salary of JAMES is Rs 950
The Basic Salary of FORD is Rs 3000
The Basic Salary of MILLER is Rs 1300
14 rows selected.
SQL> select 'The Basic Salary of '||ename||' is Rs: ' ||sal from emp;
'THEBASICSALARYOF'||ENAME||'ISRS:'||SAL
-----------------------------------------------------------------------------------
The Basic Salary of SMITH is Rs: 800
The Basic Salary of ALLEN is Rs: 1600
The Basic Salary of WARD is Rs: 1250
The Basic Salary of JONES is Rs: 2975
The Basic Salary of MARTIN is Rs: 1250
The Basic Salary of BLAKE is Rs: 2850
The Basic Salary of CLARK is Rs: 2450
The Basic Salary of SCOTT is Rs: 3000
The Basic Salary of KING is Rs: 5000
The Basic Salary of TURNER is Rs: 1500
The Basic Salary of ADAMS is Rs: 1100
The Basic Salary of JAMES is Rs: 950
The Basic Salary of FORD is Rs: 3000
The Basic Salary of MILLER is Rs: 1300
14 rows selected.
SQL> select 'The Basic Salary of '||ename||' is Rs: ' ||sal designation from emp;
DESIGNATION
-----------------------------------------------------------------------------------
The Basic Salary of SMITH is Rs: 800
The Basic Salary of ALLEN is Rs: 1600
The Basic Salary of WARD is Rs: 1250
The Basic Salary of JONES is Rs: 2975
The Basic Salary of MARTIN is Rs: 1250
The Basic Salary of BLAKE is Rs: 2850
The Basic Salary of CLARK is Rs: 2450
The Basic Salary of SCOTT is Rs: 3000
The Basic Salary of KING is Rs: 5000
The Basic Salary of TURNER is Rs: 1500
The Basic Salary of ADAMS is Rs: 1100
The Basic Salary of JAMES is Rs: 950
The Basic Salary of FORD is Rs: 3000
The Basic Salary of MILLER is Rs: 1300
14 rows selected.
SQL> select 'The Basic Salary of '||ename||' is Rs: ' ||sal 'designation' from emp;
select 'The Basic Salary of '||ename||' is Rs: ' ||sal 'designation' from emp
*
ERROR at line 1:
ORA-00923: FROM keyword not found where expected
SQL> select 'The Basic Salary of '||ename||' is Rs: ' ||sal "designation" from emp;
designation
-----------------------------------------------------------------------------------
The Basic Salary of SMITH is Rs: 800
The Basic Salary of ALLEN is Rs: 1600
The Basic Salary of WARD is Rs: 1250
The Basic Salary of JONES is Rs: 2975
The Basic Salary of MARTIN is Rs: 1250
The Basic Salary of BLAKE is Rs: 2850
The Basic Salary of CLARK is Rs: 2450
The Basic Salary of SCOTT is Rs: 3000
The Basic Salary of KING is Rs: 5000
The Basic Salary of TURNER is Rs: 1500
The Basic Salary of ADAMS is Rs: 1100
The Basic Salary of JAMES is Rs: 950
The Basic Salary of FORD is Rs: 3000
The Basic Salary of MILLER is Rs: 1300
14 rows selected.
SQL> cl scr
SQL> select distinct job from emp;
JOB
---------------
SALESMAN
CLERK
PRESIDENT
MANAGER
ANALYST
SQL> select distinct deptno from emp;
DEPTNO
----------
30
20
10
SQL> select distinct deptno from emp oder by desc;
select distinct deptno from emp oder by desc
*
ERROR at line 1:
ORA-00933: SQL command not properly ended
SQL> select unique deptno from emp oder by desc;
select unique deptno from emp oder by desc
*
ERROR at line 1:
ORA-00933: SQL command not properly ended
SQL> select unique deptno from emp;
DEPTNO
----------
30
20
10
SQL> select unique deptno,job from emp;
DEPTNO JOB
---------- ---------------
30 CLERK
30 SALESMAN
20 MANAGER
20 CLERK
10 PRESIDENT
30 MANAGER
10 CLERK
10 MANAGER
20 ANALYST
20 SALESMAN
10 rows selected.
SQL> select unique deptno unique job from emp;
select unique deptno unique job from emp
*
ERROR at line 1:
ORA-00923: FROM keyword not found where expected
SQL> select unique deptno,unique job from emp;
select unique deptno,unique job from emp
*
ERROR at line 1:
ORA-00936: missing expression
SQL> select unique deptno job from emp;
JOB
----------
30
20
10
SQL> select unique deptno, job from emp;
DEPTNO JOB
---------- ---------------
30 CLERK
30 SALESMAN
20 MANAGER
20 CLERK
10 PRESIDENT
30 MANAGER
10 CLERK
10 MANAGER
20 ANALYST
20 SALESMAN
10 rows selected.
SQL> select ename,empno, sal,job
2 from emp
3 where job>clerk;
where job>clerk
*
ERROR at line 3:
ORA-00904: "CLERK": invalid identifier
SQL> where job>'clerk';
SP2-0734: unknown command beginning "where job>..." - rest of line ignored.
SQL> where job>'CLERK';
SP2-0734: unknown command beginning "where job>..." - rest of line ignored.
SQL> select ename,empno, sal,job
2 from emp
3 where job>'CLERK';
ENAME EMPNO SAL JOB
--------------- ---------- ---------- ---------------
SMITH 7369 800 SALESMAN
WARD 7521 1250 SALESMAN
JONES 7566 2975 MANAGER
MARTIN 7654 1250 SALESMAN
BLAKE 7698 2850 MANAGER
CLARK 7782 2450 MANAGER
KING 7839 5000 PRESIDENT
TURNER 7844 1500 SALESMAN
8 rows selected.
SQL> select ename,empno, sal,job from emp where job>'B';
ENAME EMPNO SAL JOB
--------------- ---------- ---------- ---------------
SMITH 7369 800 SALESMAN
ALLEN 7499 1600 CLERK
WARD 7521 1250 SALESMAN
JONES 7566 2975 MANAGER
MARTIN 7654 1250 SALESMAN
BLAKE 7698 2850 MANAGER
CLARK 7782 2450 MANAGER
KING 7839 5000 PRESIDENT
TURNER 7844 1500 SALESMAN
ADAMS 7876 1100 CLERK
JAMES 7900 950 CLERK
MILLER 7934 1300 CLERK
12 rows selected.
SQL> select ename,empno, sal,job from emp where job>'R';
ENAME EMPNO SAL JOB
--------------- ---------- ---------- ---------------
SMITH 7369 800 SALESMAN
WARD 7521 1250 SALESMAN
MARTIN 7654 1250 SALESMAN
TURNER 7844 1500 SALESMAN
SQL> select ename,empno, sal,job from emp where job>='M';
ENAME EMPNO SAL JOB
--------------- ---------- ---------- ---------------
SMITH 7369 800 SALESMAN
WARD 7521 1250 SALESMAN
JONES 7566 2975 MANAGER
MARTIN 7654 1250 SALESMAN
BLAKE 7698 2850 MANAGER
CLARK 7782 2450 MANAGER
KING 7839 5000 PRESIDENT
TURNER 7844 1500 SALESMAN
8 rows selected.
SQL> select ename,empno, sal,job from emp where job>='M' and ename<='M';
ENAME EMPNO SAL JOB
--------------- ---------- ---------- ---------------
JONES 7566 2975 MANAGER
BLAKE 7698 2850 MANAGER
CLARK 7782 2450 MANAGER
KING 7839 5000 PRESIDENT
SQL> select ename, sal, from emp where not sal!=800 ;
select ename, sal, from emp where not sal!=800
*
ERROR at line 1:
ORA-00936: missing expression
SQL> select ename, sal from emp where not sal!=800 ;
ENAME SAL
--------------- ----------
SMITH 800
SQL> select ename, sal from emp where not sal!=<>800 ;
select ename, sal from emp where not sal!=<>800
*
ERROR at line 1:
ORA-00936: missing expression
SQL> select ename, sal from emp where not (sal!=800 and job^='SALESMAN' or mgr<>7839) ;
no rows selected
SQL> select ename, sal from emp where not sal!=800 and job^='SALESMAN' or mgr<>7839 ;
ENAME SAL
--------------- ----------
SMITH 800
ALLEN 1600
WARD 1250
MARTIN 1250
SCOTT 3000
TURNER 1500
ADAMS 1100
JAMES 950
FORD 3000
MILLER 1300
10 rows selected.
SQL> select ename, sal from emp where not sal!=800 or (job^='SALESMAN' and mgr<>7839) ;
ENAME SAL
--------------- ----------
SMITH 800
ALLEN 1600
SCOTT 3000
ADAMS 1100
JAMES 950
FORD 3000
MILLER 1300
7 rows selected.
SQL> select ename,job,mgr sal from emp where not sal!=800 or (job^='SALESMAN' and mgr<>7839) ;
ENAME JOB SAL
--------------- --------------- ----------
SMITH SALESMAN 7902
ALLEN CLERK 7698
SCOTT ANALYST 7566
ADAMS CLERK 7788
JAMES CLERK 7698
FORD ANALYST 7566
MILLER CLERK 7782
7 rows selected.
SQL> select ename,job,mgr,sal from emp where not sal!=800 or (job^='SALESMAN' and mgr<>7839) ;
ENAME JOB MGR SAL
--------------- --------------- ---------- ----------
SMITH SALESMAN 7902 800
ALLEN CLERK 7698 1600
SCOTT ANALYST 7566 3000
ADAMS CLERK 7788 1100
JAMES CLERK 7698 950
FORD ANALYST 7566 3000
MILLER CLERK 7782 1300
7 rows selected.
SQL> select ename,job,mgr,sal from emp where not sal!=800 or not job^='SALESMAN' and not mgr<>7839;
ENAME JOB MGR SAL
--------------- --------------- ---------- ----------
SMITH SALESMAN 7902 800
SQL> select ename,job,mgr,sal from emp where not sal!=800 or not job^='SALESMAN' or not mgr<>7839;
ENAME JOB MGR SAL
--------------- --------------- ---------- ----------
SMITH SALESMAN 7902 800
WARD SALESMAN 7698 1250
JONES MANAGER 7839 2975
MARTIN SALESMAN 7698 1250
BLAKE MANAGER 7839 2850
CLARK MANAGER 7839 2450
TURNER SALESMAN 7698 1500
7 rows selected.
SQL> select ename,job,mgr,sal from emp where not sal!=800 or not job^='SALESMAN' or not mgr<>7839;
ENAME JOB MGR SAL
--------------- --------------- ---------- ----------
SMITH SALESMAN 7902 800
WARD SALESMAN 7698 1250
JONES MANAGER 7839 2975
MARTIN SALESMAN 7698 1250
BLAKE MANAGER 7839 2850
CLARK MANAGER 7839 2450
TURNER SALESMAN 7698 1500
7 rows selected.
SQL> cl scr
SQL> select ename,job
2 from emp
3 where deptno IN(20.10);
no rows selected
SQL> select ename,job
2 from emp
3 where deptno IN(20,10);
ENAME JOB
--------------- ---------------
SMITH SALESMAN
JONES MANAGER
CLARK MANAGER
SCOTT ANALYST
KING PRESIDENT
ADAMS CLERK
FORD ANALYST
MILLER CLERK
8 rows selected.
SQL> select ename,deptno
2 from emp
3 where deptno IN(20,10);
ENAME DEPTNO
--------------- ----------
SMITH 20
JONES 20
CLARK 10
SCOTT 20
KING 10
ADAMS 20
FORD 20
MILLER 10
8 rows selected.
SQL> select ename,job
2 from emp
3 where deptno IN(salesman,clerk);
where deptno IN(salesman,clerk)
*
ERROR at line 3:
ORA-00904: "CLERK": invalid identifier
SQL> where deptno IN('MANAGER','CLERK');
SP2-0734: unknown command beginning "where dept..." - rest of line ignored.
SQL> select ename,job
2 from emp
3 where job IN('MANAGER','CLERK');
ENAME JOB
--------------- ---------------
ALLEN CLERK
JONES MANAGER
BLAKE MANAGER
CLARK MANAGER
ADAMS CLERK
JAMES CLERK
MILLER CLERK
7 rows selected.
SQL> select ename,job
2 from emp
3 where deptno IN(salesman,clerk);
where deptno IN(salesman,clerk)
*
ERROR at line 3:
ORA-00904: "CLERK": invalid identifier
SQL> select ename,job
2 from emp
3 where deptno IN('salesman','clerk');
where deptno IN('salesman','clerk')
*
ERROR at line 3:
ORA-01722: invalid number
SQL> cl scr
SQL> select ename from emp where ename between 'A' and 'M';
ENAME
---------------
ALLEN
JONES
BLAKE
CLARK
KING
ADAMS
JAMES
FORD
8 rows selected.
SQL> select ename from emp where ename not between 'A' and 'M';
ENAME
---------------
SMITH
WARD
MARTIN
SCOTT
TURNER
MILLER
6 rows selected.
SQL> select ename from emp where ename not between 100 and 200;
select ename from emp where ename not between 100 and 200
*
ERROR at line 1:
ORA-01722: invalid number
SQL> select ename,deptno from emp where deptno not between 10 and 20;
ENAME DEPTNO
--------------- ----------
ALLEN 30
WARD 30
MARTIN 30
BLAKE 30
TURNER 30
JAMES 30
6 rows selected.
SQL> select ename,deptno from emp where deptno not between 30 and 20;
ENAME DEPTNO
--------------- ----------
SMITH 20
ALLEN 30
WARD 30
JONES 20
MARTIN 30
BLAKE 30
CLARK 10
SCOTT 20
KING 10
TURNER 30
ADAMS 20
JAMES 30
FORD 20
MILLER 10
14 rows selected.
SQL> select ename,deptno from emp where deptno not between 30 and 10;
ENAME DEPTNO
--------------- ----------
SMITH 20
ALLEN 30
WARD 30
JONES 20
MARTIN 30
BLAKE 30
CLARK 10
SCOTT 20
KING 10
TURNER 30
ADAMS 20
JAMES 30
FORD 20
MILLER 10
14 rows selected.
SQL> select ename,deptno from emp where sal not between 30 and 10;
ENAME DEPTNO
--------------- ----------
SMITH 20
ALLEN 30
WARD 30
JONES 20
MARTIN 30
BLAKE 30
CLARK 10
SCOTT 20
KING 10
TURNER 30
ADAMS 20
JAMES 30
FORD 20
MILLER 10
14 rows selected.
SQL> select ename,deptno from emp where sal between 30 and 10;
no rows selected
SQL> select ename,deptno from emp where deptno between 30 and 10;
no rows selected
SQL> select ename,hiredate from emp where hierdate between '17-dec-81' and '17-jan-81';
select ename,hiredate from emp where hierdate between '17-dec-81' and '17-jan-81'
*
ERROR at line 1:
ORA-00904: "HIERDATE": invalid identifier
SQL> desc emp
Name Null? Type
------------------------------
EMPNO NUMBER(5)
ENAME VARCHAR2(15)
JOB VARCHAR2(15)
MGR NUMBER(5)
HIREDATE TIMESTAMP(6)
SAL NUMBER(6,2)
COMM NUMBER(6,2)
DEPTNO NUMBER(3)
SQL> select ename,hiredate from emp where hiredate between '17-dec-81' and '17-jan-81';
no rows selected
SQL> select ename,hiredate from emp where hiredate not between '17-dec-81' and '17-jan-81';
ENAME HIREDATE
--------------- ---------------------------------
SMITH 17-DEC-80 12.00.00.000000 AM
ALLEN 20-FEB-81 12.00.00.000000 AM
WARD 22-FEB-81 12.00.00.000000 AM
JONES 02-APR-81 12.00.00.000000 AM
MARTIN 28-SEP-81 12.00.00.000000 AM
BLAKE 01-MAY-81 12.00.00.000000 AM
CLARK 09-JUN-81 12.00.00.000000 AM
SCOTT 09-DEC-82 12.00.00.000000 AM
KING 17-NOV-81 12.00.00.000000 AM
TURNER 08-SEP-81 12.00.00.000000 AM
ADAMS 12-JAN-83 12.00.00.000000 AM
JAMES 03-DEC-81 12.00.00.000000 AM
FORD 03-DEC-81 12.00.00.000000 AM
MILLER 23-JAN-82 12.00.00.000000 AM
14 rows selected.
SQL> select ename,hiredate from emp where hiredate not between '17-dec-80' and '17-jan-81';
ENAME HIREDATE
--------------- ---------------------------------
ALLEN 20-FEB-81 12.00.00.000000 AM
WARD 22-FEB-81 12.00.00.000000 AM
JONES 02-APR-81 12.00.00.000000 AM
MARTIN 28-SEP-81 12.00.00.000000 AM
BLAKE 01-MAY-81 12.00.00.000000 AM
CLARK 09-JUN-81 12.00.00.000000 AM
SCOTT 09-DEC-82 12.00.00.000000 AM
KING 17-NOV-81 12.00.00.000000 AM
TURNER 08-SEP-81 12.00.00.000000 AM
ADAMS 12-JAN-83 12.00.00.000000 AM
JAMES 03-DEC-81 12.00.00.000000 AM
FORD 03-DEC-81 12.00.00.000000 AM
MILLER 23-JAN-82 12.00.00.000000 AM
13 rows selected.
SQL> select ename,hiredate from emp where hiredate not between '17-dec-80' and '17-jan-82';
ENAME HIREDATE
--------------- ---------------------------------
SCOTT 09-DEC-82 12.00.00.000000 AM
ADAMS 12-JAN-83 12.00.00.000000 AM
MILLER 23-JAN-82 12.00.00.000000 AM
SQL> cl scr
SQL> select ename from emp where ename like '%%';
ENAME
---------------
SMITH
ALLEN
WARD
JONES
MARTIN
BLAKE
CLARK
SCOTT
KING
TURNER
ADAMS
JAMES
FORD
MILLER
14 rows selected.
SQL> select ename from emp where ename like '%s%';
no rows selected
SQL> select ename from emp where ename like '%a%';
no rows selected
SQL> select ename from emp where ename like '%A%';
ENAME
---------------
ALLEN
WARD
MARTIN
BLAKE
CLARK
ADAMS
JAMES
7 rows selected.
SQL> select ename from emp where ename not like '%A%';
ENAME
---------------
SMITH
JONES
SCOTT
KING
TURNER
FORD
MILLER
7 rows selected.
SQL> select ename from emp where like '_____';
select ename from emp where like '_____'
*
ERROR at line 1:
ORA-00936: missing expression
SQL> select ename from emp where ename like '_____';
ENAME
---------------
SMITH
ALLEN
JONES
BLAKE
CLARK
SCOTT
ADAMS
JAMES
8 rows selected.
SQL> select ename from emp where ename like '____';
ENAME
---------------
WARD
KING
FORD
SQL> select ename from emp where ename like '__%';
ENAME
---------------
SMITH
ALLEN
WARD
JONES
MARTIN
BLAKE
CLARK
SCOTT
KING
TURNER
ADAMS
JAMES
FORD
MILLER
14 rows selected.
SQL> select ename from emp where ename like '_%';
ENAME
---------------
SMITH
ALLEN
WARD
JONES
MARTIN
BLAKE
CLARK
SCOTT
KING
TURNER
ADAMS
JAMES
FORD
MILLER
14 rows selected.
SQL> select ename from emp where ename like '%';
ENAME
---------------
SMITH
ALLEN
WARD
JONES
MARTIN
BLAKE
CLARK
SCOTT
KING
TURNER
ADAMS
JAMES
FORD
MILLER
14 rows selected.
SQL> select ename from emp where ename like '%_';
ENAME
---------------
SMITH
ALLEN
WARD
JONES
MARTIN
BLAKE
CLARK
SCOTT
KING
TURNER
ADAMS
JAMES
FORD
MILLER
14 rows selected.
SQL> select hiredate from emp where hiredate like '%feb%';
no rows selected
SQL> select hiredate from emp where hiredate like '%FEB%';
HIREDATE
--------------------------------
20-FEB-81 12.00.00.000000 AM
22-FEB-81 12.00.00.000000 AM
SQL> desc dept
Name Null? Type
---------------------- -------- -----
DEPTNO NUMBER(3)
DNAME VARCHAR2(15)
LOC VARCHAR2(15)
SQL> select *from dept
2 /
DEPTNO DNAME LOC
---------- --------------- ---------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATION BOSTON
SQL> select *from dept where dname like'__\_%'escape'\';
SQL> select ename,mgr,comm from emp where comm is null;
SQL> select ename,mgr,comm from emp where comm>=0
SQL> select ename,mgr,comm from emp where comm is not null;
SQL> select ename,mgr,comm from emp where comm is null;
SQL> select ename,mgr,comm from emp where comm is null;
ENAME MGR COMM
--------------- ---------- ----------
SMITH 7902
JONES 7839
BLAKE 7839
CLARK 7839
SCOTT 7566
KING
ADAMS 7788
JAMES 7698
FORD 7566
MILLER 7782
10 rows selected.
SQL> select ename,mgr,comm from emp where comm is not null;
ENAME MGR COMM
--------------- ---------- ----------
ALLEN 7698 300
WARD 7698 500
MARTIN 7698 1400
TURNER 7698 0
SQL> select ename,mgr,comm from emp where comm>=0
2 /
ENAME MGR COMM
--------------- ---------- ----------
ALLEN 7698 300
WARD 7698 500
MARTIN 7698 1400
TURNER 7698 0
SQL> rem select ename from emp;
SQL> SELECT ename,job,sal
2 FROM emp
3 ORDER BY 2
4 /
ENAME JOB SAL
--------------- --------------- ----------
SCOTT ANALYST 3000
FORD ANALYST 3000
MILLER CLERK 1300
JAMES CLERK 950
ADAMS CLERK 1100
ALLEN CLERK 1600
BLAKE MANAGER 2850
JONES MANAGER 2975
CLARK MANAGER 2450
KING PRESIDENT 5000
TURNER SALESMAN 1500
MARTIN SALESMAN 1250
WARD SALESMAN 1250
SMITH SALESMAN 800
14 rows selected.
SQL> ed
Wrote file afiedt.buf
1 SELECT ename,job,empno
2 FROM emp
3 WHERE job='MANAGER'
4* ORDER BY 3
SQL> /
ENAME JOB EMPNO
--------------- --------------- ----------
JONES MANAGER 7566
BLAKE MANAGER 7698
CLARK MANAGER 7782
SQL> ed
Wrote file afiedt.buf
1 SELECT ename,sal,empno
2 FROM emp
3* ORDER BY ename
4 /
ENAME SAL EMPNO
--------------- ---------- ----------
ADAMS 1100 7876
ALLEN 1600 7499
BLAKE 2850 7698
CLARK 2450 7782
FORD 3000 7902
JAMES 950 7900
JONES 2975 7566
KING 5000 7839
MARTIN 1250 7654
MILLER 1300 7934
SCOTT 3000 7788
SMITH 800 7369
TURNER 1500 7844
WARD 1250 7521
14 rows selected.
SQL> SELECT lower('Raju.chinthapatla') from duel;
LOWER('RAJU.CHINT
-----------------
raju.chinthapatla
SQL> SELECT lower('Raju.chinthapatla') "My Name Is" from duel;
My Name Is
-----------------
raju.chinthapatla
SQL> cl scr
SQL> SELECT lower('Raju.chinthapatla') "My Name Is" from duel;
My Name Is
-----------------
raju.chinthapatla
SQL> SELECT upper('Raju.chinthapatla') "My Name Is" from duel;
My Name Is
-----------------
RAJU.CHINTHAPATLA
SQL> SELECT initcap('RaJu chInTHaPaTla') "My Name Is" from duel;
My Name Is
-----------------
Raju Chinthapatla
SQL> SELECT ename||'is friend of' initcap('Raju.chinthapatla') "My Name Is" from duel;
SELECT ename||'is friend of' initcap('Raju.chinthapatla') "My Name Is" from duel
*
ERROR at line 1:
ORA-00923: FROM keyword not found where expected
SQL> SELECT name||'is friend of' initcap('Raju.chinthapatla') "My Name Is" from duel;
SELECT name||'is friend of' initcap('Raju.chinthapatla') "My Name Is" from duel
*
ERROR at line 1:
ORA-00923: FROM keyword not found where expected
SQL> desc duel
Name Null? Type
----------------------------------------------------------------------------------- -------- --------------------------------------------------------
ENAME VARCHAR2(15)
DEPTNO NUMBER(3)
SQL> select ename||'snsdsd'|| from emp;
select ename||'snsdsd'|| from emp
*
ERROR at line 1:
ORA-00936: missing expression
SQL> select ename||'snsdsd'|| from duel;
select ename||'snsdsd'|| from duel
*
ERROR at line 1:
ORA-00936: missing expression
SQL> select ename||deptno from duel;
ENAME||DEPTNO
-------------------------------------------------------
ALLEN30
SQL> cl scr
SQL> select 'The Basic Salary of '||ename||' is Rs: ' ||sal "designation" from emp;
designation
-----------------------------------------------------------------------------------
The Basic Salary of SMITH is Rs: 800
The Basic Salary of ALLEN is Rs: 1600
The Basic Salary of WARD is Rs: 1250
The Basic Salary of JONES is Rs: 2975
The Basic Salary of MARTIN is Rs: 1250
The Basic Salary of BLAKE is Rs: 2850
The Basic Salary of CLARK is Rs: 2450
The Basic Salary of SCOTT is Rs: 3000
The Basic Salary of KING is Rs: 5000
The Basic Salary of TURNER is Rs: 1500
The Basic Salary of ADAMS is Rs: 1100
The Basic Salary of JAMES is Rs: 950
The Basic Salary of FORD is Rs: 3000
The Basic Salary of MILLER is Rs: 1300
14 rows selected.
SQL> var name varchar2(20)
SQL> exec:name:='raju.chinthapatla'
PL/SQL procedure successfully completed.
SQL> print:exec
SP2-0552: Bind variable "EXEC" not declared.
SQL> print:name
NAME
--------------------------------
raju.chinthapatla
SQL> select initcap(:name) "My Name Is" from duel;
My Name Is
--------------------------------
Raju.Chinthapatla
1 row selected.
SQL> spool off
SQL> select concat('Raju','.Chinthapatla') "My Name Is" from duel;
My Name Is
-----------------
Raju.Chinthapatla
1 row selected.
SQL> select concat(concat(concat('Raju','.Chinthapatla'),' From'),'Warangal') "My Name Is" from duel;
My Name Is
------------------------------
Raju.Chinthapatla FromWarangal
1 row selected.
SQL> select concat(concat('Raju','.Chinthapatla'),concat('From','Warangal')) "My Name Is" from duel;
My Name Is
-----------------------------
Raju.ChinthapatlaFromWarangal
1 row selected.
SQL> spool off
SQL> SELECT SubStr('Shiva_Rama_Krishna',0) FROM d;
SUBSTR('SHIVA_RAMA
------------------
Shiva_Rama_Krishna
SQL> SELECT SubStr('Shiva_Rama_Krishna',11) FROM d;
SUBSTR('
--------
_Krishna
SQL> SELECT SubStr('Shiva_Rama_Krishna',122) FROM d;
S
-
SQL> SELECT SubStr('Shiva_Rama_Krishna',3,11) FROM d;
SUBSTR('SHI
-----------
iva_Rama_Kr
SQL> SELECT SubStr('Shiva_Rama_Krishna',9,-1) FROM d;
S
-
SQL> SELECT SubStr('Shiva_Rama_Krishna',-4,6) FROM d;
SUBS
----
shna
SQL> SELECT ename,job,substr(job,6) FROM emp WHERE substr(job,4,2)=upper('es');
ENAME JOB SUBSTR(JOB
--------------- --------------- ----------
SMITH SALESMAN MAN
WARD SALESMAN MAN
MARTIN SALESMAN MAN
TURNER SALESMAN MAN
SQL> SELECT Concat(InitCap(ename),Concat(' is a ',Concat(SubStr(job,1,3),' Eater'))) FROM emp WHERE SubStr(job,4,3)=Upper('age');
CONCAT(INITCAP(ENAME),CONCAT('
------------------------------
Jones is a MAN Eater
Blake is a MAN Eater
Clark is a MAN Eater
SQL> SELECT Length('Raju.chinthapatla') "My NAME Length is" FROM d;
My NAME Length is
-----------------
17
SQL> --> T0_CHAR NUMBER CONVERTION <--
SQL>
SQL> --Digit Maker
SQL> SELECT 12.34,12,To_Char(12.34-12,'009900'),To_Char(12-12.34,'009900') FROM d;
12.34 12 TO_CHAR TO_CHAR
---------- ---------- ------- -------
12.34 12 000000 -000000
SQL> SELECT 12.55,12,To_Char(12.55-12,'009900'),To_Char(12-12.55,'009900') FROM d;
12.55 12 TO_CHAR TO_CHAR
---------- ---------- ------- -------
12.55 12 000001 -000001
SQL>
SQL> --> T0_CHAR DATE CONVERTION <--
SQL> SELECT 123456789,
2 To_Char(123456789,'L99G99G99G999D99')"Currency,GROUP,Decimal",
3 To_Char(123456789,'$99,99,99,999.99')"Currency,GROUP,DECIMAL SYMBOLS",
4 To_Char(123456789,'xxxxxxxxx')"HexaDecimals",
5 To_Char(000056789,'000099999')"Zero Indicator"
6 FROM d;
123456789 Currency,GROUP,Decimal Currency,GROUP,DE HexaDecima Zero Indic
---------- -------------------------- ----------------- ---------- ----------
123456789 $12,34,56,789.00 $12,34,56,789.00 75bcd15 000056789
SQL>
SQL> SELECT comm,sal,comm-sal,
2 To_Char(comm-sal,'99,999.99mi')," MINUS "
3 To_Char(comm-sal,'99,999.99pr')"Nagative"
4 FROM e;
To_Char(comm-sal,'99,999.99pr')"Nagative"
*
ERROR at line 3:
ORA-00923: FROM keyword not found where expected
SQL>
SQL> -->SIGN
SQL> SELECT comm,sal,comm-sal,To_Char(comm-sal,'s99,999.99') FROM e;
COMM SAL COMM-SAL TO_CHAR(CO
---------- ---------- ---------- ----------
800
300 1600 -1300 -1,300.00
500 1250 -750 -750.00
2975
1400 1250 150 +150.00
2850
2450
3000
5000
0 1500 -1500 -1,500.00
1100
950
3000
1300
14 rows selected.
SQL>
SQL> --Digit Maker
SQL> SELECT 12.34,12,To_Char(12.34-12,'009900'),To_Char(12-12.34,'009900') FROM d;
12.34 12 TO_CHAR TO_CHAR
---------- ---------- ------- -------
12.34 12 000000 -000000
SQL> SELECT 12.55,12,To_Char(12.55-12,'009900'),To_Char(12-12.55,'009900') FROM d;
12.55 12 TO_CHAR TO_CHAR
---------- ---------- ------- -------
12.55 12 000001 -000001
SQL>
SQL> --> T0_CHAR DATE CONVERTION <--
SQL>
SQL> -->A.D. OR B.C.//AD OR BC
SQL> SELECT hiredate,
2 To_Char(hiredate,'b.c.')"Before Crist",
3 To_Char(hiredate,'ad')"After Death",
4 To_Char(hiredate,'a.d.')"After Death",
5 To_Char(sysdate,'AM')"AM",
6 To_Char(sysdate,'PM')"PM"
7 FROM e;
HIREDATE Befo Af Afte AM PM
--------------------------------------------------------------------------- ---- -- ---- -- --
17-DEC-80 12.00.00.000000 AM a.d. ad a.d. AM AM
20-FEB-81 12.00.00.000000 AM a.d. ad a.d. AM AM
22-FEB-81 12.00.00.000000 AM a.d. ad a.d. AM AM
02-APR-81 12.00.00.000000 AM a.d. ad a.d. AM AM
28-SEP-81 12.00.00.000000 AM a.d. ad a.d. AM AM
01-MAY-81 12.00.00.000000 AM a.d. ad a.d. AM AM
09-JUN-81 12.00.00.000000 AM a.d. ad a.d. AM AM
09-DEC-82 12.00.00.000000 AM a.d. ad a.d. AM AM
17-NOV-81 12.00.00.000000 AM a.d. ad a.d. AM AM
08-SEP-81 12.00.00.000000 AM a.d. ad a.d. AM AM
12-JAN-83 12.00.00.000000 AM a.d. ad a.d. AM AM
03-DEC-81 12.00.00.000000 AM a.d. ad a.d. AM AM
03-DEC-81 12.00.00.000000 AM a.d. ad a.d. AM AM
23-JAN-82 12.00.00.000000 AM a.d. ad a.d. AM AM
14 rows selected.
SQL>
SQL> SELECT sysdate,
2 To_Char(sysdate,'cc-ad')"CENTURY",
3 To_Char(sysdate,'d')"DAy IN WEEK",
4 To_Char(sysdate,'dd')"DAY IN MONTH",
5 To_Char(sysdate,'ddd')"DAY IN YEAR",
6 To_Char(sysdate,'dddd')"DAY In YEAR and IN WEEK",
7 To_Char(sysdate,'DY')"DAY SPELL(sun)",
8 To_Char(sysdate,'DAY')"DAY SPELL",
9 To_Char(sysdate,'W')"WEEK OF THIS MONTH",
10 To_Char(sysdate,'IW')"(WI)WEEK OF THIS YEAR",
11 To_Char(sysdate,'WW')"(WW)WEEK OF THIS YEAR",
12 To_Char(sysdate,'WWW')"WEEK OF THIS YEAR AND MONTH",
13 To_Char(sysdate,'MM')"MONTH NO",
14 To_Char(sysdate,'MON')"MONTH SPELL(jan)",
15 To_Char(sysdate,'MONTH')"MONTH SPELL",
16 To_Char(sysdate,'y')"LAST DIGIT OF THE YEAR",
17 To_Char(sysdate,'yy')"LAST 2 DIGIT OF THE YEAR",
18 To_Char(sysdate,'yyy')"LAST 3 DIGIT OF THE YEAR",
19 To_Char(sysdate,'yyyy')"4 DIGIT OF THE YEAR",
20 To_Char(sysdate,'YYYYSP')"YEAR SPELL",
21 To_Char(sysdate,'YEAR')"YEAR SPELL",
22 To_Char(sysdate,'DD-MM-YYYY')"DATE",
23 To_Char(sysdate,'DD-MON-YYYY')"DATE",
24 To_Char(sysdate,'DD-RM-YYYY')"ROMAN NUMBER",
25 To_Char(sysdate,'Q')"QUARTER OF THE YEAR",
26 To_Char(sysdate,'J')"JULLIAN",
27 To_Char(sysdate,'HH-MI-SS:AM')"12 HOURS TIME",
28 To_Char(sysdate,'HH24-MI-SS:AM')"24 HOURS TIME",
29 To_Char(sysdate,'DDth-DDthsp')"DAY NUMBER SPELL",
30 To_Char(SYSDATE,'DDthsp-MONTH-YYYYsp')"DATE SPELL",
31 To_Char(SYSDATE,'fmDDthsp-MONTH-YYYYsp')"REMOVE THE BLANK SPASES"
32 FROM d;
SYSDATE CENTU D DA DAY DAY DAY DAY SPELL W (W (W WEE MO MON MONTH SPE L LA LAS 4 DI YEAR SPELL YEAR SPELL DATE DATE ROMAN NUMBER Q JULLIAN 12 HOURS TI 24 HOURS TI DAY NUMBER SPELL DATE SPELL REMOVE THE BLANK SPASES
--------- ----- - -- --- ---- --- --------- - -- -- --- -- --- --------- - -- --- ---- ----------------- ----------- ---------- ----------- ------------ -- ------- --- ------- ---- ------ ------ - ------- --------------------------------- --------------------------------
03-FEB-10 21-ad 4 03 034 0344 WED WEDNESDAY 1 05 05 051 02 FEB FEBRUARY 0 10 010 2010 TWO THOUSAND TEN TWENTY TEN 03-02-2010 03-FEB-2010 03-II -2010 1 2455231 12-08-17:AM 00-08-17:AM 03RD-THIRD THIRD-FEBRUARY -TWO THOUSAND TEN THIRD-FEBRUARY-TWO THOUSAND TEN
SQL> SELECT 'raju.chinthapatla',translate('raju.chinthapatla','abcdefghijklmnopqrstuvwxyz.','1234567890!@#$%^&*()-=_+:,<>')" Encrypted NAME " FROM d;
'RAJU.CHINTHAPATL Encrypted NAME
----------------- -----------------
raju.chinthapatla *10-<389$)81^1)@1
SELECT '*10-.389$)81^1)@1'" Encrypted NAME ",Translate('*10-<389$)81^1)@1','1234567890!@#$%^&*()-=_+:,<>','abcdefghijklmnopqrstuvwxyz.')" Encrypted NAME " FROM d;
Encrypted NAME Encrypted NAME
----------------- -----------------
*10-.389$)81^1)@1 raju.chinthapatla
SQL> --> HAVING CLAUS
SQL> SELECT deptno,Sum(sal),Min(sal),Max(sal) FROM e WHERE job='CLERK' GROUP BY deptno HAVING min(sal)<1000;
DEPTNO SUM(SAL) MIN(SAL) MAX(SAL)
---------- ---------- ---------- ----------
30 2550 950 1600
SQL> SELECT deptno,Sum(sal),Min(sal),Max(sal) FROM e GROUP BY deptno HAVING Count(deptno)>=1;
DEPTNO SUM(SAL) MIN(SAL) MAX(SAL)
---------- ---------- ---------- ----------
30 9400 950 2850
20 10875 800 3000
10 8750 1300 5000
SQL> SELECT deptno,Count(deptno) FROM e GROUP BY deptno HAVING Count(deptno)>3;
DEPTNO COUNT(DEPTNO)
---------- -------------
30 6
20 5
SQL>
--> NESTING OF GROUP FUNCTIONS
SQL> SELECT sum(min(sal)) FROM e GROUP BY sal;
SUM(MIN(SAL))
-------------
24775
SQL>
SQL> --> MISCELLANEOUS FUNCTION:
SQL>
SQL> --> GREATEST and LEAST FUNCTION:-
SQL> SELECT greatest('D','f','e','f','d','e','f','V','h','V','r') from d;
G
-
r
SQL> SELECT least('D','f','e','f','d','e','f','V','h','V','r') from d;
L
-
D
SQL>
SQL> SELECT USER,UID FROM d;
USER UID
------------------------------ ----------
A 36
SQL> SELECT username,user_id FROM all_users;
USERNAME USER_ID
------------------------------ ----------
A 36
FLOWS_020100 35
FLOWS_FILES 34
HR 33
MDSYS 32
ANONYMOUS 28
XDB 27
CTXSYS 25
DBSNMP 23
TSMSYS 20
DIP 18
USERNAME USER_ID
------------------------------ ----------
OUTLN 11
SYSTEM 5
SYS 0
14 rows selected.
SQL> SELECT UserEnv('isdba') FROM d;
USEREN
------
FALSE
SQL> SELECT UserEnv('language') FROM d;
USERENV('LANGUAGE')
----------------------------------------------------
AMERICAN_AMERICA.WE8MSWIN1252
SQL> SELECT UserEnv('terminal') FROM d;
USERENV('TERMINA
----------------
ANONYMOUS
SQL> SELECT UserEnv('sessionid') FROM d;
USERENV('SESSIONID')
--------------------
202
SQL> SELECT UserEnv('lang') FROM d;
USERENV('LANG')
----------------------------------------------------
US
SQL> SELECT UserEnv('instance') FROM d;
USERENV('INSTANCE')
-------------------
1
SQL> SELECT INSTANCE_name FROM v$instance;
SELECT INSTANCE_name FROM v$instance
*
ERROR at line 1:
ORA-00942: table or view does not exist
------------------->>>>>>>>>> JOINS <<<<<<<<------------------
SQL> --> CORTITION JOIN
SQL> SELECT ename ,dname,loc,e.deptno,empno,sal,grade,losal,mgr FROM dept d,emp e,salgrade s;
ENAME DNAME LOC DEPTNO EMPNO SAL
--------------- --------------- --------------- ---------- ---------- ----------
GRADE LOSAL MGR
---------- ---------- ----------
SMITH ACCOUNTING NEW YORK 20 7369 800
1 700 7902
ALLEN ACCOUNTING NEW YORK 30 7499 1600
1 700 7698
---- - ------- - ---- -- - - - - - - - - - - - - - - - - - - - - -
280 rows selected.
SQL>
SQL> --> EQUI JOIN
SQL> SELECT ename ,d.deptno, dname, job,loc FROM emp e,dept d WHERE e.deptno=d.deptno;
ENAME DEPTNO DNAME JOB LOC
--------------- ---------- --------------- --------------- ---------------
SMITH 20 RESEARCH SALESMAN DALLAS
ALLEN 30 SALES CLERK CHICAGO
WARD 30 SALES SALESMAN CHICAGO
JONES 20 RESEARCH MANAGER DALLAS
MARTIN 30 SALES SALESMAN CHICAGO
BLAKE 30 SALES MANAGER CHICAGO
CLARK 10 ACCOUNTING MANAGER NEW YORK
SCOTT 20 RESEARCH ANALYST DALLAS
KING 10 ACCOUNTING PRESIDENT NEW YORK
TURNER 30 SALES SALESMAN CHICAGO
ADAMS 20 RESEARCH CLERK DALLAS
JAMES 30 SALES CLERK CHICAGO
FORD 20 RESEARCH ANALYST DALLAS
MILLER 10 ACCOUNTING CLERK NEW YORK
14 rows selected.
SQL> --> SEIF JOIN
SQL> select e.ename,m.ename,e.mgr,m.mgr from emp e,emp m where e.empno=m.mgr;
ENAME ENAME MGR MGR
--------------- --------------- ---------- ----------
JONES FORD 7839 7566
JONES SCOTT 7839 7566
BLAKE JAMES 7839 7698
BLAKE TURNER 7839 7698
BLAKE MARTIN 7839 7698
BLAKE WARD 7839 7698
BLAKE ALLEN 7839 7698
CLARK MILLER 7839 7782
SCOTT ADAMS 7566 7788
KING CLARK 7839
KING BLAKE 7839
KING JONES 7839
FORD SMITH 7566 7902
13 rows selected.
SQL>
SQL> --> RIGHT//LEFT OUTR JOIN
SQL> select e.ename,d.deptno,e.mgr from emp e,dept d where e.deptno=d.deptno(+);
ENAME DEPTNO MGR
--------------- ---------- ----------
MILLER 10 7782
KING 10
CLARK 10 7839
FORD 20 7566
ADAMS 20 7788
SCOTT 20 7566
JONES 20 7839
SMITH 20 7902
JAMES 30 7698
TURNER 30 7698
BLAKE 30 7839
MARTIN 30 7698
WARD 30 7698
ALLEN 30 7698
14 rows selected.
SQL> select e.ename,d.deptno,e.mgr from emp e,dept d where e.deptno(+)=d.deptno;
ENAME DEPTNO MGR
--------------- ---------- ----------
SMITH 20 7902
ALLEN 30 7698
WARD 30 7698
JONES 20 7839
MARTIN 30 7698
BLAKE 30 7839
CLARK 10 7839
SCOTT 20 7566
KING 10
TURNER 30 7698
ADAMS 20 7788
JAMES 30 7698
FORD 20 7566
MILLER 10 7782
40
15 rows selected.
SQL> select e.ename,d.deptno,e.mgr from emp e,dept d where e.deptno=d.deptno(+) ORDER BY deptno;
ENAME DEPTNO MGR
--------------- ---------- ----------
CLARK 10 7839
KING 10
MILLER 10 7782
SCOTT 20 7566
ADAMS 20 7788
FORD 20 7566
JONES 20 7839
SMITH 20 7902
JAMES 30 7698
TURNER 30 7698
BLAKE 30 7839
MARTIN 30 7698
WARD 30 7698
ALLEN 30 7698
14 rows selected.
SQL> SELECT e.ename,Nvl(m.ename,'supreme authority') FROM emp e,emp m WHERE e.mgr(+) =m.empno;
ENAME NVL(M.ENAME,'SUPR
--------------- -----------------
SMITH FORD
ALLEN BLAKE
WARD BLAKE
JONES KING
MARTIN BLAKE
BLAKE KING
CLARK KING
SCOTT JONES
TURNER BLAKE
ADAMS SCOTT
JAMES BLAKE
FORD JONES
MILLER CLARK
TURNER
WARD
MARTIN
ALLEN
MILLER
SMITH
ADAMS
JAMES
21 rows selected.
SQL>
SQL> --> JOIN MORE THAN ONE TABLES
SQL> SELECT e.ename,m.ename "manager name",d.dname "department name",e.sal "Employe Salary",se.grade "Employee Grade",m.sal "Manager Sal",sm.grade "Manager sal"
2 FROM emp e,dept d,emp m,salgrade se,salgrade sm
3 WHERE (e.deptno=d.deptno) AND (e.mgr=m.empno) AND (e.sal between se.losal AND se.hisal) AND (m.sal BETWEEN sm.losal AND sm.hisal);
ENAME manager name department name Employe Salary Employee Grade
--------------- --------------- --------------- -------------- --------------
Manager Sal Manager sal
----------- -----------
SMITH FORD RESEARCH 800 1
3000 4
ALLEN BLAKE SALES 1600 3
2850 4
WARD BLAKE SALES 1250 2
2850 4
JONES KING RESEARCH 2975 4
5000 5
MARTIN BLAKE SALES 1250 2
2850 4
BLAKE KING SALES 2850 4
5000 5
CLARK KING ACCOUNTING 2450 4
5000 5
SCOTT JONES RESEARCH 3000 4
2975 4
TURNER BLAKE SALES 1500 3
2850 4
ADAMS SCOTT RESEARCH 1100 1
3000 4
JAMES BLAKE SALES 950 1
2850 4
FORD JONES RESEARCH 3000 4
2975 4
MILLER CLARK ACCOUNTING 1300 2
2450 4
13 rows selected.
SQL>
SQL> -->JOIN MORE THAN ONE TABLE
SQL> SELECT e.ename,m.ename "manager name",d.dname "department name",e.sal "Employe Salary",se.grade "Employee Grade",m.sal "Manager Sal",sm.grade "Manager sal" FROM emp e,dept d,emp m,salgrade se,salgrade sm WHERE (e.deptno=d.deptno) AND (e.mgr=m.empno) AND (e.sal between se.losal AND se.hisal) AND (m.sal BETWEEN sm.losal AND sm.hisal);
ENAME manager name department name Employe Salary Employee Grade
--------------- --------------- --------------- -------------- --------------
Manager Sal Manager sal
----------- -----------
SMITH FORD RESEARCH 800 1
3000 4
ALLEN BLAKE SALES 1600 3
2850 4
WARD BLAKE SALES 1250 2
2850 4
JONES KING RESEARCH 2975 4
5000 5
MARTIN BLAKE SALES 1250 2
2850 4
BLAKE KING SALES 2850 4
5000 5
CLARK KING ACCOUNTING 2450 4
5000 5
SCOTT JONES RESEARCH 3000 4
2975 4
TURNER BLAKE SALES 1500 3
2850 4
ADAMS SCOTT RESEARCH 1100 1
3000 4
JAMES BLAKE SALES 950 1
2850 4
FORD JONES RESEARCH 3000 4
2975 4
MILLER CLARK ACCOUNTING 1300 2
2450 4
13 rows selected.
SQL> -->NATURAL JOIN
SQL> SELECT ename,deptno,empno,dname,sal,loc FROM emp NATURAL join dept;
ENAME DEPTNO EMPNO DNAME SAL LOC
--------------- ---------- ---------- --------------- ---------- ---------------
SMITH 20 7369 RESEARCH 800 DALLAS
ALLEN 30 7499 SALES 1600 CHICAGO
WARD 30 7521 SALES 1250 CHICAGO
JONES 20 7566 RESEARCH 2975 DALLAS
MARTIN 30 7654 SALES 1250 CHICAGO
BLAKE 30 7698 SALES 2850 CHICAGO
CLARK 10 7782 ACCOUNTING 2450 NEW YORK
SCOTT 20 7788 RESEARCH 3000 DALLAS
KING 10 7839 ACCOUNTING 5000 NEW YORK
TURNER 30 7844 SALES 1500 CHICAGO
ADAMS 20 7876 RESEARCH 1100 DALLAS
JAMES 30 7900 SALES 950 CHICAGO
FORD 20 7902 RESEARCH 3000 DALLAS
MILLER 10 7934 ACCOUNTING 1300 NEW YORK
14 rows selected.
SQL> SELECT ename,deptno,empno,sal,grade FROM emp NATURAL join salgrade ;
ENAME DEPTNO EMPNO SAL GRADE
--------------- ---------- ---------- ---------- ----------
SMITH 20 7369 800 1
ALLEN 30 7499 1600 1
WARD 30 7521 1250 1
JONES 20 7566 2975 1
MARTIN 30 7654 1250 1
BLAKE 30 7698 2850 1
CLARK 10 7782 2450 1
SCOTT 20 7788 3000 1
KING 10 7839 5000 1
TURNER 30 7844 1500 1
ADAMS 20 7876 1100 1
JAMES 30 7900 950 1
FORD 20 7902 3000 1
MILLER 10 7934 1300 1
SMITH 20 7369 800 2
ALLEN 30 7499 1600 2
WARD 30 7521 1250 2
JONES 20 7566 2975 2
MARTIN 30 7654 1250 2
BLAKE 30 7698 2850 2
CLARK 10 7782 2450 2
SCOTT 20 7788 3000 2
KING 10 7839 5000 2
TURNER 30 7844 1500 2
ADAMS 20 7876 1100 2
JAMES 30 7900 950 2
FORD 20 7902 3000 2
MILLER 10 7934 1300 2
SMITH 20 7369 800 3
ALLEN 30 7499 1600 3
WARD 30 7521 1250 3
JONES 20 7566 2975 3
MARTIN 30 7654 1250 3
BLAKE 30 7698 2850 3
CLARK 10 7782 2450 3
SCOTT 20 7788 3000 3
KING 10 7839 5000 3
TURNER 30 7844 1500 3
ADAMS 20 7876 1100 3
JAMES 30 7900 950 3
FORD 20 7902 3000 3
MILLER 10 7934 1300 3
SMITH 20 7369 800 4
ALLEN 30 7499 1600 4
WARD 30 7521 1250 4
JONES 20 7566 2975 4
MARTIN 30 7654 1250 4
BLAKE 30 7698 2850 4
CLARK 10 7782 2450 4
SCOTT 20 7788 3000 4
KING 10 7839 5000 4
TURNER 30 7844 1500 4
ADAMS 20 7876 1100 4
JAMES 30 7900 950 4
FORD 20 7902 3000 4
MILLER 10 7934 1300 4
SMITH 20 7369 800 5
ALLEN 30 7499 1600 5
WARD 30 7521 1250 5
JONES 20 7566 2975 5
MARTIN 30 7654 1250 5
BLAKE 30 7698 2850 5
CLARK 10 7782 2450 5
SCOTT 20 7788 3000 5
KING 10 7839 5000 5
TURNER 30 7844 1500 5
ADAMS 20 7876 1100 5
JAMES 30 7900 950 5
FORD 20 7902 3000 5
MILLER 10 7934 1300 5
70 rows selected.
SQL>
SQL> -->USING CLAUSE
SQL> SELECT e.ename,d.dname,deptno FROM emp e join dept d USING(deptno);
ENAME DNAME DEPTNO
--------------- --------------- ----------
SMITH RESEARCH 20
ALLEN SALES 30
WARD SALES 30
JONES RESEARCH 20
MARTIN SALES 30
BLAKE SALES 30
CLARK ACCOUNTING 10
SCOTT RESEARCH 20
KING ACCOUNTING 10
TURNER SALES 30
ADAMS RESEARCH 20
JAMES SALES 30
FORD RESEARCH 20
MILLER ACCOUNTING 10
14 rows selected.
SQL>
SQL> -->INNER JOIN
SQL> SELECT e.ename,e.deptno,d.dname,loc,sal FROM emp e INNER JOIN dept d ON(d.deptno=e.deptno);
ENAME DEPTNO DNAME LOC SAL
--------------- ---------- --------------- --------------- ----------
SMITH 20 RESEARCH DALLAS 800
ALLEN 30 SALES CHICAGO 1600
WARD 30 SALES CHICAGO 1250
JONES 20 RESEARCH DALLAS 2975
MARTIN 30 SALES CHICAGO 1250
BLAKE 30 SALES CHICAGO 2850
CLARK 10 ACCOUNTING NEW YORK 2450
SCOTT 20 RESEARCH DALLAS 3000
KING 10 ACCOUNTING NEW YORK 5000
TURNER 30 SALES CHICAGO 1500
ADAMS 20 RESEARCH DALLAS 1100
JAMES 30 SALES CHICAGO 950
FORD 20 RESEARCH DALLAS 3000
MILLER 10 ACCOUNTING NEW YORK 1300
14 rows selected.
SQL> SELECT e.empno,e.ename,m.ename,e.mgr FROM emp e inner join emp m ON(e.mgr=m.empno) ORDER BY e.sal desc;
EMPNO ENAME ENAME MGR
---------- --------------- --------------- ----------
7902 FORD JONES 7566
7788 SCOTT JONES 7566
7566 JONES KING 7839
7698 BLAKE KING 7839
7782 CLARK KING 7839
7499 ALLEN BLAKE 7698
7844 TURNER BLAKE 7698
7934 MILLER CLARK 7782
7654 MARTIN BLAKE 7698
7521 WARD BLAKE 7698
7876 ADAMS SCOTT 7788
7900 JAMES BLAKE 7698
7369 SMITH FORD 7902
13 rows selected.
SQL> SELECT e.ename,job,d.deptno,d.dname,e.sal,grade,losal,hisal FROM emp e inner join dept d ON(e.deptno=d.deptno) inner join salgrade ON(e.sal BETWEEN losal AND hisal);
ENAME JOB DEPTNO DNAME SAL GRADE
--------------- --------------- ---------- --------------- ---------- ----------
LOSAL HISAL
---------- ----------
SMITH SALESMAN 20 RESEARCH 800 1
700 1200
ALLEN CLERK 30 SALES 1600 3
1401 2000
WARD SALESMAN 30 SALES 1250 2
1201 1400
JONES MANAGER 20 RESEARCH 2975 4
2001 3000
MARTIN SALESMAN 30 SALES 1250 2
1201 1400
BLAKE MANAGER 30 SALES 2850 4
2001 3000
CLARK MANAGER 10 ACCOUNTING 2450 4
2001 3000
SCOTT ANALYST 20 RESEARCH 3000 4
2001 3000
KING PRESIDENT 10 ACCOUNTING 5000 5
3001 9999
TURNER SALESMAN 30 SALES 1500 3
1401 2000
ADAMS CLERK 20 RESEARCH 1100 1
700 1200
JAMES CLERK 30 SALES 950 1
700 1200
FORD ANALYST 20 RESEARCH 3000 4
2001 3000
MILLER CLERK 10 ACCOUNTING 1300 2
1201 1400
14 rows selected.
SQL> SELECT e.ename,m.ename "manager name",d.dname "department name",e.sal "Employe Salary",se.grade "Employee Grade",m.sal "Manager Sal",sm.grade "Manager sal" FROM emp e inner join dept d on(e.deptno=d.deptno) inner join emp m on(e.mgr=m.empno) inner join salgrade se on(e.sal between se.losal AND se.hisal) inner join salgrade sm on(m.sal BETWEEN sm.losal AND sm.hisal);
ENAME manager name department name Employe Salary Employee Grade
--------------- --------------- --------------- -------------- --------------
Manager Sal Manager sal
----------- -----------
SMITH FORD RESEARCH 800 1
3000 4
ALLEN BLAKE SALES 1600 3
2850 4
WARD BLAKE SALES 1250 2
2850 4
JONES KING RESEARCH 2975 4
5000 5
MARTIN BLAKE SALES 1250 2
2850 4
BLAKE KING SALES 2850 4
5000 5
CLARK KING ACCOUNTING 2450 4
5000 5
SCOTT JONES RESEARCH 3000 4
2975 4
TURNER BLAKE SALES 1500 3
2850 4
ADAMS SCOTT RESEARCH 1100 1
3000 4
JAMES BLAKE SALES 950 1
2850 4
FORD JONES RESEARCH 3000 4
2975 4
MILLER CLARK ACCOUNTING 1300 2
2450 4
13 rows selected.
SQL>
SQL> -->LEFT//RIGHT//FULL OUTER JOIN
SQL> SELECT e.ename,e.mgr,d.dname,d.deptno FROM emp e LEFT OUTER JOIN dept d ON(e.deptno=d.deptno);
ENAME MGR DNAME DEPTNO
--------------- ---------- --------------- ----------
MILLER 7782 ACCOUNTING 10
KING ACCOUNTING 10
CLARK 7839 ACCOUNTING 10
FORD 7566 RESEARCH 20
ADAMS 7788 RESEARCH 20
SCOTT 7566 RESEARCH 20
JONES 7839 RESEARCH 20
SMITH 7902 RESEARCH 20
JAMES 7698 SALES 30
TURNER 7698 SALES 30
BLAKE 7839 SALES 30
MARTIN 7698 SALES 30
WARD 7698 SALES 30
ALLEN 7698 SALES 30
14 rows selected.
SQL> SELECT e.ename,e.mgr,d.dname,d.deptno FROM emp e right OUTER JOIN dept d ON(e.deptno=d.deptno);
ENAME MGR DNAME DEPTNO
--------------- ---------- --------------- ----------
SMITH 7902 RESEARCH 20
ALLEN 7698 SALES 30
WARD 7698 SALES 30
JONES 7839 RESEARCH 20
MARTIN 7698 SALES 30
BLAKE 7839 SALES 30
CLARK 7839 ACCOUNTING 10
SCOTT 7566 RESEARCH 20
KING ACCOUNTING 10
TURNER 7698 SALES 30
ADAMS 7788 RESEARCH 20
JAMES 7698 SALES 30
FORD 7566 RESEARCH 20
MILLER 7782 ACCOUNTING 10
OPERATION 40
15 rows selected.
SQL> SELECT e.ename,e.mgr,d.dname,d.deptno FROM emp e full OUTER JOIN dept d ON(e.deptno=d.deptno);
ENAME MGR DNAME DEPTNO
--------------- ---------- --------------- ----------
MILLER 7782 ACCOUNTING 10
KING ACCOUNTING 10
CLARK 7839 ACCOUNTING 10
FORD 7566 RESEARCH 20
ADAMS 7788 RESEARCH 20
SCOTT 7566 RESEARCH 20
JONES 7839 RESEARCH 20
SMITH 7902 RESEARCH 20
JAMES 7698 SALES 30
TURNER 7698 SALES 30
BLAKE 7839 SALES 30
MARTIN 7698 SALES 30
WARD 7698 SALES 30
ALLEN 7698 SALES 30
OPERATION 40
15 rows selected.
SQL> SPOOL OFF
--------->>>>>>>>>>><<<<<<<<<>>>>>>>>>><<<<<<<<<<------------
SQL> SELECT deptno,job FROM e ORDER BY deptno;
DEPTNO JOB
---------- ---------------
10 MANAGER
10 PRESIDENT
10 CLERK
20 MANAGER
20 ANALYST
20 CLERK
20 SALESMAN
20 ANALYST
30 SALESMAN
30 SALESMAN
30 CLERK
30 CLERK
30 MANAGER
30 SALESMAN
14 rows selected.
SQL> SELECT deptno,job FROM e WHERE deptno=10 UNION ALL SELECT deptno,job FROM e WHERE deptno=30;
DEPTNO JOB
---------- ---------------
10 MANAGER
10 PRESIDENT
10 CLERK
30 CLERK
30 SALESMAN
30 SALESMAN
30 MANAGER
30 SALESMAN
30 CLERK
9 rows selected.
SQL> SELECT deptno,job FROM e WHERE deptno=30 MINUS SELECT deptno,job FROM e WHERE deptno=20 UNION ALL SELECT deptno,job FROM e WHERE deptno=30;
DEPTNO JOB
---------- ---------------
30 CLERK
30 MANAGER
30 SALESMAN
30 CLERK
30 SALESMAN
30 SALESMAN
30 MANAGER
30 SALESMAN
30 CLERK
9 rows selected.
-------------------------->>>>>>>>>> VIEWS <<<<<<<<<---------------------------
SQL> CREATE VIEW e1 AS SELECT empno "Emp Id", ename Name,sal Salary FROM e;
CREATE VIEW e1 AS SELECT empno "Emp Id", ename Name,sal Salary FROM e
*
ERROR at line 1:
ORA-00955: name is already used by an existing object
SQL> CREATE OR REPLACE VIEW e1 AS SELECT empno "Emp Id", ename Name,sal Salary,job FROM e;
View created.
SQL> insert into e1 values(1001,'raju',4000,'sales');
1 row created.
SQL> SELECT * FROM e;
EMPNO ENAME JOB MGR
---------- --------------- --------------- ----------
HIREDATE SAL COMM
--------------------------------------------------------------------------- ---------- ----------
DEPTNO
----------
7369 SMITH SALESMAN 7902
17-DEC-80 12.00.00.000000 AM 800
20
7499 ALLEN CLERK 7698
20-FEB-81 12.00.00.000000 AM 1600 300
30
7521 WARD SALESMAN 7698
22-FEB-81 12.00.00.000000 AM 1250 500
30
7566 JONES MANAGER 7839
02-APR-81 12.00.00.000000 AM 2975
20
7654 MARTIN SALESMAN 7698
28-SEP-81 12.00.00.000000 AM 1250 1400
30
7698 BLAKE MANAGER 7839
01-MAY-81 12.00.00.000000 AM 2850
30
7782 CLARK MANAGER 7839
09-JUN-81 12.00.00.000000 AM 2450
10
7788 SCOTT ANALYST 7566
09-DEC-82 12.00.00.000000 AM 3000
20
7839 KING PRESIDENT
17-NOV-81 12.00.00.000000 AM 5000
10
7844 TURNER SALESMAN 7698
08-SEP-81 12.00.00.000000 AM 1500 0
30
7876 ADAMS CLERK 7788
12-JAN-83 12.00.00.000000 AM 1100
20
7900 JAMES CLERK 7698
03-DEC-81 12.00.00.000000 AM 950
30
7902 FORD ANALYST 7566
03-DEC-81 12.00.00.000000 AM 3000
20
7934 MILLER CLERK 7782
23-JAN-82 12.00.00.000000 AM 1300
10
1001 raju sales
4000
15 rows selected.
SQL> SELECT * FROM e1;
Emp Id NAME SALARY JOB
---------- --------------- ---------- ---------------
7369 SMITH 800 SALESMAN
7499 ALLEN 1600 CLERK
7521 WARD 1250 SALESMAN
7566 JONES 2975 MANAGER
7654 MARTIN 1250 SALESMAN
7698 BLAKE 2850 MANAGER
7782 CLARK 2450 MANAGER
7788 SCOTT 3000 ANALYST
7839 KING 5000 PRESIDENT
7844 TURNER 1500 SALESMAN
7876 ADAMS 1100 CLERK
7900 JAMES 950 CLERK
7902 FORD 3000 ANALYST
7934 MILLER 1300 CLERK
1001 raju 4000 sales
15 rows selected.
SQL>
SQL> DROP VIEW e1;
View dropped.
SQL> SELECT * FROM e;
EMPNO ENAME JOB MGR
---------- --------------- --------------- ----------
HIREDATE SAL COMM
--------------------------------------------------------------------------- ---------- ----------
DEPTNO
----------
7369 SMITH SALESMAN 7902
17-DEC-80 12.00.00.000000 AM 800
20
7499 ALLEN CLERK 7698
20-FEB-81 12.00.00.000000 AM 1600 300
30
7521 WARD SALESMAN 7698
22-FEB-81 12.00.00.000000 AM 1250 500
30
7566 JONES MANAGER 7839
02-APR-81 12.00.00.000000 AM 2975
20
7654 MARTIN SALESMAN 7698
28-SEP-81 12.00.00.000000 AM 1250 1400
30
7698 BLAKE MANAGER 7839
01-MAY-81 12.00.00.000000 AM 2850
30
7782 CLARK MANAGER 7839
09-JUN-81 12.00.00.000000 AM 2450
10
7788 SCOTT ANALYST 7566
09-DEC-82 12.00.00.000000 AM 3000
20
7839 KING PRESIDENT
17-NOV-81 12.00.00.000000 AM 5000
10
7844 TURNER SALESMAN 7698
08-SEP-81 12.00.00.000000 AM 1500 0
30
7876 ADAMS CLERK 7788
12-JAN-83 12.00.00.000000 AM 1100
20
7900 JAMES CLERK 7698
03-DEC-81 12.00.00.000000 AM 950
30
7902 FORD ANALYST 7566
03-DEC-81 12.00.00.000000 AM 3000
20
7934 MILLER CLERK 7782
23-JAN-82 12.00.00.000000 AM 1300
10
1001 raju sales
4000
15 rows selected.
SQL> SELECT * FROM e1;
SELECT * FROM e1
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL>
SQL> CREATE OR REPLACE VIEW e1 AS SELECT empno "Emp Id", ename Name,sal Salary,job FROM e;
View created.
SQL> insert into e1 values(1001,'raju',4000,'sales');
1 row created.
SQL> DELETE FROM e WHERE sal=4000;
2 rows deleted.
SQL> SELECT * FROM e;
EMPNO ENAME JOB MGR
---------- --------------- --------------- ----------
HIREDATE SAL COMM
--------------------------------------------------------------------------- ---------- ----------
DEPTNO
----------
7369 SMITH SALESMAN 7902
17-DEC-80 12.00.00.000000 AM 800
20
7499 ALLEN CLERK 7698
20-FEB-81 12.00.00.000000 AM 1600 300
30
7521 WARD SALESMAN 7698
22-FEB-81 12.00.00.000000 AM 1250 500
30
7566 JONES MANAGER 7839
02-APR-81 12.00.00.000000 AM 2975
20
7654 MARTIN SALESMAN 7698
28-SEP-81 12.00.00.000000 AM 1250 1400
30
7698 BLAKE MANAGER 7839
01-MAY-81 12.00.00.000000 AM 2850
30
7782 CLARK MANAGER 7839
09-JUN-81 12.00.00.000000 AM 2450
10
7788 SCOTT ANALYST 7566
09-DEC-82 12.00.00.000000 AM 3000
20
7839 KING PRESIDENT
17-NOV-81 12.00.00.000000 AM 5000
10
7844 TURNER SALESMAN 7698
08-SEP-81 12.00.00.000000 AM 1500 0
30
7876 ADAMS CLERK 7788
12-JAN-83 12.00.00.000000 AM 1100
20
7900 JAMES CLERK 7698
03-DEC-81 12.00.00.000000 AM 950
30
7902 FORD ANALYST 7566
03-DEC-81 12.00.00.000000 AM 3000
20
7934 MILLER CLERK 7782
23-JAN-82 12.00.00.000000 AM 1300
10
14 rows selected.
SQL> SELECT * FROM e1;
Emp Id NAME SALARY JOB
---------- --------------- ---------- ---------------
7369 SMITH 800 SALESMAN
7499 ALLEN 1600 CLERK
7521 WARD 1250 SALESMAN
7566 JONES 2975 MANAGER
7654 MARTIN 1250 SALESMAN
7698 BLAKE 2850 MANAGER
7782 CLARK 2450 MANAGER
7788 SCOTT 3000 ANALYST
7839 KING 5000 PRESIDENT
7844 TURNER 1500 SALESMAN
7876 ADAMS 1100 CLERK
7900 JAMES 950 CLERK
7902 FORD 3000 ANALYST
7934 MILLER 1300 CLERK
14 rows selected.
No comments:
Post a Comment