SQL Interview Questions and Answers

My Learning: Log in Learning Report [Log in]


Remove Ads or Log in
Remove Ads or Log in
+1  |  -0

Welcome to our SQL Interview Questions and Answers page. Want TOP 10 Questions? How about TOP 13 Mixed level questions and answers related to SQL Querying (Select)?

I start with basic questions, move on to intermediate level and finish on advanced questions.

NOTE: If you would like to focus on basic SQL interview questions only then we recommend visiting our Basic Interview Q&As page

Top 13 "mixed" questions

Questions are split into 3 levels:

  • For Freshers (Beginner Level),
  • For Experienced (Intermediate Level - 2 to 3 years of experience) and
  • Advanced (4 and 5 plus years of experience)

Questions For Freshers:

1. What type of joins have you used? [answer]

2. How can you combine two tables/views together? For instance one table contains 100 rows and the other one contains 200 rows, have exactly the same fields and you want to show a query with all data (300 rows). This sql interview question can get complicated. [answer]

3. What is the difference between where and having clause? [answer]

4. How would apply date range filter? [answer]

5. What type of wildcards have you used? This is usually one of mandatory sql interview question. [answer]

6. How do you find orphans? [answer]

7. How would you solve the following sql queries using today's date: First day of previous month, First day of current month, Last day of previous month, Last day of current month? [answer]

Questions For Experienced:

8. You have a table that records website traffic. The table contains website name (multiple websites), page name, IP address and UTC date time. What would be the query to show all websites visited in the last 30 days with total number or visits, total number if unique page view and total number of unique visitors (using IP Address)? [answer]

9. How to display top 5 employees with the higest number of sales (total) and display position as a field. Note that if both of employees have the same total sales values they should receive the same position, in other words Top 5 employees might return more than 5 employees. [answer]

Advanced Questions:

10. How to get accurate age of an employee using SQL? [answer]

11. This is SQL Server interview question. You have three fields ID, Date and Total. Your table contains multiple rows for the same day which is valid data however for reporting purpose you need to show only one row per day. The row with the highest ID per day should be returned the rest should be hidden from users (not returned). [answer]

12. How to return truly random data from a table? Let say top 100 random rows? [answer]

13. How to create recursive query in SQL Server? [answer]


Remove Ads or Log in

We need you!

By becoming a proud member we can:
  • Switch off annoying ads
  • Create more articles and videos
  • Develop more useful features supporting your learning
  • Provide FREE webinars (for members)
  • Feel our hard work since 2011 is appreciated
Become a proud member and get exclusive features.

If 2% of our 64,943 visitors (Last 30 days) become a proud member than we can quit our jobs and work full time on this website. We started membership in May 2014 and so far 0.07% of our visitors are members and we would like to say THANK YOU to those who become a proud member and support our efforts.

Thanks to members:
  • Education is for FREE and we create more FREE articles and videos
  • We provide new features (members) that can speed up learning.
  • 53% of our page views (82,000 each month) do not have page ads.
  • We switched video ads from 14% of our videos (50 out of 345).
We have invested 4 years of our time in this website and we estimate it will take about 5 years before we can quit our jobs and dedicate all our time to develop considerably our website which should help to truly achieve our vision of free education for everyone; all thanks to support of our members only. Become a proud member

Visitor Voting

We are thinking about re-introducing webinars (SQL, SSIS, SSRS, SSAS) but this time for members only (due to effort).
Is that something you might be interested in?
Visitor Voting: Yes +31  |  No -7
Add comment (no email required).



Add Comment
Name: San
Date Posted: 9/29/2011 4:17:25 AM
Comment: THANKS


Name: John
Date Posted: 10/31/2011 4:14:52 PM
Comment: I like your sql interview questions and the answers you have given. Keep adding them


Name: Smart Tester
Date Posted: 11/2/2011 5:04:53 PM
Comment: How about questions listed at top (without answers) and then links to the question/ full answer? This will allow me to have a go at answering the question first. Currently, if I read the question, i can see your suggested answer in my field of vision.


Name: Emil
Date Posted: 11/2/2011 5:10:03 PM
Comment: Hi Smart tester. Thank you for your suggestion and I see where you are coming from and I must admit that when I read any sql question I can see key words in the answers as you pointed out. I will add extra section where I will list only questions and later on I will provide links to the answers. Regards Emil


Name: sachin gaike
Date Posted: 12/20/2011 2:41:51 AM
Comment: very simple


Name: Vikas
Date Posted: 12/26/2011 4:48:53 PM
Comment: Nice... very usefull website..


Name: Vikas Palav
Date Posted: 12/26/2011 4:51:19 PM
Comment: Nice... very usefull website.. Thnks


Name: Tina
Date Posted: 1/10/2012 7:33:49 AM
Comment: I like your questions.Please add more of them.


Name: cnukaus
Date Posted: 1/23/2012 8:21:46 PM
Comment: Group by and Max should work. Can you let me know why you think it would not work here? Cheers > Answer: Usually Group By and aggregate function are used (MAX/MIN) but in this case that will not work. Removing duplications with this kind of rules is not so easy however SQL Server provides ranking functions and the candidate can use dense_rank function partition by Date and order by id (desc) and then use cte/from query and filter it using rank = 1. There are several other ways to solve that but I found this way to be most efficient and simple.


Name: Emil
Date Posted: 1/30/2012 10:00:37 PM
Comment: Hi cnukaus. The task is to remove "duplicates" using specific logic and not to use aggregate function to show MAX or MIN value. For instance if you look at the example I provided you will see that you cannot use aggregate function on third column because it would "mix" rows and you might get highest ID but third column might actually come from a different row if you use aggregate function on it.


Name: deepak
Date Posted: 2/9/2012 12:30:18 PM
Comment:

the correct function is newid()

:)




Name: Emil
Date Posted: 2/10/2012 7:28:02 PM
Comment:

Hi deepak

I don't really understand how new_ID() can help as it does not contain any logic it is just random ID? Feel free to provide code sample.




Name: harji
Date Posted: 3/1/2012 5:57:17 AM
Comment:

nice site




Name: VARMA
Date Posted: 3/15/2012 1:08:46 PM
Comment:

thanks....

very useful to me....

 

 




Name: suresh songire
Date Posted: 4/9/2012 10:25:37 AM
Comment:

All question & ansewer is very good but all answers are provided in Details specialy Queries .




Name: Developer
Date Posted: 4/23/2012 1:31:56 PM
Comment:

Thanks for the post. Questions are really good!




Name: Mustafa
Date Posted: 5/24/2012 8:46:20 AM
Comment:

very usefull Question and answers

 




Name: Megha
Date Posted: 7/6/2012 3:32:08 PM
Comment:

i found these questions very practical.




Name: vijay pawar
Date Posted: 7/17/2012 7:55:24 AM
Comment:

Nice..its very use full..

 




Name: tanaya
Date Posted: 7/23/2012 7:47:34 AM
Comment:

i want to get like this result:

 

by firing this querry i m getting

select add_months(trunc(sysdate,'MON'),-13) column_name from dual

 

o/p

column_name

----------------------

6/1/2011

 

i want a querry to get o/p as

6/1/2011 as column title

----------------

6/1/2011




Name: ganesh jagdale
Date Posted: 8/22/2012 9:11:45 AM
Comment:

thanks u very much




Name: Jas
Date Posted: 9/1/2012 5:54:20 PM
Comment:

Awesome blog to practise questions. But It will be better if complete answers are provided.




Name: Mohd Yusuf Khan
Date Posted: 11/27/2012 12:03:25 PM
Comment:

its great thanx guys...




Name: Anton
Date Posted: 12/13/2012 6:19:23 AM
Comment:

re: "Hi cnukaus. The task is to remove "duplicates" using specific logic and not to use aggregate function to show MAX or MIN value. For instance if you look at the example I provided you will see that you cannot use aggregate function on third column because it would "mix" rows and you might get highest ID but third column might actually come from a different row if you use aggregate function on it. "

you're not right, we CAN use MAX or MIN functions to complete that task, you just don't have enough SQL experience...

As first step, you can group the records by Date to find MAX(id).  Then you can use these results to join with original table:

select t.ID, t.date, t.total

from table t

inner join

(select date, MAX(id) as maxid

group by date) Q on Q.maxid = t.id




Name: Anton
Date Posted: 12/13/2012 6:41:21 AM
Comment:

"How to return truly random data from a table? Let say top 100 random rows?
I must admit I didn't answer correctly this sql interview question a few years back
"

you can use count() + rand() + row_number() + round() + distinct functions + [while loop and temp table] to generate 100 random numbers (otherwise rand() will return same value for each row)

that would be really random rows.




Name: Emil
Date Posted: 12/14/2012 2:07:23 PM
Comment:

Hi Anton,

With MAX / MIN I had group by in mind. You can do it another way... with joins, stored procedures even .net but that is not the point. The point is to use the most effective approach avialable to you that is easy to maintain. The same applies to your second comment.

 

If we forget readability and performance is important than it would nice to see difference in performance.

Also think about your answer during an interview (which is the purpose of this blog post). Which one would be better?

I will update the blog post to make it more clear.

Regards

Emil




Name: Anton
Date Posted: 12/17/2012 1:38:50 AM
Comment:

Hi Emil,

re "With MAX / MIN I had group by in mind. You can do it another way... with joins, stored procedures even .net but that is not the point. The point is to use the most effective approach avialable to you that is easy to maintain. The same applies to your second comment.

If we forget readability and performance is important than it would nice to see difference in performance."

Readability and performance are both great for that query. That query is really simple and efficient. You may try to compare execution plan, IO, CPU...

Anyway, that wasn't a part of the interview question. If the question was 'to prepare most efficient query' then the candidate may consider different approches and choose right one. If the interviwer expects to see right answer in terms of performance, then he/she should provide more infomation to candidate regarding table structure, existing indexes, table size, etc., and then the candidate can choose which approach is the best. E.g. sometimes the right approach to use query hints.

Also you can't just answer "I will use CTE" as you should first know if the SQL version is 2005 or above. I see that there are still lots of DB servers runing SQL 2K, so if that question is general (not applicable only to certain SQL versions), then the answer should be general as well.  MAX, GROUP BY and INNER JOIN is a part of SQL standard and it will work on any platform, and that query will work very fast.

And readability is mostly regarding dev experience. E.g. if develover never used i++ instruction in C, then he/she can assume i = i + 1 instruction is more readable, but that's not true for majority, plus i++ is more sufficient.

re: 2nd question (truly random numbers)

If you need trully random numbers, you should use some hardware generator of random numbers that can be intergated into SQL.

Your answer was "newid() + order by". If you really care about performance that wouldn't work.   If the table has billions of rows, it it need to generate NEWID for ALL rows, then sort them ALL and only then show first 100 rows. Actually SQL can die against large tables.

So my approach is not bad as you think. It's universal. Firstly, you find the number of rows - that's very quick operation if the table has primary key (99.9999% cases). Then you should prepare the temp table with 100 random numbers in [1..count(*)] range - that is quick as well. Then you should prepare ROW_NUMBER for each row - that operation is similar to "generate new ID for all rows". Finally you just need to find 100 rows where ID is in that list of random numbers - this command will be really fast.   In real rime, my query won't generate ROW_NUMBER for ALL rows - in most cases it will finish earlier till it finds all needed rows. But in your approach the system will generate NEWID for ALL rows, and only then it will sort ALL records, and finally once the sorting is complete it will show TOP rows.

So as you can see my approach is not worse then yours for small / average tables and it's definitely better for large tables. Your query may be simpler, but it may kill SQL server. If seniour DBA is a member of the team that will interview you, you may fail...

Regards,

Anton




Name: Anton
Date Posted: 12/17/2012 2:16:08 AM
Comment:

One more thing (important):

ROW_NUMBER requires sorting as well, but we don't need to actually sort anything. We may choose the ID for sorting (if that ID has primary key on it, the data are already sorted). Even if there are no any clustered indexes, we may choose the filed with non-clustered index and the sorting will be much faster)




Name: Emil
Date Posted: 12/18/2012 1:25:42 PM
Comment:

Hi Anton,

The questions are open and not specific for a reason. A person like you with greater knowledge of SQL will have opportunity to provide more comprehensive answer. Check our Telephone SQL Questions for more specific questions. 

Answers are samples... There are not necessarily answers I look for or I give during my interviews.

I appreciate your feedback but I think we should close this discussion on this page. Feel free to send me a private email using the contact us menu and I will also reply to your comment in an email.

Take care

Emil




Name: CeeCee
Date Posted: 5/6/2013 9:01:46 PM
Comment:

I was given a quiz like this for my job interview. Just thought I should share!

 

Candidate _____________________________________________
 
 
Sample Tables:
employee (Table)
 
emp_ID     fname        lname         dept_ID     
-----------      ------            -------           ----------- 
1                  John           Doe             3
2                  Jane           Doe             2
3                  Ron             Smith         3
4                  Nancy        King            4
5                  James       Monroe      1
6                  Lisa            Jones         0
 
 
department (Table)
 
dept_ID     dept_name                      
-----------      ------------------------------ 
1                  Sales
2                  Customer Service
3                  Accounting
4                  Marketing
5                  Purchasing

 

 

 

Write the SQL required to do the following:

 

 

1. Add a new department named Janitorial Services whose department ID is 6.

 

 

 

 

 

 

 

2. Modify the database to reflect that Ron Smith has changed jobs and now works in the Sales Department.

 

 

 

 

3. Retrieve the last name and department name of all employees whose last name begins with A through J. 

 




Name: Emil
Date Posted: 5/9/2013 1:59:01 PM
Comment:

Hi CeeCee,

Thanks for sharing.

Take care

Emil




Name: sahar
Date Posted: 5/23/2013 1:27:06 PM
Comment:
Hi, I wanna write the Sql code of the type TIME field and I want to calculate difference between two times
Sum arithmetic operations on different time then I do, but I can not find the code. Thank

 




Name: Emil
Date Posted: 5/24/2013 7:40:21 PM
Comment:

Hi Try using DateDiff and Hour (interval)




Name: Sudhir
Date Posted: 6/7/2013 5:40:39 PM
Comment:

Thanks very much..this was very useful






Are you a member?

If yes than please log in

Learning Map is for members with Bronze, Silver and Gold Membership.
For more information visit membership options.


Close this window , log in or learn more about membership benefits
  Become a Proud Member