It's good for people who either have no idea what scuba is about, or do but aren't sure if it's for them. It isn't a certification course, but as Elbert says it can (subject to conditions) count towards the full certification course.
It takes around 1/2 a day, and starts with a chat with the instructor. Then there's a video to watch (which will be a different video if you think you'll want to go on to a certification course), followed by introduction to the dive gear you'll be using. Then it's into a pool or shallow sea to go over the basic safety skills you need before you venture underwater. Once that's satisfactorily completed then as Elbert says you go out in a boat and do a shallow dive at a proper dive site, where there'll be lots to see to take your mind off what you're doing!
You have probably heard the term "Open Water Diver". In PADI terms that is their principal entry-level course, and in our convenient and warm water can be completed in three days. It's common for it to take longer in colder climes.
In case that's still too long for you, PADI offer a shorter course called "Scuba Diver" which can be completed in two days. It only contains two open water dives compared with the four of the Open Water course.
PADI is the largest recreational dive training agency by far but not the only one. Other agencies represented locally include NAUI and SDI. Beware that terminology varies across agencies, so that "Scuba Diver" represents a lower-level and restricted qualification under PADI but is the full standard qualification with some others.