Posts

Find missing sequence number. Get the numbers where sequence is missing.

Most of the time the question was asked while having Database interview that we have number sequence present in a table but some numbers are missing. Can you write the query to detect where number is missing from sequence and if possible then also can get the missing number? OK, it’s not a rocket science so let’s begin. Please give me a paper and a pen to write query Sir. I am using a Temporary variable table here. DECLARE @Temp TABLE (        Id INT IDENTITY ( 1 , 1 ),        Num INT ) INSERT INTO @Temp ( Num ) VALUES ( 1 ) ,( 2 ), ( 4 ), ( 6 ), ( 7 ), ( 9 ) SELECT * FROM @Temp SELECT a . Id , a . Num , b . Num , ( a . Num - b . Num ) as Diff , ( b . Num + 1 ) as MissingNum FROM @Temp a LEFT JOIN @Temp b ON b . Id = ( a . Id - 1 ) WHERE ( a . Num - b . Num ) > 1 You see it’s just a simple SELECT statement but tricky. OK, yes it will produce my result but we do not have any Id field in our database, w
Post a Comment
Read more

Find difference pattern of the given sequence.

This one is from the interview question I have asked. A sequence was given to me and asked to write a SQL query to find the difference pattern of the sequence and complete the sequence by adding missing numbers. Give sequence:  '1,5,13,21,25,29,41' Below is the query to find the answer: SET NOCOUNT ON   DECLARE @temp AS TABLE ( Id INT IDENTITY , Nums INT , NextNum INT DEFAULT 0 ,  Pattern AS ( NextNum - Nums )   )   INSERT @temp ( Nums )   SELECT * FROM dbo . Split ( '1,5,13,21,25,29,41' , ',' ) -- Here assume that Split function you have added already OR please follow the  link  --  http://anishrana.blogspot.in/2012/03/split-string-in-sql-server-using-xml.html -- and create one.   UPDATE a        SET NextNum = b . Nums   FROM @temp a   JOIN @temp b ON a . Id = ( b . Id - 1 )   DECLARE @pattern INT = NULL,               @firstNum INT = NULL,               @lastNum INT = NULL   SE
Post a Comment
Read more

Print Fibonacci Series in SQL

Some times you were asked to write a program to print Fibonacci series in interview. So below is the SQL query that will print the Fibonacci series. Copy , Paste and run. DECLARE @A INT = 1 , @B INT = 0 , @C INT = 0 DECLARE @Limit INT = 50 WHILE ( @C <= @LIMIT ) BEGIN        PRINT @C        SET @C = @A + @B        SET @A = @B        SET @B = @C   END Output:  0 1 1 2 3 5 8 13 21 34  
Post a Comment
Read more

Change Text Color with Javascript at more that one part in single page.

Below code is the modified version of the topic name  Change Text Color with Javascript as one of the reader demands. Copy and paste the code in notepad and save it as any name with .html extension and run in browser. Logic behind to use the same feature in different parts of the page is by using class instead of id. Id is unique and cannot be used more than once but class can be used more than once to group the feature. The previous code uses ID and this one use Class on div. < html > < head >     < title > Text Color Change </ title >     < script type ="text/javascript" language ="javascript">         elmColor = 0;         function changeColor(elmColor) {             for (i = 0; i < document.getElementsByClassName( 'colorChange' ).length; i++) {                 switch (elmColor) {                     case 0:                         document.getElementsByClassName( 'colo
Post a Comment
Read more