SQL Interview Questions and Answers Part1

Q) i have emp table in which columns are empid,empname,sal now i want to increase the sal of the particular emp whose sal is <10000 with 2000RS & sal >10000 & <20000 with 4000RS and whose sal is >20000 with 5000RS now write a single update query.

UPDATE Emp

SET sal = sal +

CASE

WHEN sal>=1000 AND sal<2000 THEN 200

WHEN sal>=2000 AND sal<3000 THEN 300

END;

1. The following query retrieves "2" highest paid employees FROM each Department :

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;

2. Query that will display the total no. of employees, and of that total the number who were hired in 1980, 1981, 1982, and 1983. Give appropriate column headings.

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;

3. Query for listing Deptno, ename, sal, SUM(sal in that dept) :

     

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

4. Create a matrix query to display the job, the salary for that job based on department number, and the total salary for that job for all departments, giving each column an appropriate heading.

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 ;

5. 4th Top Salary of all the employees:

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;

6. Retrieving the 5th row FROM a table:

SELECT DEPTNO, ENAME, SAL

FROM EMP 

WHERE ROWID = (SELECT ROWID FROM EMP   WHERE ROWNUM <= 5

      MINUS

      SELECT ROWID FROM EMP WHERE ROWNUM < 5)

7. Tree Query :

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';

8.  Eliminate duplicates rows in a table :

DELETE FROM table_name A

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

9.  Displaying EVERY 4th row in a table : (If a table has 14 rows,  4,8,12 rows will be selected)

SELECT *

FROM   emp

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

                                       FROM   emp);

10. Top N rows FROM a table : (Displays top 9 salaried people)

SELECT ename, deptno, sal

FROM (SELECT * FROM emp ORDER BY sal DESC)

WHERE ROWNUM < 10;

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

        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;

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

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;

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

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;

14. Query to get the product of all the values of a column :

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

15. Query to display only the duplicate records in a table:

SELECT num

FROM satyam

GROUP BY num

HAVING COUNT(*) > 1;

16. Query for getting the following output as many number of rows in the table :

*

**

***

****

*****

SELECT RPAD(DECODE(temp,temp,'*'),ROWNUM,'*')

FROM srinu1;

17. Function for getting the Balance Value :

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;

18.  Function for getting the Element Value :

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;

19.  SELECT Query for counting No of words :

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-Z including 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 with nothing in the above result.

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

20. Function to check for a leap year :

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';