SQL Interview Questions and answers Part 16 | How to print Prime Numbers in SQL Server

SQL Interview Questions and answers Part 16 | How to print Prime Numbers in SQL Server

In this video, it has been shown how to print Prime Number in SQL server. The problem has been solved using Recursive CTE and Corelated Subquery

#SQLInterviewQuestionsandanswers #sqlInterviewQuestions #SqlInterview #sqlInterviewQuestionsForTesting #sql InterviewQuestionsQuery #sqlInterviewQuestionsOnJoins #sqlTechnicalInterviewQuestions #SQLforETLTesters #CommonSqlInterviewQuestions #ITJunction4All

[MK-lhxd]

Best and shortest solution! Thank you!

[childhoodMem]

Easiest of all solutions. This is the exact way I would have done it in Java.

[shikharohilla]

Is it not necessary to use recursive word after with clause?

[ranjitsingh]

with cte as
(
select
2 as f2
union all
select f2+1
from cte where f2<30 )
select f2 from cte group by f2 having f2=2 or f2%2<>0

[anirvansen]

MY SQL Solution


with recursive prime_numbers as (
select 2 as prime_number
UNION ALL
select prime_number + 1 as prime_number from prime_numbers
where prime_number < 10
)
select * from prime_numbers t1 where not exists
(
select 1 from prime_numbers t2
where t1.prime_number % t2.prime_number = 0
and t1.prime_number <>
)

[prasanth]

Do freshers are also asked these kind of questions?

[shashankdhomne]

Query in Oracle

with t as (SELECT 0 + level v_num from dual connect by level <=10)
select distinct a.v_num from t a, t b
where mod(a.v_num, b.v_num) = 0
group by a.v_num
having count(a.v_num) =2
order by 1;

[ThanhNguyen-qwus]

When i try to replace A.PrimeNbr < 10 to A.PrimeNbr< 1000. It says "Msg 530, Level 16, State 1.The statement terminated. The maximum recursion 100 has been exhausted before statement completion."
How to fix this? Please let me know

[sandeepkhawas]

Hi, as i tried many times to understand the the query but couldn't understand so is there any other way to find out prime numbers.. thanks in advance

[nigang]

A.PrimeNbr % B.PrimeNbr = 0. --> Its checking mod value of current number with numbers from 2 to current (number-1) recursively
A.PrimeNbr != B.PrimeNbr --> This ensures it does not try to divide number by itself which will return 1 and fail condition.
Am I right?

[mnatum-v]

in subquery, why you used A.primenumber != B.PrimeNumber ?

[MohammedAsif-ccno]

I'm not able to understand the login of
not exists ( A.PrimeNbr % B.PrimeNbr = 0 and
A.PrimeNbr != B.PrimeNbr )
can someone theoretically explain it?

[funzone-gzks]

I was trying this in Snowflake which at the moment has few limitations with sub queries(co related), so I came up with the below approach which seems to be working:

with seq as
(select 2 as prime
union all
select prime+1 from seq where prime<10)

--non prime will have more than 1 factor, primes will only have 1
select prime from (select a.prime, sum(case when a.prime%b.prime =0 then 1 else 0 end) as number_of_factors
from seq a
join seq b
on a.prime>=b.prime
group by a.prime)
where number_of_Factors=1
order by prime ;

[pranavgupta]

How does the value of b increments while a value doesn't??

[pankajkharade]

With DS AS
(
Select Level LV From Dual
Connect By Level<=10
)
Select LV From DS
Where Mod(LV, 2)!=0;

[devanuj]

If all the numbers in the sub query return 'False' and by the use of 'NOT EXISTS' all the value are accepted then we wont be able to get only the Prime Numbers, instead we will get all the numbers. Is this instead, supposed to work recursively for A.PrimeNumber where whenever it returns 'True' the loop is ended and we move to the next number?