String Aggregation Techniques

==============================================

STEP1:--CREATE TABLE EMP (DEPTNO NUMBER, ENAME VARCHAR2(20));

STEP2:-- SELECT * FROM EMP;

  DEPTNO ENAME
---------- ----------
    20 SMITH
    30 ALLEN
    30 WARD
    20 JONES
    30 MARTIN
    30 BLAKE
    10 CLARK
    20 SCOTT
    10 KING
    30 TURNER
    20 ADAMS
    30 JAMES
    20 FORD
    10 MILLER


STEP3: NO WE WRITE A QUERY FOR BELLOW FORMAT OUTPUT.


Desired Output:

  DEPTNO EMPLOYEES
---------- --------------------------------------------------
    10 CLARK,KING,MILLER
    20 SMITH,FORD,ADAMS,SCOTT,JONES
    30 ALLEN,BLAKE,MARTIN,TURNER,JAMES,WARD

STEP4: QUERY

FOR 11G

SELECT deptno, LISTAGG(ename, ',') WITHIN GROUP (ORDER BY ename) AS employees
FROM   emp
GROUP BY deptno;


FOR 10G

SELECT deptno, wm_concat(ename) AS employees
FROM   emp
GROUP BY deptno;

Age in Years and months and days:

===================================

select
trunc
( months_between( to_date('14-05-2014','DD-MM-YYYY'), to_date('24-10-1986','DD-MM-YYYY') ) /12 ) Years,
mod
( trunc( months_between( to_date('14-05-2014','DD-MM-YYYY'), to_date('24-10-1986','DD-MM-YYYY') ) ), 12 ) months,
to_date(
'14-05-2014','DD-MM-YYYY') -
add_months(to_date(
'24-10-1986','DD-MM-YYYY'),trunc( months_between( to_date('14-05-2014','DD-MM-YYYY'),
to_date(
'24-10-1986','DD-MM-YYYY') ) )) days
from
dual ;

Interface:

Interfaces are core part of java programming language.

An interface is a contract  of what the classes can do. When a class implements a certain interface, it promises to provide implementation to all the abstract methods declared in the interface.
Interface defines a set of common behaviors. The classes implement the interface agree to these behaviors and provide their own implementation to the behaviors. This allows you to program at the interface, instead of the actual implementation. One of the main usage of interface is provide a communication contract between two objects. If you know a class implements an interface, then you know that class contains concrete implementations of the methods declared in that interface, and you are guaranteed to be able to invoke these methods safely. In other words, two objects can communicate based on the contract defined in the interface, instead of their specific implementation.

Secondly, Java does not support multiple inheritance (whereas C++ does). Multiple inheritance permits you to derive a subclass from more than one direct superclass. This poses a problem if two direct superclasses have conflicting implementations. (Which one to follow in the subclass?). However, multiple inheritance does have its place. Java does this by permitting you to "implements" more than one interfaces (but you can only "extends" from a single superclass). Since interfaces contain only abstract methods without actual implementation, no conflict can arise among the multiple interfaces. (Interface can hold constants but is not recommended. If a subclass implements two interfaces with conflicting constants, the compiler will flag out a compilation error.)

Interfaces provide a way to achieve abstraction in java and used to define the contract for the subclasses to implement.
For example, lets say we want to create a drawing consists of multiple shapes, here we can create an interface Shape and define all the methods that different types of Shape objects will implement. For simplicity purpose, we can keep only two methods – draw() to draw the shape and getArea() that will return the area of the shape.

Java Interface Example

Based on above requirements, our Shape interface will look like this.

Shape.java

package com.journaldev.design;   public interface Shape {       //implicitly public, static and final     public String LABLE="Shape";           //interface methods are implicitly abstract and public     void draw();           double getArea(); }

Important Points about Interfaces in Java

• interface is the code that is used to create an interface in java.
• We can’t instantiate an interface in java.
• Interface provides absolute abstraction, in last post we learned about abstract classes in java to provide abstraction but abstract classes can have method implementations but interface can’t.
• Interfaces can’t have constructors because we can’t instantiate them and interfaces can’t have a method with body.
• By default any attribute of interface is public, static and final, so we don’t need to provide access modifiers to the attributes but if we do, compiler doesn’t complain about it either.
• By default interface methods are implicitly abstract and public, it makes total sense because the method don’t have body and so that subclasses can provide the method implementation.
• An interface can’t extend any class but it can extend another interface. public interface Shape extends Cloneable{} is an example of an interface extending another interface. Actually java provides multiple inheritance in interfaces, what is means is that an interface can extend multiple interfaces.
• implements keyword is used by classes to implement an interface.
• A class implementing an interface must provide implementation for all of its method unless it’s an abstract class. For example, we can implement above interface in abstract class like this:

  ShapeAbs.java

  package com.journaldev.design;   public abstract class ShapeAbs implements Shape {       @Override     public double getArea() {         // TODO Auto-generated method stub         return 0;     }   }

• We should always try to write programs in terms of interfaces rather than implementations so that we know beforehand that implementation classes will always provide the implementation and in future if any better implementation arrives, we can switch to that easily.

Interface Implementation Example

Now lets see some implementation of our Shape interface.

Circle.java

package com.journaldev.design;   public class Circle implements Shape {       private double radius;       public Circle(double r){         this.radius = r;     }           @Override     public void draw() {         System.out.println("Drawing Circle");     }           @Override     public double getArea(){         return Math.PI*this.radius*this.radius;     }       public double getRadius(){         return this.radius;     } }

Notice that Circle class has implemented all the methods defined in the interface and it has some of its own methods also like getRadius(). The interface implementations can have multiple type of constructors. Lets see another interface implementation for Shape interface.

Rectangle.java

package com.journaldev.design;   public class Rectangle implements Shape {       private double width;     private double height;           public Rectangle(double w, double h){         this.width=w;         this.height=h;     }     @Override     public void draw() {         System.out.println("Drawing Rectangle");     }       @Override     public double getArea() {         return this.height*this.width;     }   }

Notice the use of override annotation, learn about annotations in java and why we should always use override annotation when overriding a method in java.
Here is a test program showing how to code in terms of interfaces and not implementations.

ShapeTest.java

package com.journaldev.design;   public class ShapeTest {       public static void main(String[] args) {                   //programming for interfaces not implementation         Shape shape = new Circle(10);                   shape.draw();         System.out.println("Area="+shape.getArea());                   //switching from one implementation to another easily         shape=new Rectangle(10,10);         shape.draw();         System.out.println("Area="+shape.getArea());         }   }

Output of the above programs is:

Drawing Circle Area=314.1592653589793 Drawing Rectangle Area=100.0

Java Interface Benefits

• Interface provides a contract for all the implementation classes, so its good to code in terms of interfaces because implementation classes can’t remove the methods we are using.
• Interfaces are good for starting point to define Type and create top level hierarchy in our code.
• Since a java class can implements multiple interfaces, it’s better to use interfaces as super class in most of the cases.

Java Interface Disadvantages

Although interfaces provide a lot of advantages but it has some disadvantages too.

• We need to chose interface methods very carefully at the time of designing our project because we can’t add of remove any methods from the interface at later point of time, it will lead compilation error for all the implementation classes. Sometimes this leads to have a lot of interfaces extending the base interface in our code that becomes hard to maintain.
• If the implementation classes has its own methods, we can’t use them directly in our code because the type of Object is an interface that doesn’t have those methods. For example, in above code we will get compilation error for code shape.getRadius(). To overcome this, we can use typecasting and use the method like this:

  Circle c = (Circle) shape; c.getRadius();

  Although class typecasting has its own disadvantages.

Thats all I have for interfaces in java and since we use it a lot in code as well as design patterns we should be aware of its features. Make sure you use interfaces in designing the system and as a contract between the client and the subclasses implementing the interfaces.

Update: Java 8 has changed the definition of interfaces with the introduction of default methods and static methods implementation in interfaces. For more details, please read Java 8 interface default methods.