Paul Grenier (http://www.endusersharepoint.com/) has a great code snippet using jQuery that you can simply add to a content editor web part. The possibilities are truly endless. This can be used as an alternative to using CSS ( show and hide content ), navigation, or even Connected Web Parts. Check it out: JQuery for Everyone: Preview Pane for Multiple Lists along with JQuery for Everyone: Evolution of the Preview
Also, check out Toni Frankola's post: Deploy Custom JQuery Scripts to the SharePoint Site Collection and Chris Edwards post: Integrating jQuery for Use in Your WebParts and Application Layout Pages
Jan Tielens also has some great intros on integrating jQuery and SharePoint:
Integrating SharePoint 2007 and jQuery [Part One]
Integrating SharePoint 2007 and jQuery [Part Two]
Peter Serzo (via Joel Oleson's Blog) also gives the top 5 reasons to use jQuery.
Speaking of Jan Tielens, check out SmartTools for SharePoint
what is jQuery?