I've flown into SLC Airport 3 times in the winter to ski at the resorts in Park City and once for Deer Valley and have rented a car each time. I've never had a problem with driving due to the snow.
Would the rental cars typically offer winter tyres? - The rental cars
I have rented have all come with basic all-season tires.
Is the snow cleared often? - Yes, on the roads that you will be taking
as a tourist between your airport, your hotel, the ski resorts and main tourism areas.
Do I need an AWD/4WD to reach ski resorts e.g. Deer Valley, Park
City? - No, but AWD/4WD would make driving safer if it is snowing during
your drive.