Infolinks

Friday 22 February 2013

ORACLE COURSE CONTENT

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>

No comments:

Post a Comment