Two passwords for one account

Not really. It's essentially one password, with a press of the return key as one character.

It adds complexity to the log in process, which isn't generally a good thing (users would probably choose one good password, and one quick to type password). Don't forget @AviD's rule: "Security at the expense of usability, comes at the expense of security"

Depending on how the passwords were stored, they would slightly decrease the ability of attackers to brute force accounts, since an attacker would need to break both parts. I doubt that this balances out the usability issue though.

I've seen banking sites where a user is required to answer 2 security questions(randomly chosen out of a set of 5 pre-decided questions, at the time of account creation).

The point is, if one password can be compromised(either on user's front or because of website loopholes), how much likely is that the second password will remain safe? If the website can employ better encryption algorithm for one of the passwords, why not employ it for a single password?

I guess a better option is, the user can concatenate the 2 passwords to make a much stronger password. For example, setting 2 passwords "aBcD" and "eFgH" can be cracked within minutes(or hours), but a password like "aBcDeFgH" would require much more time to get broken from a hash.

A disadvantage not mentioned yet in the other answers, is that such a scheme will probably defeat some popular password managers.

Since you should be encouraging your users to use password managers, so that they can use long completely random sequences of characters as their password, this is probably a bad idea.