Hey guys, Hope you all are doing great. I am back with a new domain SQL. So the problem goes like, you need to print prime numbers less than 1000.

Seems easy right!! Just use a for loop check for the list of natural numbers for prime condition whether they are only divisible by 1 and itself.

But if you are asked to do it only using plain SQL, i will be using Oracle in my case, how would you do that.

In PL/SQL its possible with straight approach, but in SQL its bit tricky.Lets start....
Write a query to print all prime numbers less than or equal to 1000. Print your result on a single line, and use the (,) character as your separator (instead of a space).
For example, the output for all prime numbers  would be:
2,3,5,7

Algorithm:


1. First we will generate the numbers from 1 to 1000. This can be achieved by using level connect in SQL i.e. Hierarchical SQL. A condition that identifies the relationship between parent rows and child rows of the hierarchy. So we will get number 1, 2, 3.....1000.
This is how it will look on Oracle SQL format....

select level l from dual connect by level  <= 1000



2. Now we can use above query to generate another set of numbers and proceed with our logic.
So for checking the prime condition there should be a number which is when divided return 0 as remainder only 2 times, that is divided by 1 and divided by num (number itself).

 So we have a condition a SQL where we have Count = 2

 This is how we can implement it:


select l prime_number
from (select level l from dual connect by level <= 1000)
, (select level m from dual connect by level < = 1000)
where m<=l
group by l
having count(case l/m when trunc(l/m) then 'Y' end) = 2;

Output of above looks like:

PRIME_NUMBER
43
83
151
167
173
179
227
269

 

 3. Now for the final step we need to connect the output is separate lines to single line output separated by comma (,). For this purpose we will use LISTAGG function in SQL

 Program/Script:
So our final script looks like...

SELECT LISTAGG(prime_number,',') WITHIN GROUP (ORDER BY prime_number) AS NUMBERS
FROM(    
    select l prime_number
from (select level l from dual connect by level <= 1000)
, (select level m from dual connect by level <= 1000)
where m<=l
group by l
having count(case l/m when trunc(l/m) then 'Y' end) = 2);

Output will be

 
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

Hope you guys find my post useful, let me know how i can improve....
Till then. Take care!!
Posted by
Labels: CONNECT BY Hackerrank Hackerrank.com Prime Numbers Programming SQL
13

View comments

Greetings!! Here's a series of Cold Golf problems and solutions in Python as a part of 21 days of Code | Code Golf by Skillenza 

https://skillenza.com/challenge/21days-of-code

DAY - I

A Little Complex

Given n complex numbers each x + yi, where x is the real part and y is the imaginary part, find their product.

Input Format

The first line of input consists of an integer t denoting the number of test cases. t test cases follow.
1

For the need of a simple tabular Grid in your Angular JS application, one of the most basic and simple to use package is angular-ui-grid. The implementation is easy in many of the use cases, but when it comes to extend the limits of the ui-grid, life becomes tough.

One of the tricky task is to set the Grid height as dynamic, in other words to make the grid more flexible to number of rows and the grid size would depend on it.

Hey guys, Hope you all are doing great. 

Algorithm To Convert Post-fix Expression into Infix expression:-

Scan the Post-fix String from Left to Right. If the character is an Operand, then Push it on to the deque. If the character is an Operator, then Pop left Operator 1 and Operand 2 and concatenate them using Infix notation where the Operator is in between the Two Operands. The resultant expression is then pushed on the deque.

Hey guys, Hope you all are doing great.

As the first part of this series of Python package and installation we will cover pip vs Conda

pip vs Conda

pip

Python packages only. Compiles everything from source. Installs binary, wheel, the ones which pypi supply Blessed by the core Python community (i.e., Python 3.4+ includes code that automatically boostraps pip). conda

Python agnostic.

Hey guys,

Hope you all are doing great. I love Code Golfing, what it brings out in you is the hidden concepts of a programming language and especially the push to implement same thing in numerous ways.

Code Golfing and Python - Yeah that's my Poison!

Below are few tips and tricks which will give you an edge over other programmer to write the shortest code:

1. The foo if condition else bar condition can be shortened to

True if condition else False ------- [False,True][condition]

2.

Hey guys, Hope you all are doing great.

I have always found Regular expressions or REGEX quite amazing. You can solve your string related problem in just some simple magic of patterns.

Our brain works very well to identify the pattern in our day to day life. As these are visual clues, we tend to catch and analyse them better. In fact, their you make negligible mistake to tackle the scenario.
1

Hey guys, Hope you all are doing great. I am back with Some Python Programming tips for

CODE GOLF Challenges.

Here are the Second part of code golfing Tips in Python

I prefer Python or Python3 myself for code golfing

So we will discuss dome tips as generics for code golf programming in Python:

7. Use `n` to convert an integer to a string instead of using str(n).

Hey guys, Hope you all are doing great. I am back with Some Python Programming tips for

CODE GOLF Challenges.

So, What is CODE GOLF??

As per Wikipedia, it is a type of recreational computer programming competition in which participants strive to achieve the shortest possible source code (not to be confused with binary Sizecoding) that implements a certain algorithm.
12

Hey guys, Hope you all are doing great. I am back with with a byte of python performance.

I have been playing on codingame.com a lot now a days during my weekends.

Its cool site for the player aka coders who want to try there hands into gaming domain.
Loading