Monday, July 20, 2015

INLINE view in Oracle with 12c New Features


  • Named sub Query in FROM clause is call it as INLINE VIEW.
  • Oracle process a inline view faster than sub query

Inline View in Oracle

--Query to display all the employees who are all getting salary more than their department average salary

SELECT First_name
     , last_name
     , salary
     , department_id
  FROM employees a , ( SELECT b.department_id 
      , AVG(b.salary) avg_sal
           FROM employees b
                              GROUP BY b.department_id
) b
 WHERE a.department_id = b.department_id
   AND a.salary > b.avg_salary;


Display all the customer who have placed more number of orders by using INLINE view


CREATE TABLE customer_order_det_tb
  ( custm_id NUMBER               ,
    custm_nm VARCHAR2(30)         ,
    order_dt DATE DEFAULT SYSDATE ,
    ttl_amt  NUMBER(7,2)  
  );
BEGIN  
  INSERT INTO customer_order_det_tb VALUES(100, 'Murugappan', '11-Jul-15' , 45000.00);
  INSERT INTO customer_order_det_tb VALUES(100, 'Murugappan', '14-Jul-15' , 27000.00);
  INSERT INTO customer_order_det_tb VALUES(101, 'Raja'      , '13-Jul-15' , 17000.00);
  INSERT INTO customer_order_det_tb VALUES(102, 'Ramesh'    , '15-Jul-15' , 34000.00);
  INSERT INTO customer_order_det_tb VALUES(102, 'Ramesh'    , '15-Jul-15' , 38000.00);
  COMMIT;
END;


SELECT c.* 
  FROM customer_order_det_tb c;

--------------------------------------------
custm_id  custm_nm      order_dt    ttl_amt
--------------------------------------------
100       Murugappan 7/11/2015   45000.00
100       Murugappan 7/14/2015   27000.00
101       Raja          7/13/2015   17000.00
102       Ramesh 7/15/2015   34000.00
102       Ramesh 7/15/2015   38000.00
--------------------------------------------

--Writing a Quer to display all the customer who have placed maximum number of order

--Query #1 for inline view
                
  SELECT c.custm_id, c.custm_nm, COUNT(*) total_odr
    FROM customer_order_det_tb c 
GROUP BY c.custm_id,c.custm_nm
ORDER BY total_odr DESC;

--------------------------------
custm_id  custm_nm    total_odr
--------------------------------
     100  Murugappan 2
     102  Ramesh 2  
     101  Raja        1
--------------------------------

--Query #2 for inline view

  SELECT MAX(COUNT(*)) total_odr
    FROM customer_order_det_tb cnt
GROUP BY cnt.custm_id, cnt.custm_nm;

----------
total_odr
----------
     2
----------

Inline view to find all the customers who have placed maximum number of orders

/**************************************** 
Syntax 

SELECT * FROM <query1> x, <query2> y
WHERE contition; 
****************************************/

SELECT query1.* FROM 
         (   SELECT c.custm_id, c.custm_nm, COUNT(*) total_odr
               FROM customer_order_det_tb c 
           GROUP BY c.custm_id,c.custm_nm
           ORDER BY total_odr DESC
         ) query1,
         (   SELECT MAX(COUNT(*)) total_odr
               FROM customer_order_det_tb cnt
           GROUP BY cnt.custm_id, cnt.custm_nm  
         ) query2
WHERE query1.total_odr = query2.total_odr;

-------------------------------
custm_id  custm_nm    total_odr
-------------------------------   
     100  Murugappan
     102  Ramesh 2
-------------------------------

DROP TABLE customer_order_det_tb;

--Display name and salary for top three money makers of the company and their designation should not be SALESMAN and PRESIDENT


SELECT first_name, salary
  FROM (  SELECT first_name, salary
            FROM employees e
           WHERE job_id NOT IN ('SALESMAN','PRESIDENT')
        ORDER BY salary DESC
)
 WHERE ROWNUM < 4;



New Oracle 12c Inline view Syntax


  • LATERAL clause for In-line views
  • Allows for columns in the inline view to be accessed!!

Example :

--in 11g
SELECT * FROM employees e, (
SELECT * 
 FROM departments d
        WHERE e.department_id = d.department_id);

ORA-0090: "E"."DEPARTMENT_ID": Invalid Identifier


--in 12c
SELECT * FROM employees e, LATERAL(
                                    SELECT * 
                                      FROM departments d
            WHERE e.department_id = d.department_id
                                  );


--in 11g
WITH t 
AS              
(   SELECT LEVEL a 
      FROM dual 
CONNECT BY LEVEL <= 30)
        SELECT * 
          FROM t, (SELECT * 
                     FROM employees
                    WHERE department_id = t.a
                  );
                  
ORA-00904: "T"."A": invalid Identifier

--in 12c
WITH t 
AS              
(SELECT LEVEL a FROM dual CONNECT BY LEVEL <= 30)
        SELECT * FROM t,
        LATERAL(SELECT  * 
           FROM employees
          WHERE department_id = t.a); 


Hope you have enjoyed this little article 

                Feed backs are always Welcome :) (:

Friday, January 23, 2015

Salary Related Questions in Oracle




       
 --creating table

 CREATE TABLE employee_data
       (
       eid        NUMBER(4),
       ename      VARCHAR2(30),
       depno      NUMBER(3),
       salary     NUMBER(8)
       );
    



BEGIN      

    --Inserting records
    INSERT INTO employee_data VALUES(1000,'Name01',90,30000);
    INSERT INTO employee_data VALUES(1001,'Name02',90,6000);
    INSERT INTO employee_data VALUES(1002,'Name03',90,23000);
    INSERT INTO employee_data VALUES(1003,'Name04',60,35000);
    INSERT INTO employee_data VALUES(1004,'Name05',60,60000);
    INSERT INTO employee_data VALUES(1005,'Name06',60,30000);
    INSERT INTO employee_data VALUES(1006,'Name07',80,36000);
    INSERT INTO employee_data VALUES(1007,'Name08',80,29000);
    INSERT INTO employee_data VALUES(1008,'Name09',80,37000);
    INSERT INTO employee_data VALUES(1009,'Name10',80,41000);
    COMMIT;
END;
/


SELECT * FROM employee_data
ORDER BY salary DESC;

--query to find maximum salary from employee_data  table


SELECT MAX(salary) FROM employee_data; 


--query to find minimum salary from employee_data  table

SELECT MIN(salary) FROM employee_data;


--query to find second maximum salary from employee_data  table

SELECT MAX(salary)
  FROM employee_data
 WHERE salary NOT IN (SELECT MAX(salary)
                        FROM employee_data);


--query to select 5th maximum salary


SELECT MIN(salary)
  FROM (SELECT salary
          FROM (SELECT salary
                  FROM employee_data
                 ORDER BY salary DESC)
         WHERE rownum <= 5);


--query to select nth maximum salary



SELECT MIN(salary)
  FROM (SELECT salary
          FROM (SELECT salary
                  FROM employee_data
                 ORDER BY salary DESC)
         WHERE rownum <= &n);

--query to select all the details of the employee whose getting nth maximum salary

 

SELECT *
  FROM (SELECT emp.*,
               rownum rn
          FROM (SELECT *
                  FROM employee_data
                 ORDER BY salary DESC) emp)
 WHERE rn = 9;

--same query using row_numbr( ) function

SELECT *
  FROM (SELECT emp.*,
               row_number() over(ORDER BY salary) rank
          FROM employee_data emp)
 WHERE rank = 9;

--same query using rank( ) function


SELECT *
  FROM (SELECT emp.*,
               rank() over(ORDER BY salary DESC) rank
          FROM employee_data emp)
 WHERE rank = 4;

--same query using dense_rank( ) function



SELECT *
  FROM (SELECT emp.*,
               dense_rank() over(ORDER BY salary DESC) rank
          FROM employee_data emp)
 WHERE rank = 4;

--query to display display maximum salary in all the department

SELECT e.depno "Department id",
       MAX(salary) "Maximum Salary"
  FROM employee_data e
 GROUP BY e.depno;

--query to display all the employees who are all getting salary more than average salary of the company


SELECT e.* FROM employee_data e
WHERE salary > (SELECT AVG(salary) FROM employee_data);

--query to display all the employees who are all getting salary more than their department average salary



SELECT employee_id,
       first_name,
       salary
  FROM employees OUT
 WHERE salary > (SELECT AVG(salary)
                   FROM employees
                  WHERE department_id = OUT.department_id);
                               
--query to display all the employees who are all getting salary more than their manager salary


                    
SELECT employee_id,
       first_name,
       salary
  FROM employees OUT
 WHERE salary > (SELECT AVG(salary)
                   FROM employees
                  WHERE employee_id = OUT.manager_id);





Documented By : Murugappan Annamalai
Posted on         : 23-Jan-15 16:34:00 IST.
                               
                                

Hope you have enjoyed this little article
               Feed backs are always welcome :)                  
                               

Thursday, January 22, 2015

Why prefer COALESCE over NVL


Documented By : Nimish Garg

 I prefer using "COALESCE" over "NVL" is some of the scenarios. Last week One of my friend asked me what is the advantage of using "COALESCE" where we can simply use "NVL". I simply gave him the reply from Oracle Docs i.e. NVL lets you replace null (returned as a blank) with a string in the results of a query and COALESCE returns the first non-null expr in the expression list. Oracle Database uses short-circuit evaluation with "COALESCE".

He replied that he knows the difference, he knows that "COALESCE" can take multiple arguments and so on. He was more interested in understanding "short-circuit evaluation" of "COALESCE" and the scenarios where I prefer using "COALESCE" over "NVL".

To make him understand I created following function which takes One second in every execution and simply returns '--null--' and executed 2 very similar queries one with NVL and other with COALESCE.
 

SQL> create or replace function f_null return varchar2
  2  is
  3  begin
  4     dbms_lock.sleep(1);
  5     return '--null--';
  6  end;
  7  /

Function created.
 
 Following are the queries which I used to demonstrate that NVL evaluates both arguments even if the second argument is not used and COALESCE uses short-circuit evaluation i.e. it only evaluates the arguments only if they are needed.


SQL> select e.empno, e.ename ename, nvl(m.ename,f_null) mname
  2  from scott.emp e, scott.emp m
  3  where e.mgr = m.empno(+);

     EMPNO ENAME      MNAME
---------- ---------- ----------
      7902 FORD       JONES
      7788 SCOTT      JONES
      7900 JAMES      BLAKE
      7844 TURNER     BLAKE
      7654 MARTIN     BLAKE
      7521 WARD       BLAKE
      7499 ALLEN      BLAKE
      7934 MILLER     CLARK
      7876 ADAMS      SCOTT
      7782 CLARK      KING
      7698 BLAKE      KING
      7566 JONES      KING
      7369 SMITH      FORD
      7839 KING       --null--

14 rows selected.

Elapsed: 00:00:14.01

SQL> select e.empno, e.ename ename, coalesce(m.ename,f_null) mname
  2  from scott.emp e, scott.emp m
  3  where e.mgr = m.empno(+);

     EMPNO ENAME      MNAME
---------- ---------- ----------
      7902 FORD       JONES
      7788 SCOTT      JONES
      7900 JAMES      BLAKE
      7844 TURNER     BLAKE
      7654 MARTIN     BLAKE
      7521 WARD       BLAKE
      7499 ALLEN      BLAKE
      7934 MILLER     CLARK
      7876 ADAMS      SCOTT
      7782 CLARK      KING
      7698 BLAKE      KING
      7566 JONES      KING
      7369 SMITH      FORD
      7839 KING       --null--

14 rows selected.

Elapsed: 00:00:01.01

Here we can see easily that the first query with NVL took 14+ seconds, one second for each record even if the "f_null" value was used only in one record. On the contrary as mentioned in Oracle Documentation "COALESCE" uses its "short-circuit evaluation" and "f_null" was called only once and so second query took only One Second. "COALESCE" is certainly use less resources than NVL.

Sunday, December 21, 2014

Constraints


It Enforce Rule On Table



We can create constraint at the time of Creating Table

      1. Column Level Declaration 
2. Table Level Declaration

1. We can provide own name. 

          Format : [part_of_project_name-table_name-column_name-constraint_type]
           Example : gmind_emp_id_pk _uk _ck _fk

            2. system name name                                                

             Format  :  sys_cn 
Example : SYS_C405323

We can create constraint after the table has been created

Types

 

Primary Key

Not allowed Null value
Not allowed you to enter duplicate value
Ex :  Employee_id,  Student_id

Unique Key

allowed Null value
Not allowed you to enter duplicate value
Ex  :  Contact_no, email

  Foreign Key

 It will allowed you to enter NULL and Duplicate value.

c1(pk)  :  1  2   3    
c2(fk)  :  1  1  2  3       4  Not allowed


Allowed you to enter duplicate value and null value

Not Null

It will not allowed you to enter null value

   Check

 You can check your own condition

Simple Table for understanding


------------------------------------
             Duplicate    NULL
------------------------------------
Primary Key      X       X
Unique Key       X       Allowed 
Foreign Key   Allowed    Allowed
------------------------------------


creating table with all constraint type

 
 Create table my_stu_details
(
S_id       Number(2),
S_name     Varchar2(30) Not Null,   --SYS_Cn
s_mail     Varchar2(30),
s_gender   char(1),
s_did      Number(3),
Constraint my_stu_sid_pk   PRIMARY KEY (s_id),
constraint my_stu_mail_uk  UNIQUE  (s_mail)  ,
Constraint my_stu_gen_ck   CHECK (s_gender IN ('M', 'F', 'm', 'f')),
Constraint my_stu_did_fk   FOREIGN KEY (s_did) References Departments(Department_id)
);


DESC my_stu_details;

------------------------------------
Name       Type            Nullable
------------------------------------
S_ID     NUMBER(2)
S_NAME   VARCHAR2(30)
S_MAIL   VARCHAR2(30)      Y
S_GENDER  CHAR(1)           Y
S_DID    NUMBER(3)         Y
------------------------------------


Insert into my_stu_details (s_id, s_name  , s_mail            , s_gender, s_did) 
                    values (1   , 'name1' , 'name1@gmail.com' , 'm'     , 60   );



 Select * from my_stu_details;


 
Validating Primary Key

 
Insert into my_stu_details (s_id, s_name  , s_mail            , s_gender, s_did) 
                    values (1   , 'name2' , 'name2@gmail.com' , 'F'     , 90   );

ORA-00001: unique constraint (HR.MY_STU_SID_PK) violated

Insert into my_stu_details (s_id, s_name  , s_mail            , s_gender, s_did) 
                    values (NULL, 'name2' , 'name2@gmail.com' , 'F'     , 90   );
                    
ORA-01400: cannot insert NULL into (HR.MY_STU_SID_PK) violated

Insert into my_stu_details (s_id, s_name  , s_mail            , s_gender, s_did) 
                    values (2   , 'name2' , 'name2@gmail.com' , 'F'     , 90   );

Select * from my_stu_details;

 
Validating NOT NULL Constraint


 
Insert into my_stu_details (s_id, s_name  , s_mail            , s_gender, s_did) 
                    values (3   , NULL    , 'name3@gmail.com' , 'F'     , 80   );

ORA-01400: cannot insert NULL into ("HR"."MY_STU_DETAILS"."S_NAME")

Insert into my_stu_details (s_id, s_name  , s_mail            , s_gender, s_did) 
                    values (3   , 'Name3' , 'name3@gmail.com' , 'F'     , 80   );

Select * from my_stu_details;

 
Validating Unique Key Constraint


 
--Duplicate Check


 
Insert into my_stu_details (s_id, s_name  , s_mail            , s_gender, s_did) 
                    values (4   , 'Name4' , 'name3@gmail.com' , 'F'     , 80   );

ORA-00001: unique constraint (HR.MY_STU_MAIL_UK) violated

--Null Check

Insert into my_stu_details (s_id, s_name  , s_mail  , s_gender, s_did) 
                    values (4   , 'Name4' , null    , 'M'     , 70   );


Select * from my_stu_details;

 
Validating Check constraint


 
Insert into my_stu_details (s_id, s_name  , s_mail             , s_gender , s_did) 
                    values (5   , 'Name5' , 'name5@gmail.com'  , 'j'      , 70   );

ORA-02290: check constraint (HR.MY_STU_GEN_CK) violated

Insert into my_stu_details (s_id, s_name  , s_mail             , s_gender , s_did) 
                    values (5   , 'Name5' , 'name5@gmail.com'  , 'M'      , 70   );

Select * from my_stu_details;

 
Validating Foreign Key


 
Select * from Departments;

Insert into my_stu_details (s_id, s_name  , s_mail             , s_gender , s_did) 
                    values (6   , 'Name6' , 'name6@gmail.com'  , 'F'      , 3   );

 
ORA-02291: integrity constraint (HR.MY_STU_DID_FK) violated - parent key not found


 
Departments table  :  parent table
my_stu_details     :  Child table

Insert into my_stu_details (s_id, s_name  , s_mail             , s_gender , s_did) 
                    values (6   , 'Name6' , 'name6@gmail.com'  , 'F'      , 90   );
                    

Select * from my_stu_details;

--------------------------------------------------------
s_id     s_name   s_mail                s_gender  s_did
--------------------------------------------------------
 1 name1  name1@gmail.com   m     60
 2 name2  name2@gmail.com   F     90
 3 Name3  name3@gmail.com   F     80
 4 Name4                   M     70
 5 Name5  name5@gmail.com   M     70
 6 Name6  name6@gmail.com   F     90
--------------------------------------------------------



Deleting the Primary key value which is referred by Foreign Key


Select * from Departments;

DEPARTMENT_ID DEPARTMENT_NAME MANAGER_ID LOCATION_ID
------------------------------------------------------------
60        IT        103        1400           <----  Delete


-- don't execute this query

--Delete From Departments
--Where  Department_id = 60;

1) ON DELETE SET NULL

-------------------------------------------------
S_ID S_NAME S_MAIL         S_GENDER  S_DID
-------------------------------------------------
6 Name6 name6@gmail.com F   90
1 name1 name1@gmail.com m   -
2 name2 name2@gmail.com F   90
3 Name3 name3@gmail.com F   80
4 Name4 -               M   70
5 Name5 name5@gmail.com M   70
-------------------------------------------------

2) ON DELETE SET CASCADE

-------------------------------------------------
S_ID S_NAME S_MAIL         S_GENDER  S_DID
-------------------------------------------------
6 Name6 name6@gmail.com F   90
2 name2 name2@gmail.com F   90
3 Name3 name3@gmail.com F   80
4 Name4 -               M   70
5 Name5 name5@gmail.com M   70
-------------------------------------------------

Syntax

Constraint my_stu_did_fk   FOREIGN KEY (s_did) References Departments(Department_id)
ON DELETE CASCADE

Constraint my_stu_did_fk   FOREIGN KEY (s_did) References Departments(Department_id)
ON DELETE SET NULL


Constraint Related Data Dictionary Table information.


To view constraint Information

SELECT c.owner,
       c.constraint_name,
       c.constraint_type,
       c.table_name,
       c.r_constraint_name,
       c.status
  FROM user_constraints c
 WHERE c.table_name = 'MY_STU_DETAILS';

 
------------------------------------------------------------------------------------
owner  constraint_name constraint_type table_name        r_constraint_name  status 
------------------------------------------------------------------------------------
 HR SYS_C004023       C       MY_STU_DETAILS                    ENABLED
 HR MY_STU_GEN_CK     C       MY_STU_DETAILS              ENABLED
 HR MY_STU_SID_PK     P       MY_STU_DETAILS              ENABLED
 HR MY_STU_MAIL_UK    U       MY_STU_DETAILS              ENABLED
 HR MY_STU_DID_FK     R       MY_STU_DETAILS     DEPT_ID_PK    ENABLED
------------------------------------------------------------------------------------



To view constraint name with corresponding column name

SELECT * FROM user_cons_columns c
WHERE c.table_name = 'MY_STU_DETAILS';

-------------------------------------------------------------
owner  constraint_name   table_name    column_name  position 
-------------------------------------------------------------
  HR    MY_STU_MAIL_UK MY_STU_DETAILS  S_MAIL 1
  HR    MY_STU_SID_PK MY_STU_DETAILS  S_ID           1
  HR    MY_STU_GEN_CK    MY_STU_DETAILS  S_GENDER
  HR SYS_C004023 MY_STU_DETAILS  S_NAME
  HR MY_STU_DID_FK    MY_STU_DETAILS  S_DID         
-------------------------------------------------------------


Creating Composite Primary Key


Creating Primary Key with more than one number of column

ALTER TABLE table_name
ADD CONSTRAINT constraint_name PRIMARY KEY(column1, column_2 , column3);

In user_cons_columns  table position if the column1 will be 1, position of column2 will be 2 and column3 will be 3.


creating constraint after the table creation


ALTER TABLE table_name
ADD CONSTRAINT constraint_name CONSTRAINT_TYPE(column_name); 

ALTER TABLE table_name
DROP CONSTRAINT constraint_name; 

ALTER TABLE table_name
DROP PRIMARY KEY; 


To drop related foreign key


ALTER TABLE table_name
DROP PRIMARY KEY CASCADE;

ALTER TABLE table_name
DISABLE CONSTRAINT constraint_name;  

 
Disable constraint with related foreign key constraint

 
ALTER TABLE table_name
DISABLE CONSTRAINT constraint_name CASCADE;

ALTER TABLE table_name
ENABLE CONSTRAINT constraint_name;  



Interview Questions


  • What are all the type of constraint available in oracle?
  • Difference between PRIMARY KEY and UNIQUE key.
  • Difference between PRIMARY KEY ans FOREIGN KEY.
  • Difference between UNIQUE key and FOREIGN KEY.
  • Is it possible to create tow primary key in single table.
  • Write a syntax to create FOREIGN constraint.
  • How to create constraint after the table has been created?
  • How to enable and disable a constraint.
  • Which data dictionary table contains information about constraints?
  • Which Data Dictionary table contains information about constraint with corresponding column name?
  • What is composite primary key?
  • Explain ON DELETE CASCADE and ON DELETE SET NULL option.


~Thanks For Reading~
Post your valuable feedback below



Thursday, December 4, 2014

listagg in Oracle 11g


Oracle 11gR2 listagg built-in function allows for many table columns to be displayed within a single row, a non-first-normal form display

Syntax :

     listagg(column_value,'delimiter') WITHIN GROUP(ORDER BY column_name)


--Example 1:

CREATE TABLE test_ins(empid NUMBER,product VARCHAR2(30));

BEGIN
   INSERT INTO test_ins VALUES( 101,'pendrive');
   INSERT INTO test_ins VALUES( 102,'toy');
   INSERT INTO test_ins VALUES( 101,'ipod');
   INSERT INTO test_ins VALUES( 102,'hat');
   INSERT INTO test_ins VALUES( 103,'cpu');
   INSERT INTO test_ins VALUES( 104,'pen');
   INSERT INTO test_ins VALUES( 104,'car');
   INSERT INTO test_ins VALUES( 104,'mat');
   INSERT INTO test_ins VALUES( 105,'tv');
   INSERT INTO test_ins VALUES( 106,'laptop');
   commit;
END;

SELECT * FROM test_ins;

    SELECT empid "Employee",
           listagg(product,
               ',') within
    GROUP
(ORDER BY empid) "Products"
     FROM test_ins
 GROUP BY empid;

-->-- Result set --<--



 









DROP TABLE test_ins;

-->-- Example 2

select department_id "Department id", listagg(first_name,', ') WITHIN GROUP(ORDER BY employee_id) "Employees"
FROM employees
WHERE department_id IN (10,20,30,60)
GROUP BY department_id;

-->-- Result set --<--