I'm finding it very difficult to write complex SQL queries involving joins across many (at least 3-4) tables and involving several nested conditions.  The queries I'm being asked to write are easily described by a few sentences, but can require a deceptive amount of code to complete.  I'm finding myself often using temporary views to write these queries, which seem like a bit of a crutch.  What tips can you provide that I can use to make these complex queries easier?  More specifically, how do I break these queries down into the steps I need to use to actually write the SQL code?

Note that I'm the SQL I'm being asked to write is part of homework assignments for a database course, so I don't want software that will do the work for me.  I want to actually understand the code I'm writing.

More technical details:


The database is hosted on a PostgreSQL server running on the local machine.
The database is very small: there are no more than seven tables and the largest table has less than about 50 rows.
The SQL queries are being passed unchanged to the server, via LibreOffice Base.


I'm basing most of this on just trying to get the "right" answer, so you may discover there are some performance issues. No point in speeding up an incorrect query.

Understand the table relationships - Most will be one to many. Know the "many" table. Identify the fields required for your joins.

Think about LEFT join scenarios - Select all the employees and their paycheck from last month. What if they didn't get a paycheck last month? 

Know the result set: 1) In a spreadsheet, manually enter at least one correct record for your query. 2) Write the query in a simple enough form to identify how many records should be returned. Use both of these to test your query to make sure joining a new table doesn't alter the result.

Break up your query into managable parts - You don't have to write it all at once. Complex queries can sometimes just be a collection of simple queries.

Beware of mixed levels of aggregation: If you have to put monthly, quarterly and year-to-date values in the same result set, you'll need to calculate them separately in queries grouped on different values.

Know when to UNION Sometimes it's easier to break up subgroups into their own select statements. If you have a table mixed with managers and other employees, and on each column you have to do Case statements based on membership in one of these groups, it may be easier to write a Manager query and union to an Employee query. Each one would contain their own logic. Having to include items from different tables in different rows is an obvious use.

Complex/Nested formulas - Try to consistently indent and don't be afraid to use multiple lines. "CASE WHEN CASE WHEN CASE WHEN" will drive you nuts. Take the time to think these through. Save the complex calcs for last. Get the correct records selected first. Then you attack complex formulas knowing you're working with the right values. Seeing the values used in the formulas will help you spot areas where you have to account for NULL values and where to handle the divide by zero error. 

Test often as you add new tables to make sure you're still getting the desired result set and knowing which join or clause is the culprit.
