what are the different types of languages present in sql ?
data definition language(ddl)--create,alter,rename,drop,truncate,purge,flashback(auto commit is on)
data maipulation language(dml)--insert,update,delete,merge(auto commit is off)
data control language(dcl)---grant,revoke
transaction control language(tcl)----commit(save),rollback(undo),savepoint
data query/retriew language(drl)---select
create table table_name
(column_1 datatype_1,
column_1 datatype_1,
column_2 datatype_2,
column_3 datatype_3,
.
.
.
.
column_n datatype_n);
ex:
create table demo
(sno number(4),
name varchar2(15),
college char(10),
sal number(6,2),
doj date,
fees number(10))
SQL> desc demo
Name Null? Type
-------------------------------------------------------------------------------------- -------- -----------------------------
SNO NUMBER(4)
NAME VARCHAR2(15)
COLLEGE CHAR(10)
SAL NUMBER(6,2)
DOJ DATE
FEES NUMBER(10)
data types:
----------
number(size) ex:number(38),number(p,s) ex:number(6,2)--1111.22(,111111(wrong)
char(size) --char(5)--abc
varchar2(size) varchar2(15)--abc
date default date format is DD-MON-YY ex:SYSDATE
---------
21-FEB-13
alter table table_name modify column_name datatype;
SQL> alter table demo modify fees number(5);
Table altered.
SQL> desc demo
Name Null? Type
-------------------------------------------------------------------------------------- -------- -----------------------------
SNO NUMBER(4)
NAME VARCHAR2(15)
COLLEGE CHAR(10)
SAL NUMBER(6,2)
DOJ DATE
FEES NUMBER(5)
SQL> alter table demo add address varchar2(30);
Table altered.
SQL> desc demo
Name Null? Type
-------------------------------------------------------------------------------------- -------- -----------------------------
SNO NUMBER(4)
NAME VARCHAR2(15)
COLLEGE CHAR(10)
SAL NUMBER(6,2)
DOJ DATE
FEES NUMBER(5)
ADDRESS VARCHAR2(30)
to drop a column
alter table table_name drop column column_name;
ex:
SQL> alter table demo drop column address;
Table altered.
SQL> desc demo
Name Null? Type
-------------------------------------------------------------------------------------- -------- -----------------------------
SNO NUMBER(4)
NAME VARCHAR2(15)
COLLEGE CHAR(10)
SAL NUMBER(6,2)
DOJ DATE
FEES NUMBER(5)
to rename a table
rename old_tablename to new_tablename
SQL> rename demo to orademo;
Table renamed.
SQL> desc demo
ERROR:
ORA-04043: object demo does not exist
SQL> desc orademo
Name Null? Type
-------------------------------------------------------------------------------------- -------- -----------------------------
SNO NUMBER(4)
NAME VARCHAR2(15)
COLLEGE CHAR(10)
SAL NUMBER(6,2)
DOJ DATE
FEES NUMBER(5)
SQL> create table demo as select * from orademo;
Table created.
SQL> desc demo
Name Null? Type
-------------------------------------------------------------------------------------- -------- -----------------------------
SNO NUMBER(4)
NAME VARCHAR2(15)
COLLEGE CHAR(10)
SAL NUMBER(6,2)
DOJ DATE
FEES NUMBER(5)
SQL> select * from demo;
no rows selected
drop: it is a ddl commat, which is used to delete the structure + data(i can not use rollback for ddl because for this auto commit is on)
SQL> select * from demo;
no rows selected
SQL> drop table demo;
Table dropped.
SQL> desc demo
ERROR:
ORA-04043: object demo does not exist
SQL> insert into orademo VALUES (1,'BBB','SSN',2000,SYSDATE,30000);
1 row created.
SQL> select * from orademo;
SNO NAME COLLEGE SAL DOJ FEES
---------- --------------- ---------- ---------- --------- ----------
1 AAA
1 BBB SSN 2000 21-FEB-13 30000
SQL> commit;
Commit complete.
SQL> insert into orademo VALUES (1,'BBB');
insert into orademo VALUES (1,'BBB')
*
ERROR at line 1:
ORA-00947: not enough values
SQL> insert into orademo values(&sno,&name,&college,&sal,&doj,&fees);
Enter value for sno: 1
Enter value for name: 'CCC'
Enter value for college: 'QISCET'
Enter value for sal: 1500.25
Enter value for doj: '13-JAN-13'
Enter value for fees: 30000
old 1: insert into orademo values(&sno,&name,&college,&sal,&doj,&fees)
new 1: insert into orademo values(1,'CCC','QISCET',1500.25,'13-JAN-13',30000)
1 row created.
SQL> /
Enter value for sno: 4
Enter value for name: 'DDD'
Enter value for college: 'VGIT'
Enter value for sal: 1111111111111111111
Enter value for doj: SYSDATE
Enter value for fees: 1111111111
old 1: insert into orademo values(&sno,&name,&college,&sal,&doj,&fees)
new 1: insert into orademo values(4,'DDD','VGIT',1111111111111111111,SYSDATE,1111111111)
insert into orademo values(4,'DDD','VGIT',1111111111111111111,SYSDATE,1111111111)
*
ERROR at line 1:
ORA-01438: value larger than specified precision allowed for this column
SQL> COMMIT
2 /
Commit complete.
SQL> insert into orademo VALUES (1,'BBB');
insert into orademo VALUES (1,'BBB')
*
ERROR at line 1:
ORA-00947: not enough values
SQL> insert into orademo(sno,name) values(1,'AAA');
1 row created.
SQL> ROLL
Rollback complete.
SQL> COMMIT;
Commit complete.
SQL>
SQL> insert into orademo(sno,name) values(1,'AAA');
1 row created.
SQL> ALTER TABLE ORADEMO ADD CONS NUMBER(6);
Table altered.
SQL> CREATE TABLE ABC AS SELECT * FROM ORADEMO WHERE 1=2;
Table created.
SQL> SELECT * FROM ABC;
no rows selected
SQL> CREATE TABLE ABC1 AS SELECT * FROM ORADEMO;
Table created.
SQL> SELECT * FROM ABC1;
SNO NAME COLLEGE SAL DOJ FEES CONS
---------- --------------- ---------- ---------- --------- ---------- ----------
1 AAA
1 BBB SSN 2000 21-FEB-13 30000
1 CCC QISCET 1500.25 13-JAN-13 30000
1 AAA
SQL> TRUNCATE TABLE ABC1;
Table truncated.
SQL> DESC ABC1
Name Null? Type
-------------------------------------------------------------------------------------- -------- -----------------------------
SNO NUMBER(4)
NAME VARCHAR2(15)
COLLEGE CHAR(10)
SAL NUMBER(6,2)
DOJ DATE
FEES NUMBER(5)
CONS NUMBER(6)
SQL> SELECT * FROM ABC1;
no rows selected
DROP:
SQL> SELECT * FROM ABC1;
no rows selected
SQL> CREATE TABLE ABC11 AS SELECT * FROM ORADEMO;
Table created.
SQL> SELECT * FROM ABC11;
SNO NAME COLLEGE SAL DOJ FEES CONS
---------- --------------- ---------- ---------- --------- ---------- ----------
1 AAA
1 BBB SSN 2000 21-FEB-13 30000
1 CCC QISCET 1500.25 13-JAN-13 30000
1 AAA
SQL> DROP TABLE ABC11;
Table dropped.
SQL> SELECT * FROM ABC11;
SELECT * FROM ABC11
*
ERROR at line 1:
ORA-00942: table or view does not exist
UPDATE:
SQL> SELECT * FROM ORADEMO;
SNO NAME COLLEGE SAL DOJ FEES CONS
---------- --------------- ---------- ---------- --------- ---------- ----------
1 AAA
1 BBB SSN 2000 21-FEB-13 30000
1 CCC QISCET 1500.25 13-JAN-13 30000
1 AAA
SQL> UPDATE ORADEMO SET CONS=500 WHERE SNO=1;
4 rows updated.
SQL> SELECT * FROM ORADEMO;
SNO NAME COLLEGE SAL DOJ FEES CONS
---------- --------------- ---------- ---------- --------- ---------- ----------
1 AAA 500
1 BBB SSN 2000 21-FEB-13 30000 500
1 CCC QISCET 1500.25 13-JAN-13 30000 500
1 AAA 500
SQL> ROLL
Rollback complete.
SQL> COMMIT;
Commit complete.
SQL> UPDATE ORADEMO SET CONS=500 WHERE SNO=1;
4 rows updated.
SQL> COMMIT;
Commit complete.
1* DELETE AXZ
SQL> /
4 rows deleted.
SQL> ROLL
Rollback complete.
SQL> SELECT * FROM AXZ;
SNO NAME COLLEGE SAL DOJ FEES CONS
---------- --------------- ---------- ---------- --------- ---------- ----------
1 AAA 500
1 BBB SSN 2000 21-FEB-13 30000 500
1 CCC QISCET 1500.25 13-JAN-13 30000 500
1 AAA 500
SQL> DELETE AXZ WHERE FEES=30000;
2 rows deleted.
SQL> COMMIT;
Commit complete.
SQL> SELECT * FROM AXZ;
SNO NAME COLLEGE SAL DOJ FEES CONS
---------- --------------- ---------- ---------- --------- ---------- ----------
1 AAA 500
1 AAA 500
GRANT:
SQL> GRANT ALL ON EMP TO SYSTEM;
Grant succeeded.
SQL> CONN SYSTEM/TIGER
Connected.
SQL> SELECT * FROM EMP;
SELECT * FROM EMP
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> ED
Wrote file afiedt.buf
1* SELECT * FROM SCOTT.EMP
SQL> /
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
1021 122
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 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7876 ADAMS CLERK 7788 23-MAY-87 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> SHOW USER
USER is "SYSTEM"
revoke:
SQL> conn scott/tiger
Connected.
SQL> revoke all on emp from system;
Revoke succeeded.
what are the different clauses in sql:
from
where
group by
having
order by
select
SQL> select * from emp11;
select * from emp11
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> select from emp;
select from emp
*
ERROR at line 1:
ORA-00936: missing expression
SQL> select * fro emp;
select * fro emp
*
ERROR at line 1:
ORA-00923: FROM keyword not found where expected
SQL> select * from emp;
select * from emp
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> show user
USER is "SYSTEM"
SQL> conn scott/tiger
Connected.
SQL> /
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
1021 122
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 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7876 ADAMS CLERK 7788 23-MAY-87 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 sal,comm from emp where comm is null;
SAL COMM
---------- ----------
122
800
2975
2850
2450
3000
5000
1100
950
3000
1300
11 rows selected.
SQL> ed
Wrote file afiedt.buf
1* select deptno,max(sal) from emp where comm is null
SQL> /
select deptno,max(sal) from emp where comm is null
*
ERROR at line 1:
ORA-00937: not a single-group group function
SQL> ed
Wrote file afiedt.buf
1* select deptno,max(sal) from emp where comm is null group by deptno
SQL> /
DEPTNO MAX(SAL)
---------- ----------
122
30 2850
20 3000
10 5000
SQL> ed
Wrote file afiedt.buf
1* select deptno,max(sal) from emp where comm is null group by deptno having max(sal)>2900
SQL> /
DEPTNO MAX(SAL)
---------- ----------
20 3000
10 5000
SQL> ed
Wrote file afiedt.buf
1* select deptno,max(sal) from emp where comm is null group by deptno having max(sal)>2900 order by deptno
SQL> /
DEPTNO MAX(SAL)
---------- ----------
10 5000
20 3000
SQL> ed
Wrote file afiedt.buf
1* select deptno,max(sal) from emp where comm is null group by deptno having max(sal)>2900 order by deptno desc
SQL> /
DEPTNO MAX(SAL)
---------- ----------
20 3000
10 5000
SQL> ed
Wrote file afiedt.buf
1* select deptno,max(sal) from emp where comm is null group by deptno having max(sal)>2900 order by deptno asc
SQL> /
DEPTNO MAX(SAL)
---------- ----------
10 5000
20 3000
SQL> select deptno,max(sal) from emp group by deptno having max(sal)>2900;
DEPTNO MAX(SAL)
---------- ----------
20 3000
10 5000
SQL> select deptno,max(sal) from emp having max(sal)>2900;
select deptno,max(sal) from emp having max(sal)>2900
*
ERROR at line 1:
ORA-00937: not a single-group group function
SQL> ed
Wrote file afiedt.buf
1* select ename,deptno,max(sal),avg(sal),min(sal) from emp having max(sal)>2900
SQL> /
select ename,deptno,max(sal),avg(sal),min(sal) from emp having max(sal)>2900
*
ERROR at line 1:
ORA-00937: not a single-group group function
SQL> ed
Wrote file afiedt.buf
1* select ename,deptno,max(sal),avg(sal),min(sal) from emp group by ename,deptno having max(sal)>2900
SQL> /
ENAME DEPTNO MAX(SAL) AVG(SAL) MIN(SAL)
---------- ---------- ---------- ---------- ----------
JONES 20 2975 2975 2975
FORD 20 3000 3000 3000
SCOTT 20 3000 3000 3000
KING 10 5000 5000 5000
SQL> ed
Wrote file afiedt.buf
1* select empno,ename,deptno,max(sal),avg(sal),min(sal) from emp group by ename,deptno having max(sal)>2900
SQL> /
select empno,ename,deptno,max(sal),avg(sal),min(sal) from emp group by ename,deptno having max(sal)>2900
*
ERROR at line 1:
ORA-00979: not a GROUP BY expression
SQL> ed
Wrote file afiedt.buf
1* select empno,ename,deptno,max(sal),avg(sal),min(sal) from emp group by ename,deptno,empno having max(sal)>2900
SQL> /
EMPNO ENAME DEPTNO MAX(SAL) AVG(SAL) MIN(SAL)
---------- ---------- ---------- ---------- ---------- ----------
7788 SCOTT 20 3000 3000 3000
7839 KING 10 5000 5000 5000
7902 FORD 20 3000 3000 3000
7566 JONES 20 2975 2975 2975
SQL>
grop functions:
max(),min(),count(),mod(),avg()
set operators
union,minus,intersection,union all
SQL*Plus: Release 10.2.0.1.0 - Production on Fri Feb 22 10:33:58 2013
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> select max(sal) from emp where sal<(select max(sal) from emp);
MAX(SAL)
----------
3000
SQL> select * from (select dense_rank over (order by sal desc)) rnk where rnk=&n;
Enter value for n: 1
old 1: select * from (select dense_rank over (order by sal desc)) rnk where rnk=&n
new 1: select * from (select dense_rank over (order by sal desc)) rnk where rnk=1
select * from (select dense_rank over (order by sal desc)) rnk where rnk=1
*
ERROR at line 1:
ORA-00923: FROM keyword not found where expected
SQL> ed
Wrote file afiedt.buf
1* select * from (select dense_rank over (order by sal desc)) rnk where rnk=&n
SQL> select max(sal) from emp where sal<(select max(sal) from emp where sal <(select max(sal) from emp);
select max(sal) from emp where sal<(select max(sal) from emp where sal <(select max(sal) from emp)
*
ERROR at line 1:
ORA-00921: unexpected end of SQL command
SQL> ed
Wrote file afiedt.buf
1* select max(sal) from emp where sal<(select max(sal) from emp where sal<(select max(sal) from emp)
SQL> /
select max(sal) from emp where sal<(select max(sal) from emp where sal<(select max(sal) from emp)
*
ERROR at line 1:
ORA-00921: unexpected end of SQL command
SQL> ed
Wrote file afiedt.buf
1* select max(sal) from emp where sal<(select max(sal) from emp where sal<(select max(sal) from emp))
SQL> /
MAX(SAL)
----------
2975
SQL> select max(sal) from emp;
MAX(SAL)
----------
5000
SQL> select
2 sal
3 from emp where sal<(select max(sal) from emp));
from emp where sal<(select max(sal) from emp))
*
ERROR at line 3:
ORA-00933: SQL command not properly ended
SQL> ed
Wrote file afiedt.buf
1 select
2 sal
3* from emp where sal<(select max(sal) from emp
SQL> /
from emp where sal<(select max(sal) from emp
*
ERROR at line 3:
ORA-00921: unexpected end of SQL command
SQL> ed
Wrote file afiedt.buf
1 select
2 sal
3* from emp where sal<(select max(sal) from emp)
SQL> /
SAL
----------
122
800
1600
1250
2975
1250
2850
2450
3000
1500
1100
SAL
----------
950
3000
1300
14 rows selected.
SQL> ed
Wrote file afiedt.buf
1 select
2 max(sal)
3* from emp where sal<(select max(sal) from emp)
SQL> /
MAX(SAL)
----------
3000
SQL> select mod(7,3) from dual;
MOD(7,3)
----------
1
SQL> select count(*) from emp;
COUNT(*)
----------
15
SQL> select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- --------- ---------- ----------
DEPTNO
----------
1021 122
7369 SMITH CLERK 7902 17-DEC-80 800
20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300
30
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- --------- ---------- ----------
DEPTNO
----------
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
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- --------- ---------- ----------
DEPTNO
----------
7698 BLAKE MANAGER 7839 01-MAY-81 2850
30
7782 CLARK MANAGER 7839 09-JUN-81 2450
10
7788 SCOTT ANALYST 7566 19-APR-87 3000
20
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- --------- ---------- ----------
DEPTNO
----------
7839 KING PRESIDENT 17-NOV-81 5000
10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0
30
7876 ADAMS CLERK 7788 23-MAY-87 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
15 rows selected.
SQL> set linesize 155
SQL> /
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
1021 122
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 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7876 ADAMS CLERK 7788 23-MAY-87 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 min(sal) from emp;
MIN(SAL)
----------
122
SQL> set pages 255
SQL> select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
1021 122
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 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 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 rank(5000) with in group'
2
SQL> select min(sal),level from emp where level=&level
2 connect by prior sal<sal group by level;
Enter value for level: 1
old 1: select min(sal),level from emp where level=&level
new 1: select min(sal),level from emp where level=1
MIN(SAL) LEVEL
---------- ----------
122 1
SQL>
SQL> /
Enter value for level: 2
old 1: select min(sal),level from emp where level=&level
new 1: select min(sal),level from emp where level=2
MIN(SAL) LEVEL
---------- ----------
800 2
SQL> /
Enter value for level: 3
old 1: select min(sal),level from emp where level=&level
new 1: select min(sal),level from emp where level=3
MIN(SAL) LEVEL
---------- ----------
950 3
SQL> /
Enter value for level: 4
old 1: select min(sal),level from emp where level=&level
new 1: select min(sal),level from emp where level=4
MIN(SAL) LEVEL
---------- ----------
1100 4
SQL> /
Enter value for level: 5
old 1: select min(sal),level from emp where level=&level
new 1: select min(sal),level from emp where level=5
MIN(SAL) LEVEL
---------- ----------
1250 5
SQL> /
Enter value for level: 6
old 1: select min(sal),level from emp where level=&level
new 1: select min(sal),level from emp where level=6
MIN(SAL) LEVEL
---------- ----------
1300 6
SQL> /
Enter value for level: 7
old 1: select min(sal),level from emp where level=&level
new 1: select min(sal),level from emp where level=7
MIN(SAL) LEVEL
---------- ----------
1500 7
SQL> /
Enter value for level: 8
old 1: select min(sal),level from emp where level=&level
new 1: select min(sal),level from emp where level=8
MIN(SAL) LEVEL
---------- ----------
1600 8
SQL> ed
Wrote file afiedt.buf
1 select min(sal),level from emp where level=&level
2* connect by prior sal<sal group by level
SQL> SELECT MAX(SAL) FROM(SELECT ENAME,EMPNO,SAL FROM EMP WHERE DEPTNO=20);
MAX(SAL)
----------
3000
SQL> select max(sal),deptno from emp group by deptno;
MAX(SAL) DEPTNO
---------- ----------
122
2850 30
3000 20
5000 10
SQL> ed
Wrote file afiedt.buf
1* SELECT MAX(SAL) FROM(SELECT sal FROM EMP WHERE DEPTNO=20);
SQL> /
SELECT MAX(SAL) FROM(SELECT sal FROM EMP WHERE DEPTNO=20);
*
ERROR at line 1:
ORA-00911: invalid character
SQL> ed
Wrote file afiedt.buf
1* SELECT MAX(SAL) FROM(SELECT sal FROM EMP WHERE DEPTNO=20)
SQL> /
MAX(SAL)
----------
3000
SQL> select * from emp
2 /
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
1021 122
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 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 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 * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
1021 122
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 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 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> ed
Wrote file afiedt.buf
1* select * from emp
SQL> /
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
1021 122
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 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 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>
SQL>
SQL> select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
1021 122
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 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 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 * from emp where rownum<=14;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
1021 122
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 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
14 rows selected.
SQL> select * from emp where rownum<=14
2 minus
3 select * from emp where rownum<=13;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7902 FORD ANALYST 7566 03-DEC-81 3000 20
SQL> select * from emp where rownum<=14
2 minus
3 select * from dept where rownum<=3;
select * from emp where rownum<=14
*
ERROR at line 1:
ORA-01789: query block has incorrect number of result columns
SQL> ed
Wrote file afiedt.buf
1 select * from emp where rownum<=14
2 minus
3* select * from dept where rownum<=3
SQL> select uid from dual;
UID
----------
54
SQL> show user
USER is "SCOTT"
SQL> select vsize('oracle') from dual;
VSIZE('ORACLE')
---------------
6
SQL> SELECT SAL,DENSE_RANK() OVER (ORDER BY SAL DESC) AS RANK FROM EMP;
SAL RANK
---------- ----------
5000 1
3000 2
3000 2
2975 3
2850 4
2450 5
1600 6
1500 7
1300 8
1250 9
1250 9
1100 10
950 11
800 12
122 13
15 rows selected.
SQL> ed
Wrote file afiedt.buf
1* SELECT SAL,DENSE_RANK() OVER (ORDER BY SAL DESC) AS RANK FROM EMP where rank=&n
SQL> /
Enter value for n: 1
old 1: SELECT SAL,DENSE_RANK() OVER (ORDER BY SAL DESC) AS RANK FROM EMP where rank=&n
new 1: SELECT SAL,DENSE_RANK() OVER (ORDER BY SAL DESC) AS RANK FROM EMP where rank=1
SELECT SAL,DENSE_RANK() OVER (ORDER BY SAL DESC) AS RANK FROM EMP where rank=1
*
ERROR at line 1:
ORA-00904: "RANK": invalid identifier
SQL> ed
Wrote file afiedt.buf
1* SELECT SAL,DENSE_RANK() OVER (ORDER BY SAL DESC) RNK FROM EMP where RNK=&N
SQL> /
Enter value for n: 1
old 1: SELECT SAL,DENSE_RANK() OVER (ORDER BY SAL DESC) RNK FROM EMP where RNK=&N
new 1: SELECT SAL,DENSE_RANK() OVER (ORDER BY SAL DESC) RNK FROM EMP where RNK=1
SELECT SAL,DENSE_RANK() OVER (ORDER BY SAL DESC) RNK FROM EMP where RNK=1
*
ERROR at line 1:
ORA-00904: "RNK": invalid identifier
SQL> ED
Wrote file afiedt.buf
1* SELECT * FROM (SELECT SAL,DENSE_RANK() OVER (ORDER BY SAL DESC)RNK FROM EMP) where RNK=&N
SQL> SELECT SAL,DENSE_RANK() OVER (ORDER BY SAL DESC)RNK FROM EMP;
SAL RNK
---------- ----------
5000 1
3000 2
3000 2
2975 3
2850 4
2450 5
1600 6
1500 7
1300 8
1250 9
1250 9
1100 10
950 11
800 12
122 13
15 rows selected.
SQL> SELECT * FROM (SELECT SAL,DENSE_RANK() OVER (ORDER BY SAL DESC)RNK FROM EMP) where RNK=&N;
Enter value for n: 1
old 1: SELECT * FROM (SELECT SAL,DENSE_RANK() OVER (ORDER BY SAL DESC)RNK FROM EMP) where RNK=&N
new 1: SELECT * FROM (SELECT SAL,DENSE_RANK() OVER (ORDER BY SAL DESC)RNK FROM EMP) where RNK=1
SAL RNK
---------- ----------
5000 1
SQL> /
Enter value for n: 2
old 1: SELECT * FROM (SELECT SAL,DENSE_RANK() OVER (ORDER BY SAL DESC)RNK FROM EMP) where RNK=&N
new 1: SELECT * FROM (SELECT SAL,DENSE_RANK() OVER (ORDER BY SAL DESC)RNK FROM EMP) where RNK=2
SAL RNK
---------- ----------
3000 2
3000 2
SQL> /
Enter value for n: 3
old 1: SELECT * FROM (SELECT SAL,DENSE_RANK() OVER (ORDER BY SAL DESC)RNK FROM EMP) where RNK=&N
new 1: SELECT * FROM (SELECT SAL,DENSE_RANK() OVER (ORDER BY SAL DESC)RNK FROM EMP) where RNK=3
SAL RNK
---------- ----------
2975 3
SQL> /4
Enter value for n: 4
old 1: SELECT * FROM (SELECT SAL,DENSE_RANK() OVER (ORDER BY SAL DESC)RNK FROM EMP) where RNK=&N
new 1: SELECT * FROM (SELECT SAL,DENSE_RANK() OVER (ORDER BY SAL DESC)RNK FROM EMP) where RNK=4
SAL RNK
---------- ----------
2850 4
SQL> /
Enter value for n: 5
old 1: SELECT * FROM (SELECT SAL,DENSE_RANK() OVER (ORDER BY SAL DESC)RNK FROM EMP) where RNK=&N
new 1: SELECT * FROM (SELECT SAL,DENSE_RANK() OVER (ORDER BY SAL DESC)RNK FROM EMP) where RNK=5
SAL RNK
---------- ----------
2450 5
SQL> /
Enter value for n: 6
old 1: SELECT * FROM (SELECT SAL,DENSE_RANK() OVER (ORDER BY SAL DESC)RNK FROM EMP) where RNK=&N
new 1: SELECT * FROM (SELECT SAL,DENSE_RANK() OVER (ORDER BY SAL DESC)RNK FROM EMP) where RNK=6
SAL RNK
---------- ----------
1600 6
SQL> ED
Wrote file afiedt.buf
1* SELECT * FROM (SELECT SAL,DENSE_RANK() OVER (ORDER BY SAL DESC)RNK FROM EMP) where RNK=&N
SQL> SELECT * FROM (SELECT SAL,RANK() OVER (ORDER BY SAL DESC)RNK FROM EMP) where RNK=&N;
Enter value for n: 2
old 1: SELECT * FROM (SELECT SAL,RANK() OVER (ORDER BY SAL DESC)RNK FROM EMP) where RNK=&N
new 1: SELECT * FROM (SELECT SAL,RANK() OVER (ORDER BY SAL DESC)RNK FROM EMP) where RNK=2
SAL RNK
---------- ----------
3000 2
3000 2
SQL> /
Enter value for n: 3
old 1: SELECT * FROM (SELECT SAL,RANK() OVER (ORDER BY SAL DESC)RNK FROM EMP) where RNK=&N
new 1: SELECT * FROM (SELECT SAL,RANK() OVER (ORDER BY SAL DESC)RNK FROM EMP) where RNK=3
no rows selected
SQL> SELECT SAL,RANK() OVER (ORDER BY SAL DESC)RNK FROM EMP;
SAL RNK
---------- ----------
5000 1
3000 2
3000 2
2975 4
2850 5
2450 6
1600 7
1500 8
1300 9
1250 10
1250 10
1100 12
950 13
800 14
122 15
15 rows selected.
SQL> SELECT SAL,DENSE_RANK() OVER (ORDER BY SAL DESC)RNK FROM EMP;
SAL RNK
---------- ----------
5000 1
3000 2
3000 2
2975 3
2850 4
2450 5
1600 6
1500 7
1300 8
1250 9
1250 9
1100 10
950 11
800 12
122 13
15 rows selected.
SQL>
data definition language(ddl)--create,alter,rename,drop,truncate,purge,flashback(auto commit is on)
data maipulation language(dml)--insert,update,delete,merge(auto commit is off)
data control language(dcl)---grant,revoke
transaction control language(tcl)----commit(save),rollback(undo),savepoint
data query/retriew language(drl)---select
create table table_name
(column_1 datatype_1,
column_1 datatype_1,
column_2 datatype_2,
column_3 datatype_3,
.
.
.
.
column_n datatype_n);
ex:
create table demo
(sno number(4),
name varchar2(15),
college char(10),
sal number(6,2),
doj date,
fees number(10))
SQL> desc demo
Name Null? Type
-------------------------------------------------------------------------------------- -------- -----------------------------
SNO NUMBER(4)
NAME VARCHAR2(15)
COLLEGE CHAR(10)
SAL NUMBER(6,2)
DOJ DATE
FEES NUMBER(10)
data types:
----------
number(size) ex:number(38),number(p,s) ex:number(6,2)--1111.22(,111111(wrong)
char(size) --char(5)--abc
varchar2(size) varchar2(15)--abc
date default date format is DD-MON-YY ex:SYSDATE
---------
21-FEB-13
alter table table_name modify column_name datatype;
SQL> alter table demo modify fees number(5);
Table altered.
SQL> desc demo
Name Null? Type
-------------------------------------------------------------------------------------- -------- -----------------------------
SNO NUMBER(4)
NAME VARCHAR2(15)
COLLEGE CHAR(10)
SAL NUMBER(6,2)
DOJ DATE
FEES NUMBER(5)
SQL> alter table demo add address varchar2(30);
Table altered.
SQL> desc demo
Name Null? Type
-------------------------------------------------------------------------------------- -------- -----------------------------
SNO NUMBER(4)
NAME VARCHAR2(15)
COLLEGE CHAR(10)
SAL NUMBER(6,2)
DOJ DATE
FEES NUMBER(5)
ADDRESS VARCHAR2(30)
to drop a column
alter table table_name drop column column_name;
ex:
SQL> alter table demo drop column address;
Table altered.
SQL> desc demo
Name Null? Type
-------------------------------------------------------------------------------------- -------- -----------------------------
SNO NUMBER(4)
NAME VARCHAR2(15)
COLLEGE CHAR(10)
SAL NUMBER(6,2)
DOJ DATE
FEES NUMBER(5)
to rename a table
rename old_tablename to new_tablename
SQL> rename demo to orademo;
Table renamed.
SQL> desc demo
ERROR:
ORA-04043: object demo does not exist
SQL> desc orademo
Name Null? Type
-------------------------------------------------------------------------------------- -------- -----------------------------
SNO NUMBER(4)
NAME VARCHAR2(15)
COLLEGE CHAR(10)
SAL NUMBER(6,2)
DOJ DATE
FEES NUMBER(5)
SQL> create table demo as select * from orademo;
Table created.
SQL> desc demo
Name Null? Type
-------------------------------------------------------------------------------------- -------- -----------------------------
SNO NUMBER(4)
NAME VARCHAR2(15)
COLLEGE CHAR(10)
SAL NUMBER(6,2)
DOJ DATE
FEES NUMBER(5)
SQL> select * from demo;
no rows selected
drop: it is a ddl commat, which is used to delete the structure + data(i can not use rollback for ddl because for this auto commit is on)
SQL> select * from demo;
no rows selected
SQL> drop table demo;
Table dropped.
SQL> desc demo
ERROR:
ORA-04043: object demo does not exist
SQL> insert into orademo VALUES (1,'BBB','SSN',2000,SYSDATE,30000);
1 row created.
SQL> select * from orademo;
SNO NAME COLLEGE SAL DOJ FEES
---------- --------------- ---------- ---------- --------- ----------
1 AAA
1 BBB SSN 2000 21-FEB-13 30000
SQL> commit;
Commit complete.
SQL> insert into orademo VALUES (1,'BBB');
insert into orademo VALUES (1,'BBB')
*
ERROR at line 1:
ORA-00947: not enough values
SQL> insert into orademo values(&sno,&name,&college,&sal,&doj,&fees);
Enter value for sno: 1
Enter value for name: 'CCC'
Enter value for college: 'QISCET'
Enter value for sal: 1500.25
Enter value for doj: '13-JAN-13'
Enter value for fees: 30000
old 1: insert into orademo values(&sno,&name,&college,&sal,&doj,&fees)
new 1: insert into orademo values(1,'CCC','QISCET',1500.25,'13-JAN-13',30000)
1 row created.
SQL> /
Enter value for sno: 4
Enter value for name: 'DDD'
Enter value for college: 'VGIT'
Enter value for sal: 1111111111111111111
Enter value for doj: SYSDATE
Enter value for fees: 1111111111
old 1: insert into orademo values(&sno,&name,&college,&sal,&doj,&fees)
new 1: insert into orademo values(4,'DDD','VGIT',1111111111111111111,SYSDATE,1111111111)
insert into orademo values(4,'DDD','VGIT',1111111111111111111,SYSDATE,1111111111)
*
ERROR at line 1:
ORA-01438: value larger than specified precision allowed for this column
SQL> COMMIT
2 /
Commit complete.
SQL> insert into orademo VALUES (1,'BBB');
insert into orademo VALUES (1,'BBB')
*
ERROR at line 1:
ORA-00947: not enough values
SQL> insert into orademo(sno,name) values(1,'AAA');
1 row created.
SQL> ROLL
Rollback complete.
SQL> COMMIT;
Commit complete.
SQL>
SQL> insert into orademo(sno,name) values(1,'AAA');
1 row created.
SQL> ALTER TABLE ORADEMO ADD CONS NUMBER(6);
Table altered.
SQL> CREATE TABLE ABC AS SELECT * FROM ORADEMO WHERE 1=2;
Table created.
SQL> SELECT * FROM ABC;
no rows selected
SQL> CREATE TABLE ABC1 AS SELECT * FROM ORADEMO;
Table created.
SQL> SELECT * FROM ABC1;
SNO NAME COLLEGE SAL DOJ FEES CONS
---------- --------------- ---------- ---------- --------- ---------- ----------
1 AAA
1 BBB SSN 2000 21-FEB-13 30000
1 CCC QISCET 1500.25 13-JAN-13 30000
1 AAA
SQL> TRUNCATE TABLE ABC1;
Table truncated.
SQL> DESC ABC1
Name Null? Type
-------------------------------------------------------------------------------------- -------- -----------------------------
SNO NUMBER(4)
NAME VARCHAR2(15)
COLLEGE CHAR(10)
SAL NUMBER(6,2)
DOJ DATE
FEES NUMBER(5)
CONS NUMBER(6)
SQL> SELECT * FROM ABC1;
no rows selected
DROP:
SQL> SELECT * FROM ABC1;
no rows selected
SQL> CREATE TABLE ABC11 AS SELECT * FROM ORADEMO;
Table created.
SQL> SELECT * FROM ABC11;
SNO NAME COLLEGE SAL DOJ FEES CONS
---------- --------------- ---------- ---------- --------- ---------- ----------
1 AAA
1 BBB SSN 2000 21-FEB-13 30000
1 CCC QISCET 1500.25 13-JAN-13 30000
1 AAA
SQL> DROP TABLE ABC11;
Table dropped.
SQL> SELECT * FROM ABC11;
SELECT * FROM ABC11
*
ERROR at line 1:
ORA-00942: table or view does not exist
UPDATE:
SQL> SELECT * FROM ORADEMO;
SNO NAME COLLEGE SAL DOJ FEES CONS
---------- --------------- ---------- ---------- --------- ---------- ----------
1 AAA
1 BBB SSN 2000 21-FEB-13 30000
1 CCC QISCET 1500.25 13-JAN-13 30000
1 AAA
SQL> UPDATE ORADEMO SET CONS=500 WHERE SNO=1;
4 rows updated.
SQL> SELECT * FROM ORADEMO;
SNO NAME COLLEGE SAL DOJ FEES CONS
---------- --------------- ---------- ---------- --------- ---------- ----------
1 AAA 500
1 BBB SSN 2000 21-FEB-13 30000 500
1 CCC QISCET 1500.25 13-JAN-13 30000 500
1 AAA 500
SQL> ROLL
Rollback complete.
SQL> COMMIT;
Commit complete.
SQL> UPDATE ORADEMO SET CONS=500 WHERE SNO=1;
4 rows updated.
SQL> COMMIT;
Commit complete.
1* DELETE AXZ
SQL> /
4 rows deleted.
SQL> ROLL
Rollback complete.
SQL> SELECT * FROM AXZ;
SNO NAME COLLEGE SAL DOJ FEES CONS
---------- --------------- ---------- ---------- --------- ---------- ----------
1 AAA 500
1 BBB SSN 2000 21-FEB-13 30000 500
1 CCC QISCET 1500.25 13-JAN-13 30000 500
1 AAA 500
SQL> DELETE AXZ WHERE FEES=30000;
2 rows deleted.
SQL> COMMIT;
Commit complete.
SQL> SELECT * FROM AXZ;
SNO NAME COLLEGE SAL DOJ FEES CONS
---------- --------------- ---------- ---------- --------- ---------- ----------
1 AAA 500
1 AAA 500
GRANT:
SQL> GRANT ALL ON EMP TO SYSTEM;
Grant succeeded.
SQL> CONN SYSTEM/TIGER
Connected.
SQL> SELECT * FROM EMP;
SELECT * FROM EMP
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> ED
Wrote file afiedt.buf
1* SELECT * FROM SCOTT.EMP
SQL> /
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
1021 122
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 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7876 ADAMS CLERK 7788 23-MAY-87 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> SHOW USER
USER is "SYSTEM"
revoke:
SQL> conn scott/tiger
Connected.
SQL> revoke all on emp from system;
Revoke succeeded.
what are the different clauses in sql:
from
where
group by
having
order by
select
SQL> select * from emp11;
select * from emp11
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> select from emp;
select from emp
*
ERROR at line 1:
ORA-00936: missing expression
SQL> select * fro emp;
select * fro emp
*
ERROR at line 1:
ORA-00923: FROM keyword not found where expected
SQL> select * from emp;
select * from emp
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> show user
USER is "SYSTEM"
SQL> conn scott/tiger
Connected.
SQL> /
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
1021 122
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 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7876 ADAMS CLERK 7788 23-MAY-87 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 sal,comm from emp where comm is null;
SAL COMM
---------- ----------
122
800
2975
2850
2450
3000
5000
1100
950
3000
1300
11 rows selected.
SQL> ed
Wrote file afiedt.buf
1* select deptno,max(sal) from emp where comm is null
SQL> /
select deptno,max(sal) from emp where comm is null
*
ERROR at line 1:
ORA-00937: not a single-group group function
SQL> ed
Wrote file afiedt.buf
1* select deptno,max(sal) from emp where comm is null group by deptno
SQL> /
DEPTNO MAX(SAL)
---------- ----------
122
30 2850
20 3000
10 5000
SQL> ed
Wrote file afiedt.buf
1* select deptno,max(sal) from emp where comm is null group by deptno having max(sal)>2900
SQL> /
DEPTNO MAX(SAL)
---------- ----------
20 3000
10 5000
SQL> ed
Wrote file afiedt.buf
1* select deptno,max(sal) from emp where comm is null group by deptno having max(sal)>2900 order by deptno
SQL> /
DEPTNO MAX(SAL)
---------- ----------
10 5000
20 3000
SQL> ed
Wrote file afiedt.buf
1* select deptno,max(sal) from emp where comm is null group by deptno having max(sal)>2900 order by deptno desc
SQL> /
DEPTNO MAX(SAL)
---------- ----------
20 3000
10 5000
SQL> ed
Wrote file afiedt.buf
1* select deptno,max(sal) from emp where comm is null group by deptno having max(sal)>2900 order by deptno asc
SQL> /
DEPTNO MAX(SAL)
---------- ----------
10 5000
20 3000
SQL> select deptno,max(sal) from emp group by deptno having max(sal)>2900;
DEPTNO MAX(SAL)
---------- ----------
20 3000
10 5000
SQL> select deptno,max(sal) from emp having max(sal)>2900;
select deptno,max(sal) from emp having max(sal)>2900
*
ERROR at line 1:
ORA-00937: not a single-group group function
SQL> ed
Wrote file afiedt.buf
1* select ename,deptno,max(sal),avg(sal),min(sal) from emp having max(sal)>2900
SQL> /
select ename,deptno,max(sal),avg(sal),min(sal) from emp having max(sal)>2900
*
ERROR at line 1:
ORA-00937: not a single-group group function
SQL> ed
Wrote file afiedt.buf
1* select ename,deptno,max(sal),avg(sal),min(sal) from emp group by ename,deptno having max(sal)>2900
SQL> /
ENAME DEPTNO MAX(SAL) AVG(SAL) MIN(SAL)
---------- ---------- ---------- ---------- ----------
JONES 20 2975 2975 2975
FORD 20 3000 3000 3000
SCOTT 20 3000 3000 3000
KING 10 5000 5000 5000
SQL> ed
Wrote file afiedt.buf
1* select empno,ename,deptno,max(sal),avg(sal),min(sal) from emp group by ename,deptno having max(sal)>2900
SQL> /
select empno,ename,deptno,max(sal),avg(sal),min(sal) from emp group by ename,deptno having max(sal)>2900
*
ERROR at line 1:
ORA-00979: not a GROUP BY expression
SQL> ed
Wrote file afiedt.buf
1* select empno,ename,deptno,max(sal),avg(sal),min(sal) from emp group by ename,deptno,empno having max(sal)>2900
SQL> /
EMPNO ENAME DEPTNO MAX(SAL) AVG(SAL) MIN(SAL)
---------- ---------- ---------- ---------- ---------- ----------
7788 SCOTT 20 3000 3000 3000
7839 KING 10 5000 5000 5000
7902 FORD 20 3000 3000 3000
7566 JONES 20 2975 2975 2975
SQL>
grop functions:
max(),min(),count(),mod(),avg()
set operators
union,minus,intersection,union all
SQL*Plus: Release 10.2.0.1.0 - Production on Fri Feb 22 10:33:58 2013
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> select max(sal) from emp where sal<(select max(sal) from emp);
MAX(SAL)
----------
3000
SQL> select * from (select dense_rank over (order by sal desc)) rnk where rnk=&n;
Enter value for n: 1
old 1: select * from (select dense_rank over (order by sal desc)) rnk where rnk=&n
new 1: select * from (select dense_rank over (order by sal desc)) rnk where rnk=1
select * from (select dense_rank over (order by sal desc)) rnk where rnk=1
*
ERROR at line 1:
ORA-00923: FROM keyword not found where expected
SQL> ed
Wrote file afiedt.buf
1* select * from (select dense_rank over (order by sal desc)) rnk where rnk=&n
SQL> select max(sal) from emp where sal<(select max(sal) from emp where sal <(select max(sal) from emp);
select max(sal) from emp where sal<(select max(sal) from emp where sal <(select max(sal) from emp)
*
ERROR at line 1:
ORA-00921: unexpected end of SQL command
SQL> ed
Wrote file afiedt.buf
1* select max(sal) from emp where sal<(select max(sal) from emp where sal<(select max(sal) from emp)
SQL> /
select max(sal) from emp where sal<(select max(sal) from emp where sal<(select max(sal) from emp)
*
ERROR at line 1:
ORA-00921: unexpected end of SQL command
SQL> ed
Wrote file afiedt.buf
1* select max(sal) from emp where sal<(select max(sal) from emp where sal<(select max(sal) from emp))
SQL> /
MAX(SAL)
----------
2975
SQL> select max(sal) from emp;
MAX(SAL)
----------
5000
SQL> select
2 sal
3 from emp where sal<(select max(sal) from emp));
from emp where sal<(select max(sal) from emp))
*
ERROR at line 3:
ORA-00933: SQL command not properly ended
SQL> ed
Wrote file afiedt.buf
1 select
2 sal
3* from emp where sal<(select max(sal) from emp
SQL> /
from emp where sal<(select max(sal) from emp
*
ERROR at line 3:
ORA-00921: unexpected end of SQL command
SQL> ed
Wrote file afiedt.buf
1 select
2 sal
3* from emp where sal<(select max(sal) from emp)
SQL> /
SAL
----------
122
800
1600
1250
2975
1250
2850
2450
3000
1500
1100
SAL
----------
950
3000
1300
14 rows selected.
SQL> ed
Wrote file afiedt.buf
1 select
2 max(sal)
3* from emp where sal<(select max(sal) from emp)
SQL> /
MAX(SAL)
----------
3000
SQL> select mod(7,3) from dual;
MOD(7,3)
----------
1
SQL> select count(*) from emp;
COUNT(*)
----------
15
SQL> select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- --------- ---------- ----------
DEPTNO
----------
1021 122
7369 SMITH CLERK 7902 17-DEC-80 800
20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300
30
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- --------- ---------- ----------
DEPTNO
----------
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
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- --------- ---------- ----------
DEPTNO
----------
7698 BLAKE MANAGER 7839 01-MAY-81 2850
30
7782 CLARK MANAGER 7839 09-JUN-81 2450
10
7788 SCOTT ANALYST 7566 19-APR-87 3000
20
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- --------- ---------- ----------
DEPTNO
----------
7839 KING PRESIDENT 17-NOV-81 5000
10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0
30
7876 ADAMS CLERK 7788 23-MAY-87 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
15 rows selected.
SQL> set linesize 155
SQL> /
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
1021 122
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 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7876 ADAMS CLERK 7788 23-MAY-87 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 min(sal) from emp;
MIN(SAL)
----------
122
SQL> set pages 255
SQL> select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
1021 122
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 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 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 rank(5000) with in group'
2
SQL> select min(sal),level from emp where level=&level
2 connect by prior sal<sal group by level;
Enter value for level: 1
old 1: select min(sal),level from emp where level=&level
new 1: select min(sal),level from emp where level=1
MIN(SAL) LEVEL
---------- ----------
122 1
SQL>
SQL> /
Enter value for level: 2
old 1: select min(sal),level from emp where level=&level
new 1: select min(sal),level from emp where level=2
MIN(SAL) LEVEL
---------- ----------
800 2
SQL> /
Enter value for level: 3
old 1: select min(sal),level from emp where level=&level
new 1: select min(sal),level from emp where level=3
MIN(SAL) LEVEL
---------- ----------
950 3
SQL> /
Enter value for level: 4
old 1: select min(sal),level from emp where level=&level
new 1: select min(sal),level from emp where level=4
MIN(SAL) LEVEL
---------- ----------
1100 4
SQL> /
Enter value for level: 5
old 1: select min(sal),level from emp where level=&level
new 1: select min(sal),level from emp where level=5
MIN(SAL) LEVEL
---------- ----------
1250 5
SQL> /
Enter value for level: 6
old 1: select min(sal),level from emp where level=&level
new 1: select min(sal),level from emp where level=6
MIN(SAL) LEVEL
---------- ----------
1300 6
SQL> /
Enter value for level: 7
old 1: select min(sal),level from emp where level=&level
new 1: select min(sal),level from emp where level=7
MIN(SAL) LEVEL
---------- ----------
1500 7
SQL> /
Enter value for level: 8
old 1: select min(sal),level from emp where level=&level
new 1: select min(sal),level from emp where level=8
MIN(SAL) LEVEL
---------- ----------
1600 8
SQL> ed
Wrote file afiedt.buf
1 select min(sal),level from emp where level=&level
2* connect by prior sal<sal group by level
SQL> SELECT MAX(SAL) FROM(SELECT ENAME,EMPNO,SAL FROM EMP WHERE DEPTNO=20);
MAX(SAL)
----------
3000
SQL> select max(sal),deptno from emp group by deptno;
MAX(SAL) DEPTNO
---------- ----------
122
2850 30
3000 20
5000 10
SQL> ed
Wrote file afiedt.buf
1* SELECT MAX(SAL) FROM(SELECT sal FROM EMP WHERE DEPTNO=20);
SQL> /
SELECT MAX(SAL) FROM(SELECT sal FROM EMP WHERE DEPTNO=20);
*
ERROR at line 1:
ORA-00911: invalid character
SQL> ed
Wrote file afiedt.buf
1* SELECT MAX(SAL) FROM(SELECT sal FROM EMP WHERE DEPTNO=20)
SQL> /
MAX(SAL)
----------
3000
SQL> select * from emp
2 /
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
1021 122
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 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 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 * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
1021 122
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 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 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> ed
Wrote file afiedt.buf
1* select * from emp
SQL> /
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
1021 122
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 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 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>
SQL>
SQL> select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
1021 122
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 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 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 * from emp where rownum<=14;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
1021 122
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 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
14 rows selected.
SQL> select * from emp where rownum<=14
2 minus
3 select * from emp where rownum<=13;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7902 FORD ANALYST 7566 03-DEC-81 3000 20
SQL> select * from emp where rownum<=14
2 minus
3 select * from dept where rownum<=3;
select * from emp where rownum<=14
*
ERROR at line 1:
ORA-01789: query block has incorrect number of result columns
SQL> ed
Wrote file afiedt.buf
1 select * from emp where rownum<=14
2 minus
3* select * from dept where rownum<=3
SQL> select uid from dual;
UID
----------
54
SQL> show user
USER is "SCOTT"
SQL> select vsize('oracle') from dual;
VSIZE('ORACLE')
---------------
6
SQL> SELECT SAL,DENSE_RANK() OVER (ORDER BY SAL DESC) AS RANK FROM EMP;
SAL RANK
---------- ----------
5000 1
3000 2
3000 2
2975 3
2850 4
2450 5
1600 6
1500 7
1300 8
1250 9
1250 9
1100 10
950 11
800 12
122 13
15 rows selected.
SQL> ed
Wrote file afiedt.buf
1* SELECT SAL,DENSE_RANK() OVER (ORDER BY SAL DESC) AS RANK FROM EMP where rank=&n
SQL> /
Enter value for n: 1
old 1: SELECT SAL,DENSE_RANK() OVER (ORDER BY SAL DESC) AS RANK FROM EMP where rank=&n
new 1: SELECT SAL,DENSE_RANK() OVER (ORDER BY SAL DESC) AS RANK FROM EMP where rank=1
SELECT SAL,DENSE_RANK() OVER (ORDER BY SAL DESC) AS RANK FROM EMP where rank=1
*
ERROR at line 1:
ORA-00904: "RANK": invalid identifier
SQL> ed
Wrote file afiedt.buf
1* SELECT SAL,DENSE_RANK() OVER (ORDER BY SAL DESC) RNK FROM EMP where RNK=&N
SQL> /
Enter value for n: 1
old 1: SELECT SAL,DENSE_RANK() OVER (ORDER BY SAL DESC) RNK FROM EMP where RNK=&N
new 1: SELECT SAL,DENSE_RANK() OVER (ORDER BY SAL DESC) RNK FROM EMP where RNK=1
SELECT SAL,DENSE_RANK() OVER (ORDER BY SAL DESC) RNK FROM EMP where RNK=1
*
ERROR at line 1:
ORA-00904: "RNK": invalid identifier
SQL> ED
Wrote file afiedt.buf
1* SELECT * FROM (SELECT SAL,DENSE_RANK() OVER (ORDER BY SAL DESC)RNK FROM EMP) where RNK=&N
SQL> SELECT SAL,DENSE_RANK() OVER (ORDER BY SAL DESC)RNK FROM EMP;
SAL RNK
---------- ----------
5000 1
3000 2
3000 2
2975 3
2850 4
2450 5
1600 6
1500 7
1300 8
1250 9
1250 9
1100 10
950 11
800 12
122 13
15 rows selected.
SQL> SELECT * FROM (SELECT SAL,DENSE_RANK() OVER (ORDER BY SAL DESC)RNK FROM EMP) where RNK=&N;
Enter value for n: 1
old 1: SELECT * FROM (SELECT SAL,DENSE_RANK() OVER (ORDER BY SAL DESC)RNK FROM EMP) where RNK=&N
new 1: SELECT * FROM (SELECT SAL,DENSE_RANK() OVER (ORDER BY SAL DESC)RNK FROM EMP) where RNK=1
SAL RNK
---------- ----------
5000 1
SQL> /
Enter value for n: 2
old 1: SELECT * FROM (SELECT SAL,DENSE_RANK() OVER (ORDER BY SAL DESC)RNK FROM EMP) where RNK=&N
new 1: SELECT * FROM (SELECT SAL,DENSE_RANK() OVER (ORDER BY SAL DESC)RNK FROM EMP) where RNK=2
SAL RNK
---------- ----------
3000 2
3000 2
SQL> /
Enter value for n: 3
old 1: SELECT * FROM (SELECT SAL,DENSE_RANK() OVER (ORDER BY SAL DESC)RNK FROM EMP) where RNK=&N
new 1: SELECT * FROM (SELECT SAL,DENSE_RANK() OVER (ORDER BY SAL DESC)RNK FROM EMP) where RNK=3
SAL RNK
---------- ----------
2975 3
SQL> /4
Enter value for n: 4
old 1: SELECT * FROM (SELECT SAL,DENSE_RANK() OVER (ORDER BY SAL DESC)RNK FROM EMP) where RNK=&N
new 1: SELECT * FROM (SELECT SAL,DENSE_RANK() OVER (ORDER BY SAL DESC)RNK FROM EMP) where RNK=4
SAL RNK
---------- ----------
2850 4
SQL> /
Enter value for n: 5
old 1: SELECT * FROM (SELECT SAL,DENSE_RANK() OVER (ORDER BY SAL DESC)RNK FROM EMP) where RNK=&N
new 1: SELECT * FROM (SELECT SAL,DENSE_RANK() OVER (ORDER BY SAL DESC)RNK FROM EMP) where RNK=5
SAL RNK
---------- ----------
2450 5
SQL> /
Enter value for n: 6
old 1: SELECT * FROM (SELECT SAL,DENSE_RANK() OVER (ORDER BY SAL DESC)RNK FROM EMP) where RNK=&N
new 1: SELECT * FROM (SELECT SAL,DENSE_RANK() OVER (ORDER BY SAL DESC)RNK FROM EMP) where RNK=6
SAL RNK
---------- ----------
1600 6
SQL> ED
Wrote file afiedt.buf
1* SELECT * FROM (SELECT SAL,DENSE_RANK() OVER (ORDER BY SAL DESC)RNK FROM EMP) where RNK=&N
SQL> SELECT * FROM (SELECT SAL,RANK() OVER (ORDER BY SAL DESC)RNK FROM EMP) where RNK=&N;
Enter value for n: 2
old 1: SELECT * FROM (SELECT SAL,RANK() OVER (ORDER BY SAL DESC)RNK FROM EMP) where RNK=&N
new 1: SELECT * FROM (SELECT SAL,RANK() OVER (ORDER BY SAL DESC)RNK FROM EMP) where RNK=2
SAL RNK
---------- ----------
3000 2
3000 2
SQL> /
Enter value for n: 3
old 1: SELECT * FROM (SELECT SAL,RANK() OVER (ORDER BY SAL DESC)RNK FROM EMP) where RNK=&N
new 1: SELECT * FROM (SELECT SAL,RANK() OVER (ORDER BY SAL DESC)RNK FROM EMP) where RNK=3
no rows selected
SQL> SELECT SAL,RANK() OVER (ORDER BY SAL DESC)RNK FROM EMP;
SAL RNK
---------- ----------
5000 1
3000 2
3000 2
2975 4
2850 5
2450 6
1600 7
1500 8
1300 9
1250 10
1250 10
1100 12
950 13
800 14
122 15
15 rows selected.
SQL> SELECT SAL,DENSE_RANK() OVER (ORDER BY SAL DESC)RNK FROM EMP;
SAL RNK
---------- ----------
5000 1
3000 2
3000 2
2975 3
2850 4
2450 5
1600 6
1500 7
1300 8
1250 9
1250 9
1100 10
950 11
800 12
122 13
15 rows selected.
SQL>
No comments:
Post a Comment