SQL & PostgreSQL
Joins, window functions, transactions, and query optimization.
JSON vs JSONB, and views vs materialized views?
JSON stores JSON input text, while JSONB stores a processed representation that supports efficient operations and indexing. A view stores a query definition, whereas a materialized view stores the query's result for later reads. These are separate choices about data representation and precomputed results.
What are schemas, roles, sequences, functions and triggers?
PostgreSQL schemas organize database objects, and roles with grants control access to those objects. Sequences generate numeric values, functions package executable database logic, and triggers run configured logic in response to events. These features add organization and automation, but hidden trigger behavior can make writes harder to reason about.
What does an ORM do underneath?
An ORM translates object-oriented data operations into database commands and maps results back into application objects. It reduces repetitive persistence code, but the database still executes real SQL with real locks, indexes and query costs. Understanding the generated query is essential when debugging correctness or performance.
How can I practice joins and window functions?
A useful SQL exercise starts with a small dataset whose expected answer can be checked manually. Window functions then demonstrate ranking while keeping each employee's fields available. The example below returns all employees tied for the highest salary in each department.
Difficulty is an editorial estimate. Completion means you chose to mark a question as understood.