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