Using SQLite to generate test data

I’m in the process of mentoring a junior developer at work. Every so often we’ll work through a typical interview question. They can be good exercises to expand your logical problem skills. As part of the questions I’ve using SQLite to generate the test data to use within each exercise. I’ve documented a couple here in-case you find them useful.

From the command terminal, start the sqlite client:

sqlite3

Then change the output mode to ascii so that the results are easier to drop into your language of choice:

sqlite>.mode ascii

The first SQL example shown below generates twenty random numbers from one to a hundred thousand. This sample data was used in an exercise where the developer has to write a function to find the lowest and highest values within an array.

    
WITH RECURSIVE
  testdata(x) AS (
    SELECT 1
     UNION ALL
    SELECT x+1 
      FROM testdata
     LIMIT 100000
  )
SELECT x, ','
  FROM testdata
 ORDER BY RANDOM()
 LIMIT 0, 20;

Sample output:


23716,67576,18197,1730,22247,20482,43830,
54837,34242,8293,16172,98392,47051,8040,
35063,78643,23620,68373,49534,15592

The second SQL example shown below generates all the numbers between one and one hundred. Yet it only outputs ninety nine numbers in a random order. This sample data was used in an exercise where the developer has to write a function to find the missing number in the array.

    
WITH RECURSIVE
  testdata(x) AS (
    SELECT 1
     UNION ALL
    SELECT x+1 
      FROM testdata
     LIMIT 100
  )
SELECT x, ','
  FROM testdata
 ORDER BY RANDOM()
 LIMIT 0, 99;

I hope you found this article useful, you can discuss it on Twitter
I create educational content for developers, hoping to inspire and teach with a deep understanding.