Password encrypt,decrypt using DBMS Obfuscation Toolkit

CREATE OR REPLACE PACKAGE PASSWORD AS

   function encrypt(i_password varchar2) return varchar2;

   function decrypt(i_password varchar2) return varchar2;

END PASSWORD;

/

show errors

CREATE OR REPLACE PACKAGE BODY PASSWORD AS

  -- key must be exactly 8 bytes long

  c_encrypt_key varchar2(8) := 'key45678';

  function encrypt (i_password varchar2) return varchar2 is

    v_encrypted_val varchar2(38);

    v_data          varchar2(38);

  begin

     -- Input data must have a length divisible by eight

     v_data := RPAD(i_password,(TRUNC(LENGTH(i_password)/8)+1)*8,CHR(0));

     DBMS_OBFUSCATION_TOOLKIT.DESENCRYPT(

        input_string     => v_data,

        key_string       => c_encrypt_key,

        encrypted_string => v_encrypted_val);

     return v_encrypted_val;

  end encrypt;

  function decrypt (i_password varchar2) return varchar2 is

    v_decrypted_val varchar2(38);

  begin

     DBMS_OBFUSCATION_TOOLKIT.DESDECRYPT(

        input_string     => i_password,

        key_string       => c_encrypt_key,

        decrypted_string => v_decrypted_val);

     return v_decrypted_val;

  end decrypt;

end PASSWORD;

/

show errors

-- Test if it is working...

select password.encrypt('PASSWORD1') from dual;

select password.decrypt(app_password.encrypt('PASSWORD1')) from dual;

select password.encrypt('PSW2') from dual;

select password.decrypt(app_password.encrypt('PSW2')) from dual;

Select records from a cursor into PL SQL table

set serveroutput on

declare

  -- Declare the PL/SQL table

  type deptarr is table of dept%rowtype

       index by binary_integer;

  d_arr deptarr;

  -- Declare cursor

  type d_cur is ref cursor return dept%rowtype;

  c1 d_cur;

  i number := 1;

begin

  -- Populate the PL/SQL table from the cursor

  open c1 for select * from dept;

  loop

    exit when c1%NOTFOUND;

    fetch c1 into d_arr(i);

    i := i+1;

  end loop;

  close c1;

  -- Display the entire PL/SQL table on screen

  for i in 1..d_arr.last loop

    dbms_output.put_line('DEPTNO : '||d_arr(i).deptno );

    dbms_output.put_line('DNAME  : '||d_arr(i).dname  );

    dbms_output.put_line('LOC    : '||d_arr(i).loc    );

    dbms_output.put_line('---------------------------');

  end loop;

end;

/

Profile PL SQL code for execution statistics

conn / as sysdba

-- Install the profiler...

@?/rdbms/admin/proftab

@?/rdbms/admin/profload

@?/plsql/demo/profrep.sql

-- Create a test procedure to time...

CREATE OR REPLACE PROCEDURE proc1 IS

  v_dummy CHAR;

BEGIN

   FOR i IN 1..100 LOOP

      SELECT dummy INTO v_dummy FROM dual;

   END LOOP;

END;

/

SHOW ERRORS

-- Do the profilling and print the report...

set line 5000 serveroutput on size 1000000

DECLARE

  v_run NUMBER;

BEGIN

  DBMS_PROFILER.START_PROFILER('test','test1',v_run);

  proc1;

  DBMS_PROFILER.STOP_PROFILER;

  DBMS_PROFILER.ROLLUP_RUN(v_run);

  PROF_REPORT_UTILITIES.PRINT_RUN(v_run);

END;

/

Simple program to demonstrate BULK COLLECT and BULK BIND operations

set serveroutput on size 50000

DECLARE

  CURSOR emp_cur IS SELECT * FROM EMP;

  TYPE emp_tab_t IS TABLE OF emp%ROWTYPE INDEX BY BINARY_INTEGER;

  emp_tab emp_tab_t;            -- In-memory table

  rows NATURAL        := 10000;   -- Number of rows to process at a time

  i    BINARY_INTEGER := 0;

BEGIN

  OPEN emp_cur;

  LOOP

    -- Bulk collect data into memory table - X rows at a time

    FETCH emp_cur BULK COLLECT INTO emp_tab LIMIT rows;

    EXIT WHEN emp_tab.COUNT = 0;

    DBMS_OUTPUT.PUT_LINE( TO_CHAR(emp_tab.COUNT)|| ' rows bulk fetched.');

    FOR i IN emp_tab.FIRST .. emp_tab.LAST loop

      -- Manipumate data in the memory table...

      dbms_output.put_line('i = '||i||', EmpName='||emp_tab(i).ename);

    END LOOP;

    -- Bulk bind of data in memory table...

    FORALL i in emp_tab.FIRST..emp_tab.LAST

      INSERT /*+APPEND*/ INTO emp2 VALUES emp_tab(i);

  END LOOP;

  CLOSE emp_cur;

END;

Update, delete from a huge table with intermittent commits

loop

      update tab1 set col1 = 'value2'

             where rowid = c1.rowid;

      i := i + 1;              -- Commit after every X records

      if i > 10000 then

         commit;

         i := 0;

      end if;

  end loop;

  commit;

end;

/

-- Note: More advanced users can use the mod() function to commit every N rows. 

--       No counter variable required:

--

-- if mod(i, 10000) 

--    commit;

--    dbms_output.put_line('Commit issued for rows up to: '||c1%rowcount);

--  end if;

Delete duplicate values from a table

DELETE FROM my_table 

 WHERE ROWID NOT IN (SELECT MIN(ROWID) 

                       FROM my_table 

                      GROUP BY delete_col_name);

-- Example :

--

-- Given a table called emp with the following columns:

--   id   number

--   name varchar2(20)

--   sal  number

--

-- To delete the duplicate values:

-- 

-- DELETE FROM emp 

--  WHERE ROWID NOT IN (SELECT MIN(ROWID) FROM emp GROUP BY id); 

-- 

-- COMMIT;

Convert LONG data types to LOBs

create table old_long_table(c1 number, c2 long);

insert into  old_long_table values (1, 'LONG data to convert to CLOB');

create table new_lob_table(c1 number, c2 clob);

-- Use TO_LOB function to convert LONG to LOB...

insert into  new_lob_table

       select c1, to_lob(c2) from old_long_table;

-- Note: the same procdure can be used to convert LONG RAW datatypes to BLOBs.

Demonstrate Oracle temporary tables

drop table x

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

create global temporary table x (a date)

        on commit delete rows     -- Delete rows after commit

        -- on commit preserve rows   -- Delete rows after exit session

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

select table_name, temporary, duration

from   user_tables

where  table_name = 'X'

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

insert into x values (sysdate);

select * from x;

commit;

-- Inserted rows are missing after commit

select * from x;

Demonstrate VARRAY database types

CREATE OR REPLACE TYPE vcarray AS VARRAY(10) OF VARCHAR2(128);

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

CREATE TABLE varray_table (id number, col1 vcarray);

INSERT INTO varray_table VALUES (1, vcarray('A'));

INSERT INTO varray_table VALUES (2, vcarray('B', 'C'));

INSERT INTO varray_table VALUES (3, vcarray('D', 'E', 'F'));

SELECT * FROM varray_table;

SELECT * FROM USER_VARRAYS;

-- SELECT * FROM USER_SEGMENTS;

-- Unnesting the collection:

select t1.id, t2.COLUMN_VALUE

from   varray_table t1, TABLE(t1.col1) t2

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

-- Use PL/SQL to access the varray...

set serveroutput on

declare

  v_vcarray vcarray;

begin

  for c1 in (select * from varray_table) loop

      dbms_output.put_line('Row fetched...');

      FOR i IN c1.col1.FIRST..c1.col1.LAST LOOP

          dbms_output.put_line('...property fetched: '|| c1.col1(i));

      END LOOP;

  end loop;

end;

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

 -- Clean-up...

DROP TABLE varray_table;

Demonstrate Oracle database types and object tables

drop type employee_typ;

create type employee_typ as object (

        empno NUMBER,

        emp_name varchar2(30),

        hiredate date,

        member function days_at_company return NUMBER,

        pragma restrict_references(days_at_company, WNDS)

)

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

create type body employee_tye is

begin

        member function days_at_company return number is

        begin

                return (SYSDATE-hiredate);

        end;

end;

Count the number of rows for ALL tables in current schema

set termout off echo off feed off trimspool on head off pages 0

spool countall.tmp

select 'SELECT count(*), '''||table_name||''' from '||table_name||';'

from   user_tables

/

spool off

set termout on

@@countall.tmp

set head on feed on

SQL*Plus Help script,Test for Leap Years, Spell out numbers to words,Demonstrate simple encoding and decoding of messages

SQL*Plus Help script

select info from   system.help where  upper(topic)=upper('&1')

Test for Leap Years

select year,

       decode( mod(year, 4), 0,

          decode( mod(year, 400), 0, 'Leap Year',

             decode( mod(year, 100), 0, 'Not a Leap Year', 'Leap Year')

          ), 'Not a Leap Year'

       ) as leap_year_indicator

from   my_table

 

 Spell out numbers to words

select decode( sign( &num ), -1, 'Negative ', 0, 'Zero', NULL ) ||

decode( sign( abs(&num) ), +1, to_char( to_date( abs(&num),'J'),'Jsp') ) from dual

 

Demonstrate simple encoding and decoding of messages

SELECT TRANSLATE(

        'HELLO WORLD',                  -- Message to encode

        'ABCDEFGHIJKLMNOPQRSTUVWXYZ ',

        '1234567890!@#$%^&*()-=_+;,.') ENCODED_MESSAGE

FROM DUAL

/ 

SELECT TRANSLATE(

        '85@@%._%*@4',                  -- Message to decode

        '1234567890!@#$%^&*()-=_+;,.',

        'ABCDEFGHIJKLMNOPQRSTUVWXYZ ') DECODED_MESSAGE

FROM DUAL

Pass application info through to the Oracle RDBMS

The following code tells the database what the application is up to: 

begin

   dbms_application_info.set_client_info('BANCS application info');

   dbms_application_info.set_module('BANCS XYZ module', 'BANCS action name');

end;

/

-- Retrieve application info from the database:

select module, action, client_info

from   sys.v_$session where audsid = USERENV('SESSIONID')

/

select sql_text

from   sys.v_$sqlarea

where  module = 'BANCS XYZ module'

and  action = 'BANCS action name'

Display table and column comments

set pages 50000

set null 'No Comments'

tti 'Table Comments'

col comments format a29 wrap word

select * from user_tab_comments;

tti 'Column Comments'

col comments format a18 wrap word

break on table_name skip 1

select * from user_col_comments;

clear break

set null ''

set pages 23

Demonstrate default column values

drop table x

-- /

create table x (a char, b number default 99999, c date, d varchar2(6))

/

alter table x modify (c date default sysdate)

/

insert into x(a, d) values ('a', 'qwerty')

/

select * from x

/

--

-- Expected output:

--

--  A          B C           D

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

--  a      99999 25-APR-2001 qwerty

-

Select the Nth lowest value from a table

select level, min('col_name') from my_table

where level = '&n'

connect by prior ('col_name') < 'col_name')

group by level;

-- Example:

--

-- Given a table called emp with the following columns:

--   id   number

--   name varchar2(20)

--   sal  number

--

-- For the second lowest salary:

--

-- select level, min(sal) from emp

-- where level=2

-- connect by prior sal < sal

-- group by level

Select the Nth highest value from a table

select level, max('col_name') from my_table

where level = '&n'

connect by prior ('col_name') > 'col_name')

group by level;

-- Example :

--

-- Given a table called emp with the following columns:

--   id   number

--   name varchar2(20)

--   sal  number

--

-- For the second highest salary:

--

 select level, max(sal) from emp

 where level=2

 connect by prior sal > sal

 group by level

--

Who am I, script

set termout off

store set store rep

set head off

set pause off

set termout on

select 'User: '|| user || ' on database ' || global_name,

       '  (term='||USERENV('TERMINAL')||

       ', audsid='||USERENV('SESSIONID')||')' as MYCONTEXT

from   global_name;

@store

set termout on

Display Database version, installed options and port string

set head off feed off pages 0 serveroutput on

col banner format a72 wrap

select banner

from   sys.v_$version;

select '   With the '||parameter||' option'

from   sys.v_$option

where  value = 'TRUE';

select '   The '||parameter||' option is not installed'

from   sys.v_$option

where  value <> 'TRUE';

begin

    dbms_output.put_line('Port String: '||dbms_utility.port_string);

end;

/

set head on feed on

Lookup Oracle error messages

set serveroutput on

set veri off feed off

prompt Lookup Oracle error messages:

prompt

prompt Please enter error numbers as negatives. E.g. -1

prompt

exec dbms_output.put_line('==> '||sqlerrm( &errno ) );

set veri on feed on

undef errno

Sample SQL matrix report

SELECT job,

                sum(decode(deptno,10,sal)) DEPT10,

                sum(decode(deptno,20,sal)) DEPT20,

                sum(decode(deptno,30,sal)) DEPT30,

                sum(decode(deptno,40,sal)) DEPT40

           FROM scott.emp

       GROUP BY job

/

-- Sample output:

--

-- JOB           DEPT10     DEPT20     DEPT30     DEPT40

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

-- ANALYST                    6000

-- CLERK           1300       1900        950

-- MANAGER         2450       2975       2850

-- PRESIDENT       5000

-- SALESMAN                              5600

--

SQL Interview questions -5

26)How does one find the next value of a sequence?

Perform an "ALTER SEQUENCE ... NOCACHE" to unload the unused cached sequence numbers from the Oracle library cache. This way, no cached numbers will be lost. If you then select from the USER_SEQUENCES dictionary view, you will see the correct high water mark value that would be returned for the next NEXTVALL call. Afterwards, perform an "ALTER SEQUENCE ... CACHE" to restore caching.

You can use the above technique to prevent sequence number loss before a SHUTDOWN ABORT, or any other operation that would cause gaps in sequence values.

27)Workaround for snapshots on tables with LONG columns

You can use the SQL*Plus COPY command instead of snapshots if you need to copy LONG and LONG RAW variables from one location to another. Eg:

COPY TO SCOTT/TIGER@REMOTE     -

CREATE IMAGE_TABLE USING       -

       SELECT IMAGE_NO, IMAGE  -

       FROM   IMAGES;

Note: If you run Oracle8, convert your LONGs to LOBs, as it can be replicated. 

SQL Interview questions -4

21)How does one implement IF-THEN-ELSE in a select statement?

The Oracle decode function acts like a procedural statement inside an SQL statement to return different values or columns based on the values of other columns in the select statement.

Some examples:

        select decode(sex, 'M', 'Male', 'F', 'Female', 'Unknown')

        from   employees;

        select a, b, decode( abs(a-b), a-b, 'a > b',

                                       0,   'a = b',

                                            'a < b') from  tableX;

        select decode( GREATEST(A,B), A, 'A is greater OR EQUAL than B', 'B is greater than A')...

        select decode( GREATEST(A,B), 

                  A, decode(A, B, 'A NOT GREATER THAN B', 'A GREATER THAN B'), 

                  'A NOT GREATER THAN B')...

Note: The decode function is not ANSI SQL and is rarely implemented in other RDBMS offerings. It is one of the good things about Oracle, but use it sparingly if portability is required.

From Oracle 8i one can also use CASE statements in SQL. Look at this example:

        SELECT ename, CASE WHEN sal>1000 THEN 'Over paid' ELSE 'Under paid' END

        FROM   emp;

22)How can one dump/ examine the exact content of a database column?

        SELECT DUMP(col1)

        FROM tab1

        WHERE cond1 = val1;

        DUMP(COL1)

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

        Typ=96 Len=4: 65,66,67,32

For this example the type is 96, indicating CHAR, and the last byte in the column is 32, which is the ASCII code for a space. This tells us that this column is blank-padded.

23)Can one drop a column from a table?

From Oracle8i one can DROP a column from a table. Look at this sample script, demonstrating the ALTER TABLE table_name DROP COLUMN column_name; command.

Other workarounds:

1. SQL> update t1 set column_to_drop = NULL;

   SQL> rename t1 to t1_base;

   SQL> create view t1 as select <specific columns> from t1_base;

2. SQL> create table t2 as select <specific columns> from t1;

   SQL> drop table t1;

   SQL> rename t2 to t1;

24)Can one rename a column in a table?

From Oracle9i one can RENAME a column from a table. Look at this example:

ALTER TABLE tablename RENAME COLUMN oldcolumn TO newcolumn;

Other workarounds:

1. -- Use a view with correct column names...

   rename t1 to t1_base;

   create view t1 <column list with new name> as select * from t1_base;

2. -- Recreate the table with correct column names...

   create table t2 <column list with new name> as select * from t1;

   drop table t1;

   rename t2 to t1;

3. -- Add a column with a new name and drop an old column...

   alter table t1 add ( newcolame datatype );  

   update t1 set newcolname=oldcolname;

   alter table t1 drop column oldcolname;

25)How can I change my Oracle password?

Issue the following SQL command: ALTER USER <username> IDENTIFIED BY <new_password>
/

From Oracle8 you can just type "password" from SQL*Plus, or if you need to change another user's password, type "password user_name". 

SQL Interview questions -3

16)Can one retrieve only rows X to Y from a table?

Shaik Khaleel provided this solution to the problem:

         SELECT * FROM (

            SELECT ename, rownum rn 

              FROM emp WHERE rownum < 101

         ) WHERE  RN between 91 and 100 ;

Note: the 101 is just one greater than the maximum row of the required rows (means x= 90, y=100, so the inner values is y+1).

Ravi Pachalla provided this solution:

        SELECT rownum, f1 FROM t1

        GROUP BY rownum, f1 HAVING rownum BETWEEN 2 AND 4;

Another solution is to use the MINUS operation. For example, to display rows 5 to 7, construct a query like this:

        SELECT *

        FROM   tableX

        WHERE  rowid in (

           SELECT rowid FROM tableX

           WHERE rownum <= 7

          MINUS

           SELECT rowid FROM tableX

           WHERE rownum < 5);

Youssef Youssef provided this soluton: "this one was faster for me and allowed for sorting before filtering by rownum. The inner query (table A) can be a series of tables joined together with any operation before the filtering by rownum is applied."

  

        SELECT * 

          FROM (SELECT a.*, rownum RN 

                 FROM (SELECT * 

                          FROM t1 ORDER BY key_column) a

                 WHERE rownum <=7)

         WHERE rn >=5

Please note, there is no explicit row order in a relational database. However, this query is quite fun and may even help in the odd situation.

17)How does one select EVERY Nth row from a table?

One can easily select all even, odd, or Nth rows from a table using SQL queries like this:

Method 1: Using a subquery

        SELECT *

        FROM   emp

        WHERE  (ROWID,0) IN (SELECT ROWID, MOD(ROWNUM,4)

                             FROM   emp);

Method 2: Use dynamic views (available from Oracle7.2):

        SELECT *

        FROM   ( SELECT rownum rn, empno, ename

                 FROM emp

               ) temp

        WHERE  MOD(temp.ROWNUM,4) = 0;

Method 3: Using GROUP BY and HAVING - provided by Ravi Pachalla

        SELECT rownum, f1

        FROM t1

        GROUP BY rownum, f1 HAVING MOD(rownum,n) = 0 OR rownum = 2-n

Please note, there is no explicit row order in a relational database. However, these queries are quite fun and may even help in the odd situation.

18)How does one select the TOP N rows from a table?

Form Oracle8i one can have an inner-query with an ORDER BY clause. Look at this example:

        SELECT *

        FROM   (SELECT * FROM my_table ORDER BY col_name_1 DESC)

        WHERE  ROWNUM < 10;

Use this workaround with prior releases:

        SELECT *

          FROM my_table a

         WHERE 10 >= (SELECT COUNT(DISTINCT maxcol)

                        FROM my_table b

                       WHERE b.maxcol >= a.maxcol)

         ORDER BY maxcol DESC;

19)How does one code a tree-structured query?

Tree-structured queries are definitely non-relational (enough to kill Codd and make him roll in his grave). Also, this feature is not often found in other database offerings.

The SCOTT/TIGER database schema contains a table EMP with a self-referencing relation (EMPNO and MGR columns). This table is perfect for tesing and demonstrating tree-structured queries as the MGR column contains the employee number of the "current" employee's boss.
The LEVEL pseudo-column is an indication of how deep in the tree one is. Oracle can handle queries with a depth of up to 255 levels. Look at this example:

        select  LEVEL, EMPNO, ENAME, MGR

          from  EMP

        connect by prior EMPNO = MGR

          start with MGR is NULL;

One can produce an indented report by using the level number to substring or lpad() a series of spaces, and concatenate that to the string. Look at this example:

        select lpad(' ', LEVEL * 2) || ENAME ........

One uses the "start with" clause to specify the start of the tree. More than one record can match the starting condition. One disadvantage of having a "connect by prior" clause is that you cannot perform a join to other tables. The "connect by prior" clause is rarely implemented in the other database offerings. Trying to do this programmatically is difficult as one has to do the top level query first, then, for each of the records open a cursor to look for child nodes.

One way of working around this is to use PL/SQL, open the driving cursor with the "connect by prior" statement, and the select matching records from other tables on a row-by-row basis, inserting the results into a temporary table for later retrieval.

20)How does one code a matrix report in SQL?

Look at this example query with sample output:

        SELECT  *

        FROM  (SELECT job,

                      sum(decode(deptno,10,sal)) DEPT10,

                      sum(decode(deptno,20,sal)) DEPT20,

                      sum(decode(deptno,30,sal)) DEPT30,

                      sum(decode(deptno,40,sal)) DEPT40

                 FROM scott.emp

                GROUP BY job)

        ORDER BY 1;

        JOB           DEPT10     DEPT20     DEPT30     DEPT40

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

        ANALYST                    6000

        CLERK           1300       1900        950

        MANAGER         2450       2975       2850

        PRESIDENT       5000

        SALESMAN                              5600

SQL Interview questions -2

11)How does one get the time difference between two date columns?

Look at this example query:

        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   ...

If you don't want to go through the floor and ceiling math, try this method (contributed by Erik Wile):

        select to_char(to_date('00:00:00','HH24:MI:SS') +

                    (date1 - date2), 'HH24:MI:SS') time_difference

        from ...

Note that this query only uses the time portion of the date and ignores the date itself. It will thus never return a value bigger than 23:59:59.

12)How does one add a day/hour/minute/second to a date value?

The SYSDATE pseudo-column shows the current system date and time. Adding 1 to SYSDATE will advance the date by 1 day. Use fractions to add hours, minutes or seconds to the date. Look at these examples:

        SQL> select sysdate, sysdate+1/24, sysdate +1/1440, sysdate + 1/86400 from dual;

        SYSDATE              SYSDATE+1/24         SYSDATE+1/1440       SYSDATE+1/86400

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

        03-Jul-2002 08:32:12 03-Jul-2002 09:32:12 03-Jul-2002 08:33:12 03-Jul-2002 08:32:13

The following format is frequently used with Oracle Replication:

       select sysdate NOW, sysdate+30/(24*60*60) NOW_PLUS_30_SECS from dual;

        NOW                  NOW_PLUS_30_SECS

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

        03-JUL-2002 16:47:23 03-JUL-2002 16:47:53

Here are a couple of examples:

Description	Date Expression
Now	SYSDATE
Tomorow/ next day	SYSDATE + 1
Seven days from now	SYSDATE + 7
One hour from now	SYSDATE + 1/24
Three hours from now	SYSDATE + 3/24
An half hour from now	SYSDATE + 1/48
10 minutes from now	SYSDATE + 10/1440
30 seconds from now	SYSDATE + 30/86400
Tomorrow at 12 midnight	TRUNC(SYSDATE + 1)
Tomorrow at 8 AM	TRUNC(SYSDATE + 1) + 8/24
Next Monday at 12:00 noon	NEXT_DAY(TRUNC(SYSDATE), 'MONDAY') + 12/24
First day of next month at 12 midnight	TRUNC(LAST_DAY(SYSDATE ) + 1)
First day of the current month	TRUNC(LAST_DAY(ADD_MONTHS(SYSDATE,-1))) + 1
The next Monday, Wednesday or Friday at 9 a.m	TRUNC(LEAST(NEXT_DAY(sysdate,''MONDAY'' ),NEXT_DAY(sysdate,''WEDNESDAY''), NEXT_DAY(sysdate,''FRIDAY'' ))) + (9/24)

13)How does one count different data values in a column?

Use this simple query to count the number of data values in a column:

        select my_table_column, count(*)

        from   my_table

        group  by my_table_column;

A more sophisticated example...

        select dept, sum(  decode(sex,'M',1,0)) MALE,

                     sum(  decode(sex,'F',1,0)) FEMALE,

                     count(decode(sex,'M',1,'F',1)) TOTAL

        from   my_emp_table

        group  by dept;

14)How does one count/sum RANGES of data values in a column?

A value x will be between values y and z if GREATEST(x, y) = LEAST(x, z). Look at this example:

        select f2,

               sum(decode(greatest(f1,59), least(f1,100), 1, 0)) "Range 60-100",

               sum(decode(greatest(f1,30), least(f1, 59), 1, 0)) "Range 30-59",

               sum(decode(greatest(f1, 0), least(f1, 29), 1, 0)) "Range 00-29"

        from   my_table

        group  by f2;

For equal size ranges it might be easier to calculate it with DECODE(TRUNC(value/range), 0, rate_0, 1, rate_1, ...). Eg.

        select ename "Name", sal "Salary",

               decode( trunc(f2/1000, 0), 0, 0.0,

                                          1, 0.1,

                                          2, 0.2,

                                          3, 0.31) "Tax rate"

        from   my_table;

15)Can one retrieve only the Nth row from a table?

Rupak Mohan provided this solution to select the Nth row from a table:

        SELECT * FROM t1 a

        WHERE  n = (SELECT COUNT(rowid)

               FROM t1 b

               WHERE a.rowid >= b.rowid);

Shaik Khaleel provided this solution:

         SELECT * FROM (

            SELECT ENAME,ROWNUM RN FROM EMP WHERE ROWNUM < 101 )

         WHERE  RN = 100;

Note: In this first query we select one more than the required row number, then we select the required one. Its far better than using a MINUS operation.

  

Ravi Pachalla provided these solutions:

        SELECT f1 FROM t1

        WHERE  rowid = (

               SELECT rowid FROM t1

               WHERE  rownum <= 10

               MINUS

               SELECT rowid FROM t1

               WHERE  rownum < 10);

        SELECT rownum,empno FROM scott.emp a

        GROUP BY rownum,empno HAVING rownum = 4;

Alternatively...

        SELECT * FROM emp WHERE rownum=1 AND rowid NOT IN

           (SELECT rowid FROM emp WHERE rownum < 10);

Please note, there is no explicit row order in a relational database. However, this query is quite fun and may even help in the odd situation.

SQL Interview questions -1

1.      Write a query to eliminate duplicate rows in a table?

Delete from emp x where rowid <= ( select max(rowid) from emp y where x.rowid = y.rowid)

2.      Write a query to find max 5 salaries of employees ?

Select * from (select * from emp order by sal desc) where rownum < 6

3)Show all data for Clerks hired after the year 1997

  Select * from employees where job_id=’ST_CLERK’ and hire_date >’31-dec-1997’;

4) show the last name,job,salary and commission of those employees who earn commission sort the data by the salary in descending order

        SELECT LAST_NAME,JOB_ID,SALARY,COMMISSION_PCT FROM EMPLOYEES WHERE COMMISSION_PCT IS NOT NULL ORDER BY SALARY DESC;

5) Show the employees who have no commission not have a 10% raise in salary (round off salaries)

      Select ‘the salary of’|| last_name||’after a 10% raise is’ from employees

Where  commission_pct is null;

6)What is SQL and where does it come from?

Structured Query Language (SQL) is a language that provides an interface to relational database systems. SQL was developed by IBM in the 1970s for use in System R, and is a de facto standard, as well as an ISO and ANSI standard. SQL is often pronounced SEQUEL.

In common usage SQL also encompasses DML (Data Manipulation Language), for INSERTs, UPDATEs, DELETEs and DDL (Data Definition Language), used for creating and modifying tables and other database structures.
The development of SQL is governed by standards. A major revision to the SQL standard was completed in 1992, called SQL2. SQL3 support object extensions and are (partially?) implemented in Oracle8 and 9. 

7)What are the difference between DDL, DML and DCL commands?

DDL is Data Definition Language statements. Some examples:

• CREATE - to create objects in the database
• ALTER - alters the structure of the database
• DROP - delete objects from the database
• TRUNCATE - remove all records from a table, including all spaces allocated for the records are removed
• COMMENT - add comments to the data dictionary
• GRANT - gives user's access privileges to database
• REVOKE - withdraw access privileges given with the GRANT command

DML is Data Manipulation Language statements. Some examples:

• SELECT - retrieve data from the a database
• INSERT - insert data into a table
• UPDATE - updates existing data within a table
• DELETE - deletes all records from a table, the space for the records remain
• CALL - call a PL/SQL or Java subprogram
• EXPLAIN PLAN - explain access path to data
• LOCK TABLE - control concurrency

DCL is Data Control Language statements. Some examples:

• COMMIT - save work done
• SAVEPOINT - identify a point in a transaction to which you can later roll back
• ROLLBACK - restore database to original since the last COMMIT
• SET TRANSACTION - Change transaction options like what rollback segment to use 

8)How does one escape special characters when building SQL queries?

The LIKE keyword allows for string searches. The '_' wild card character is used to match exactly one character, '%' is used to match zero or more occurrences of any characters. These characters can be escaped in SQL. Example:

        SELECT name FROM emp WHERE id LIKE '%\_%' ESCAPE '\';

Use two quotes for every one displayed. Example:

        SELECT 'Franks''s Oracle site' FROM DUAL;

        SELECT 'A ''quoted'' word.' FROM DUAL;

        SELECT 'A ''''double quoted'''' word.' FROM DUAL;                           

9)How does one eliminate duplicates rows from a table?

Choose one of the following queries to identify or remove duplicate rows from a table leaving only unique records in the table:

Method 1:

   SQL> DELETE FROM table_name A WHERE ROWID > (

     2    SELECT min(rowid) FROM table_name B

     3    WHERE A.key_values = B.key_values);

Method 2:

   SQL> create table table_name2 as select distinct * from table_name1;

   SQL> drop table_name1;

   SQL> rename table_name2 to table_name1;

   SQL> -- Remember to recreate all indexes, constraints, triggers, etc on table... 

Method 3: (thanks to Dennis Gurnick)

   SQL> delete from my_table t1

   SQL> where  exists (select 'x' from my_table t2

   SQL>                 where t2.key_value1 = t1.key_value1

   SQL>                   and t2.key_value2 = t1.key_value2

   SQL>                   and t2.rowid      > t1.rowid);

Note: One can eliminate N^2 unnecessary operations by creating an index on the joined fields in the inner loop (no need to loop through the entire table on each pass by a record). This will speed-up the deletion process.

Note 2: If you are comparing NOT-NULL columns, use the NVL function. Remember that NULL is not equal to NULL. This should not be a problem as all key columns should be NOT NULL by definition.

10)How does one generate primary key values for a table?

Create your table with a NOT NULL column (say SEQNO). This column can now be populated with unique values:

        SQL> UPDATE table_name SET seqno = ROWNUM;

or use a sequences generator:

        SQL> CREATE SEQUENCE sequence_name START WITH 1 INCREMENT BY 1;

        SQL> UPDATE table_name SET seqno = sequence_name.NEXTVAL;

Finally, create a unique index on this column. 

Database

Database Information and all.