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.