HackerRank: [SQL Alternative Queries] (3/3) PRINT PRIME NUMBERS | declare, set, while, if, exists, substring in ms-sql

I started studying SQL from a very famous site - HackerRank. Here I will try to provide multiple approaches & solutions to the same problem. It will help you learn and understand SQL in a better way.

Please make use of my blog posts for learning purpose only and feel free to ask your questions in the comment box below in case of any doubt.

Click Here for the previous blog-post in the series.

Recommended SQL Courses:

1. Udemy: SQL - MySQL for Data Analytics and Business Intelligence
2. LinkedIn: Master SQL for Data Science
3. edX: Databases: Advanced Topics in SQL
4. edureka: SQL Essentials Training & Certification
5. Coursera: Learn SQL Basics for Data Science
6. Eduonix: SQL Queries

---

SQL Problem Statement:

Write a query to print all prime numbers less than or equal to 1000. Print your result on a single line, and use the ampersand (&) character as your separator (instead of a space).

For example, the output for all prime numbers <=10 would be:

2&3&5&7

Solution-1: (MS-SQL Query):

DECLARE @table TABLE (PrimeNumber INT)

DECLARE @final AS VARCHAR(1500)
SET @final = ''

DECLARE @counter INT
SET @counter = 2

WHILE @counter <= 1000
  BEGIN
      IF NOT EXISTS (SELECT primenumber
                     FROM   @table
                     WHERE  @counter % primenumber = 0)
        BEGIN
            INSERT INTO @table
            SELECT @counter

            SET @final = @final + Cast(@counter AS VARCHAR(20)) + '&'
        END

      SET @counter = @counter + 1
  END

SELECT Substring(@final, 0, Len(@final));

NOTE: 

• The DECLARE statement is used to declare a variable in SQL and its value is set to NULL.
  
  
• SET is for initializing the variable you declared previously, and you cannot SET the variable until you DECLARE it.
  
  
• SQL WHILE loop is used to execute the SQL statement(s) repeatedly until the specified condition result turn out to be false.
  
  
• The EXISTS operator is used to test for the existence of any record in a subquery. The EXISTS operator returns TRUE if the subquery returns one or more records.

Explanation (Flow Chart):

---

Expected Output:

2&3&5&7&11&13&17&19&23&29&31&37&41&43&47&53&59&61&67&71&73&79&83&89&97&101&103&
107&109&113&127&131&137&139&149&151&157&163&167&173&179&181&191&193&197&199&211
&223&227&229&233&239&241&251&257&263&269&271&277&281&283&293&307&311&313&317&331
&337&347&349&353&359&367&373&379&383&389&397&401&409&419&421&431&433&439&443&449
&457&461&463&467&479&487&491&499&503&509&521&523&541&547&557&563&569&571&577&587
&593&599&601&607&613&617&619&631&641&643&647&653&659&661&673&677&683&691&701&709
&719&727&733&739&743&751&757&761&769&773&787&797&809&811&821&823&827&829&839&853
&857&859&863&877&881&883&887&907&911&919&929&937&941&947&953&967&971&977&983&991
&997

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

Click here to see solutions for all Machine Learning Coursera Assignments.

&

Click here to see more codes for Raspberry Pi 3 and similar Family.

&

Click here to see more codes for NodeMCU ESP8266 and similar Family.

&

Click here to see more codes for Arduino Mega (ATMega 2560) and similar Family.

Feel free to ask doubts in the comment section. I will try my best to answer it.

If you find this helpful by any mean like, comment and share the post.

This is the simplest way to encourage me to keep doing such work.

Thanks & Regards,

-Akshay P Daga