Sunday, August 7, 2016

Visual Explanation of SQL Joins

     SQL Joins are used to relate information in different tables. In short and crisp, there are 4 types of SQL joins. Inner, Left Outer, Right Outer and Full joins. Let’s see the differences between these four using Venn diagrams and then can see the SQL commands.
     Let’s say we have two sets of data in our relational database. Table A and Table B with some sort of relation specified by primary and foreign keys. The result of joining these tables together can be visually represented by following diagram:
See how each of the joins are represented.
Select all records from Table A and Table B, where the join condition                                                   is met.
Select all records from Table A, along with records from Table B for which                                                                 the join condition is met.
Select all records from Table B, along with records from Table A for which                                                                  the join condition is met.
Select all records from Table A and Table B, regardless of whether the join                                                                 condition is met or not.






Let's see them now in Tables & SQLs representation. If table A and table B exist like below.


Inner Join:
SQL Query is:
SELECT * FROM TableA
INNER JOIN TableB ON TableA.Name = TableB.Name;
Result will something look like...

Left Outer Join:
SQL Query is:

SELECT * FROM TableA
LEFT OUTER JOIN TableB ON TableA.name = TableB.name;
Result will something look like...

Right Outer Join:
SQL Query is:

SELECT * FROM TableA
RIGHT OUTER JOIN TableB ON TableA.name = TableB.name;
Result will something look like...

Full Join:
SQL Query is:

SELECT * FROM TableA
FULL JOIN TableB ON TableA.Name = TableB.Name;
Result will something look like...

     Likewise, you can create different types of joins just by adding few additional conditions to the SQL. For example, you can make Full Join as Full Outer Join just by adding a condition. 

     SQLs mentioned here are in ANSI 92/99 syntax. One of the good reasons to use ANSI syntax over the old conventional(+) join syntax is that, there are nil chances of accidentally creating a cartesian product. With more number of tables, there is a chance to miss an implicit join with older join syntax, however, with ANSI syntax you cannot miss any join as you must explicitly mention them. 

Happy Learning.. :) 

How To Escape Special Characters in Writing SQLs!


Oracle DB reserved below characters with specific meaning and purpose.
  1. _ (underscore) wildcard character is used to match exactly one character
  2. % (percentage) is used to match zero or more occurrences of any character
  3. ‘ (apostrophe, quote) is used to mark the value supplied
     If you want to insert the data with any of these characters or when performing string search with LIKE keyword, they are not interpreted literally and cause errors. One should escape them if these need to be interpreted as part of string value instead of a pre-defined meaning.
Let’s look at how each of these characters can be escaped.

Escape wildcard characters:
The LIKE keyword allows for string searches. As mentioned earlier, _ is used to match exactly one character.

1
2
SQL> SELECT name FROM emp WHERE id LIKE '%/_%' ESCAPE '/';
SQL> SELECT name FROM emp WHERE id LIKE '%\%%' ESCAPE '\';

Escape quotes('):
Quotes can be escaped in two ways. Let’s look at each.
  •      Use another quote to escape one quote. For example,
1
2
3
4
5
SQL> SELECT 'VENKI''S' AS WHOS FROM DUAL;

WHOS
-------
VENKIS
Another example is... 

1
2
3
4
5
SQL> SELECT 'VENKI''S LAPTOP: ''''VAIO''''' AS WHATS FROM DUAL;

WHATS
---------------------
VENKIS LAPTOP: ''VAIO''
  •        Use q expression. For example
1
2
SQL> SELECT q'[VENKI'S]' AS QUOTE_S FROM DUAL;
SQL> SELECT q'[VENKI'S LAPTOP: ''VAIO'']' AS QUOTE_S FROM DUAL;

Escape Ampersand (&):
This can be escaped in 3 ways. Let’s look at each.
  •        Use DEFINE. For example,
1
2
3
SQL> CREATE TABLE temp (name VARCHAR2(30));
SQL> SET DEFINE ~
SQL> INSERT INTO temp VALUES('Tom & Jerry');
Instead of ~, you can use ON or OFF to turn it on or off.
  •        Use ESCAPE. For example,
1
2
SQL> SET ESCAPE '\'
SQL> SELECT 'Tom \& Jerry' FROM DUAL;
You can define any other character also as escape character.
  •        Use SCAN. For example,
1
2
SQL> SET SCAN OFF;
SQL> SELECT 'Tom & Jerry' FROM DUAL;
But, this has been made obsolete. Please see for some more obsolete commands in Oracle DB https://docs.oracle.com/cd/B19306_01/server.102/b14357/apc.htm
  •        Another best way is to use to concatenation to escape the &.
1
SQL> SELECT 'Tom ' || '&' || ' Jerry' FROM DUAL;

Happy Learning… J

Saturday, August 6, 2016

Comparable vs Comparator examples to sort Objects!


What are these?
Both are interfaces. Comparable is from java.lang where as Comparator is from java.util packages.
A Comparable object is capable of comparing itself with another object. The class itself must implement the Comparable interface in order to be able to compare its instances.
A Comparator object is capable of comparing two different objects. The class is not comparing its instances, but some other class’s instances. Hence, the class in which this comparison is made must implement Comparator interface. However, this can be tweaked not to implement this interface when used in same class.

When to use what?
If there is a natural or default way of sorting objects then use Comparable.
If an object can be sorted on multiple ways and client is specifying on which parameter sorting should take place then use Comparator. Usually if you are not the author of a class, you wouldn’t have access to use Comparable so, the only option is to use Comparator.

Let’s see how we can sort primitive types or object array and list with a simple program.
PrimitiveSort.java
 package com.javaoracleadf.sorting;  
 import java.util.ArrayList;  
 import java.util.Arrays;  
 import java.util.Collections;  
 import java.util.List;  
 public class PrimitiveSort {  
   public static void main(String[] args) {  
     //sort int array  
     int[] intArr = {4,8,2,9};  
     Arrays.sort(intArr);  
     System.out.println(Arrays.toString(intArr));  
     //sorting String array  
     String[] strArr = {"A", "C", "B", "Z", "E"};  
     Arrays.sort(strArr);  
     System.out.println(Arrays.toString(strArr));  
     //sorting list of objects of Wrapper classes  
     List strList = new ArrayList();  
     strList.add("A");  
     strList.add("C");  
     strList.add("B");  
     strList.add("Z");  
     strList.add("E");  
     Collections.sort(strList);  
     for(String str: strList) System.out.print(" "+str);  
   }  
 }  
Output of the above program is:
 [2, 4, 8, 9]  
 [A, B, C, E, Z]  
 A B C E Z  
Now let’s try to sort an array of a custom class we have.
Employee.java
 package com.javaoracleadf.sorting;  
 public class Employee {  
   private int id;  
   private String name;  
   private int age;  
   private long salary;  
   public int getId() {  
     return id;  
   }  
   public String getName() {  
     return name;  
 }  
   public int getAge() {  
     return age;  
 }  
   public long getSalary() {  
     return salary;  
   }  
   public Employee(int id, String name, int age, int salary) {  
     this.id = id;  
     this.name = name;  
     this.age = age;  
     this.salary = salary;  
 }  
   public String toString() {  
     return "[id=" + this.id + ", name=" + this.name + ", age=" + this.age + ", salary=" + this.salary + "]";  
   }  
 }  
Here is the code I used to sort the array of Employee objects.
 //sorting custom object array  
 Employee[] empArr = new Employee[4];  
 empArr[0] = new Employee(10, "Manoj", 25, 10000);  
 empArr[1] = new Employee(20, "Anand", 29, 20000);  
 empArr[2] = new Employee(5, "Lakshmi", 35, 5000);  
 empArr[3] = new Employee(1, "Pavan", 32, 50000);  
   
 //sorting employees array using Comparable interface implementation  
 Arrays.sort(empArr);  
 System.out.println("Default Sorting of Employees list:\n"+Arrays.toString(empArr));  
When I tried to run this, it throws following runtime exception.
 Exception in thread "main" java.lang.ClassCastException: com.javaoracleadf.sorting.Employee cannot be cast to java.lang.Comparable  
   at java.util.ComparableTimSort.countRunAndMakeAscending(ComparableTimSort.java:290)  
   at java.util.ComparableTimSort.sort(ComparableTimSort.java:157)  
   at java.util.ComparableTimSort.sort(ComparableTimSort.java:146)  
   at java.util.Arrays.sort(Arrays.java:472)  
   at com.javaoracleadf.sorting.JavaSorting.main(JavaSorting.java:41)  
If you want to use Arrays or Collections sorting methods, your custom class should implement the Comparable interface. Also, you should override the method compareTo(T obj) in such a way that it returns a negative integer, zero, or a positive integer if “this” object is less than, equal, or greater than the object passed as an argument. After implementing Comparable interface, resulting Employee.java file look like below.
Employee.java
 package com.javaoracleadf.sorting;  
 import java.util.Comparator;  
 public class Employee implements Comparable {  
   private int id;  
   private String name;  
   private int age;  
   private long salary;  
   public int getId() {  
     return id;  
   }  
   public String getName() {  
     return name;  
 }  
   public int getAge() {  
     return age;  
 }  
   public long getSalary() {  
     return salary;  
   }  
   public Employee(int id, String name, int age, int salary) {  
     this.id = id;  
     this.name = name;  
     this.age = age;  
     this.salary = salary;  
 }  
   @Override  
   public int compareTo(Employee emp) {  
     //let's sort the employee based on id in ascending order  
     //returns a negative integer, zero, or a positive integer as this employee id  
     //is less than, equal to, or greater than the specified object.  
     return (this.id - emp.id);  
   }  
   public String toString() {  
     return "[id=" + this.id + ", name=" + this.name + ", age=" + this.age + ", salary=" + this.salary + "]";  
   }  
 }  
If I execute the above sorting logic, it sorts the objects like below.
 Default Sorting of Employees list:  
 [[id=1, name=Pavan, age=32, salary=50000], [id=5, name=Lakshmi, age=35, salary=5000], [id=10, name=Manoj, age=25, salary=10000], [id=20, name=Anand, age=29, salary=20000]]  
As you can see that Employees objects array is sorted by id in ascending order.

But, in the other scenarios, sorting is needed based on different parameters. For example, as a manager, he would like to sort the employees based on Salary. As a HR, he would like to sort them based on the age. In this situation, using Comparator interface is the best fit because Comparable.compareTo(Object o) method can sort based on one field only and we can’t chose the field on which we want to sort the Object.
Comparator interface’s compare(Object o1, Object o2) method need to be implemented which takes two Object arguments and it should be implemented in such a way that it returns negative integer if first argument is less than the second one and returns zero if they are equal and positive integer if first argument is greater than second one.
After implementing Comparator’s methods, resulting Employee.java file look like below.
Employee.java
 package com.javaoracleadf.sorting;  
 import java.util.Comparator;  
 public class Employee implements Comparable {  
   private int id;  
   private String name;  
   private int age;  
   private long salary;  
   public int getId() {  
     return id;  
   }  
   public String getName() {  
     return name;  
 }  
   public int getAge() {  
     return age;  
 }  
   public long getSalary() {  
     return salary;  
 }  
   public Employee(int id, String name, int age, int salary) {  
     this.id = id;  
     this.name = name;  
     this.age = age;  
     this.salary = salary;  
 }  
   @Override  
   public int compareTo(Employee emp) {  
     //let's sort the employee based on id in ascending order  
     //returns a negative integer, zero, or a positive integer as this employee id  
     //is less than, equal to, or greater than the specified object.  
     return (this.id - emp.id);  
   }  
   public String toString() {  
     return "[id=" + this.id + ", name=" + this.name + ", age=" + this.age + ", salary=" + this.salary + "]";  
   }  
   /**  
    * Comparator to sort employees list or array in order of Salary  
    */  
   public static Comparator SalaryComparator = new Comparator() {  
     @Override  
     public int compare(Employee e1, Employee e2) {  
       return (int) (e1.getSalary() - e2.getSalary());  
     }  
 };  
   /**  
    * Comparator to sort employees list or array in order of Age  
    */  
   public static Comparator AgeComparator = new Comparator() {  
     @Override  
     public int compare(Employee e1, Employee e2) {  
       return e1.getAge() - e2.getAge();  
     }  
 };  
   /**  
    * Comparator to sort employees list or array in order of Name  
    */  
   public static Comparator NameComparator = new Comparator() {  
     @Override  
     public int compare(Employee e1, Employee e2) {  
       return e1.getName().compareTo(e2.getName());  
     }  
 };  
 }  
We can use these comparators to pass as argument to sort function of Arrays and Collections classes.
 //sort employees array using Comparator by Salary  
 Arrays.sort(empArr, Employee.SalaryComparator);  
 System.out.println("Employees list sorted by Salary:\n"+Arrays.toString(empArr));  
   
 //sort employees array using Comparator by Age  
 Arrays.sort(empArr, Employee.AgeComparator);  
 System.out.println("Employees list sorted by Age:\n"+Arrays.toString(empArr));  
   
 //sort employees array using Comparator by Name  
 Arrays.sort(empArr, Employee.NameComparator);  
 System.out.println("Employees list sorted by Name:\n"+Arrays.toString(empArr));  
Here is the output of the above code snippet:
 Employees list sorted by Salary:  
 [[id=5, name=Lakshmi, age=35, salary=5000], [id=10, name=Manoj, age=25, salary=10000], [id=20, name=Anand, age=29, salary=20000], [id=1, name=Pavan, age=32, salary=50000]]  
   
 Employees list sorted by Age:  
 [[id=10, name=Manoj, age=25, salary=10000], [id=20, name=Anand, age=29, salary=20000], [id=1, name=Pavan, age=32, salary=50000], [id=5, name=Lakshmi, age=35, salary=5000]]  
   
 Employees list sorted by Name:  
 [[id=20, name=Anand, age=29, salary=20000], [id=5, name=Lakshmi, age=35, salary=5000], [id=10, name=Manoj, age=25, salary=10000], [id=1, name=Pavan, age=32, salary=50000]]  
We can also create separate class that implements Comparator interface and then use it.

EmployeeComparatorByIdAndName.java
 package com.javaoracleadf.sorting;  
 import java.util.Comparator;  
 public class EmployeeComparatorByIdAndName implements Comparator {  
   @Override  
   public int compare(Employee o1, Employee o2) {  
     int flag = o1.getId() - o2.getId();  
     if(flag==0) flag = o1.getName().compareTo(o2.getName());  
     return flag;  
 }  
 }  
test class where we are using different ways to sort Objects in java.
JavaObjectSorting.java
 package com.javaoracleadf.sorting;  
 import java.util.Arrays;  
 public class JavaObjectSorting {  
   /**  
    * This class shows how to sort custom objects array/list  
    * implementing Comparable and Comparator interfaces  
    * @param args  
    */  
   public static void main(String[] args) {  
     //sorting custom object array  
     Employee[] empArr = new Employee[4];  
     empArr[0] = new Employee(10, "Manoj", 25, 10000);  
     empArr[1] = new Employee(20, "Anand", 29, 20000);  
     empArr[2] = new Employee(5, "Lakshmi", 35, 5000);  
     empArr[3] = new Employee(1, "Pavan", 32, 50000);  
     //sorting employees array using Comparable interface implementation  
     Arrays.sort(empArr);  
     System.out.println("Default Sorting of Employees list:\n"+Arrays.toString(empArr));  
     //sort employees array using Comparator by Salary  
     Arrays.sort(empArr, Employee.SalaryComparator);  
     System.out.println("Employees list sorted by Salary:\n"+Arrays.toString(empArr));  
     //sort employees array using Comparator by Age  
     Arrays.sort(empArr, Employee.AgeComparator);  
     System.out.println("Employees list sorted by Age:\n"+Arrays.toString(empArr));  
     //sort employees array using Comparator by Name  
     Arrays.sort(empArr, Employee.NameComparator);  
     System.out.println("Employees list sorted by Name:\n"+Arrays.toString(empArr));  
     //Employees list sorted by ID and then name using Comparator class  
     empArr[0] = new Employee(1, "Manoj", 25, 10000);  
     Arrays.sort(empArr, new EmployeeComparatorByIdAndName());  
     System.out.println("Employees list sorted by ID and Name:\n"+Arrays.toString(empArr));  
 }  
 }  
Here is the output of the above program:
 Default Sorting of Employees list:  
 [[id=1, name=Pavan, age=32, salary=50000], [id=5, name=Lakshmi, age=35, salary=5000], [id=10, name=Manoj, age=25, salary=10000], [id=20, name=Anand, age=29, salary=20000]]  
   
 Employees list sorted by Salary:  
 [[id=5, name=Lakshmi, age=35, salary=5000], [id=10, name=Manoj, age=25, salary=10000], [id=20, name=Anand, age=29, salary=20000], [id=1, name=Pavan, age=32, salary=50000]]  
   
 Employees list sorted by Age:  
 [[id=10, name=Manoj, age=25, salary=10000], [id=20, name=Anand, age=29, salary=20000], [id=1, name=Pavan, age=32, salary=50000], [id=5, name=Lakshmi, age=35, salary=5000]]  
   
 Employees list sorted by Name:  
 [[id=20, name=Anand, age=29, salary=20000], [id=5, name=Lakshmi, age=35, salary=5000], [id=10, name=Manoj, age=25, salary=10000], [id=1, name=Pavan, age=32, salary=50000]]  
   
 Employees list sorted by ID and Name:  
 [[id=1, name=Manoj, age=25, salary=10000], [id=1, name=Pavan, age=32, salary=50000], [id=5, name=Lakshmi, age=35, salary=5000], [id=10, name=Manoj, age=25, salary=10000]]  
Happy Learning.. :)