Sunday, March 18, 2012

Few important SQL in Oracle

Dear reader,
I am writing few sql queries in this blog article which is used for interview purposes,
I hope you will find useful. I have given table creation, seed data and all needed sql 
query for the first Question only as it will help to start. I mean from "Question 2"
onwards only necessary query has been provided. All queries I have tested on my machine.

Q.1) Given a table EMPLOYEE with data below:
    --------------
    ID    EMPNAME        MANAGERID
    110    Saikumar    114
    111    Deepak        114
    112    Srikanth    114
    113    Gudeti_Shekhar    114
    114    Sandeep        118
    115    Kotesh        118
    118    Rishi        120
    119    Khem        120
    120    Ganesh        null
    --------------
   Assuming MANAGERS are also EMPLOYEE, write a query to fetch ID, EMPNAME, MANAGERNAME.

Ans): This can be done in three ways: Either SELF JOIN, Normal JOIN operation or Sub-query.
 Writing TABLE creation and SEED DATA entry first:
--------------
    CREATE TABLE EMPLOYEE(ID NUMBER, EMPNAME VARCHAR(20), MANAGERID NUMBER);
    ALTER TABLE EMPLOYEE ADD CONSTRAINT PK_ID PRIMARY KEY (ID);

    INSERT INTO Employee VALUES(110,'Saikumar',114);
    INSERT INTO Employee VALUES(111,'Deepak',114);
    INSERT INTO Employee VALUES(112,'Srikanth',114);
    INSERT INTO Employee VALUES(113,'Gudeti_Shekhar',114);
    INSERT INTO Employee VALUES(114,'Sandeep',118);
    INSERT INTO Employee VALUES(115,'Kotesh',118);
    INSERT INTO Employee VALUES(118,'Rishi',120);
    INSERT INTO Employee VALUES(119,'Khem',120);
    INSERT INTO Employee VALUES(120,'Ganesh',null);
--------------
Fetching Query 1:
 SELECT a.id eid, a.empname empname, b.empname MANAGER FROM EMPLOYEE a, EMPLOYEE b WHERE a.managerid=b.id;

Fetching Query 2:
 SELECT A.ID, A.EMPNAME, B.EMPNAME AS MANAGER FROM EMPLOYEE A JOIN EMPLOYEE B ON A.MANAGERID = B.ID;
 
Fetching Query 3:
 SELECT A.ID,A.EMPNAME,B.EMPNAME AS MANAGER FROM
 (SELECT ID,EMPNAME,MANAGERID FROM EMPLOYEE)A,
 (SELECT ID,EMPNAME FROM EMPLOYEE)B
 WHERE A.MANAGERID=B.ID;

Output from all 3 queries:
    ID    EMPNAME            MANAGER
    113    Gudeti_Shekhar    Sandeep
    112    Srikanth        Sandeep
    111    Deepak            Sandeep
    110    Saikumar        Sandeep
    115    Kotesh            Rishi
    114    Sandeep            Rishi
    119    Khem            Ganesh
    118    Rishi            Ganesh

NOTE: Here the last inserted record where there is no manager (super boss) is not 
      showing in the output. To show such null records too, I have modified the "Fetching Query 2" 
      and added LEFT OUTER JOIN as below:
      
      SELECT A.ID, A.EMPNAME, B.EMPNAME AS MANAGER FROM EMPLOYEE A LEFT OUTER JOIN EMPLOYEE B 
      ON A.MANAGERID = B.ID;

      Now all the records will show properly. Can be fine tuned more not to show NULL values:
      
      SELECT A.ID, A.EMPNAME, NVL(B.EMPNAME,'NO MANAGER') AS MANAGER FROM EMPLOYEE A LEFT OUTER JOIN 
      EMPLOYEE B ON A.MANAGERID = B.ID;
      
    
==================================
Q.2) Write a query to find 2nd Highest salary employee.
 Table data:
  EMP_ID    EMP_NAME    EMP_SAL
    1    Anees    10000
    2    Rick    12000
    3    John    11000
    4    Stephen    13000
    5    Maria    14000
    6    Deepak    13500
    7    Rajesh    12500
Ans:)
Max Salary:  SELECT Max(emp_sal) FROM employee_test;
2nd Max salary: 
SELECT EMP_ID, emp_sal, EMP_NAME FROM employee_test WHERE emp_sal=
 (
  SELECT Max(emp_sal) FROM employee_test WHERE emp_sal IN
    (
    SELECT emp_sal FROM employee_test WHERE emp_sal< (SELECT Max(emp_sal) FROM employee_test)
    )
  );
//Output 
EMP_ID    EMP_SAL    EMP_NAME
6    13500    Deepak    
==================================

No comments:

Post a Comment