Can Mathematica be used for developing "normal" stand-alone software? I understand "normal" is not a very good qualifier. What I mean by it is software that people usually develop in Java and C++ so it can be "installed" on computers and be launched by double clicking etc. I hope this makes sense. If there is, could you list some non-trivial examples besides anything made by Wolfram in-house such as Wolfram Alpha?
Yes Mathematica executables can be distributed as CDF documents either directly or the Wolfram Kernel can be accessed programmatically in multiple ways via the Cloud and the Internet. The Wolfram kernel that is installed by both CDF player and Mathematica can be thought of (crudely) as the Virtual Machine the executable needs to access to run - so its just like Java or .Net in this respect, more so when you consider there is also a browser plug in.
To take the analogy further - Mathematica is the IDE (integrated development environment) where you write & test your code and CDF player is the run-time.
For lots of non-trivial examples see:
My favourite is the Radial Engine.
People who are serious about writing Mathematica programs mighttake things one step further and use Wolfram Workbench (which is just the Eclipse IDE modified for Wolfram Language code development), together with a Version Control System (VCS) like Git. Infact my company has just spent some time working with a Wolfram consultant to produce a CDF Application who used exactly this set up.
A seriously heavyweight example with probably man years of Wolfram Language code in it:
A commercial Smart Meter analytics Application competing with solutions from major vendors such as SAP.
one three more for the road
As requested a video player in Mathematica - enjoy ;)
3D turn based strategy.
3D puzzler/fps/God sim Mathematica Minecraft
One more for the road