What is the order of page load in Drupal?
With regard to error pages, Drupal has its defaults, but you can create your own just like any other node.  Just create a page for the error, then go to yoursitename.com/admin/settings/error-reporting and specify it as the page to display. You can do this for 403 and 404 errors.

The error pages are loaded just like any other. Which theme files are loaded depends highly on the theme. In Drupal 6, page.tpl.php is the top-level template file in the theme, and would be used for error pages just like any other page. However, it is quite possible to have other files that only theme certain portions of the page.

I would highly recommend a slow, methodical reading of Drupal's theming guide for an introduction. This will help you understand how Drupal decides which .tpl.php files (as well as CSS, JS, etc.) to use for a given page request.

Also, note that a lot of the details have changed significantly between Drupal 6 and Drupal 7. If you're doing new development, you'd probably be better off focusing on version 7&mdash;but of course if you have older sites to maintain, you'll need to look at the relevant documentation.