We stayed at the Sheraton Hacienda del Mar in Cabo a month ago for two nights in a floor level garden view room, but you could still see a partial view of the ocean from there.

Transportation: Definitely echo the advice to rent a car. The Sheraton is about a 30 min drive from SJD and if you reserve a car in advance, you get some pretty good deals. We rented a tiny little manual Ford Focus from Avis for $20/day. Fit four of us fine with a couple carry on luggages. A car gives you the flexibility to go to either town, Cabo San Lucas (15 minutes) or San Jose Del Cabo (20 minutes), the Hacienda is located in a stretch of land with all the resorts (corridor turistico) between the two and there isn't anything nearby that is walkable.

Activities: The Sheraton resort has some wonderful pools and private beach so we spent a lot of time lounging around right at the hotel. We even spotted a couple of whales out in the ocean right from the pool area. We did rent a couple of ATVs for a 4 hour canyon and beach tour in Migrino (about 40 min up towards Todo Santos). We signed up the night before in the town of Cabo San Lucas, just wander around and there are lots of booths setup around town offering all sorts of sports and activities. They are setup by the numerous timeshare properties down there to entice you to tour the property with a discounted activity in exchange, but you can negotiate a undiscounted price for an activity without a tour. We ended up negotiating around $90 for two of us to each getting our own ATV (plus we had to pay some entrance fees into the park once we got there). They will come and pick you up at the hotel in the morning, bring you out to Migrino, provide helmets, goggles and bottled water and lead you around for a couple of hours through the canyons, then set you free for about an hour to ride around the beach and hills on your own. It was a lot of fun, but be careful going over some of the hills and jumps, I fell off and scratched up my arm trying to go up a steep sand dune. There are medics around watching and will patch you up.

Dining: The restaurants at the Sheraton are not cheap, but they are convenient and serve pretty good food. The main restaurant, Pitahayas, serves fine dining fusion asian cuisine in a covered open air setting near the ocean with a live jazz band playing almost every night. The food was excellent and the atmosphere is definitely worth the price.
Cabo San Lucas has a lot of bars and restaurants catering to the spring break party crowd (Cannery Row) and others cater to a less rowdy bunch. We just walked around until we found one that fit with our pace. Street parking can be a pain in Cabo, but there's a parking lot near the supermarket that is pretty reasonable. It's supposed to be for customers only, but you can buy some water or something from the supermarket.

There's definitely a lot to do in and around Cabo, but even for just a weekend trip, we had a great time. Have fun!