SQL interview question

sql query interview questions and answers
sql interview questions for testers
sql interview questions for experienced professionals
sql interview questions for data analyst
sql interview questions for 5 years experience
sql interview questions 2020
sql interview questions for business analyst
complex sql interview questions

I got following question on an interview: Given a table of natural numbers with some missing ones, provide output of two tables, beginning of number gap in first table and ending in second. Example:

 ____    ________
|    |   |   |   |
| 1  |   | 3 | 3 |
| 2  |   | 6 | 7 |
| 4  |   | 10| 12|
| 5  |   |___|___|
| 8  |
| 9  |
| 13 |
|____|

While this is pretty much the same as Phil Sandler's answer, this should return two separate tables (and I think it looks cleaner) (it works in SQL Server, at least):

DECLARE @temp TABLE (num int)
INSERT INTO @temp VALUES (1),(2),(4),(5),(8),(9),(13)

DECLARE @min INT, @max INT
SELECT @min = MIN(num), @max = MAX(num) FROM @temp

SELECT t.num + 1 AS range_start
    FROM @temp t
    LEFT JOIN @temp t2 ON t.num + 1 = t2.num
    WHERE t.num < @max AND t2.num IS NULL

SELECT t.num - 1 AS range_end
    FROM @temp t
    LEFT JOIN @temp t2 ON t.num - 1 = t2.num
    WHERE t.num > @min AND t2.num IS NULL

Top SQL Interview Questions You must Prepare For 2020, Top 50 SQL Interview Questions & Answers. Details: Last Updated: 07 July 2020. 1. What is DBMS? A Database Management System (DBMS) is a program that� Our SQL Interview Questions blog is the one-stop resource from where you can boost your interview preparation. It has a set of top 65 questions which an interviewer plans to ask during an interview process. It starts with the basic SQL interview questions and later continues to advanced questions based on your discussions and answers.

Itzik Ben Gan writes a lot about these "gaps and islands" problems. His row_number solution to this is

WITH C AS
(
SELECT N, ROW_NUMBER() OVER (ORDER BY N) AS RN
FROM t
)
SELECT Cur.N+1,Nxt.N-1
FROM C AS Cur 
JOIN C AS Nxt ON Nxt.RN = Cur.RN+1
WHERE Nxt.N-Cur.N>1

And a solution without row_number from the same source.

SELECT N+1 AS start_range,
(SELECT MIN(B.N) FROM t AS B WHERE B.N > A.N)-1 AS end_range
FROM t AS A
WHERE NOT EXISTS(SELECT * FROM t AS B WHERE B.N = A.N+1)
AND N< (SELECT MAX(N) FROM t)

Top 50 SQL Interview Questions & Answers, Comprehensive, community-driven list of essential SQL interview questions. Whether you're a candidate or interviewer, these interview questions will help� Comprehensive, community-driven list of essential SQL interview questions. Whether you're a candidate or interviewer, these interview questions will help prepare you for your next SQL interview ahead of time.

This works without DB Specific SQL and it could probably be made a little cleaner but it does work

EDIT: You can see this working at on this Query at StackExchange Data Explorer

SELECT low,high FROM 

(

SELECT col1, low 

FROM
(Select n1.col1 col1, min(n2.col1) + 1 low
 from numbers n1
inner join numbers n2
on n1.col1 < n2.col1 

Group by n1.col1) t
WHERE t.low not in (SELECT col1 FROM NUMBERS)
and t.low < (Select MAX(col1) from numbers) 
) t

INNER JOIN 
(

SELECT col1 - 1 col1, high
 FROM
(Select n1.col1 col1 , min(n2.col1) - 1 high
 from numbers n1
inner join numbers n2
on n1.col1 < n2.col1 

Group by n1.col1) t
WHERE t.high not in (SELECT col1 FROM NUMBERS) 
) t2
ON t.col1 = t2.col1

41 Essential SQL Interview Questions and Answers, Basic Interview Questions. 1. State the differences between HAVING and WHERE clauses Duration: 42:05 Posted: 4 days ago Dear readers, these SQL Interview Questions have been designed specially to get you acquainted with the nature of questions you may encounter during your interview for the subject of SQL. As per my experience good interviewers hardly plan to ask any particular question during your interview, normally questions start with some basic concept of

This is SQL Server syntax:

CREATE TABLE #temp (columnA int)

INSERT INTO #temp VALUES(1)
INSERT INTO #temp VALUES(2)
INSERT INTO #temp VALUES(4)
INSERT INTO #temp VALUES(5)
INSERT INTO #temp VALUES(8)
INSERT INTO #temp VALUES(9)
INSERT INTO #temp VALUES(13)

SELECT 
    t1.columnA - 1
FROM 
    #temp t1
    LEFT JOIN #temp t2 ON t1.columnA = t2.ColumnA + 1
WHERE 
    t2.ColumnA IS NULL
    AND t1.ColumnA != (SELECT MIN(ColumnA) from #temp)  

SELECT 
    t1.columnA + 1
FROM 
    #temp t1
    LEFT JOIN #temp t2 ON t1.columnA = t2.ColumnA - 1
WHERE 
    t2.ColumnA IS NULL  
    AND t1.ColumnA != (SELECT MAX(ColumnA) from #temp)  

DROP table #temp

SQL Interview Questions asked in Top Companies in 2020, SQL Interview Questions. 26-01-2017. What is SQL? SQL stands for Structured Query Language. It is a language used to interact with the database, i.e to create � Mindmajix offers Advanced SQL Interview Questions and Answers 2019 that helps you in cracking your interview & acquire dream career as SQL Developer. If you want to become a Certified SQL Developer, then visit Mindmajix - A Global online training platform: “ SQL Server Training ”.

Something like this:

SELECT col1, col2 FROM
(
    SELECT x + 1 as col1, 
        ROW_NUMBER() OVER(ORDER BY x) AS 'rownum'  
    FROM tbl y 
    WHERE NOT EXISTS (SELECT x FROM tbl z WHERE z.x = y.x + 1) 
        AND x <> (SELECT MAX(x) FROM tbl)
) a
INNER JOIN
(
    SELECT x - 1 as col2,
        ROW_NUMBER() OVER(ORDER BY x) AS 'rownum'  
    FROM tbl y 
    WHERE NOT EXISTS (SELECT x FROM tbl z WHERE z.x = y.x - 1) 
        AND x <> (SELECT MIN(x) FROM tbl)
) b
ON a.rownum = b.rownum

The "rownum" syntax will be different for different DBMS. The above might work for SQL Server, but I haven't tested it.

As one of the comments pointed out, many DBMS's have analytics that will make this easier.

SQL Interview Questions, SQL Interview Questions. There is given sql interview questions and answers that has been asked in many companies. For PL/SQL interview questions, visit our� Common SQL Interview Questions; Practical SQL Query Interview Questions (SQL Server Queries examples with answers). I don’t want to take much time of yours but I couldn’t move further without mentioning about this inevitable job interview question which every hiring manager asks you in any interview i.e., Tell Me About Yourself. Answering

SQL Interview Questions, Frequently Asked SQL Interview Questions - [Updated 2020]. What is the difference between SQL Vs NoSQL ? What do you know about a Database� Create an SQL query that shows the TOP 3 authors who sold the most books in total! (Note: I got a very, very similar SQL interview question for a data scientist position at a very well-known Swedish IT company.) SQL Interview Question #2. You work for a startup that makes an online presentation software.

▷Top 90 SQL Interview Questions And Answers [UPDATED 2020], Best SQL Interview Questions and Answers. Here we have listed top SQL interview questions and answers that are frequently asked by the� 90 most popular SQL Interview Questions and Answers: These are the most common and useful SQL interview questions for freshers as well as experienced candidates. Basics to advanced concepts of SQL are covered in this article. Refer to these questions for a quick revision of major SQL concepts before appearing for an interview.

50+ Top SQL Interview Questions and Answers in 2020 [Updated], Best SQL Interview Questions. Let's start. Q #1) What is SQL? Answer: Structured Query Language SQL is a database tool that is used�

Comments
  • Hmm... You could probably do this easily with analytic functions like lag and lead (maybe when I have time at lunch)... but that would be specific to Oracle (or others that support those functions). Was this to be a generic solution that can run on any RDBMS, or are you allowed to assume a specific implementation?
  • I suppose it had to be something generic, because other questions, which was on programming, were language-agnostic.
  • I think you should be able to do this with two exists statements and a self comparison to populate a temp table (one exists to see when the next number isn't in the table, another for when the preceding number isn't).
  • Didn't know about Data Explorer!!
  • Um, how does this produce the two-column result...?
  • Wow, I'm getting downvotes while a query that produces the wrong result is getting upvotes.
  • We came up with the same concept (but slightly different implementation), so you get an upvote from me.
  • @egrunin: the questions says output of two tables, not two columns.
  • @Phil: you're right: the illustration showed one result table, the text showed two (and some of the other answers read it as it did as well). Unfortunately SO won't let me retract my downvote unless the answer gets edited. Sorry.
  • For first and last, you could add something for MAX and MIN values of the column.
  • @Phil: not anymore :) and if you want a two-table solution, just remove the row-number clauses and the outer SELECT
  • Welcome to Stack Overflow, please explain why this answers the question.
  • Code only answers are discouraged. Please add some explanation as to how this solves the problem, or how this differs from the existing answers. From Review