Units 10 & 11
SQL is quite nice to work with as a language because of how close it is to natural English. This thought prompted me to read more about the theory behind it to better understand why it’s so effective, and this led me to discover the concept of declarative programming. Declarative programming is a form of programming that is built around the concept of a developer merely expressing their intentions code, while any complex logic involved in achieving that intention is left to the computer. A good example of this is the use of the WHERE clause in SQL. Rather than write loops with conditionals which would allow you to select specific entries, you would simply write WHERE, and SQL would decide how to approach this request by creating a query plan (a query plan can be thought of as the lower level instructions generated to execute a given query).
Although the goals and mechanisms simple to understand, in the advanced exercises, I have found that complex queries and operators (such as window functions) make this paradigm less noticeable. Expressing intentions clearly can quickly fall away once nesting, joins, or other complex operations are combined together. As an example, I (coincidentally) had to write complex PostgreSQL queries at work during the same time as the unit, in order to complete a specific task. My solution for one of the tasks was to first perform a nested query which needed a window function, thereafter I had to perform some casting along with some mathematical conversions, and finally, use an aggregate over a specific column. The final query was large, and while fairly expressive, I felt that the lower level operations I made (such as nesting and casting), took away from the declarative intentions of the language because I had to dictate more specifics in order for the problem to be solved. I do think, however, that this depends on one’s skill level to an extent. A simple example which I encountered during the assigment, was a scenario where there are two tables, and data needed to be collected from both tables, where specific columns match (e.g., some ID number). The approach that’s easiest to understand is a SELECT statement with a final clause
WHERE tableA.ID = tableB.ID. A JOIN could also be used to do this, but the intention of the query becomes much harder to understand and the similarity to natural English gets lost as well. I do think the query written for work could have been made clearer, but my lack of familiarity with window functions and available tools in PostgreSQL impacted this. I’d like to focus on understanding these factors better in order to keep the spirit of declarative programming.