Since the term 'Supercomputer' was first used in 1920 it's been the description of a moving target...  
As a starting place for this guide and to get the big picture on most any relatively common term, practice, place, etc. we routinely turn to www-Wikipedia-Org for an explanation.  Here's what they have to say: (See: en-wikipedia-org/wiki/Supercomputer copy-paste-&-correct)

What? (a Definition)

A supercomputer (<-- See related items and paraphernalia on eBay, [e.g for Cray, etc.]) is a computer that leads the world in terms of processing capacity, particularly speed of calculation, at the time of its introduction. The term "Super Computing" was first used by New York World newspaper in 1920 to refer to large custom-built tabulators IBM made for Columbia University.

A key aspect of the modern day definition of supercomputers is massively parallel processing, described on the Cray website (See: www-cray-com/about_cray/history.html) as follows:

In 1993, Cray Research offered its first massively parallel processing (MPP) system, the Cray T3D™ supercomputer, and quickly captured MPP market leadership from early MPP companies such as Thinking Machines and MasPar. The Cray T3D proved to be exceptionally robust, reliable, sharable and easy-to-administer, compared with competing MPP systems

Why? (What are they used for)

Supercomputers are used for highly calculation-intensive tasks such as problems involving quantum mechanical physics, weather forecasting, climate research (including research into global warming), molecular modeling (computing the structures and properties of chemical compounds, biological macromolecules, polymers, and crystals), physical simulations (such as simulation of airplanes in wind tunnels, simulation of the detonation of nuclear weapons, and research into nuclear fusion), cryptanalysis, and the like. Major universities, military agencies and scientific research laboratories are heavy users.

A particular class of problems, known as Grand Challenge problems, are problems whose full solution require semi-infinite computing resources.
 
On the topic of uses I personally recall having a Cray computer available when I was with Atlantic Richfield in the 1980's. It was used by our oil exploration folks to process massive amounts of seismic data that was being collected both domestically and internationally.  So we might way that there are also a number of 'ordinary' uses, if anything that requires trillions of calculations per second can be called 'ordinary'.

How? (How do Super Computers compare to 'regular' Computers)

A great place to start getting perspective on how computers compare, is back on the Wikipedia's Supercomputer page where they offer a history of the supercomputer with increasing speed shown in FLOPS (Floating point operations or calculations per second).  If we add to this some similar FLOPS ratings for 'regular' computers (Gathered from several Internet sites to compose the table below) we get a little clearer idea of the difference.  But it's only an idea since the entries themselves are individually suspect, and we're comparing two (2) moving targets.

Computer Processor                                                                                   Advertised BUT not necessarily tested processor rating
==================================================           =========================================
ENIAC (1947)                                                                                               500.00 - FLOPS
IBM PC/AT 80286 (1984)                                                                                  9.10 - KFLOPS
Apple Power PC (1994)                                                                                   10.00 - MFLOPS
Cray 1 (1966)                                                                                               250.00 - MFLOPS
Two (2) 16 processor Pentium Pro PCs (1997)                                                    1.00 - GFLOPS
Intel Itanium 2 at 1.5 GHz (completes 4 floating point operations per cycle)            6.00 - GFLOPS
Pentium III (2001)                                                                                              2.00 - GFLOPS
Cray 2 (1985)                                                                                                    3.90 - GFLOPS
AMD 64 or Intel Pentium 4 (CPU only performance)                                            10.00 - GFLOPS
Xbox360 (2005)                                                                                                 1.00 - TFLOPS
PlayStation 3                                                                                                    2.18 - TFLOPS
NVIDIA’s video card called the Quad SLI with
     two dual GPU GeForce 7950 GX2 (4 GeForce 7950 cards)                               6.00 - TFLOPS
IBM Blue Gene (2004)                                                                                      70.72 - TFLOPS
 

Sites referenced and used to compose this guide include (You might want to copy, paste and correct):

• www-geek-com/news/geeknews/2003Nov/bch20031104022519.htm
• en-wikipedia-org/wiki/Supercomputer
• home-earthlink-net/~mrob/pub/computer-history.html
• www-intel-com/performance/server/xeon/hpcapp.htm

.          .          .          .         .          .         .         .         .          .          .         .          .          .          .         .

If you found this guide interesting or useful would you vote [YES] below.  We're trying
 to build a reputation on eBay one-person-at-a-time, and we'd like your help to do it!  THANK YOU!