Thursday 1 November 2018

Query to Remove the Duplicates from a Data Set

Assume I have a table TMP with structure as given below.
view plaincopy to clipboardprint?
  1. CREATE TABLE TMP (
  2. NAME VARCHARW(30)
  3. )

 I have populated the duplicate values as well.
view plaincopy to clipboardprint?
  1. SELECT * FROM TEMP;

  3. Output
  4. ------
  5. Rahim
  6. Vicky
  7. Sai
  8. Tom
  9. Rahim
  10. Rahim
  11. Vicky
  12. Sai
  13. Jaz
  14. Apple
  15. Apple
  16. Rahim

Query to remove the Duplicate.

Method 1.

view plaincopy to clipboardprint?
  1. WITH ds AS ( SELECT rownum AS ID, name FROM tmp),  
  2. dup_set AS (SELECT id, RANK() OVER(PARTITION    
  3. BY name ORDER BY ROWNUM) AS rk, name from ds)  
  4. SELECT name FROM dup_set WHERE rk=1;  

Output:

  1. Rahim
  2. Vicky
  3. Sai
  4. Tom
  5. Jaz
  6. Apple

Method 2.

  1. WITH set_a AS  
  2.   ( SELECT ROWNUM AS id, name FROM tmp a  
  3.   ),  
  4.   set_b AS  
  5.   (SELECT ROWNUM AS id, name FROM tmp b  
  6.   )  
  7. SELECT a.name  
  8. FROM set_a a  
  9. WHERE a.id >= ALL  
  10.   (SELECT id FROM set_b b   
  11.   WHERE a.name=b.name  
  12.   );  
Method 3.


  1. WITH rem_dup_set AS (
  2. SELECT ROW_NUMBER() OVER(PARTITION BY name
  3.    ORDER BY name) AS rk, name
  4. FROM temp )
  5. SELECT name FROM rem_dup_set WHERE rk=1;
Output:

  1. Rahim
  2. Vicky
  3. Sai
  4. Tom
  5. Jaz
  6. Apple

Posted by at No comments:

Friday 10 November 2017

C Program to prrint number in Words

The following C Program converts a number to its correspoiding Words.

    /*   numtoWord.c */
   /*Program to convert a no. having
                                maxim of 10 digitsinto equivalent words*/
   //#include<conio.h>
   #include<stdio.h>
   #include<stdlib.h>
   int ctr=0;
   char str[][20] = {" ","One","Two","Three","Four","Five","Six",
                         "Seven","Eight","Nine","Ten","Eleven","Twelve",
                         "Thirteen","fourteen","fifteen","sixteen","seventeen",
                         "Eighteen","Ninteen","Twenty","Thirty","Fourty","Fifty",                        "Sixty","Seventy","Eighty","Ninty"};
   int print(int,int);
   void numword(long int, int);
   void main()
   {
      long int i=0;
      int l=10,k=10;
      unsigned long int no,n;
      //clrscr();
      printf("Enter a number( having less than 11 digit ):: ");
      scanf("%ld",&n);
      printf("\nThe number in words is:\n\n");
      no=n;
      if(no==0)
      {
          printf("Zero ");
                printf("\n");
         exit(1);
      }
      /*****Count no of  digits*****/
      for(i=n;i>0;i=i/10)
           ctr++;
      /*****Function call*****/
      numword(no,10);
      printf("\n\n");

   }
   /*****Recursive Function converts no: into words*****/
   void numword(long int no, int k)
   {
         static int a,b;
         static int rm1,rm2;
         int rm;
         rm=no%k;
         if(no == 0)
             return;
         else
         {
             numword(no/k, k);
             /*****Checks for the digits*****/
             if(ctr==10)
             {
                 printf("%s   ",str[rm]);
                 printf("Billion   ");
                 ctr=9;
             }
             else if(ctr==9 )
             {
                 rm1 = rm;
                 ctr=8;
             }
             else if(ctr==8 )
             {
                 rm1=print(rm1,rm);
                 if(rm1>0)
                     printf("Crore   ");
                 ctr=7;
             }
             else if(ctr==7)
             {
                 rm1=rm;
                 ctr=6;
             }
             else if(ctr==6 )
             {
                 rm1=print(rm1,rm);
                 if(rm1>0)
                     printf("Lakh   ");
                 ctr=5;
             }
             else if(ctr==5)
             {
                rm1=rm;
                ctr=4;

             }
             else if(ctr==4)
             {
                 rm1=print(rm1,rm);
                 if(rm1>0)
                     printf("Thousand   ");
                 ctr=3;
             }
             else if(ctr==3)
             {
                if(rm>0)
                {
                   printf("%s   ", str[rm]);
                   printf("Hundred   ");
                }
                ctr=2;
            }
            else if(ctr==2)
            {
                rm1=rm;
                ctr=1;
            }
            else if(ctr==1)
                print(rm1,rm);
         }

   }
   /*****Function to Print equivalent words*****/
   int print(int rm1,int rm)
   {
       int a,b;
       rm1=rm1*10+rm;
       /***Only one digit & hence points to the correct position***/
       if(rm1>0 &&rm1<21)
           printf("%s   ",str[rm1]);
       else
       {
           /*****First & 10th digit to be extracted seperately*****/
           if(rm1>0)
           {
               a=rm1/10;
               b=rm1%10;
               if(a>0)
                   printf("%s   ",str[18+a]);
               if(b>0)
                   printf("%s   ",str[b]);
           }
       }
       /*variable rm1 to be available at the calling portion*/
       return rm1;

   }

 Output
---------- 

 $gcc -o numtoWord numbtoWord.c

 $./numtoWord

 Enter a number( having less than 11 digit ):: 1234567890

 The number in words is:
One   Billion   Twenty   Three   Crore   Fourty   Five   Lakh   Sixty   Seven   Thousand   Eight   Hundred   Ninty
Posted by at No comments:

Tuesday 24 October 2017

Generating JSON Objects in Oracle SQL using JSON Functions

Oracle 12c Release 2 has enhanced the JSON Functionality where we can generate the JSON Objects directly from the table data. The following are the functions available. 
  • JSON_OBJECT

    • The JSON_OBJECT function converts a comma-separated list of key-value pairs into object members within a JSON object. 
    1. SELECT json_object (  
    2.          KEY 'EmployeeId' VALUE empno,  
    3.          KEY 'EmployeeName' VALUE ename  
    4.        ) AS employee  
    5. FROM   emp   
    6. ORDER BY empno;  
    7.   
    8. Output  
    9. ------  
    10. {"EmployeeId":7369,"EmployeeName":"SMITH"}  
    11. {"EmployeeId":7499,"EmployeeName":"ALLEN"}  
    12. {"EmployeeId":7521,"EmployeeName":"WARD"}  
    13. {"EmployeeId":7566,"EmployeeName":"JONES"}  
    14. {"EmployeeId":7654,"EmployeeName":"MARTIN"}  
    15. {"EmployeeId":7698,"EmployeeName":"BLAKE"}  
    16. {"EmployeeId":7782,"EmployeeName":"CLARK"}  
    17. {"EmployeeId":7788,"EmployeeName":"SCOTT"}  
    18. {"EmployeeId":7839,"EmployeeName":"KING"}  
    19. {"EmployeeId":7844,"EmployeeName":"TURNER"}  
  • JSON_OBJECTAGG

    • The JSON_OBJECTAGG aggregate function creates a single JSON object containing a list of object members formed by aggregating a key-value pair from each row.
    1. SELECT JSON_OBJECTAGG(KEY dname VALUE total_count) FROM (  
    2. SELECT d.dname, count(1) total_count from emp e, dept d  
    3. WHERE e.deptno=d.deptno  
    4. group by d.dname);  
    5.   
    6. Output  
    7. ------  
    8. {"ACCOUNTING":3,"RESEARCH":5,"SALES":6}  

  • JSON_ARRAY

    • The JSON_ARRAY function converts a comma-separated list of expressions into a JSON array of JSON values. 

    1. SELECT json_array(  empno, ename, sal,mgr,e.deptno,dname,loc  ) json_array    
    2. FROM emp e, dept d WHERE e.deptno=d.deptno  
    3. ORDER BY empno;    
    4.   
    5. Output  
    6. ------  
    7. [7369,"SMITH",800,7902,20,"RESEARCH","DALLAS"]  
    8. [7499,"ALLEN",1600,7698,30,"SALES","CHICAGO"]  
    9. [7521,"WARD",1250,7698,30,"SALES","CHICAGO"]  
    10. [7566,"JONES",2975,7839,20,"RESEARCH","DALLAS"]  
    11. [7654,"MARTIN",1250,7698,30,"SALES","CHICAGO"]  
    12. [7698,"BLAKE",2850,7839,30,"SALES","CHICAGO"]  
    13. [7782,"CLARK",2450,7839,10,"ACCOUNTING","NEW YORK"]  
    14. [7788,"SCOTT",3000,7566,20,"RESEARCH","DALLAS"]  
    15. [7839,"KING",5000,10,"ACCOUNTING","NEW YORK"]  
    16. [7844,"TURNER",1500,7698,30,"SALES","CHICAGO"] 


    1. SELECT json_array( json_object('EmployeeId' VALUE empno, 'EmployeeName' VALUE ename  
    2. , 'Salary' VALUE sal, 'Manager' VALUE mgr),  
    3. json_object('DepartmentId' VALUE d.deptno ,'DepartmentName' VALUE d.dname,  
    4. 'Location' VALUE d.loc)  
    5. ) json_array  
    6. FROM emp e, dept d   
    7. WHERE e.deptno=d.deptno   
    8. ORDER BY empno;  
    9.   
    10. Output  
    11. ------  
    12. [{"EmployeeId":7369,"EmployeeName":"SMITH","Salary":800,"Manager":7902},{"DepartmentId":20,"DepartmentName":"RESEARCH","Location":"DALLAS"}]  
    13. [{"EmployeeId":7499,"EmployeeName":"ALLEN","Salary":1600,"Manager":7698},{"DepartmentId":30,"DepartmentName":"SALES","Location":"CHICAGO"}]  
    14. [{"EmployeeId":7521,"EmployeeName":"WARD","Salary":1250,"Manager":7698},{"DepartmentId":30,"DepartmentName":"SALES","Location":"CHICAGO"}]  
    15. [{"EmployeeId":7566,"EmployeeName":"JONES","Salary":2975,"Manager":7839},{"DepartmentId":20,"DepartmentName":"RESEARCH","Location":"DALLAS"}]  
    16. [{"EmployeeId":7654,"EmployeeName":"MARTIN","Salary":1250,"Manager":7698},{"DepartmentId":30,"DepartmentName":"SALES","Location":"CHICAGO"}]  
    17. [{"EmployeeId":7698,"EmployeeName":"BLAKE","Salary":2850,"Manager":7839},{"DepartmentId":30,"DepartmentName":"SALES","Location":"CHICAGO"}]  
    18. [{"EmployeeId":7782,"EmployeeName":"CLARK","Salary":2450,"Manager":7839},{"DepartmentId":10,"DepartmentName":"ACCOUNTING","Location":"NEW YORK"}]  
    19. [{"EmployeeId":7788,"EmployeeName":"SCOTT","Salary":3000,"Manager":7566},{"DepartmentId":20,"DepartmentName":"RESEARCH","Location":"DALLAS"}]  
    20. [{"EmployeeId":7839,"EmployeeName":"KING","Salary":5000,"Manager":null},{"DepartmentId":10,"DepartmentName":"ACCOUNTING","Location":"NEW YORK"}]  
    21. [{"EmployeeId":7844,"EmployeeName":"TURNER","Salary":1500,"Manager":7698},{"DepartmentId":30,"DepartmentName":"SALES","Location":"CHICAGO"}]  

  • JSON_ARRAYAGG

    • The JSON_ARRAYAGG aggregate function, similar to the LISTAGG function, aggregates an expression from each row into a single JSON array. 

    1. SELECT JSON_ARRAYAGG(e.ename) employee_array  
    2. FROM   emp e  
    3. WHERE  e.deptno = 20;  
    4.   
    5. Output  
    6. ------  
    7. ["SMITH","JONES","SCOTT","ADAMS","FORD"]  

  • Complex Example

    • The Below Query gives a single JSON Array Object contains all the employee & department informations. 
    1. SELECT JSON_ARRAYAGG(json_object(KEY 'EmployeeDetails' VALUE   
    2.   json_array(json_object(KEY 'DepartmentInfo' VALUE json_object(  
    3.   KEY 'DepartmentId' VALUE department_id, KEY 'DepartmentName'   
    4.   VALUE department_name,KEY 'EmployeeInfo' VALUE   
    5.   (SELECT json_arrayagg(json_object(KEY 'EmployeeId' VALUE e.employee_id,  
    6.                 KEY 'FirstName' VALUE e.first_name   
    7.             ,   KEY 'LastName' VALUE e.last_name     
    8.             ,   KEY 'Email' VALUE e.email    
    9.             ,   KEY 'PhoneNumber' VALUE e.phone_number             
    10.                 ))  
    11.                 FROM employees e   
    12.                 WHERE e.department_id=d.department_id)  
    13.   )))) ) json FROM departments d  
    14. WHERE ROWNUM<5;  
    15. Output  
    16. ------  
    17. [  
    18.    {  
    19.       "EmployeeDetails": [  
    20.          {  
    21.             "DepartmentInfo": {  
    22.                "DepartmentId": 10,  
    23.                "DepartmentName": "Administration",  
    24.                "EmployeeInfo": [  
    25.                   {  
    26.                      "EmployeeId": 200,  
    27.                      "FirstName": "Jennifer",  
    28.                      "LastName": "Whalen",  
    29.                      "Email": "JWHALEN",  
    30.                      "PhoneNumber": "515.123.4444"  
    31.                   }  
    32.                ]  
    33.             }  
    34.          }  
    35.       ]  
    36.    },  
    37.    {  
    38.       "EmployeeDetails": [  
    39.          {  
    40.             "DepartmentInfo": {  
    41.                "DepartmentId": 20,  
    42.                "DepartmentName": "Marketing",  
    43.                "EmployeeInfo": [  
    44.                   {  
    45.                      "EmployeeId": 201,  
    46.                      "FirstName": "Michael",  
    47.                      "LastName": "Hartstein",  
    48.                      "Email": "MHARTSTE",  
    49.                      "PhoneNumber": "515.123.5555"  
    50.                   },  
    51.                   {  
    52.                      "EmployeeId": 202,  
    53.                      "FirstName": "Pat",  
    54.                      "LastName": "Fay",  
    55.                      "Email": "PFAY",  
    56.                      "PhoneNumber": "603.123.6666"  
    57.                   }  
    58.                ]  
    59.             }  
    60.          }  
    61.       ]  
    62.    },  
    63.    {  
    64.       "EmployeeDetails": [  
    65.          {  
    66.             "DepartmentInfo": {  
    67.                "DepartmentId": 30,  
    68.                "DepartmentName": "Purchasing",  
    69.                "EmployeeInfo": [  
    70.                   {  
    71.                      "EmployeeId": 114,  
    72.                      "FirstName": "Den",  
    73.                      "LastName": "Raphaely",  
    74.                      "Email": "DRAPHEAL",  
    75.                      "PhoneNumber": "515.127.4561"  
    76.                   },  
    77.                   {  
    78.                      "EmployeeId": 115,  
    79.                      "FirstName": "Alexander",  
    80.                      "LastName": "Khoo",  
    81.                      "Email": "AKHOO",  
    82.                      "PhoneNumber": "515.127.4562"  
    83.                   },  
    84.                   {  
    85.                      "EmployeeId": 116,  
    86.                      "FirstName": "Shelli",  
    87.                      "LastName": "Baida",  
    88.                      "Email": "SBAIDA",  
    89.                      "PhoneNumber": "515.127.4563"  
    90.                   },  
    91.                   {  
    92.                      "EmployeeId": 117,  
    93.                      "FirstName": "Sigal",  
    94.                      "LastName": "Tobias",  
    95.                      "Email": "STOBIAS",  
    96.                      "PhoneNumber": "515.127.4564"  
    97.                   },  
    98.                   {  
    99.                      "EmployeeId": 118,  
    100.                      "FirstName": "Guy",  
    101.                      "LastName": "Himuro",  
    102.                      "Email": "GHIMURO",  
    103.                      "PhoneNumber": "515.127.4565"  
    104.                   },  
    105.                   {  
    106.                      "EmployeeId": 119,  
    107.                      "FirstName": "Karen",  
    108.                      "LastName": "Colmenares",  
    109.                      "Email": "KCOLMENA",  
    110.                      "PhoneNumber": "515.127.4566"  
    111.                   }  
    112.                ]  
    113.             }  
    114.          }  
    115.       ]  
    116.    },  
    117.    {  
    118.       "EmployeeDetails": [  
    119.          {  
    120.             "DepartmentInfo": {  
    121.                "DepartmentId": 40,  
    122.                "DepartmentName": "Human Resources",  
    123.                "EmployeeInfo": [  
    124.                   {  
    125.                      "EmployeeId": 203,  
    126.                      "FirstName": "Susan",  
    127.                      "LastName": "Mavris",  
    128.                      "Email": "SMAVRIS",  
    129.                      "PhoneNumber": "515.123.7777"  
    130.                   }  
    131.                ]  
    132.             }  
    133.          }  
    134.       ]  
    135.    }  
    136. ]  

    Another Example shown below is an SQL query which will return the Employee Details JSON Objects which contains a nested array object called EmployeeInfo. It contains all the employees for a particular department. 
    1. SELECT json_object(KEY 'EmployeeDetails' VALUE   
    2.   json_array(json_object(KEY 'DepartmentInfo' VALUE json_object(  
    3.   KEY 'DepartmentId' VALUE department_id, KEY 'DepartmentName'   
    4.   VALUE department_name,KEY 'EmployeeInfo' VALUE   
    5.   (SELECT json_arrayagg(json_object(KEY 'EmployeeId' VALUE e.employee_id,  
    6.                 KEY 'FirstName' VALUE e.first_name  
    7.             ,   KEY 'LastName' VALUE e.last_name       
    8.             ,   KEY 'Email' VALUE e.email   
    9.             ,   KEY 'PhoneNumber' VALUE phone_number               
    10.                 ))  
    11.                 FROM employees e   
    12.                 WHERE e.department_id=d.department_id)  
    13.   )))) json FROM departments d;  
    14.   
    15. Output  
    16. ------  
    17. {  
    18.    "EmployeeDetails": [  
    19.       {  
    20.          "DepartmentInfo": {  
    21.             "DepartmentId": 20,  
    22.             "DepartmentName": "Marketing",  
    23.             "EmployeeInfo": [  
    24.                {  
    25.                   "EmployeeId": 201,  
    26.                   "FirstName": "Michael",  
    27.                   "LastName": "Hartstein",  
    28.                   "Email": "MHARTSTE",  
    29.                   "PhoneNumber": "515.123.5555"  
    30.                },  
    31.                {  
    32.                   "EmployeeId": 202,  
    33.                   "FirstName": "Pat",  
    34.                   "LastName": "Fay",  
    35.                   "Email": "PFAY",  
    36.                   "PhoneNumber": "603.123.6666"  
    37.                }  
    38.             ]  
    39.          }  
    40.       }  
    41.    ]  
    42. }  
















    Posted by at No comments:
    Subscribe to: Posts (Atom)