1. Since PostgreSQL is built on top of SQL - I’m assuming that all querying can be done using SQL statements in addition to some of the stuff Postgres adds to seed/traverse the database via the terminal? Are there any weird things that Postgres adds to SQL?
PSQL is not built on top of SQL. PSQL uses its own flavor of SQL language to communicate with a database. PSQL is a DBMS and SQL is a querying language.
They work together to accomplish the goal of communicating with a database. PSQL has its own features that make it unique as a DBMS such as JSON storage support (No-SQL style). I would not say there is anything “weird” in PSQL, other than extra features that you might find handy.
2. Is it ever best practice (or even possible) to store actual JavaScript code into a table? If so, what are the use cases for that?
It is relativity easy to store code in a database. Code of any language always breaks down simply into a long string that you could hypothetically store in a field. It is highly recommended you DO NOT store code in a database unless you have the proper tools to stop security attacks such as someone executing the code on your server from the database. I personally have never worked with problems such as a platform like HackerRank that needs to execute code but my guess is they have a lot of precautions around saving other peoples code.
Looks like you are looking for the INTO clause. You can find more info here: https://www.w3schools.com/sql/sql_select_into.asp
4. When diagramming a join box, is that also considered an “entity”? Is it accurate to say that it doesn’t represent an actual table you’d make in your database, but you’d simulate it with a JOIN statement?
Good question! A join table is technically its own entity, just like when we did the figure skating example and we had the “Score” table. This table was a join table but actually represented a significant part of the application and as such would be called its own entity (The table representing the score of the skaters). A JOIN statement is not to be confused with a Join Table. A Join table is a physical table in your database where a JOIN statement is a SQL keyword for querying data. You can use a JOIN statement on a Join table to query data from that table.
5. Are there any simple rules of thumb you can keep in mind to help you realize when a join box should be diagrammed to represent the relationship between two other entities, instead of just using foreign keys in the existing entities?
Whenever diagramming a ERD, first come up with the relationships they need. Sometime I find it helpful to even write out a few rows in the database to see what I will be storing. If you have come to the conclusion that you are going to have TWO one-to-many relationships between these two entities, then you can consider adding a join table to connect them.