SQL Interview Questions and Answers




Welcome to our Top 13 SQL Interview Questions and Answers page, we I start with basic questions, move on to intermediate level and finish on advanced questions.

The best results achieve people who study and practice their skills and I would recommend to avoid simple memorization of answers.

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:

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

Question 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]

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

Question 4: How would apply date range filter? [answer]

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

Question 6: How do you find orphans? [answer]

Question 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:

Question 8You 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]

Question 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:

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

Question 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]

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

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

Questions with answers

Questions For Freshers:

Question 1: What type of joins have you used? [all questions]

Answer: Joins knowledge is a MUST HAVE. This interview question is quite nice because most people have used inner join and (left/right) outer join which is rather mandatory knowledge but those more experienced will also mention cross join and self-join. In SQL Server you can also get full outer join. Join also strongly relate to understanding database concepts which are very important.

Question 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.[all questions]

Answer: You use UNION operator. You can drill down this question and ask what is the difference between UNION and UNION ALL. More tricky question are how to sort the view (you use order by at the last query), how to name fields so they appear in query results/view schema (first query field names are used). How to filter groups when you use union using SQL (you would create separate query or use common table expression (CTE) or use unions in from with () or what happens when you have a combination of UNION and UNION ALL (very rare question!)

Question 3: What is the difference between where and having clause?[all questions]

Answer: In SQL WHERE Clause filters data on row level (before grouping). HAVING Clause filters data after GROUP BY has been performed so it filters on "groups".

Question 4: How would apply date range filter?[all questions]

Answer: This is a tricky question.

  • You can use simple condition >= and <= or similar or use between/and but the trick is to know your exact data type.
  • Sometimes date fields contain time and that is where the query can go wrong so it is recommended to use some date related functions to remove the time issue. In SQL Server common function to do that is datediff function.
  • You also have to be aware of different time zones and server time zone.
  • To increase query performance you may still want to use between however you should be aware of proper format you should use if not it might misbehave during filtering.

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

Answer: First question is what is a wildcard? Wildcards are special characters that allow matching string without having exact match. In simple words they work like contains or begins with. For more info visit how to use Wildcards.

Question 6: How do you find orphans?[all questions]

Answer: This is more comprehensive SQL and database interview question. First of all we test if the candidate knows what an orphan is. An Orphan is a foreign key value in "child table" which doesn’t exist in primary key column in parent table. To get it you can use left outer join (important: child table on left side) with join condition on primary/foreign key columns and with where clause where primary key is null. Adding distinct or count to select is common precise. In SQL Server you can also you except which will show all unique values from first query that don't exist in second query.

Question 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?[all questions]

Answer

These tasks require good grasp of SQL functions but also logical thinking which is one of the primary skills involved in solving sql questions. In this case I provided links to actual answers with code samples. Experienced people should give correct answer almost immediately. People with less experience might need more time or would require some help (Google).

Questions For Experienced:

Question 8You 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)?[all questions]

Answer: This test is mainly about good understanding of aggregate functions and date time. In this we need to group by Website, Filter data using datediff but the trick in here is to use correct time zone. If I want to do that using UTC time than I could use GetUTCDate() in sql server and the final answer related to calculated fields using aggregate functions that I will list on separate lines below:

TotalNumberOfClicks = Count(*) 'nothing special here

TotalUniqueVisitors = Count(distinct Ipaddress) ' we count ipaddress fields but only unique ip addresses. The next field should be in here but as it is more complicated I put it as third field.

TotalNumberOfUniquePageViews = Count(distinct PageName+IPAddress) 'This one is tricky to get unique pageview we need to count all visits but per page but only for unique IP address. So I combined pagename with ipaddress to counted unique values. Just to explain one page could receive 3 vists from 2 unique visits and another page could receive one visit from ip that visited previous page so Unique IP is 2, PageView is 3 (1 visitor 2 pages and 1 visitor 1 page) and visits is 4

Question 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.[all questions]

Answer: Microsoft introduced in SQL Server 2005 ranking function and it is ideal to solve this query. RANK() function can be used to do that, DENSE_Rank() can also be used. Actually the question is ambiguous because if your two top employees have the same total sales which position should the third employee get 2 (Dense_Rank() function) or 3 (Rank() Function)? In order to filter the query Common Table Expression (CTE) can be used or query can be put inside FROM using brackets ().

Advanced Questions:

Question 10: How to get accurate age of an employee using SQL?[all questions]

 

Are you a paid member?

Log in or Buy Membership from $9/month




Did you find this page useful? +9  |  -0
(9 Votes)

The website contains interesting materials, interview questions are also good.
good questions.