Teradata: Find Prime Numbers

Teradata: Find Prime Numbers

One of the typical computer programming language problem statement for beginners is to find Prime Number Series. In this tutorial, we will see how we can find prime number series using SQL in Teradata.

• Dataset: Populate table with consecutive numbers till where prime number series is required.

  
  CREATE MULTISET VOLATILE TABLE  prime_number_example
  (
   numbers INTEGER
   )
  ON COMMIT PRESERVE ROWS;
  
  
  --Data
  INSERT INTO prime_number_example
  SELECT 
    row_number() over (order by calendar_date) numbers 
  FROM 
    Sys_Calendar.CALENDAR 
  WHERE 
    calendar_date >= CURRENT_DATE - 30 
    AND calendar_date < CURRENT_DATE;
  

  Note: Table prime_number_example will have numbers from 1 to 30, this number can be increased to extend prime number series.
  
  
  
• Query to find Prime number: MINUS set operator can be leveraged to find prime number

  
  --Step 2:
  SELECT 
    numbers 
  FROM 
    prime_number_example 
  MINUS 
  SELECT 
    a.numbers * b.numbers numbers 
  FROM 
    prime_number_example a, 
    prime_number_example b 
  WHERE 
    a.numbers <> 1 
    AND b.numbers <> 1;
  
  Output:
  numbers
  -------
       17
       13
       11
       19
        7
        5
        3
        1
       23
        2
       29
  

  Note: If you don't want to create table then just replace table 'prime_number_example' in above query with SELECT query from dataset.