Smart contract code is stored on a distributed blockchain ledger, just like bitcoin transactions. Just as bitcoin transactions can't be changed once they are on the bitcoin blockchain, neither can smart contracts. They just sit there, ready to be called. Smart contracts are also like bitcoin accounts (or even bank accounts), as they can receive, store and pay out value.
Because smart contracts self execute, the cost of administering them is nearly zero. This opens up a whole range of new applications, by enabling lots of small-value micro-transactions.
Generally, a smart contract will work with things called "oracles," which provide information important to the contract. For example, a contract for crop insurance will need a data feed about rainfall. A football tipping competition is going to need information about who won each game. As soon as the information is received by the oracle, the contract pays out automatically. This automated execution is part of what makes smart contracts so powerful.
Smart contracts form the basis of "distributed applications" or "Dapps," which are basically smart contracts with a user interface.