DB2 - GROUP BY - HAVING

DB2 - GROUP BY - HAVING
GROUP BY : To group different rows of data on specific coloumn name .
HAVING : on finale output of select query put we can put a condition to filter specific
Note : While "WHERE" is used to apply selection criteria to the base data, "HAVING" is used to apply selection criteria to the grouped data:

Example 1 :

TOUR_GROUP : Table has following coloumns

TOUR    GUIDE    LANGUAGE    TOUR_DATE    START_TIME    END_TIME    GROUP_SIZE    AVAILABILITY
Query 1 :

SELECT LANGUAGE, COUNT(*) "NUMBER_OF_TOURS", MAX(GROUP_SIZE) "MAX_GROUP_SIZE", MIN(GROUP_SIZE) "MIN_GROUP_SIZE"
FROM TOUR_GROUP
GROUP BY LANGUAGE
ORDER BY NUMBER_OF_TOURS;

Query 2 :
SELECT LANGUAGE, COUNT(*) "NUMBER_OF_TOURS", MAX(GROUP_SIZE) "MAX_GROUP_SIZE", MIN(GROUP_SIZE) "MIN_GROUP_SIZE"
FROM TOUR_GROUP
WHERE GROUP_SIZE <= 20
GROUP BY LANGUAGE
HAVING COUNT(*) > 1
ORDER BY NUMBER_OF_TOURS;
Query 3 :
SELECT TOUR, COUNT (DISTINCT LANGUAGE) "NUMBER_LANGUAGES"
FROM TOUR_GROUP
GROUP BY TOUR
ORDER BY COUNT(DISTINCT LANGUAGE), TOUR;

Reference : [1] [2]

Read more


Design pattern interview questions

Design pattern interview questions
  •  What is design patterns ? Have you used any design pattern in your code ?
Design patterns are tried and tested way to solve particular design issues by various programmers in the world. Design patterns are extension of code reuse.
  • Can you name few design patterns used in standard JDK library?
    • Decorator design pattern which is used in various Java IO classes,
    • Singleton pattern which is used in Runtime ,Calendar and various other classes,
    • Factory pattern which is used along with various Immutable classes likes Boolean e.g. Boolean.valueOf.
    • Observer pattern which is used in Swing and many event listener frameworks.
  • What is Singleton design pattern in Java ? write code for thread-safe singleton in Java.
    • Singleton pattern focus on sharing of expensive object in whole system. Only one instance of a particular class is maintained in whole application which is shared by all modules. Java.lang.Runtime is a classical example of Singleton design pattern. From Java 5 onwards you can use enum to thread-safe singleton.
  • What is MVC design pattern ? Give one example of MVC design pattern ?
    • Model-view-controller (MVC) is a pattern used to isolate business logic from the user interface. Using MVC, the Model represents the information (the data) of the application and the business rules used to manipulate the data, the View corresponds to elements of the user interface such as text, checkbox items, and so forth, and the Controller manages details involving the communication between the model and view. The controller handles user actions such as keystrokes and mouse movements and pipes them into the model or view as required.
    • MVC is often seen in web applications, where the view is the actual HTML page, and the controller is the code that gathers dynamic data and generates the content within the HTML. Finally, the model is represented by the actual content, usually stored in a database or XML files, and the business rules that transform that content based on user actions.
    • Struts frame work follows MVC.
    • caliculator design cane be one example of MVC.
  • Adapter design pattern ? Give examples of adapter design pattern in Java?
    • The Adapter Design Pattern allows you to make an existing class work with other existing class libraries without changing the code of the existing class.
    • We often need to use the methods of an existing class to work with other existing libraries. The way to do this is by creating another class, named the Adapter, that inherits from the existing class while implementing the interface of the existing library. The end result is that the Adapter can call the method of the existing class (since the Adapter inherits from the existing class) and can work in the existing library (since the Adapter implements the interface of the existing library).
    • Adaptee >Adapter > Interface.
    • adapter design pattern
  • ddd
References : [1] [2] [3]

Read more


Sorting a list using comparator (Difference between Comparator and Comparable in Java)


One of the common interview question is "What are differences between Comparator and Comparable". or "How will you sort collection of employee objects by its id or name". For that we can use two interfaces, i.e., Comparator and Comparable. Before we actually see differences,let me give you brief introduction of both.
Comparable interface
Class whose objects to be sorted must implement this interface. In this, we have to implement compareTo(Object) method.
Comparator interface
The class whose objects to be sorted do not need to implement this interface. Some third class can implement this interface to sort. E.g., xyz class can implement Comparator interface to sort collection of ABC object by id , by name etc
Here is a simple example using comparator interface sorted a simple list with custom objects...
Program :
import java.util.ArrayList;
import java.util.Collections;
import java.util.Comparator;
import java.util.List;

public class CollectionEmployee implements Comparator
 {

    /**
     * @param args
     */
    public int empID;
    public String empName;

    public CollectionEmployee(int eid, String ename) {
        empID = eid;
        empName = ename;
    }

    public int compare(CollectionEmployee object1, CollectionEmployee object2) {
        // TODO Auto-generated method stub
        return ((object1.empID > object2.empID) ? 1 : 0);
    }

    public static void main(String[] args) {
        List
 empList = new ArrayList
();
        empList.add(new CollectionEmployee(100, "saurabh"));
        empList.add(new CollectionEmployee(300, "Anirudh"));
        empList.add(new CollectionEmployee(200, "Babu"));
        System.out.println("----list as it is ------");
        for (CollectionEmployee emp : empList) {
            System.out.println("No: " + emp.empID + " Name: " + emp.empName);
        }
        System.out.println("----Sorted by id------");
        Collections.sort(empList, new CollectionEmployee(200, "hari"));
        for (CollectionEmployee emp : empList) {
            System.out.println("No: " + emp.empID + " Name: " + emp.empName);
        }
        System.out.println("----Sortd by name-----------");
        // Sort by name
        Collections.sort(empList, new CollectionEmployee(200, "hari") {

            @Override
            public int compare(CollectionEmployee o1, CollectionEmployee o2) {
                return o1.empName.compareTo(o2.empName);
            }
        });
        for (CollectionEmployee emp : empList) {
            System.out.println("No: " + emp.empID + " Name: " + emp.empName);
        }
    }

}
Output :
----list as it is ------
No: 100 Name: saurabh
No: 300 Name: Anirudh
No: 200 Name: Babu
----Sorted by id------
No: 100 Name: saurabh
No: 200 Name: Babu
No: 300 Name: Anirudh
----Sortd by name-----------
No: 300 Name: Anirudh
No: 200 Name: Babu
No: 100 Name: saurabh
Reference : [1]

Read more

Popular Posts

Enter your email address:

Buffs ...

Tags


Powered by WidgetsForFree