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.