GeoServer has always been good at simply publishing database tables. But anything more complex (e.g. pre-filtering data in a table, joining two tables together, or generating values on the fly) could be painful. With Geoserver 2.1 one can finally create a layer directly from an SQL query.
Even dynamic queries are possible, e.g.
select gid, state_name, the_geom from pgstates where persons between %low% and %high%
To select for example all states with 2 to 5 millions inhabitants, the following parameters can be added to the normal GetMap request:
&viewparams=low:2000000;high:5000000
Find more information on SQL layers in Geoserver 2.1 documentation.