HOW CAN YOU FIND THE NAMES OF THE EMPLOYEES WHOSE SALARIES ARE SAME?

SQL> select e1.ename,e1.sal ,e2.ename,e2.sal from emp e1,emp e2 where e1.sal=e2.sal 

           and

           e1.empno!=e2.empno;

ENAME       SAL    ENAME       SAL

_______     ____    ________    _____
WARD       1250     MARTIN     1250
MARTIN    1250    WARD        1250
SCOTT      3000     FORD           3000
FORD        3000     SCOTT        3000

PRINTING Nth HIGHEST SALARY

SQL> select sal from emp e where 3=(select count(distinct sal) from emp where e.sal>=sal);

 SAL
----
1100



SQL> SELECT A.ENAME,A.SAL FROM (SELECT ENAME,SAL,DENSE_RANK() OVER (ORDER BY SAL)  RK FROM EMP)  A WHERE RK=2;

ENAME       SAL
---------- ----
JAMES       950

DID U KNOW THIS.................................WE CAN LOAD COMBINED PHYSICAL RECORDS...................

There may be chances that we get the data in which each row starts with a special character like '@','#','$' etc.Then we will have to use continueif to load the data from such kind of flat file into staging table.

There may be scenerios where we need to load two or three rows of the flat-file as a single row in the staging table then we will use Concatenate option to acheive that requirement.

USING CONTINUEIF:

load data

infile *

replace

continueif this (1)= '@'

into table vnk_emp

fields terminated by ','

optionally enclosed by '"'

trailing nullcols

(

c1,

c2,

c3,

c4 char terminated by whitespace

)

BEGINDATA

@1,2,

    3,4

@5,6,

    7,8

USING CONCATENATE:

load data

infile *

replace

concatenate 2

into table vnk_emp

fields terminated by ','

optionally enclosed by '"'

trailing nullcols

(

c1,

c2,

c3,

c4 

)

BEGINDATA

1,2,

3,4

5,6,

7,8

Staging table:

Create table vnk_emp(c1 number,

                                   c2 number,

                                   c3 number,

                                   c4 number

                                  )

Procure to Pay Process - Project Spend Management

Oracle Training - Defining Ledgers in General Ledger Part 1 - Oracle E-B...

Order to Cash cycle

HA...... HA........... WE CAN CREATE A PROCEDURE FOR DISPLAYING OUTPUT.......................................EEEEEEEEEEEEEEEEEEEEE

SQL> CREATE OR REPLACE PROCEDURE DISPLAY(ARG VARCHAR2)
  2  AS
  3  BEGIN
  4  DBMS_OUTPUT.PUT_LINE(ARG);
  5  END;
  6  /

Procedure created.

SQL>
SQL> BEGIN
  2  DISPLAY('hai');
  3  END;
  4  /

PL/SQL procedure successfully completed.

SQL> SET SERVEROUTPUT ON
SQL> /
hai

PL/SQL procedure successfully completed.

SQL>
SQL> BEGIN
  2  DISPLAY('hai'||'HELLO');
  3  END;
  4  /
haiHELLO

PL/SQL procedure successfully completed.

CAN WE PRINT A COLUMN DATA IN DIFFERENT ROWS...........................??????????????????????????

YES WE CAN DO THAT.......................


SQL> select substr('xyz',rownum,1) from dual connect by level<=3;

S
-
x
y
z

CAN WE GET THE COLUMN NAME AS IF WE WANT...............?

SQL>  create table veena("Veena1" varchar2(10),"veena2 veena" varchar2(10));

Table created.

SQL> desc veena;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 Veena1                                             VARCHAR2(10)
 veena2 veena                                       VARCHAR2(10)

HOW CAN WE INSERT THE NEEDED NO OF ROWS FROM FLAT FILE TO STAGE TABLE USING STAR LOADER

This can be achieved using the key word "load" followed by skip;

skip=2 load=20;

this will load the records from 2nd row to the 20th row;  

HOW TO DISPLAY 1,2,3,.................IN SQL

HOW TO DISPLAY 1,2,3,........IN SQL.

SQL> select rownum from dual connect by level<10;

    ROWNUM
----------
         1
         2
         3
         4
         5
         6
         7
         8
         9

9 rows selected.

HOW TO DISPLAY SECOND OR THIRD ECT ROW INDIVIDUALLY

HI,

TO PRINT THE INDIVIDUAL ROWS 

select * from (select rownum rno,*.emp  from emp)

where rno=&rno;

EXPLANATION:

when user gives the user defined number for the 'rno',the system will first executes the sub query then passes the bind patameters corresponding rownum.

Filler use in sqlloader

Filler use in sqlloader

SQL> select * from emp31;

no rows selected

SQL> select * from emp32;

no rows selected

****************************emp.txt**********************

7369,SMITH,7902,17-DEC-80,800,,20

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

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

7566,JONES,7839,02-APR-81,2975,,20

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

7698,BLAKE,7839,01-MAY-81,2850,,30

7782,CLARK,7839,09-JUN-81,2450,,10

7788,SCOTT,7566,19-APR-87,3000,,20

7839,KING,,17-NOV-81,5000,,10

7844,TURNER,7698,08-SEP-81,1500,0,30

7876,ADAMS,7788,23-MAY-87,1100,,20

7900,JAMES,7698,03-DEC-81,950,,30

7902,FORD,7566,03-DEC-81,3000,,20

7934,MILLER,7782,23-JAN-82,1300,,10

********************************emp31.ctl*********************

load data

infile 'I:\SQLLOADER\emp.txt'

insert into table emp31

fields terminated by ","

optionally enclosed by '"'

(empno 

,ename

,job

,mgr

,hiredate

,sal

,comm

,deptno)

into table emp32

fields terminated by ","

(empno position(1)

,ename

,job

,mgr filler

,hiredate filler

,sal

,comm

,deptno)

*********************command prompt syntax****************

C:\Users\RAJASEKHAR>sqlldr scott/tiger@ORCL control=I:\SQLLOADER\emp31.ctl

SQL*Loader: Release 10.2.0.3.0 - Production on Wed Apr 2 14:13:30 2014

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Commit point reached - logical record count 13

Commit point reached - logical record count 14

************************output********************************

SQL> select * from emp31;

EMPNO ENAME      JOB         MGR HIREDATE    SAL  COMM DEPTNO

----- ---------- --------- ----- --------- ----- ----- ------

 7369 SMITH      CLERK      7902 17-DEC-80   800           20

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

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

 7566 JONES      MANAGER    7839 02-APR-81  2975           20

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

 7698 BLAKE      MANAGER    7839 01-MAY-81  2850           30

 7782 CLARK      MANAGER    7839 09-JUN-81  2450           10

 7788 SCOTT      ANALYST    7566 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

14 rows selected.

SQL> select * from emp32;

EMPNO ENAME      JOB         MGR HIREDATE    SAL  COMM DEPTNO

----- ---------- --------- ----- --------- ----- ----- ------

 7369 SMITH      CLERK                       800           20

 7499 ALLEN      SALESMAN                   1600   300     30

 7521 WARD       SALESMAN                   1250   500     30

 7566 JONES      MANAGER                    2975           20

 7654 MARTIN     SALESMAN                   1250  1400     30

 7698 BLAKE      MANAGER                    2850           30

 7782 CLARK      MANAGER                    2450           10

 7788 SCOTT      ANALYST                    3000           20

 7839 KING       PRESIDENT                  5000           10

 7844 TURNER     SALESMAN                   1500     0     30

 7876 ADAMS      CLERK                      1100           20

 7900 JAMES      CLERK                       950           30

 7902 FORD       ANALYST                    3000           20

 7934 MILLER     CLERK                      1300           10

14 rows selected.