SCSI (small computer systems interface) is an I/O interface for storage devices and peripherals. Mainly HDs. However, you can also use SCSI for optical drives (including tape), and scanners (they don't make scsi scanners anymore AFAIK).
A SCSI controller as mentioned previously is needed to run these devices. The controller is what makes the SCSI drive superior to things like PATA, and even SATA. The controlers themselves can have processors and RAM on them, allowing the systems CPU(s) to free up any cycles maintaining and using the drives. The controllers also directly control the devices so they run in more optimal states. That is why SCSI is really only found in server side solutions these days. However, even a few of our servers at my work have SATA RAID in them, SCSI is still the way to go.
For example, we have many of the HP Proliant 380 G4 servers at my work. They run RAID 5 with SCSI drives, because its faster and more reliable.
To an end user, a SATA drive will suffice since they would not utilize the need to have something like an Ultra320 SCSI drive in their system. Just like most end users won't benefit from running a RAID 0 in their system.
It is also expensive, which is why SATA is more practical for the end user. I can only assume you want to play games, surf the net, and maybe burn some music or movies with your computer. For this a SATA drive is all you need.