3 July 2012

Important Sql Queries



SELECT deptno, empno, sal
FROM emp e
WHERE
2 > ( SELECT COUNT(e1.sal)
      FROM emp e1
      WHERE e.deptno = e1.deptno AND e.sal < e1.sal )
ORDER BY 1,3 DESC;


I am looking at the following output. We need to stick to this format.

Total                1980                1981                1982                1983
-----------          ------------         ------------         -------------        -----------
14                    1                      10                    2                      1


SELECT COUNT (*),  COUNT(DECODE(TO_CHAR (hiredate, 'YYYY'),'1980', empno)) "1980",
                             COUNT (DECODE (TO_CHAR (hiredate, 'YYYY'), '1981', empno)) "1981",
                             COUNT (DECODE (TO_CHAR (hiredate, 'YYYY'), '1982', empno)) "1982",
                             COUNT (DECODE (TO_CHAR (hiredate, 'YYYY'), '1983', empno)) "1983"
FROM emp;

     
SELECT a.deptno, ename, sal, (SELECT SUM(sal) FROM emp b WHERE a.deptno = b.deptno)
FROM emp a
ORDER BY a.deptno;

OUTPUT :
=======
DEPTNO                      ENAME            SAL            SUM (SAL)
=========                  =======          ====           =========
       10                         KING                5000                   11725
       30                         BLAKE             2850                 10900
       10                         CLARK             2450                 11725
       10                         JONES             2975                 11725
       30                         MARTIN            1250                 10900
       30                         ALLEN              1600                 10900
       30                         TURNER           1500                 10900
       30                         JAMES               950                 10900
       30                         WARD              2750                 10900
       20                         SMITH              8000                 33000
       20                         SCOTT             3000                 33000
       20                         MILLER          20000                 33000




The output is as follows - we need to stick to this format :

Job                  Dept 10                                    Dept 20                        Dept 30                        Total
----------            ---------------                             -------------                    -------------                    ---------
ANALYST                                                         6000                                                                6000
CLERK               1300                                      1900                            950                              4150
MANAGER         2450                                      2975                            2850                            8275
PRESIDENT       5000                                                                                                              5000
SALESMAN                                                                                         5600                            5600

SELECT job "Job", SUM (DECODE (deptno, 10, sal)) "Dept 10",
                             SUM (DECODE (deptno, 20, sal)) "Dept 20",
                             SUM (DECODE (deptno, 30, sal)) "Dept 30",
                             SUM (sal) "Total"
FROM emp
GROUP BY job ;


SELECT DEPTNO, ENAME, SAL
FROM EMP A
WHERE
3 = (SELECT COUNT(B.SAL) FROM EMP B
WHERE A.SAL < B.SAL) ORDER BY SAL DESC;


SELECT DEPTNO, ENAME, SAL
FROM EMP 
WHERE ROWID = (SELECT ROWID FROM EMP   WHERE ROWNUM <= 5
      MINUS
      SELECT ROWID FROM EMP WHERE ROWNUM < 5)


Name                            Null?              Type
-------------------------------------------------------------------
SUB                             NOT NULL       VARCHAR2(4)
SUPER                                    VARCHAR2(4)
PRICE                                      NUMBER(6,2)

SELECT sub, super
FROM parts
CONNECT BY PRIOR sub = super
START WITH sub = 'p1';


DELETE FROM table_name A
WHERE ROWID > (  SELECT min(ROWID) FROM table_name B  WHERE A.col = B.col);


SELECT *
FROM   emp
WHERE  (ROWID,0) IN (SELECT ROWID, MOD(ROWNUM,4)
                                       FROM   emp);


SELECT ename, deptno, sal
FROM (SELECT * FROM emp ORDER BY sal DESC)
WHERE ROWNUM < 10;


        SELECT
    f2,
               COUNT(DECODE(greatest(f1,59), least(f1,100), 1, 0)) "Range 60-100",
               COUNT(DECODE(greatest(f1,30), least(f1, 59), 1, 0)) "Range 30-59",
               COUNT(DECODE(greatest(f1,29), least(f1,  0), 1, 0)) "Range 00-29"
        FROM   my_table
        GROUP BY f2;



SELECT ename "Name", sal "Salary",
              DECODE( TRUNC(sal/1000, 0), 0, 0.0,
                                          1, 0.1,
                                          2, 0.2,
                                          3, 0.3) "Tax rate"
FROM   emp;


COL NAME      DATATYPE
----------------------------------------
DNO                 NUMBER
SEX                 CHAR

SELECT dno, SUM(DECODE(sex,'M',1,0)) MALE,
             SUM(DECODE(sex,'F',1,0)) FEMALE,
             COUNT(DECODE(sex,'M',1,'F',1)) TOTAL
FROM   t1
GROUP BY dno;


SELECT EXP(SUM(LN(col1))) FROM srinu;


SELECT num
FROM satyam
GROUP BY num
HAVING COUNT(*) > 1;

*
**
***
****
*****

SELECT RPAD(DECODE(temp,temp,'*'),ROWNUM,'*')
FROM srinu1;


FUNCTION F_BALANCE_VALUE
(p_business_group_id number, p_payroll_action_id number,
p_balance_name varchar2, p_dimension_name varchar2) RETURN NUMBER
IS
l_bal number;
l_defined_bal_id number;
l_assignment_action_id number;
BEGIN
            SELECT assignment_action_id
              INTO l_assignment_action_id
              FROM
              pay_assignment_actions
                        WHERE
                        assignment_id = :p_assignment_id
                        AND payroll_action_id = p_payroll_action_id;
 
            SELECT
            defined_balance_id
            INTO
            l_defined_bal_id
            FROM
            pay_balance_types pbt,
            pay_defined_balances pdb,
            pay_balance_dimensions pbd
            WHERE
            pbt.business_group_id = p_business_group_id
            AND UPPER(pbt.balance_name) = UPPER(p_balance_name)
            AND pbt.business_group_id = pdb.business_group_id
            AND pbt.balance_type_id = pdb.balance_type_id
            AND UPPER(pbd.dimension_name) = UPPER(p_dimension_name)
            AND pdb.balance_dimension_id = pbd.balance_dimension_id;
           
    l_bal := pay_balance_pkg.get_value(l_defined_bal_id,l_assignment_action_id);
 
  RETURN (l_bal);
 
exception
             WHEN no_data_found THEN
                  RETURN 0; 
END;


FUNCTION f_element_value(
                         p_classification_name in varchar2,
                         p_element_name        in varchar2,
                         p_business_group_id   in number,
                         p_input_value_name    in varchar2,
                         p_payroll_action_id      in number,
                         p_assignment_id         in number
                         )
                          RETURN number
IS
     l_element_value   number(14,2) default 0;
     l_input_value_id  pay_input_values_f.input_value_id%type;
     l_element_type_id pay_element_types_f.element_type_id%type;
BEGIN
                        SELECT DISTINCT element_type_id
                        INTO   l_element_type_id
                        FROM   pay_element_types_f pet,
                                     pay_element_classifications pec
                        WHERE  pet.classification_id = pec.classification_id
                        AND    upper(classification_name) = upper(p_classification_name)
                        AND    upper(element_name) = upper(p_element_name)
                        AND    pet.business_group_id = p_business_group_id;

              SELECT input_value_id
                        INTO   l_input_value_id
                        FROM   pay_input_values_f
                        WHERE  upper(name) = upper(p_input_value_name)
                        AND    element_type_id = l_element_type_id;
           
            SELECT NVL(prrv.result_value,0)
            INTO   l_element_value
            FROM   pay_run_result_values prrv,
            pay_run_results prr,
                        pay_assignment_actions paa
              WHERE  prrv.run_result_id = prr.run_result_id
            AND    prr.assignment_ACTION_ID = paa.assignment_action_id
            AND    paa.assignment_id = p_assignment_id
            AND    input_value_id = l_input_value_id
              AND    paa.payroll_action_id = p_payroll_action_id;

            RETURN (l_element_value);
 
exception
             WHEN no_data_found THEN
                  RETURN 0;
END;


SELECT ename,
NVL(LENGTH(REPLACE(TRANSLATE(UPPER(RTRIM(ename)),'ABCDEFGHIJKLMNOPQRSTUVWXYZ'' ','                           @'),' ',''))+1,1) word_length
FROM emp;

Explanation :

TRANSLATE(UPPER(RTRIM(ename)),'ABCDEFGHIJKLMNOPQRSTUVWXYZ'' ','                           @') -- This will translate all the characters FROM A-Zincluding a single quote to a space. It will also translate a space to a @.

REPLACE(TRANSLATE(UPPER(RTRIM(ename)),'ABCDEFGHIJKLMNOPQRSTUVWXYZ'' ','                           @'),' ','') -- This will replace every space withnothing in the above result.

LENGTH(REPLACE(TRANSLATE(UPPER(RTRIM(ename)),'ABCDEFGHIJKLMNOPQRSTUVWXYZ'' ','                           @'),' ',''))+1 -- This will give u the countof @ characters in the above result.



CREATE OR REPLACE FUNCTION is_leap_year (p_date IN DATE)  RETURN VARCHAR2
 AS
 v_test DATE;
 BEGIN
             v_test := TO_DATE ('29-Feb-' || TO_CHAR (p_date,'YYYY'),'DD-Mon-YYYY');
             RETURN 'Y';
 EXCEPTION
 WHEN OTHERS THEN
             RETURN 'N';
 END is_leap_year;

SQL> SELECT hiredate, TO_CHAR (hiredate, 'Day') weekday
          FROM emp
          WHERE is_leap_year (hiredate) = 'Y';


SELECT
TRANSLATE(LOWER(ssn),'abcdefghijklmnopqrstuvwxyz- ','')
FROM DUAL;


SELECT
TRANSLATE(INITCAP(temp),
SUBSTR(temp, INSTR(temp,'''')+1,1), LOWER(SUBSTR(temp, INSTR(temp,'''')+1)))
FROM srinu1;


CREATE OR REPLACE FUNCTION to_word_i
  (amount   IN NUMBER)
  RETURN       VARCHAR2
AS
  v_length     INTEGER         := 0;
  v_num2       VARCHAR2 (50)   := NULL;
  v_amount     VARCHAR2 (50)   := TO_CHAR (TRUNC (amount));
  v_word       VARCHAR2 (4000) := NULL;
  v_word1      VARCHAR2 (4000) := NULL;
  TYPE myarray IS TABLE OF VARCHAR2 (255);
  v_str myarray := myarray (' Thousand ',
                            ' Lakh ',
                            ' Crore ');
BEGIN
    IF ((amount = 0) OR (amount IS NULL)) THEN
            v_word := 'zero';
    ELSIF (TO_CHAR (amount) LIKE '%.%') THEN
    IF (SUBSTR (amount, INSTR (amount, '.') + 1) > 0) THEN
      v_num2 := SUBSTR (amount, INSTR (amount, '.') + 1);
      IF (LENGTH (v_num2) < 2) THEN
        v_num2 := v_num2 * 10;
      END IF;
      v_word1 := ' AND ' || (TO_CHAR (TO_DATE (SUBSTR (v_num2, LENGTH (v_num2) - 1,2), 'J'),
                 'JSP' ))|| ' paise ';
                
      v_amount := SUBSTR(amount,1,INSTR (amount, '.')-1);
            v_word := TO_CHAR (TO_DATE (SUBSTR (v_amount, LENGTH (v_amount) - 2,3), 'J'), 'Jsp' ) || v_word;
      v_amount := SUBSTR (v_amount, 1, LENGTH (v_amount) - 3);
            FOR i in 1 .. v_str.COUNT
            LOOP
            EXIT WHEN (v_amount IS NULL);
            v_word := TO_CHAR (TO_DATE (SUBSTR (v_amount, LENGTH (v_amount) - 1,2), 'J'), 'Jsp' ) || v_str (i) || v_word;
            v_amount := SUBSTR (v_amount, 1, LENGTH (v_amount) - 2);
            END LOOP;
    END IF;
            ELSE
                        v_word := TO_CHAR ( TO_DATE ( TO_CHAR ( amount, '999999999') , 'J'), 'JSP');
            END IF;

  v_word := v_word || ' ' || v_word1 || ' only ';
  v_word := REPLACE (RTRIM (v_word), '  ', ' ');
  v_word := REPLACE (RTRIM (v_word), '-', ' ');
 
  RETURN INITCAP (v_word);
END to_word_i;


DELETE
FROM   srinu
WHERE  (ROWID,0) IN (SELECT ROWID, MOD(ROWNUM,2)
                                     FROM   srinu);


DELETE
FROM   srinu
WHERE  (ROWID,1) IN (SELECT ROWID, MOD(ROWNUM,2)
                                     FROM   srinu);


CREATE OR REPLACE PROCEDURE Send_Mail
IS
sender VARCHAR2(50) := 'sender@something.com';
recipient VARCHAR2(50) := 'recipient@something.com';
subject VARCHAR2(100) := 'Test Message';
message VARCHAR2(1000) := 'This is a sample mail ....';
lv_mailhost VARCHAR2(30) := 'HOTNT002';
l_mail_conn utl_smtp.connection;
lv_crlf VARCHAR2(2):= CHR( 13 ) || CHR( 10 );
BEGIN
l_mail_conn := utl_smtp.open_connection (lv_mailhost, 80);
utl_smtp.helo ( l_mail_conn, lv_mailhost);
utl_smtp.mail ( l_mail_conn, sender);
utl_smtp.rcpt ( l_mail_conn, recipient);
utl_smtp.open_data (l_mail_conn);
utl_smtp.write_data ( l_mail_conn, 'FROM: ' || sender || lv_crlf);
utl_smtp.write_data ( l_mail_conn, 'To: ' || recipient || lv_crlf);
utl_smtp.write_data ( l_mail_conn, 'Subject:' || subject || lv_crlf);
utl_smtp.write_data ( l_mail_conn,  lv_crlf || message);
utl_smtp.close_data(l_mail_conn);
utl_smtp.quit(l_mail_conn);

EXCEPTION
            WHEN OTHERS THEN
                        DBMS_OUTPUT.PUT_LINE('Error');
END;
/


SELECT case
WHEN sex = 'm' THEN 'male'
WHEN sex = 'f' THEN 'female'
ELSE 'unknown'
END
FROM mytable;


CREATE TABLE srinu(dt1 date DEFAULT SYSDATE, dt2 date,
CONSTRAINT check_dt2 CHECK ((dt2 >= dt1) AND (dt2 <= ADD_MONTHS(SYSDATE,3)));

XXIX. Query to list all the suppliers who supply all the parts supplied bysupplier 'S2' :

SELECT DISTINCT a.SUPP
FROM ORDERS a
WHERE a.supp != 'S2'
AND a.parts IN
(SELECT DISTINCT PARTS FROM ORDERS WHERE supp = 'S2')
GROUP BY a.SUPP
HAVING
COUNT(DISTINCT a.PARTS) >=
(SELECT COUNT(DISTINCT PARTS) FROM ORDERS WHERE supp = 'S2');

Table : orders

SUPP                 PARTS
-------------------- -------
S1                   P1
S1                   P2
S1                   P3
S1                   P4
S1                   P5
S1                   P6
S2                   P1
S2                   P2
S3                   P2
S4                   P2
S4                   P4
S4                   P5


SELECT NEXT_DAY(LAST_DAY(TO_DATE('26-10-2001','DD-MM-YYYY')) - 7,'sunday')
FROM DUAL;



table data :
id         name   parent_id
-------------------------------
1          a          NULL - the top level entry
2          b          1 - a child of 1
3          c          1
4          d          2 - a child of 2
5          e          2
6          f           3
7          g          3
8          h          4
9          i           8
10        j           9

SELECT ID
FROM MY_TABlE
WHERE PARENT_ID IS NOT NULL
MINUS
SELECT PARENT_ID
FROM MY_TABlE;


SELECT empno FROM emp WHERE ROWID in
(SELECT ROWID FROM emp
MINUS
SELECT ROWID FROM emp WHERE ROWNUM <= (SELECT COUNT(*)-5 FROM emp));


CREATE OR REPLACE PROCEDURE disp
AS
xTableName varchar2(25):='emp';
xFieldName varchar2(25):='ename';
xValue NUMBER;
xQuery varchar2(100);
name varchar2(10) := 'CLARK';
BEGIN
xQuery := 'SELECT SAL FROM ' || xTableName || ' WHERE ' || xFieldName ||
              ' = ''' || name || '''';

DBMS_OUTPUT.PUT_LINE(xQuery);

EXECUTE IMMEDIATE xQuery INTO xValue;
DBMS_OUTPUT.PUT_LINE(xValue);
END;


SELECT name FROM v$database;


SELECT SYS_CONTEXT('USERENV','CURRENT_SCHEMA') FROM DUAL;



SELECT column_name
FROM all_tab_columns
WHERE TABLE_NAME = 'ORDERS';


Place the following lines of code in a file and execute the file in SQLPLUS :

set heading off
set feedback off
set colsep '        '
set termout off
set verify off
spool c:\srini.txt
SELECT empno,ename FROM emp; /* Write your Query here */
spool off
/


SELECT SYS_CONTEXT('USERENV','SESSIONID') Session_ID FROM DUAL;


To display rows 5 to 7 :

SELECT DEPTNO, ENAME, SAL
FROM EMP
WHERE ROWID IN
                (SELECT ROWID FROM EMP
                 WHERE ROWNUM <= 7
                 MINUS
                SELECT ROWID FROM EMP
                WHERE ROWNUM < 5);

OR

SELECT ename
FROM emp
GROUP BY ROWNUM, ename
HAVING ROWNUM > 1 and ROWNUM < 3;       


SELECT COUNT(column_name)
FROM user_tab_columns
WHERE table_name = 'MYTABLE';


dbms_output.enable(4000);  /*allows the output buffer to be increased to the specified number of bytes */


DECLARE
BEGIN
dbms_output.enable(4000);
FOR i IN 1..400
LOOP
DBMS_OUTPUT.PUT_LINE(i);
END LOOP;
END;
/


Set the following to some other character. By default it is &.

set define '~'


XXXXVIII. How do you remove Trailing blanks in a spooled file :
Change the Environment Options Like this :
set trimspool on
set trimout on


Sample :1
CREATE OR REPLACE PROCEDURE CNT(P_TABLE_NAME IN VARCHAR2)
AS
SqlString VARCHAR2(200);
tot number;
BEGIN
SqlString:='SELECT COUNT(*) FROM '||  P_TABLE_NAME;
EXECUTE IMMEDIATE SqlString INTO tot;
DBMS_OUTPUT.PUT_LINE('Total No.Of Records In ' || P_TABLE_NAME || ' ARE=' || tot);
END;

Sample :2
DECLARE
            sql_stmt VARCHAR2(200);
            plsql_block VARCHAR2(500);
            emp_id NUMBER(4) := 7566;
            salary NUMBER(7,2);
            dept_id NUMBER(2) := 50;
            dept_name VARCHAR2(14) := ’PERSONNEL’;
            location VARCHAR2(13) := ’DALLAS’;
            emp_rec emp%ROWTYPE;
BEGIN
            EXECUTE IMMEDIATE 'CREATE TABLE bonus (id NUMBER, amt NUMBER)';

            sql_stmt := 'INSERT INTO dept VALUES (:1, :2, :3)';
            EXECUTE IMMEDIATE sql_stmt USING dept_id, dept_name, location;

            sql_stmt := 'SELECT * FROM emp WHERE empno = :id';
            EXECUTE IMMEDIATE sql_stmt INTO emp_rec USING emp_id;

            plsql_block := 'BEGIN emp_pkg.raise_salary(:id, :amt); END;';
            EXECUTE IMMEDIATE plsql_block USING 7788, 500;

            sql_stmt := 'UPDATE emp SET sal = 2000 WHERE empno = :1
            RETURNING sal INTO :2';
            EXECUTE IMMEDIATE sql_stmt USING emp_id RETURNING INTO salary;

            EXECUTE IMMEDIATE 'DELETE FROM dept WHERE deptno = :num'
            USING dept_id;

            EXECUTE IMMEDIATE ’ALTER SESSION SET SQL_TRACE TRUE’;
END;

Sample 3
CREATE OR REPLACE PROCEDURE DEPARTMENTS(NO IN DEPT.DEPTNO%TYPE) AS
              v_cursor integer;
              v_dname  char(20);
              v_rows   integer;
            BEGIN
              v_cursor := DBMS_SQL.OPEN_CURSOR;
              DBMS_SQL.PARSE(v_cursor, 'select dname from dept where deptno > :x', DBMS_SQL.V7);
              DBMS_SQL.BIND_VARIABLE(v_cursor, ':x', no);
              DBMS_SQL.DEFINE_COLUMN_CHAR(v_cursor, 1, v_dname, 20);
              v_rows := DBMS_SQL.EXECUTE(v_cursor);
              LOOP
                IF DBMS_SQL.FETCH_ROWS(v_cursor) = 0 THEN
                   EXIT;
                END IF;
                DBMS_SQL.COLUMN_VALUE_CHAR(v_cursor, 1, v_dname);
                DBMS_OUTPUT.PUT_LINE('Deptartment name: '||v_dname);
              END LOOP;
              DBMS_SQL.CLOSE_CURSOR(v_cursor);
            EXCEPTION
              WHEN OTHERS THEN
                   DBMS_SQL.CLOSE_CURSOR(v_cursor);
                   raise_application_error(-20000, 'Unknown Exception Raised: '||sqlcode||' '||sqlerrm);
END;


Difference 1:
            Oracle : select name from table1 where name like 'k%';
            Access: select name from table1 where name like 'k*';

Difference 2:
            Access: SELECT  TOP 2 name FROM Table1;
            Oracle : will not work there is no such TOP key word.


SELECT organization_id,name
FROM hr_all_organization_units
WHERE organization_id in
(
SELECT ORGANIZATION_ID_CHILD FROM  PER_ORG_STRUCTURE_ELEMENTS
CONNECT BY PRIOR
ORGANIZATION_ID_CHILD = ORGANIZATION_ID_PARENT
START WITH
ORGANIZATION_ID_CHILD = (SELECT organization_id
FROM hr_all_organization_units
WHERE name =  'EBG Corporate Group'));


CREATE OR REPLACE PROCEDURE read_data
AS
c_path             varchar2(100) := '/usr/tmp';
c_file_name     varchar2(20)  := 'EKGSEP01.CSV';
v_file_id           utl_file.file_type;
v_buffer           varchar2(1022) := This is a sample text’;
BEGIN
v_file_id := UTL_FILE.FOPEN(c_path,c_file_name,'w');
UTL_FILE.PUT_LINE(v_file_id, v_buffer);
UTL_FILE.FCLOSE(v_file_id);

v_file_id := UTL_FILE.FOPEN(c_path,c_file_name,'r');
UTL_FILE.GET_LINE(v_file_id, v_buffer);
DBMS_OUTPUT.PUT_LINE(v_buffer);
UTL_FILE.FCLOSE(v_file_id);
END;
/


SELECT DBMS_RANDOM.VALUE (1,2) FROM DUAL;


SELECT
       FLOOR((date1-date2)*24*60*60)/3600)
       || ' HOURS ' ||
       FLOOR((((date1-date2)*24*60*60) -
       FLOOR(((date1-date2)*24*60*60)/3600)*3600)/60)
       || ' MINUTES ' ||
       ROUND((((date1-date2)*24*60*60) -
       FLOOR(((date1-date2)*24*60*60)/3600)*3600 -
       (FLOOR((((date1-date2)*24*60*60) -
       FLOOR(((date1-date2)*24*60*60)/3600)*3600)/60)*60)))
       || ' SECS ' time_difference
FROM   my_table;