Do You Know-Small Queries

1.    Execute a Procedure Dynamically ( Procedure Name is passed as a Parameter to another procedure)

EXECUTE IMMEDIATE 'BEGIN '||procedure_namel||'; END;';

2.    Get count of a particular alphabet in a String

SELECT LENGTH(UPPER('Arun Kumar Gupta')) - LENGTH(REPLACE(UPPER( 'Arun  Kumar Gupta'),'A')) "Count of A"

  FROM DUAL;

3.    Get prime numbers between 2 numbers using only SQL

  SELECT l prime_number

    FROM (SELECT LEVEL l

            FROM DUAL CONNECT BY LEVEL <= 100

         )

        ,(SELECT LEVEL m

            FROM DUAL CONNECT BY LEVEL <= 100

         )

   WHERE m        <= l

GROUP BY l

  HAVING COUNT(CASE l/m WHEN TRUNC(L/M)

                        THEN 'Y'

                END

              )   = 2

ORDER BY l;

--Some Efficient methods.
--Method-1

WITH t AS
(SELECT LEVEL val, sqrt(level) sqrt_val
   FROM DUAL CONNECT BY LEVEL <= 10000
)            
SELECT prime_number
  FROM (
          SELECT t1.val prime_number
            FROM t          t1
                ,t          t2
           WHERE t2.val     <= t1.sqrt_val
             AND MOD(t1.val,t2.val) = 0
       )
GROUP BY prime_number
HAVING COUNT(1) = 1

ORDER BY prime_number; 

--Method-2

WITH t AS
(SELECT LEVEL val, sqrt(level) sqrt_val
   FROM DUAL CONNECT BY LEVEL <= 10000
)            
  SELECT t1.val     prime_number
    FROM t          t1
        ,t          t2
   WHERE t2.val     <= t1.sqrt_val
GROUP BY t1.val
  HAVING COUNT(CASE WHEN t1.val/t2.val = TRUNC(t1.val/t2.val)
                    THEN 1
                END
              )   = 1
ORDER BY t1.val

; 

4.    Get the product (multiplication) of the values in a column

SELECT EXP(SUM(LN(column_name)))

  FROM table_name;

5.    Get list Of days in a Month

SET PAGESIZE 100

SET FEEDBACK OFF

SET TIMING OFF

SELECT TO_CHAR((SYSDATE + 16 - ROWNUM),'DAY       DD-MON-YYYY')  AS "Day             Date"

  FROM all_objects

 WHERE ROWNUM <= 31;

6.    Get the sequential numbers between 2 numbers

 SELECT n + &initial_value

   FROM ( SELECT ROWNUM -1 n

            FROM DUAL      

CONNECT BY LEVEL <= (&final_value  - &initial_value) + 1

        )

7.    Select nth Highest Salary without Using ROWNUM or ROWID

SELECT EMPNO,ENAME,SAL

  FROM EMP     A

 WHERE &N       = (SELECT COUNT(DISTINCT SAL)

                    FROM EMP        B

                   WHERE B.SAL      >= A.SAL

                 );

Here N=1,2,3,4,5.......