1- what data is cached by the 'page cache' engine? does it include sql queries, css, js, html files ?
2- currently, when we enable page caching, the CSS and JS files get messed up and not working giving a bizarre front for the page, how can I enable caching the sql queries / results without touching the CSS /JS files?
3- is there any way to manipulate sql caching engine to capture certain queries for certain modules for longer periods than others ?

1. page html without header and footer is cached

2. that's correct because css and js files aren't part of page as specified in point 1, but added to head section of the page which isn't cache, so it's possible to cache only pages which don't use custom css/js files.

3. usually sql/db cache is stored indefinitely and refreshes upon meeting some conditions, for example changing site domain or site revision number.