Let's say I simply upload a module like DBD::SQLite to a certain directory, use lib on that directory and try to use the module I've just uploaded. Will it work? Or would it require a "traditional" installation?

In this case the module requires a "traditional" installation, as DBD::Sqlite has XS and C code that needs to be compiled. If you could some how contrive to compile it on another box set up *exactly* the same was as the one you wish to transfer it to (same OS, same kernel version, same libs and so forth) you could theoretically transfer just the compiled files to the host, but that is unlikely.