What is the difference between sub query and co-related subquery

Sub Query:
Here inner query gets executed first and based on the result of it the outer query is executed.

This follows BOTTOM_UP approach, first BOTTOM query (inner query) executes and then UPper query (outer query) executes.

Problem Statement: Get all the employees whose department name is ‘A’
Tables are:
Employee_Table: emp_id_col, emp_name_col, emp_sal_col, emp_dept_id_col
Dept_table: dept_id_col, dept_name_col, dept_loc_col
Here dept_id the foreign key

select * from Employee_Table where 
      empt_dept_id_col = 
      (select dept_id_col from Dept_table where dept_name_col='A')

Correlated Sub Query:
I would say this as a kind of SELF JOIN. First outer query executes and then inner query and inner query gets evaluated for each row processed by the outer query.

This follows TOP-BOTTOM approach
Problem Statement: Get the list of employees whose salary is greater than the average salary of every department.
Tables are:
Employee_Table: emp_id_col, emp_name_col, emp_sal_col, emp_dept_id_col

select * from Employee_Table e where emp_sal_col > 
   (select avg(emp_sal_col) from Employee_Table m 
        where e.emp_dept_id_col = m.emp_dept_id_col)

Here in outer query the table alias ‘e’ gets evaluated before the inner query executes.

 

Find nth maximum salary of an employee.

This is another example of co-related sub query and most frequently asked interview question.

emp_id_col emp_name_col emp_sal_col
1 A 100
2 B 200
3 C 300
4 D 400
select emp_sal_col from Employee_Table e1 
  where 
    N-1 
    = 
    (select count(distinct(emp_sal_col)) from Employee_Table e2 
     where e1.emp_sal_col > e2.emp_sal_col)
// Where N is nth highest salary that we want to find.

Explanation:
As correlated subquery operates in top_bottom approach, first outer query executes and then inner query. Here, for every row of outer query, inner query is executed.
Let’s say we want to find 3rd highest salary(i.e, 200)
: First row with employee id ‘1’ and sal ‘100’ from outer query is passed to the inner query. In inner query we check count(distinct(sal)) which is ‘4’ where outerEmployeeTableView.sal > innerEmployeeTableView.sal (it means salaries greater than ‘100’) which is ‘3’. Since N-1, that is (3-1 = 2), does not match we go for next row.
: Second row with employee id ‘2’ and sal ‘200’ from outer query is passed to the inner query. In inner query we check count(distinct(sal)) which is ‘4’ where outerEmployeeTableView.sal > innerEmployeeTableView.sal (it means salaries greater than ‘200’) which is ‘2’. Since N-1, that is (3-1 = 2), matches, outer query returns the salary ‘200’ as output which is 3rd highest salary in the table.

When should we use GROUP BY clause

When there is a need to aggregate/group values in a column based on some criteria in same/another column then we go for GROUP BY clause.

Usage of Aggregate function on column: col_sales:

select sum(col_sales) from Table_Sales;

Output: here the output would just one value

 

Usage of Aggregate function on column ‘col_sales’ based on date in column ‘col_sales_date’

Problem Statement: Calculate the total sales on day by day basis (OR daily sales) from Table_Sales table
Query:
select col_sales_date, sum(col_sales) from Table_Sales GROUP BY col_sales_date;

Output: here the output would be number of rows proportional to the number of different dates in the column ‘col_sales_date’

frequent sql interview questions

difference between ‘where’ and ‘having’ clause

‘where’ clause is used to apply a condition on an column directly.

Usage
select * from table_emp where col_emp_sal<10000;

‘having’ clause is used to apply a condition on a aggregate column.

Usage
select sum(col_emp_sal) as aggr_col_emp_sal from table_emp having aggr_col_emp_sal<10000;

Note: In the above query we cannot use where clause on aggregate alias ‘aggr_col_emp_sal’

difference between ‘distinct’ and ‘group by’ clauses

‘distinct’ keyword is used to remove duplicates
D1.select distinct(col_dept_id) from Table_Employee;  

‘group by’ enables us to use aggregate functions, see in more detail here
G1: select col_sales_date, sum(col_sales) from Table_Sales GROUP BY col_sales_date;
G2: select sum(col_sales) from Table_Sales;

If D1&G2 is considered, both returns the same result but the way they work internally, their execution plans varies across the database servers.

In MS SQL: The execution plan for both the queries is same and hence there wont be any difference
Doing an “EXPLAIN SELECT DISTINCT” shows “Using where; Using temporary ” MySQL will create a temporary table.

vs a “EXPLAIN SELECT a,b, c from T1, T2 where T2.A=T1.A GROUP BY a” just shows “Using where”

difference between UNION and UNION ALL?

UNION Combines Rows From the Two Tables and Excludes all Duplicates Rows in the Result. Execution time wise, this is slower than UNION ALL since it has to eliminate duplicates.
UNION All Returns all Rows in the UNION Operation, including Duplicates. Execution wise it is faster than UNION but it can perform slower at times due to the greater amount of data transferred across the network.

Transaction and ACID properties

A set of statements executed on a resource or resources applied with ACID properties is known as Transaction.

Note: Here database is one resource but resource need not be only database

ACID Properties:

  • Atomicity
  • Consistency
  • Isolation
  • Durability

Atomicity:
This property describes that the set of statements involved in the transaction has to work as a single unit (i.e, automic). This is the minimum requirement that has to be met for a transaction

Consistency:
If the data used in the transaction is consistent before starting the transaction, then it has to be left in a consistent state even after the transaction.
Note: What is consistent data: data applied with some rules.
Example:

accountNo | accountBal
101        | 6000

Here lets define a rule that says if: accountBal>5000; then it is said to be in consistent state.
so before transaction it is 6000 and after transaction lets say it has become 8000, then it is left in consistent state. If it falls below 5000, ie, lets say 4000, then it is said to be inconsistent state in which case we will rollback the transaction

And if the data used in the transaction is already in inconsistent state then we cannot guarantee the consistent data after the transaction.

Isolation:
It is an ability to isolate the data used in one transaction from the other transaction.

Durability:

Once the transaction starts to save, it should definitely save all the statements within the transaction at any cost else rollback all the statements and throw exception.

design patterns used in core java (j2se) source code

These are some of the design patterns used in core java API that I have gone through in my experience:

Decorator and Bridge patterns – java.io

Observer pattern is available as standard API

Iterator Pattern – All collection classes

Template Pattern – Arrays.sort() and Collection.sort()

Singleton Pattern – Runtime, Calendar

Factory Pattern – Wrappers of primitive data type’s valueOf methods [Integer.valueOf()]

Other most commonly used patterns:
Decorator: Java IO
Strategy Pattern: If you’ve ever used dependency injection, you’ve used the strategy pattern.
Facade: Any service APIs implement this pattern.
Singleton: Used in caching
Builder: While Writing Unit Tests
Prototype: Cloning
Adapter: asList , toString
Chain Of Responsibility: Logging
Factory: Action Mapping
MVC: Web frameworks
Proxy: Web services
Template

difference between design pattern and framework

Framework is a skeleton of code for developing java applications. Design pattern is a solution for a particular problem. The relationship is ‘Framework is made using one or more design patterns’

For example: Struts and Spring are frameworks developed by making use of MVC pattern.

core design principles

 

 MUST REMEMBER 4 ALL PROGRAMMERS 

– Encapsulate what varies

– Favor composition over inheritance

– Program to an interface, not implementations

– Strive for loosely coupled designs between objects that interact (Observer)

– Classes should be open for extension and closed for modification (Decorator)

– Dependency Inversion Principle: Depend on abstraction. Do not depend on concrete classes. This principle suggests that our high level components should not depend on low level components instead both should depend on abstractions. (Command Pattern)

– Principle of least knowledge: Only talk to your friends (Façade Pattern)

– Hollywood Principle: Don’t call us, we will call you. (Template, Factory Method, Observer Patterns)

– A class should have only one reason to change (Iterator Pattern, State Pattern)

core design pattern definitions

must for a programmer or designer

Strategy Pattern
This pattern lets you define a family of algorithms, encapsulate each one and makes them interchangeable
Favor composition over inheritance.

Observer Pattern
It defines one to many dependency between the objects so that when one object changes its state, all its dependents are notified and updated automatically

Decorator Pattern
It attaches additional responsibilities to an object dynamically at runtime. Decorators provide flexible alternative to sub-classing for extending functionality.
Note: Decorators should implement the same interface or abstract class as that of the component they are going to decorate.

Factory Method Pattern
It defines an interface for creating and object and lets subclasses decide which class to instantiate. Few guide lines to follow this pattern:
– No variable should hold a reference to the concrete class
– No class should be derived from concrete class
– No method should over-ride and implemented method of the base class

Singleton Pattern
Ensure a class has only one instance and provide a global point of access to it.

Command Pattern
It allows you to decouple the requestor of an action from the object that actually performs an action.

Adapter Pattern
It converts interface of a class into another interface the clients expect. It lets the classes work together that wouldn’t be otherwise possible because of incompatible interfaces.

Iterator Pattern
It provides a way to access the elements of an aggregate object sequentially without exposing its underlying implementation. It also places the task of traversal on the iterator object, not on the aggregate, which simplifies the aggregate interface.

Composite Pattern
It helps us to compose objects into tree structures to represent part whole hierarchies. Composite lets clients treat individual objects and compositions of objects uniformly.

Façade Pattern
It provides a unified interface to a set of interfaces in a sub system. Façade defines high level interface that makes the subsystem easier to use.
Note: Actually speaking one should invoke methods that belong to:

  1. Object itself
  2. Objects passed in as a parameter to the method
  3. Any object the method instantiates
  4. Any components of the object (other object references)

Note: Adaptors and Facades may wrap multiple classes but façade’s intent is to simplify while adaptor’s is to convert the interface to something different

Template Pattern
It defines steps of an algorithm and allow subclasses to provide implementation for one or more steps.
Note: It is a great design tool for creating frameworks where framework controls how something gets done but leaves you to specify what is actually happening at each step of an algorithm. Eg: Array.sort(Obj) imples that Obj should impl Comparable interface.

State Pattern
Allow an object to alter its behavior when its internal state changes. The object will appear to change its class.

Proxy Pattern
It provides and place holder for another object to control access to it.

All possible ways of implementing singleton pattern and its pros and cons

Here 5 possible ways are discussed
#1

public class Singleton {
 private static Singleton instance;

private Singleton() {
 }

public static Singleton getInstance() {
 if(instance == null) {
 instance = new Singleton();
 }
 return instance;
 }
}

Cons:
Will not suffice for multi threaded environment

#2

public class Singleton {
 private static Singleton instance;

private Singleton() {
 }

public static synchronized Singleton getInstance() {
 if(instance == null) {
 instance = new Singleton();
 }
 return instance;
 }
}

pros:
Will work in single and multithreaded env’s
Cons:
Will be uneccessary overhead once after instance is created for the first time because every time we call getInstance() method we need to acquire lock and release lock which is overhead after instance is available

#3

public class Singleton {
 private static Singleton instance;

private Singleton() {
 }

public static Singleton getInstance() {
 synchronized(this) {
 if(instance == null) {
 instance = new Singleton();
 }
 }
 return instance;
 }
}

pros:
Will not suffice in multithreaded env’s
Cons:
1. Will be uneccessary overhead once after instance is created for the first time because every time we call getInstance() method we need to acquire lock and release lock which is overhead after instance is available
2. There is a chance of more than one thread getting into if(instance == null) block there by creating multiple instances.

#4 (Double Checking)

public class Singleton {
 private static Singleton instance;

private Singleton() {
 }

public static Singleton getInstance() {
 if(instance == null) {
 synchronized(this) {
 if(instance == null) {
 instance = new Singleton();
 }
 }
 }
 return instance;
 }
}

pros:
Will work in multithreaded env’s
Cons:
1. It will not work in prior versions of JDK5.0
2. Double checking is verey pathetic way of implementing Singleton pattern and best practices doesnt suggest to go for this implementation.

#5

public class Singleton {
 private static Singleton instance = new Singleton();

private Singleton() {
 }

public static Singleton getInstance() {
 return instance;
 }
}

pros:
1. Simplest and trusted way as we are leaving the instantiation to the JVM
2. Works in both single and multithreaded env’s

Using the CSS float Property to Design Web Page Layouts

The CSS float property is a very important property for layout. It allows you to position your Web page designs exactly as you want them to display – but in order to use it you have to understand how it works.

A CSS float property looks like this:

.right {float: right;}

What Floats?

You can’t float every element on a Web page. To get technical, you can only float block-level elements. These are the elements that take up a block of space on the page, like images (<img/>), paragraphs (<p></p>), divisions (<div></div>), and lists (<ul></ul>). Other elements that affect text, but don’t create a box on the page are called inline elements and can’t be floated. These are elements like span (<span></span>), line breaks (<br/>), strong emphasis (<strong></strong>), or italics (<i></i>).
Where Do They Float?
You can float elements to the right or the left. Any element that follows the floated element will flow around the floated element on the other side.

For example, if I float an image to the left, any text or other elements following it will flow around it to the right. See the example. And if I float an image to the right, any text or other elements following it will flow around it to the left. See the example. An image that is placed in a block of text without any float style applied to it will display as the browser is set to display images. This is usually with the first line of following text displayed at the bottom of the image. See the example.
How Far Will They Float?

An element that has been floated will move as far to the left or right of the container element as it can. This results in several different situations depending upon how your code is written. For these examples, I will be floating a small <div> to the left:

  • If the floated element does not have a pre-defined width, it will take up as much horizontal space as required and available, regardless of the float. Note: some browsers attempt to place elements beside floated elements when the width isn’t defined – usually giving the non-floated element only a small amount of space. So you should always define a width on floated elements.
  • If the container element is the HTML <body>, the floated div will sit on the left margin of the page.
  • If the container element is itself contained by something else, the floated div will sit on the left margin of the container.
  • You can nest floated elements, and that can result in the float ending up in a surprising place. For example, this float is a left floated div inside a right floated div.
  • Floated elements will sit next to each other if there is room in the container. For example, this container has 3 100px wide divs floated in a 400px wide container.

You can even use floats to create a photo gallery layout. You put each thumbnail (it works best when they are all the same size) in a DIV with the caption and the float the divs in the container. No matter how wide the browser window is, the thumbnails will line up uniformly.
Turning Off the Float

Once you know how to get an element to float, it’s important to know how to turn off the float. You turn off the float with the CSS clear property. You can clear left floats, right floats or both:

clear: left;
 clear: right;
 clear: both;

Any element that you set the clear property for will appear below an element that is floated that direction. For example, in this example the first two paragraphs of text are not cleared, but the third is.

Play with the clear value of different elements in your documents to get different layout effects. One of the most interesting floated layouts is a series of images down the right or left column next to paragraphs of text. Even if the text is not long enough to scroll past the image, you can use the clear on all the images to make sure that they appear in the column rather than next to the previous image.

Images floated to the left and to the right.

The HTML (repeat this paragraph):

<p> <img src="maliwithcar_tn.jpg" alt="Mali with car" /> Duis aute irure dolor sed do eiusmod tempor incididunt in reprehenderit in voluptate. Cupidatat non proident, ut labore et dolore magna aliqua. </p>

//The CSS (to float the images to the left):

img.float { float:left;clear:left; margin:5px;}

//And to the right:

img.float { float:right;clear:right; margin:5px;}

Using Floats for Layout

Once you understand how the float property works, you can start using it to lay out your Web pages. These are the steps I take to create a floated Web page:

* Design the layout (on paper or in a graphics tool or in my head).
* Determine where the site divisions are going to be.
* Determine the widths of the various containers and the elements within them.
* Float everything. Even the outermost container element is floated to the left so that I know where it will be in relation to the browser view port.

As long as you know the widths (percentages are fine) of your layout sections, you can use the float property to put them where they belong on the page. And the nice thing is, you don’t have to worry as much about the box model being different for IE or Firefox.

Note: this is a very good article I have copied from http://webdesign.about.com/od/advancedcss/a/aa010107.htm

Mawazo

Mostly technology with occasional sprinkling of other random thoughts

amintabar

Amir Amintabar's personal page

101 Books

Reading my way through Time Magazine's 100 Greatest Novels since 1923 (plus Ulysses)

Seek, Plunnge and more...

My words, my world...

ARRM Foundation

Do not wait for leaders; do it alone, person to person - Mother Teresa

Executive Management

An unexamined life is not worth living – Socrates

javaproffesionals

A topnotch WordPress.com site

thehandwritinganalyst

Just another WordPress.com site

coding algorithms

"An approximate answer to the right problem is worth a good deal more than an exact answer to an approximate problem." -- John Tukey