#!/usr/bin/perl
use warnings;
use strict;

use DBI;

my $db_file = '1.db';
unlink $db_file;

my $db = 'DBI'->connect("dbi:SQLite:dbname=$db_file", q(), q());

# Create the tables.
$db->do($_) for
    'CREATE TABLE user_tbl   (id INT, name   VARCHAR(5))',
    'CREATE TABLE topic_tbl  (id INT, topic  VARCHAR(6))',
    'CREATE TABLE detail_tbl (id INT, detail VARCHAR(7), name_id INT, 
+topic_id INT)';

# Populate the tables.
for my $insert (
                [ 'user_tbl',   [ 1, 'test1' ],  [ 2, 'test2' ] ],
                [ 'topic_tbl',  [ 1, 'topic1' ], [ 2, 'topic2' ] ],
                [ 'detail_tbl', [ 1, 'detail1', 1, 1 ],
                                [ 2, 'detail2', 1, 2 ],
                                [ 3, 'detail3', 2, 1 ] ],
               ) {
    my $table = shift @$insert;
    my $sql = "INSERT INTO $table VALUES ("
            . join(', ', map '?', 0 .. $#{ $insert->[0] }) . ')';
    my $sth = $db->prepare($sql);
    $sth->execute(@$_) for @$insert;
}

# Extract the information.
my $query = << '__SQL__';
    SELECT d.detail, u.name, t.topic
    FROM detail_tbl AS d
    JOIN user_tbl   AS u ON u.id = d.name_id
    JOIN topic_tbl  AS t ON t.id = d.topic_id
__SQL__

my $sth = $db->prepare($query);
$sth->execute;
while (my @row = $sth->fetchrow_array) {
    print "@row\n";
}
[download]

Update: if your SQLite was compiled with foreign keys support (see here), you can turn the support on and constrain your tables to avoid invalid values:

$db->do($_) for
    'PRAGMA FOREIGN_KEYS = ON',
    'CREATE TABLE user_tbl   (id INTEGER PRIMARY KEY, name  VARCHAR(5)
+)',
    'CREATE TABLE topic_tbl  (id INTEGER PRIMARY KEY, topic VARCHAR(6)
+)',
    join ' ', 'CREATE TABLE detail_tbl (id INTEGER PRIMARY KEY, detail
+ VARCHAR(7),',
              'name_id INTEGER, topic_id INTEGER,',
              'FOREIGN KEY (name_id) REFERENCES user_tbl(id),',
              'FOREIGN KEY (topic_id) REFERENCES topic_tbl(id))';
[download]

Is your question about the Perl code you should use or about the SQL that you should use?

For the Perl code, DBI has about everything that you would need. For the SQL, I recommend practicing with Querylet or DBIx::RunSQL or dbish (or the sqlite3 tool) to see the results you get for the various queries.

From your description, I guess that you want to return all users and the respective topics they are interested in. The SQL concept to look up in your course material would be the "JOIN" keyword.

One possible SQL statement is:

SELECT detail_tbl.detail, user_tbl.name, topic_tbl.topic
FROM detail_tbl, user_tbl, topic_tbl
WHERE detail_tbl.name_id = user_tbl.id
AND detail_tbl.topic_id = topic_tbl.id
[download]

There are some modules that make handling foreign key fields somewhat easier. You may wish to have a look at DBIx::Class and more specifically DBIx::Class::Relationship that handles many-to-many relationships (which is what you are trying to do).

Thanks to all for replies. This is a great start.

Not directed to your specific question, but PM maintains a library of tutorials. GrandFather's contribution -- Databases made easy -- is oriented to your choice (and IMO a good entry choice, erix observation notwithstanding, unless -- as that observation seems to assume -- you're headed for heavy use of advanced DB tech. YMMV re a definition of 'heavy' but a

More generally, you'll be well served to make use of Super Search or even Google using site:perlmonks.com] to search only at this site. A recent big-G search with 'site:perlmonks.com SQLite' as the only terms found numerous nodes, including one in which the author notes, "I've been testing DBD::SQLite with 100_000 records...." which, in my miles, is somewhat beyond either trivial or a Christmas card list.)