As part of my daytime job, I conduct professional interviews for candidates. For that, I’m always on the search for good interview questions. I regard a good question if it’s not too complicated, has a number of ways to solve, and if needed, can be solved programmatically with SQL or C / C# / JAVA languages.
Now that I’m done with asking the prime number question (I need to change questions every now and then), I thought I will post the solution here.
The query below selects only the prime number from a table that has a sequence of integers (none can be missing, or the results won’t be correct).
|
1 2 3 4 5 6 7 8 9 10 |
select C1.Num from Numbers C1 where 1 not in ( select case when C1.Num % C2.Num <> 0 then 0 else 1 end from Numbers C2 where C2.Num < C1.Num and C2.Num > 1) and C1.Num > 0 and C1.Num <= 1000000 order by C1.Num ASC |
The query selects only the numbers that don’t have divisors (other than their selves and 1) in the subquery.
The input for the query (C1.Num) is the number of records for the select.
Do you have a better way of solving this? Have another great interview questions? Let me know in the comments below.
By the way, the query for MySql is a little different as % should be written using the function MOD:
|
1 2 3 4 5 6 7 8 9 10 |
select C1.Num from Numbers C1 where 1 not in ( select case when MOD(C1.Num,C2.Num) <> 0 then 0 else 1 end from Numbers C2 where C2.Num < C1.Num and C2.Num > 1) and C1.Num > 0 and C1.Num <= 1000000 order by C1.Num ASC |