Week 2 - SQL Reflections

SQL has the flexibility to join tables on any column(s) using any predicate (=, >, < ).    Most of the time the join will use equality between a primary and foreign key.   Think of example where joining on something other than keys would be needed.  Write the query both as an English sentence and in SQL.  If you can't think of your own example, search the textbook or internet for an example.

Let's say we want to use a SQL query to count the number of attack moves that a Pokémon can learn based on its elemental type (like Fire, Water, etc.). We can join the Monster table (representing Pokémon) with the Moveset table (representing available moves/attacks that monster can learn) based on the matching elemental type, even though the move isn't linked by ID to the Pokémon or the move itself. This allows for Pokémon to learn moves outside of their elemental type (which might be tied to the ID) as well as the moves available to them via matching type which is independent of primary/foreign keys. 

select m.name, count(ms.attack_name) as fire_attack_count

from monster m

join moveset ms on m.type = ms.type

where ms.type = 'Fire'

group by m.name;

What is your opinion of SQL as a language?  Do you think it is easy to learn and use?  When translating from an English question to SQL, what kinds of questions do you find most challenging?

I have really enjoyed learning SQL so far, it functions somewhere between being a language and a search engine. It has some very clear rules and some less clear rules, somewhat like a tabletop game akin to Dungeons & Dragons or Warhammer 40K. Early learning of SQL came very naturally; there has been an understandable/predictable ramping up of complexity that takes more focus and revisiting. Complex queries can still be a bit murky; I still feel somewhat shaky on certain query executions. I left a couple of my SQL labs with a slightly different structure, using the same keywords/tools mentioned in the problem prompts/TA Zuhra's solutions, to check if these are valid in case I don't make it to office hours before the due date--a bit risky but the solution answers match so I feel somewhat confident that my submissions are okay. A concrete example of translating English to SQL that still doesn't come very naturally to me is using nested queries that rely on aliasing a temporary table. I have managed to reverse engineer solutions from the textbook but it's not something I can quickly do from scratch yet.