Advanced SQL Interview Questions and Answers

Here are some complex SQL interview problems that are for people who are looking for more advanced and challenging questions, along with the answers and complete explanations. Try to figure out the answer to the questions yourself before reading the answers.

Suppose we have 2 tables called Orders and Salesperson shown below:

Salesperson Orders
ID Name Age Salary
1 Abe 61 140000
2 Bob 34 44000
5 Chris 34 40000
7 Dan 41 52000
8 Ken 57 115000
11 Joe 38 38000
Number order_date cust_id salesperson_id Amount
10 8/2/96 4 2 540
20 1/30/99 4 8 1800
30 7/14/95 9 1 460
40 1/29/98 7 2 2400
50 2/3/98 6 7 600
60 3/2/98 6 7 720
70 5/6/98 9 7 150

Now suppose that we want to write SQL that must conform to the SQL standard.

We want to retrieve the names of all salespeople that have more than 1 order from the tables above. You can assume that each salesperson only has one ID.

If that is the case, then what (if anything) is wrong with the following SQL?:

SELECT Name
FROM Orders, Salesperson
WHERE Orders.salesperson_id = Salesperson.ID
GROUP BY salesperson_id
HAVING COUNT( salesperson_id ) >1

The answer and explanation to advanced SQL question 1

There is definitely something wrong with the SQL above, and it is probably something that most beginner SQL programmers may not notice. The problem is that the SQL Standard says that we can not select a column that is not part of the group by clause unless it is also contained within an aggregate function. If we try to run the SQL above in SQL Server, we would get an error that looks like this:

Column 'Name' is invalid in the select list because it is 
not contained in either an aggregate function or 
the GROUP BY clause.

You might be confused now, so let’s explain what that error means in plain English and through some simple examples. The most important thing you should take out of this discussion is understanding exactly why we get that error, and how to avoid it. There is a good reason for the error – read on to understand why.

You can see in the bad SQL above that the “Name” column is clearly not also a part of the group by statement, nor is it contained within an aggregate function (like SUM, MAX, etc).

As the error above suggests, we can fix the error by either wrapping the Name column inside an aggregate function or adding it to the Group By clause.

So if we want to write SQL that complies with the standard, then we could write something like this by adding the Name column to the Group By:

SELECT Name
FROM Orders, Salesperson
WHERE Orders.salesperson_id = Salesperson.ID
GROUP BY salesperson_id, Name                  
-- we added the name column to the group by, and now it works!
HAVING COUNT( salesperson_id ) >1

The SQL above will run just fine without giving any error.

We could also fix the problem by putting the Name column in any aggregate function, and then simply make that a part of our select statement. So, we could just write this SQL instead, and it would be perfectly legal according to the SQL standard. We chose to use the MAX aggregate function, but any other aggregate would work just fine as well:

SELECT MAX(Name) --put name in an aggregate function
FROM Orders, Salesperson
WHERE Orders.salesperson_id = Salesperson.ID
GROUP BY salesperson_id              
HAVING COUNT( salesperson_id ) >1

Adding the Name column to the group by, or wrapping the Name column in an aggregate will certainly fix the error – but it’s very important to note that both of those things will change the data that is returned to a state that you may not want.

Why does the selected column have to be in the group by clause or part of an aggregate function?

So, now you understand how to fix the error – but do you understand why it is a problem in the first place? Well, you should – because that is the most important thing to understand! So, let’s explain some more about why SQL gives that error shown above .

First off, let’s talk a little bit more about aggregate functions. You probably know what aggregate functions in SQL are – we used one in the example above. In case you forgot, aggregate functions are used to perform a mathematical function on the values inside a given column, which is passed into the aggregate function. Here are some of the commonly used aggregate functions:

AVG() - Returns the average value
COUNT() - Returns the number of rows
FIRST() - Returns the first value
LAST() - Returns the last value
MAX() - Returns the largest value
MIN() - Returns the smallest value
SUM() - Returns the sum

To illustrate why the SQL standard says that a selected column has to be in the group by clause or part of an aggregate function, let’s use another example. Suppose we have some tables called Starbucks_Stores and Starbucks_Employees. In case you don’t already know, Starbucks is a popular coffee shop/cafe in the USA:

Starbucks_Employees Starbucks_Stores
ID Name Age HourlyRate StoreID
1 Abe 61 14 10
2 Bob 34 10 30
5 Chris 34 9 40
7 Dan 41 11 50
8 Ken 57 11 60
11 Joe 38 13 70
store_id city
10 San Francisco
20 Los Angeles
30 San Francisco
40 Los Angeles
50 San Francisco
60 New York
70 San Francisco

Now, given the tables above let’s say that we write some SQL like this:

SELECT count(*) as num_employees, HourlyRate
FROM Starbucks_Employees JOIN Starbucks_Stores
ON Starbucks_Employees.StoreID = Starbucks_Stores.store_id
GROUP BY city

It looks like the SQL above would just return the number of Starbucks employees in each city, along with the HourlyRate – because it will group the employees based on whatever city they work in (thanks to the “group by city” statement).

Subscribe to our newsletter for more free interview questions.

The problem with selecting a non-aggregate column that is not in the group by

But the real question here is what exactly would be returned for the HourlyRate in the SQL above? Would it return every employee’s hourly rate separated by commas? Since we group by city, will it return the highest hourly rate for each city? Will it return the hourly rate as a distinct list, so those 2 guys making 11 dollars an hour will have the 11 returned only once?

The problem here is that we do not know what will be returned because we are not specific enough with what we are asking for in the SQL! If what we are asking for is not specific enough, then the SQL processor will not know what to return.

This is why almost all database implementations return an error when the SQL above is run (with the notable exception of MySQL) – and this is why the SQL does not conform to the Standard. In SQL Server running the SQL above will return the same error that we showed earlier.

Let’s explain even further in case the problem with that SQL is not crystal clear. The order of operations in which things will happen with the SQL above is:

1.  The 2 tables are joined on the condition that the 
Starbucks_Employees.StoreID column value is equal to the
 Starbucks_Stores.store_id column values.

2.  Groups are then created for each city - which means that 
each distinct city will have it's own "group".  So, there will 
be a total of 3 groups one each for San Francisco, New York, 
and Los Angeles.

3.  The data we are interested in is selected from each group 
that is created in step 2.

Because we end up with different groups based on the city, when we select a count(*), that will find the total count of rows in each and every group. But, the problem is that when we select HourlyRate, there will be multiple values for the HourlyRate within each group. For example, for the group created by the city of San Francisco there will be 4 different values for the HourlyRate – 14, 10, 11, and 13.

So the question is which value of the HourlyRate should be selected from each group? Well, it could be any one of those values – which is why that SQL results in an error. This is because what we are asking for is NOT specific enough – hopefully this is crystal clear now to you.

If the same HourlyRate were part of an aggregate function like MAX then it would simply return the highest HourlyRate within each group. And that is why having an aggregate function would fix the SQL error – because only one value will be selected from any given group.

So, this SQL is perfectly fine because we are more specific in what we ask for – but this SQL would only work for you if you actually want the highest HourlyRate for each city:

SELECT count(*) as num_employees, MAX(HourlyRate)
FROM Starbucks_Employees JOIN Starbucks_Stores
ON Starbucks_Employees.StoreID = Starbucks_Stores.store_id
GROUP BY city

Fix the error by adding column to the group clause

Another way to fix the error is to simply add the HourlyRate column to the group by clause. This also means that having the HourlyRate column wrapped in aggregate function is no longer necessary. So you could write some SQL like this and it would fix the error:

SELECT count(*) as num_employees, HourlyRate
FROM Starbucks_Employees JOIN Starbucks_Stores
ON Starbucks_Employees.StoreID = Starbucks_Stores.store_id
GROUP BY city, HourlyRate

This would then create groups based on the unique combination of the values in the HourlyRate and City columns. This means that there will be a different group for each HourlyRate and City combination – so $11, San Francisco and $11, Los Angeles will be 2 different groups. If you need to read up more on this topic then you can go here: Group By With Multiple Columns

With the SQL above, each group will only have one value for the HourlyRate, which also means that there will be no ambiguity or confusion when selecting the HourlyRate since there is only possible value to select. It is now very clear that one and only one HourlyRate value can be returned for each group.

Adding the column to the group by clause fixes the error but will alter the data that is returned

But, one very important thing to note is that even though adding the column to the group by will fix the error, it will also change the groups that are created. This means that the data returned will be completely different from what was returned before. So, the count(*) function will no longer return the count of employees in a given city, and will instead return the number of rows in each group created by the unique combination of the HourlyRate and city columns.

MySQL – selecting non-aggregate columns not in the group by

One very important thing that you should know is that MySQL actually allows you to have non-aggregated columns in the select list even if they are not a part of the group by clause (a quick side note: a non-aggregated column is simply a column that is not wrapped within an aggregate function). What this means is that you will not receive an error if you try to run any of the “bad” SQL above in MySQL. The reason it is allowed in MySQL is because MySQL assumes that you know what you are doing – and it does actually make sense in some scenarios. For instance, let’s refer back to the SQL that we started with:

SELECT Name
FROM Orders, Salesperson
WHERE Orders.salesperson_id = Salesperson.ID
GROUP BY salesperson_id
HAVING COUNT( salesperson_id ) >1

The reason the original SQL code (presented above) works just fine in MySQL is because there is a 1 to 1 mapping of salesperson name to ID – meaning that for every unique salesperson ID there is only one possible name. Another way of saying that is that each salesperson can only have one name. So when we create groups (which is done in the “GROUP BY salesperson_id”) based on the salesperson ID, each group will only have one and only one name.

This SQL will also run just fine in MySQL without returning an error:

SELECT count(*) as num_employees, HourlyRate
FROM Starbucks_Employees JOIN Starbucks_Stores
ON Starbucks_Employees.StoreID = Starbucks_Stores.store_id
GROUP BY city

But, even though the code above will not return an error, the HourlyRate that is returned by MySQL will be some arbitrary (random) value within each group. This is because when we create each group based on the city, each group can have different values for the HourlyRate.

In other words, there is no one to one mapping between the HourlyRate and the city like we had before with the salesperson ID and the name. So, because we are not being specific as to which HourlyRate we want, MySQL will return an arbitrary value . For instance, in the group created by the city of San Francisco, MySQL could return the HourlyRate for any employee who works in San Francisco – whether it is 14, 10, 11, or 13 we don’t really know since it is arbitrary/random in MySQL.

That concludes part 1 of our more difficult and complex SQL questions. Click on next to check out the next question that’s a part of our advanced SQL interview questions list.


Hiring? Job Hunting? Post a JOB or your RESUME on our JOB BOARD >>

Subscribe to our newsletter for more free interview questions.



FOLLOW Varoon Sahgal, Author of ProgrammerInterviewon

  • Zakleadz

    superb and very helpfull to understand the concepts practically :) :) thnk u :) :)

  • guest

    Sorry, but if you GROUP BY salesperson_id, then how would HAVING COUNT( salesperson_id ) would ever returne > 1?

    • Programmerinterview

      Because COUNT(salesperson_id) will just return the number of salesperson_id entries within each group – so for instance, if you look at the Orders table there are 3 rows that have a salesperson_id of 7, so when we group by salesperson_id that will create a ‘group’ comprised of the 3 rows with salesperson_id of 7, and then using the COUNT function against that group will basically just count the number of rows in that group, which will be 3….and that is why the having count(salesperson_id) will be greater than 1.

      You should try running the code with the sample data in a mysql installation (or whatever environment you prefer).  You will see that the code works just fine

      • guest

         Thank you!!

      • adnillou

        great discussion – thanks!

    • Ritesh_rhyme

      You need to concentrate on the join.As when the join is executed the records for a single salesperson_id are multiple.When implementing count on that the result can be >1

  • Robin Varghese

    Good article. Refreshed long forgotten concepts. Thanks

  • Guest

    Hi,

    I tried executing the below query in Oracle and found that it was returning an error which states that the hourly rate is not part of the group by expression.

    SELECT count(*) as num_employees, HourlyRate
    FROM Starbucks_Employees JOIN Starbucks_Stores
    ON Starbucks_Employees.StoreID = Starbucks_Stores.store_id
    GROUP BY cityCould you please help me in understanding what could be wrong with the query…Thanks..

    • Sara

      SELECT count(*) as num_employees, HourlyRate
      FROM Starbucks_Employees JOIN Starbucks_Stores
      ON Starbucks_Employees.StoreID = Starbucks_Stores.store_id
      GROUP BY HourlyRate –Use this

    • sudhanshu

      SELECT count(*) as num_employees, HourlyRate
      FROM Starbucks_Employees JOIN Starbucks_Stores
      ON Starbucks_Employees.StoreID = Starbucks_Stores.store_id
      GROUP BY city,HourlyRate

    • so

      What ever columns we call in the select statement the same column names should be present in the group by clause other wise it will throw error.

  • Nat Vkumar

    Thanks for explaining in detail

  • Shivaling Urabiannavar

    good explanation

  • peterkin Nyamongo

    D

  • Guest

    Very good explanation

  • http://www.facebook.com/sureshreddy.medapati Sureshreddy Medapati

    Thank you…

  • Pritam

    Thanks !!! superb explanation.

  • thanuja

    nice explination

  • Mihir J Patel

    For the first query

    SELECT Name
    FROM Orders, Salesperson
    WHERE Orders.salesperson_id = Salesperson.ID
    GROUP BY salesperson_id, Name
    – we added the name column to the group by, and now it works!
    HAVING COUNT( salesperson_id ) >1

    We can Also Do:

    SELECT Name

    From Salesperson S

    Where S.salesperson_id in

    (
    SELECT O.salesperson_id
    FROM Orders O
    Group By O.Salesperson_id
    Having Count(*)>1
    )

    Which one would have better performance?

    • Mihir

      I believe the 2nd one is better for performance. It uses subquery to find all salesperson_id with more than 1 order. Then it compares it with Salesperson table and gets all the Ids that match it.

  • Greg Bishop

    Nice explaination, really helped me nail an interview. I haven't messed with SQL in a while.

  • Kris

    can you check whether the following sql is correct or not?

    SELECT S.NAME, COUNT(*) AS NO_OF_ORDERS
    FROM SALESPERSON S, ORDERS O
    WHERE S.ID=O.SALESPERSON_ID
    GROUP BY S.NAME
    HAVING COUNT(*) > 1