Skip to main content

Print Prime Numbers in SQL

 

Problem

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

DECLARE i NUMBER(3); j NUMBER(3); BEGIN dbms_output.Put_line('The prime numbers are:'); dbms_output.new_line; i := 2; LOOP j := 2; LOOP EXIT WHEN( ( MOD(i, j) = 0 ) OR ( j = i ) ); j := j + 1; END LOOP; IF( j = i )THEN dbms_output.Put(i||' '); END IF; i := i + 1; exit WHEN i = 50; END LOOP; dbms_output.new_line; END; /



Enter your query here.
Please append a semicolon ";" at the end of the query and enter your query in a single line to avoid error.
*/
DECLARE @Output AS VARCHAR(MAX) = '';
WITH digit(d)
AS
(
SELECT 0 AS d
UNION ALL
SELECT d+1 AS d FROM digit WHERE d < 9
)
SELECT
@Output += CAST(a.Number AS VARCHAR(3)) + '&'
FROM (
SELECT a.d * 100 + b.d*10 + c.d + 1 AS Number FROM digit a
CROSS JOIN digit b
CROSS JOIN digit c
) a
LEFT JOIN (
SELECT a.d * 100 + b.d*10 + c.d + 1 AS Number FROM digit a
CROSS JOIN digit b
CROSS JOIN digit c
) b ON SQRT(a.Number) >= b.Number AND b.Number > 1
WHERE a.Number > 1
GROUP BY a.Number
HAVING ISNULL(SUM(CASE WHEN a.Number % b.Number = 0 THEN 1 ELSE 0 END),0) = 0
ORDER BY a.Number
PRINT SUBSTRING(@Output,1,LEN(@Output)-1)
;

Comments

Post a Comment

Popular posts from this blog

Select Names from table which have vowels

  Problem Query the list of  CITY  names from  table  which have vowels (i.e.,  a ,  e ,  i ,  o , and  u ) as both their first  and  last characters. Your result cannot contain duplicates. Input Format The  STATION  table is described as follows: Field Type ID NUMBER CITY VARCHAR2(21) STATE VARCHAR2(2) LAT_N NUMBER LONG_W NUMBER STATION where  LAT_N  is the northern latitude and  LONG_W  is the western longitude. MYSQL select distinct city from station where (city like 'a%' or city like 'e%' or city like 'i%' or city like 'o%' or city like 'u%' ) and ( city like '%a' or city like '%e' or city like '%i' or city like '%o' or city like '%u' )
1 comment
Read more

Methods of Rank Rows in SQL Server: ROW_NUMBER(), RANK(), DENSE_RANK() and NTILE()

SQL Server provides us with a number of window functions that help us to perform calculations across a set of rows, without the need to repeat the calls to the database. Unlike the standard aggregate functions, the window functions will not group the rows into a single output row, they will return a single aggregated value for each row, keeping the separate identities for those rows. The Window term here is not related to the Microsoft Windows operating system, it describes the set of rows that the function will process. One of the most useful types of window functions is Ranking Window Functions that are used to rank specific field values and categorize them according to the rank of each row, resulting in a single aggregated value for each participated row. There are four ranking window functions supported in SQL Server;  ROW_NUMBER(),   RANK() ,  DENSE_RANK()  and  NTILE() . All these functions are used to calculate ROWID for the provided rows window in ...
Post a Comment
Read more
Q- What is DBCC command in SQL sever. A - DBCC  (Database consistency checker ) are used to check the consistency of the databases. The DBCC commands are most useful for performance and trouble shooting exercises. I have listed down and explained all the DBCC commands available in SQL Server 2005, with examples. These are four types: Maintenance Informational Validation Miscellaneous                Maintenance Commands Performs maintenance tasks on a database, index, or file group. Syntax: DBCC  CLEANTABLE ( ‘DatabaseName’,’TableName.ColumnName’ ,0) 2.  DBREINDEX  – Builds one or more indexes for the table in the specified database. (Will be  removed  in the future version, use  ALTER INDEX  instead) Syntax: USE DatabaseName DBCC  DBREINDEX ( ‘TableName.ColumnName’,’PK_ID’ ,80) 3.  DROPCLEANBUFFERS  – Removes all clean buffers from buffer pool. Synta...
1 comment
Read more