Full Stack Web Development Databases & Data Persistence Syllabus

What does the GROUP BY clause do?

It groups rows that share the same values in specified columns so aggregate functions can be applied to each group.

What are the three types of relationships in relational data modeling?

One-to-one, one-to-many, and many-to-many.

How is a many-to-many relationship implemented in a relational database?

Using a junction (join/bridge) table that holds foreign keys referencing the primary keys of both related tables.

What is a SQL JOIN?

An operation that combines rows from two or more tables based on a related column between them, usually matching foreign keys to primary keys.

What is the difference between an INNER JOIN and a LEFT JOIN?

INNER JOIN returns only rows with matches in both tables; LEFT JOIN returns all rows from the left table plus matching rows from the right (NULLs where no match).

What does a FULL OUTER JOIN return?

All rows from both tables, with matching rows combined and NULLs filled in where there is no match on either side.

What is a self join?

A join in which a table is joined to itself, using table aliases, to compare rows within the same table (e.g., employees to their managers).

What is a subquery in SQL?

A query nested inside another query (in SELECT, FROM, WHERE, etc.) whose result is used by the outer query.

What is the difference between UNION and UNION ALL?

UNION combines result sets and removes duplicate rows; UNION ALL combines them and keeps all duplicates, making it faster.

What is an index in a database and why is it used?

A data structure (often a B-tree) that stores a sorted reference to column values to speed up data retrieval, at the cost of extra storage and slower writes.

What is a transaction in SQL?

A sequence of one or more operations treated as a single atomic unit that either fully commits or fully rolls back, preserving data integrity.

What are window functions used for in advanced SQL?

They perform calculations across a set of rows related to the current row (using OVER) without collapsing rows, enabling running totals, rankings, and moving averages.

Which Node.js library is commonly used to connect to PostgreSQL?

The 'pg' (node-postgres) package.

Which Node.js package is commonly used to connect to MySQL?

mysql2 (or the older mysql package).

What is a connection pool and why use it when connecting Node to a SQL database?

A cache of reusable database connections that reduces the overhead of opening/closing connections per request, improving performance and handling concurrency.

What is a parameterized (prepared) query and why is it important?

A query where values are passed separately from the SQL text using placeholders; it prevents SQL injection and improves performance through query plan reuse.

What is MongoDB?

A document-oriented NoSQL database that stores data as flexible, JSON-like BSON documents organized into collections, with dynamic schemas.

What are the MongoDB equivalents of a SQL table, row, and column?

Collection (table), document (row), and field (column).

What is BSON in MongoDB?

Binary JSON — the binary-encoded serialization format MongoDB uses to store documents, supporting extra types like Date, ObjectId, and binary data.

What is the _id field in a MongoDB document?

The unique primary key of a document; if not provided, MongoDB auto-generates a 12-byte ObjectId for it.

Which MongoDB methods perform CRUD operations?

Create: insertOne/insertMany; Read: find/findOne; Update: updateOne/updateMany/replaceOne; Delete: deleteOne/deleteMany.

How do you find all documents in a MongoDB collection where status equals 'active'?

db.collection.find({ status: "active" })

What is the MongoDB aggregation pipeline?

A framework that processes documents through a sequence of stages (e.g., $match, $group, $sort, $project), each transforming the data and passing results to the next stage.

What do the $match and $group aggregation stages do?

$match filters documents (like WHERE); $group aggregates documents by a specified key and computes accumulated values (like GROUP BY with aggregates).

Why are indexes important in MongoDB?

They allow MongoDB to locate documents efficiently without scanning the whole collection; without them queries perform a full collection scan (COLLSCAN).

What is Mongoose?

An Object Data Modeling (ODM) library for MongoDB and Node.js that provides schema definitions, validation, type casting, middleware, and model-based queries.

What is a Mongoose schema versus a Mongoose model?

A schema defines the structure, fields, types, and validation of documents; a model is a constructor compiled from a schema used to create and query documents in a collection.

What is an ORM, and name two popular SQL ORMs for Node.js.

An Object-Relational Mapper maps database tables to programming objects, letting you query using code instead of raw SQL. Popular Node.js examples: Sequelize and Prisma (also TypeORM).

What is one key advantage and one disadvantage of using an ORM?

Advantage: faster development with cleaner, database-agnostic, injection-safe code. Disadvantage: performance overhead and less control over complex/optimized queries.

How are databases typically connected to APIs in a backend application?

The API server uses a database driver or ORM/ODM to open a (pooled) connection, then route handlers execute queries within request handling and return the results as JSON responses, with credentials kept in environment variables.