Understanding having clause in SQL

Background

If you have written queries or worked on a project that requires database support then you must have use or atleast familiar with having clause. This is also one of the popular interview questions for beginners to test database knowledge if you ask me. Simple syntax looks like -

SELECT column_name(s)
FROM table_name
WHERE condition
GROUP BY column_name(s)
HAVING condition
ORDER BY column_name(s);

So your 1st and foremost answer is that you use having clause with "group by" clause. How and why we will come to later part in of this discussion. Having said that before we proceed make sure you know what group by clause does. Also you need to have an idea of what aggregate functions are . Eg. min(), max(), count(), sum() etc.

Understanding having clause in SQL

So far we know we use having clause with group by clause. Let's answer the question why. 

Let's say you have a table employee which have basic data of an employee - id, name, department etc. 

Problem statement : Now we are interested to find out how many employees are there in each department and probably see the result in sorted order so that department with maximum employees is displayed first. How would you do this? Using following query -

select department, count(*) from employee group by department order by count(*) desc;

This works fine.  Now let's redefine our problem statement.

Problem statement :  Let's say we now want the same thing - department and number of employees in each department sorted in descending order. However this time we have an additional constraint. We want to see only those departments that have more than 10 employees in it. You would probably try -

select department, count(*) from employee group by department where count(*) > 10 order by count(*) desc;

Problem : Does not work

Error : ORA-00934: group function is not allowed here
             00934. 00000 -  "group function is not allowed here"
(Above error is show for oracle database)

NOTE :  An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference

Problem is that you cannot use aggregate functions in where clause. Solution? - Having clause. This is exactly why having clause was introduced. Once you have applied the group by clause and wish to filter the data further on the results obtained you use having clause. So your correct query would be -

select department, count(*) from employee group by department having count(*) > 10 order by count(*) desc;

Aggregate functions are allowed in having clause. So lets go over the original syntax again and see how it works -

SELECT column_name(s)
FROM table_name
WHERE condition
GROUP BY column_name(s)
HAVING condition
ORDER BY column_name(s);

1. First you select  column_name(s) from the table that match the where clause condition
2. Once result is obtained then group by clause is applied to it to get the next set of result.
3. Once that is done condition is having clause is applied to further filter the result.
4. Finally order by clause is applied to sort the result as required and return.

NOTE :  where clause us applied to filter results before group by clause is applied where as having clause is applied after.

Other alternative can be like -

select department, count from ( 
select department, count(*) count from employee group by department )\
where  count>10;

Related Links

• Remove duplicate rows from table in Oracle(OSFG)
• How does database indexing work? (OSFG) 
• Select top N records from each category in PL/SQL(OSFG)

Find fastest 3 horses out of 25 horses puzzle

Question

You have 25 horses and you need to pick fastest 3 horses out of those. In each race you can race maximum of 5 horses as there are 5 tracks available. What are minimum number of races needed to find the fastest 3 without using a stopwatch. 

Solution

7 races needed. 

Understanding

Since we don't have a stopwatch only way to find fastest is by racing horses. Lets have 5 races each of 5 horses and let following be the results -

• A > B > C > D > E
• F > G > H > I > J
• K > L > M > N > O
• P > Q > R > S > T
• U > V > W > X > Y

Above are results of each race.  We have 5 races till now. Now lest race between fastest of all previos 5 races i.e A,F,K,P,U. We have 6 races till now .Let's say the result for that is -

• F > K > A > P > U

Since We are interested in top 3 P and U are useless for us. Also since P and U were fastest among their group we can ignore all members of P and U too. So now only horses under consideration are -

• F > G > H > I > J
• K > L > M > N > O
• A > B > C > D > E

Now if we consider A as possible candidate for top 3 then others in group of A are redundant - since we already have F and K faster than A. So we can ignore B,C,D,E

Now horses under consideration are  -

• F > G > H > I > J
• K > L > M > N > O
• A

Now in the group lead by k possible horse candidates are K and L - since F is already faster than K if we consider K and L we already have 3. So we can ignore M,N and O. So remaining horses are -

• F > G > H > I > J
• K > L
• A

 Now lets consider group led by F. We can consider G and H as possible candidates for top 3 since if they are I and J are redundant. So remaining now are -

• F > G > H
• K > L
• A

 Now we already know fastest among all in F since we got that result by running fastest among each group. So only horses we need comparison for 2nd and 3rd position are - G, H, K, L, A

These are 5 horses we can have another race and find the top 2 out of them. Lets say the result was -

• L > H > K > G > A

We have done 7 races now. So fastest onces are L and H. We already the fastest among all - F. So the final answer is -

• F > L > H 

So the answer is 7 races needed.

10 coins puzzle

Question

There are 10 coin placed in front of you 5 of which are heads and 5 of which are tails. You are blind folded so you don't know which ones are which. You need to make two piles of coins so that both have equal heads. You are allowed to flip a coin any number of time. You obviously wont know which is head and which is tails by touching it.

Solution

Make two piles of coins of equal number (5 each) and then flip all the coins on one side.

Understanding

Lets say you split in into two equal piles. 1st pile has 3 heads and 2 tails. Since there were 5 heads and 5 tails other pile will have 3 tails and 2 heads. Now when you flip all in pile 1 then there will be 3 tails and 2 heads same as pile number 2.

Generically if there are n heads and 5-n tails in pile 1 then in pile 2 will have n tails and 5-n heads. When we flip all coins in pile 1 then pile 1 will have n tails and 5-n heads which is same as pile 2.

Burning island puzzle

Question

A man in stranded on an island covered in forest. Wind is blowing from the west. Lightning strikes to the west side of the forest and starts spreading with the wind. The fire will burn the whole forest killing the man in the process. There are cliffs around the island so that man cannot escape. How can man survive the fire?

Solution

Man picks up a logs , lights it up with the fire from the west end. Then he runs towards the east and lights that part of the forest. This will burn up the eastern end of the forest and then man can take shelter in that burnt area while fire from eastern end burns the remaining forest.

Four men in hats puzzle

Question

As shown in picture above there are 4 men looking forward. None of them can see back. There is a opaque wall between man number 3 and man 4 (1,2,3 cannot see pass the wall). Two of the men are wearing a black hat and two of them are wearing a white hat. Each man can see the color of the hat wore by the men in front of him. (1 can see 2,3 and 2 can see 3) but each person does not know the color of the hat he is wearing.

Now one of the man needs to call out the color of his hat else they all die in 10 mins. Which man will callout the color of his hat correctly and why?

Solution

Answer is Man no 2.

Reasoning

 Lets start by eliminating men. Man number 4 is at the other end of opaque wall facing other side. There is no way he can see any men or the color of their hat. So he is eliminated. Now man no 3 also cannot see anyone else - he cannot look back and he cannot see beyond wall. So he is eliminated too. Now man number 1 knows the color of man 2 and 3. Now lets say they (2 and 3) were wearing same color hat then man no 1 would know the color of his hat since there are 2 white and 2 black hat. But he keeps mum which means man 2 and 3 are wearing different hat. S0 man number 2 waits for sometime if he does not hear man 1 calling out that means man 2 and 3 are wearing different color hats. Since man 2 knows the color of hat wore by man 3 he know the color of his hat and calls it out.

Enabling Eclipse key map/shortcuts in Android Studio

Background

If you are from a developer using Eclipse then when you start using Android Studio then it becomes difficult to learn new shortcuts. For such cases Android Studio provides an option to use Eclipse shortcuts and in this post we will see how.

Enabling Eclipse key map/shortcuts in Android Studio

Go to 

• Android Studio -> Preferences

Type in keymap in the searchbox. You should be able to see a keymap section -

Next select Eclipse or Eclipse(Mac OS X) whichever you are more comfortable with. Then click Apply and Ok.

You should be good to use Eclipse shortcuts now. No need to restart.

Related Links

• How to add Eclipse Code Formatter file in Android Studio(OSFG)
• Android : URI is not registered (Settings | Project Settings | Schemas and DTDs) (OSFG)  

Select top N records from each category in PL/SQL

Background

Lets say you have 2 tables -

1. EMPLOYEE
2. EMPLOYEE_SALARY

EMPLOYEE table has employee id which is a primary key and his name.  EMPLOYEE_SALARY has employee id which is foreign key to id in EMPLOYEE table. This table has employee department and salary. You need to write a query that returns top 2 employees from each department that has highest salary.

Tables creation and data insertion

Table Queries :

create table schema8.EMPLOYEE(ID int, name varchar2(255));
alter table schema8.EMPLOYEE ADD CONSTRAINT PRIMARY_KEY PRIMARY KEY (ID);
create table schema8.EMPLOYEE_SALARY(EMPLOYEE_ID int, department varchar2(255), salary int);
alter table schema8.EMPLOYEE_SALARY ADD CONSTRAINT FK_EMP_ID FOREIGN KEY (EMPLOYEE_ID) REFERENCES schema8.EMPLOYEE(ID);

Data Queries for  EMPLOYEE table:

insert into schema8.EMPLOYEE values(1,'Aniket');
insert into schema8.EMPLOYEE values(2,'John');
insert into schema8.EMPLOYEE values(3,'Sam');
insert into schema8.EMPLOYEE values(4,'Ron');
insert into schema8.EMPLOYEE values(5,'Sky');
insert into schema8.EMPLOYEE values(6,'Paul');
insert into schema8.EMPLOYEE values(7,'Dan');
insert into schema8.EMPLOYEE values(8,'Jess');
insert into schema8.EMPLOYEE values(9,'Troy');
insert into schema8.EMPLOYEE values(10,'Mike');

 Data Queries for EMPLOYEE_SALARY table:

insert into schema8.EMPLOYEE_SALARY values(1,'IT',10000);
insert into schema8.EMPLOYEE_SALARY values(2,'Admin',500);
insert into schema8.EMPLOYEE_SALARY values(3,'Sales',1200);
insert into schema8.EMPLOYEE_SALARY values(4,'Sales',1500);
insert into schema8.EMPLOYEE_SALARY values(5,'IT',9000);
insert into schema8.EMPLOYEE_SALARY values(6,'Admin',4000);
insert into schema8.EMPLOYEE_SALARY values(7,'Admin',5000);
insert into schema8.EMPLOYEE_SALARY values(8,'IT',9500);
insert into schema8.EMPLOYEE_SALARY values(9,'Sales',1000);
insert into schema8.EMPLOYEE_SALARY values(10,'Admin',6000);

Final data :

select * from schema8.EMPLOYEE;
select * from schema8.EMPLOYEE_SALARY;

Solution

We are going to use RANK to partition by department and order by salary - 

select * from (
select id , name, department, salary, RANK() over (partition by department order by salary desc) as rank from(
select e.id, e.name, es.department,es.salary from schema8.EMPLOYEE e left OUTER join schema8.EMPLOYEE_SALARY es on (e.id=es.employee_id))
) where rank <= 2;

First we have done a left outer join so that we capture all employee records with their respective salaries and departments. In outer query we have ranked it based on their salaries in respective department. Finally we select all records that have rank <=2 i.e top 2 records.

Related Links

• Remove duplicate rows from table in Oracle(OSFG) 
• Basic PL/SQL syntax and query executions on Oracle 11g R2 database(OSFG) 
• How does database indexing work? (OSFG)