OraQA

The following is an interesting puzzle posted by Jsoftware:

Determine all the times whose display on a 24-hour clock is a prime number.

—————————————–SQL Solution——————-

SELECT
CASE length(num)
          WHEN 3
          THEN substr(num,0,1)||':'||substr(num,2,2)
          ELSE substr(num,0,2)||':'||substr(num,3,2) END clock_24_hr
FROM
( WITH data AS (SELECT LEVEL+1 num FROM dual CONNECT BY LEVEL <=2400)
 SELECT num
 FROM data
 WHERE num <= 2400
 AND CASE WHEN (length(num) = 4
                                    AND to_number(substr(num,0,2)) BETWEEN 1 AND 24
                                    AND to_number(substr(num,3,2)) BETWEEN 0 AND 59 )  OR
                                 (length(num) = 3
                                 AND to_number(substr(num,2,2)) BETWEEN 0 AND 59)
                     THEN  1
          END  = 1
 MINUS
 SELECT d1.num * d2.num
 FROM data d1, data d2
 WHERE  d1.num <= d2.num
 AND d1.num <= sqrt(2400)
);
CLOCK
-----
1:01
1:03
1:07
1:09
1:13
1:27
1:31
1:37
1:39
1:49
1:51    

....
....
....
....  

23:11
23:33
23:39
23:41
23:47
23:51
23:57                                                                          

194 rows selected.

Filed under SQL. Follow any responses to this entry through this RSS 2.0 feed.