There are a few common varieties of pumpkins that are good for cooking, and another that is not.
As luck might have it, the most common pumpkin grown every summer and fall is the big, rugose Jack-o-Lantern pumpkin, which is great for making scary faces but a bit watery and fibrous for fine dining.
I know of two similar cooking pumpkins: the Long Island Cheese Pumpkin and the Long Pie Pumpkin. Both of these are currently rare in New England. Sometimes you see decorative white pumpkins around Halloween; I have never tried cooking one of those. When I do, you'll read about it here!
The Sugar Pumpkin is commonly available in the fall, and it is your best bet for all sorts of baking and cooking.