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
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;
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;
FROM TOUR_GROUP
GROUP BY TOUR
ORDER BY COUNT(DISTINCT LANGUAGE), TOUR;
Reference : [1] [2]
Design pattern interview questions
- What is design patterns ? Have you used any design pattern in your code ?
- 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.
- ddd
Friday, January 03, 2014
//
Labels:
Java
//
1 comments
//
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]
Thursday, January 02, 2014
//
Labels:
Java
//
0
comments
//
Popular Posts
-
The best solution to know about these init levels is to understand the " man init " command output on Unix. There are basically 8...
-
How to Unlock BSNL 3G data card to use it with Airtel and Vodafone Model no : LW272 ? How to unlock BSNL 3G data card( Model no : LW272 )us...
-
How to transfer bike registration from one State to other (Karnataka to Andhra)?? Most of us having two wheelers purchased and registered in...
-
ఓం శ్రీ స్వామియే శరణం ఆయ్యప్ప!! Related posts : Trip to Sabarimala - Part 1 Trip to Sabarimala - Part 2 Ayappa Deeksha required things...
-
Following are some of interesting blogs I found till now ...please comment to add your blog here. Blogs in English : http://nitawriter.word...
Popular posts
- Airtel and vodafone GPRS settings for pocket PC phones
- Andhra 2 America
- Ayyappa Deeksha required things
- Blogs I watch !
- Captions for your bike
- DB2 FAQs
- Deepavali Vs The Goddes of sleep
- ETV - Dhee D2 D3
- Evolution of smoking in India Women
- How to make credit card payments?
- init 0, init 1, init 2 ..
- Java-J2EE interview preparation
- mCheck Application jar or jad download
- My SQL FAQs
- My Travelogues
- Old is blod - New is italic
- Online pay methids for credit cards
- Oracle FAQs
- Pilgrimages
- Smoking in Indian Women
- Technology Vs Humans
- Twitter feeds for all Telugu stars on single page.
- Unix best practices
- Unix FAQs