Populating a table with random people

It happened I had  to  fill a table with realistic but not real people data.Here how did I managed it (ported to MySQL sintax).

First lets create a people table like this :

CREATE TABLE PEOPLE(NAME VARCHAR(20),

                                     SURNAME VARCHAR(20),

                                     SEX VARCHAR(1),

                                     DOB DATE);

Then create tables with sample names and surnames:

CREATE TABLE NAMES(NAME VARCHAR(20), SEX VARCHAR(1));

CREATE TABLE SURNAMES(SURNAME VARCHAR(20));

And fill them with some values:

INSERT INTO NAMES(NAME,SEX) VALUES(‘Jhon’,‘M’);

INSERT INTO NAMES(NAME,SEX) VALUES(‘Jack’,‘M’);

INSERT INTO NAMES(NAME,SEX) VALUES(‘Jeff’,‘M’);

INSERT INTO NAMES(NAME,SEX) VALUES(‘Judy’,‘F’);

INSERT INTO NAMES(NAME,SEX) VALUES(‘Jade’,‘F’);

INSERT INTO NAMES(NAME,SEX) VALUES(‘Jane’,‘F’);

INSERT INTO SURNAMES(SURNAME) VALUES(‘Red’);

INSERT INTO SURNAMES(SURNAME) VALUES(‘White’);

INSERT INTO SURNAMES(SURNAME) VALUES(‘Green’);

Now we’ll be able to get a single random person with a query like this:

SELECT NAME, SEX,

           CURDATE() - INTERVAL RAND()*365.25*100 DAY AS DOB,

                 (SELECT SURNAME FROM SURNAMES ORDER BY RAND() LIMIT 1) AS SURNAME

FROM NAMES ORDER BY RAND() LIMIT 1;

We can write a simple procedure to populate the table with the desired number of rows:

CREATE PROCEDURE FILL(IN NUM INT)

   BEGIN

       WHILE NUM > 0 DO

          INSERT INTO PEOPLE(NAME,SEX,DOB,SURNAME)

          SELECT NAME, SEX,

                            CURDATE() - INTERVAL RAND()*365.25*100 DAY AS DOB,

                         (SELECT SURNAME FROM SURNAMES ORDER BY RAND() LIMIT 1) AS SURNAME

         FROM NAMES ORDER BY RAND() LIMIT 1;

         SET NUM = NUM-1;

      END WHILE;

END;

We can call this procedure like this:

mysql> call FILL(10000);

Query OK, 1 row affected (40.73 sec)

A ten thousand people population in forty seconds (on PIII at 550Mz) is not bad.

Of course many improvements can be done like adding a random place of birth (based on a table of places) or making date of birth distribution more realistic.

More about me …

  My name is Massimo, I live in Italy but my heart reside in the Philippines since 1997.(1)

  I bought my first computer when I was 15, since then I tasted the many flavors of computer programming from Basic to Pascal, C, C++, Matlab, Assembler, Java and many others.

  I actually work in Genoa where I design and develop public administration software for a local software house. I write software mainly using Java, Visual Basic and Oracle.

(1)
I owe my readers a little explanation about this phrase:  my wife is from Philippines, we married in 1997 and we went in the Philippines for our honeymoon. So I fell in love with my wife’s country too.

My Machines …

Sempron2400

(Windows, Ubuntu 9.04)

It’s actually my main desktop computer, I mainly use it for editing home videos and burning them on DVD.

PIII550

(Linux Ubuntu 8.04)

I bought this little old computer on a computer surplus fair at only 25€. I use it mostly for my “server side” linux experiments.

EEEPC900

(Linux EEEBuntu)

I bought it only recently, It’s the first really portable laptop I have. I use it for reading e-books, scratch-pad programming, watching videos and a little of gaming too.

Hello world!

Here I am, at last, my first post …

After wrestling with my own indecision about choosing the right style, pictures and colours. After writing copying a suitable disclaimer I have no more excuses for not to start writing my first post.

 I’m going to write, in this blog, about my personal programming experiments: I’m 40 but I’m still studing, and learning, everyday. Not surprising if you think how many frameworks, libraries and programming tools are published on the net nowdays.

 What else? Hello world!

 P.S.

If you are interested in posting java code bits in your blog have a look at Java2Html.

Disclaimer

The information in this weblog is provided “as is” with no warranties, and confers no rights. This weblog does not represent the thoughts, intentions, plans or strategies of my employer. It is solely my own personal opinion. Inappropriate comments will be deleted or edited at my discretion. All code samples (if any, ever) are provided “as is” without warranty of any kind, either express or implied, including but not limited to the implied warranties of merchantability and/or fitness for a particular purpose.