SQL, queries, and NoSQL databases

A database full of well-organized information is useful only if people and applications can retrieve what they need.

A question sent to a database is called a queryConcept · lights on your mapqueryA question sent to a database, a request to retrieve or change stored information. Engineers use queries to retrieve and change application data; analysts use them to investigate behavior and measure performance; product managers use them to answer questions about customers, features, and business results..

Relational databases are commonly queried using SQL.

What is SQL?

SQLConcept · lights on your mapSQLStructured Query Language, pronounced “sequel” or “S-Q-L.” The language used to query relational databases. Designed to resemble structured English, which is why reading one is far easier than writing one. stands for Structured Query Language.

You will hear it pronounced both “sequel” and “S-Q-L”, and both pronunciations are widely used.

SQL has existed for decades and remains one of the most important languages in software, analytics, finance, operations, and product work.

Engineers use SQL to retrieve and change application data. Analysts use it to investigate behavior and measure performance. Product managers may use it to answer questions about customers, features, and business results.

You do not need to become an expert SQL writer yet because the immediate goal is to reach SQL reading level: the ability to look at a query and understand what question it is asking.

SQL was designed to resemble structured English, so its basic commands are relatively approachable.

Reading a simple query

Consider this query:

You can read it as:

Give me the name and email columns from the customers table.

The result might resemble:

SQL does not normally return the entire table automatically. The query describes which information should be included in the result.

Selecting every column

An asterisk means “all columns”:

This can be read as:

Give me every column from every row in the orders table.

The asterisk is convenient when exploring a table, but production applications often request only the columns they need because retrieving unnecessary data can consume more processing, memory, and network capacity. Less data, less work.

Filtering rows with WHERE

Suppose the orders table stores money as cents:

This query retrieves orders worth more than $20:

You can read it as:

Give me every column from every row in orders whose total is greater than 2,000 cents.

WHERE filters the rows.

This connects directly to Module 3’s conditionals, since the database examines each relevant row and includes only those for which the condition is true.

Other conditions might look like:

• WHERE status = 'confirmed'
• WHERE customer_id = 88
• WHERE total_cents >= 1000

Text values are commonly surrounded by single quotation marks.

Combining conditions

SQL can combine several conditions.

For example:

This asks for orders that are both confirmed and worth more than $20.

A query could also use OR:

This asks for orders in either of those two states.

The connection to programming logic is direct:

• WHERE introduces a condition.
• AND requires both conditions.
• OR allows either condition.

The problem of information across tables

The relational model deliberately separates related facts into different tables.

The customers table might contain:

The orders table might contain:

The order rows contain customer IDs, but they do not repeat the customers’ email addresses.

What if we want a result containing both the order total and the customer’s email?

But the information lives in two different tables.

SQL solves this using a JOIN.

Reading a JOIN

Consider this query:

Read it one section at a time.

SELECT customers.email, orders.total_cents asks for the customer’s email and the order’s total.

FROM orders starts from the orders table.

JOIN customers connects matching information from the customers table.

ON orders.customer_id = customers.customer_id matches an order’s foreign key to the customer’s primary key.

WHERE orders.status = 'confirmed' keeps only confirmed orders.

Together, the query means:

Give me the customer email and total for every confirmed order.

The result might be:

You have now read one of the central operations in relational databases.

How a JOIN follows relationships

In the previous lesson, you learned that orders.customer_id refers to customers.customer_id.

The JOIN follows that relationship.

For order 1042, the database sees orders.customer_id = 88, then finds the customer row where customers.customer_id = 88 and combines information from the matching rows into the query result.

This does not permanently merge the tables, but creates a result for this particular question.

The type of JOIN matters

The plain JOIN in the example commonly means an inner join.

An inner join returns only rows that have a match on both sides, so if an order referred to no matching customer, that order would not appear in the result. No match, no row.

Other join types can preserve unmatched rows from one or both tables. You may later encounter names such as:

• LEFT JOIN
• RIGHT JOIN
• FULL JOIN

You do not need to master them yet.

For now, remember:

A JOIN combines related rows from multiple tables according to a matching condition.

Using shorter table names

SQL queries sometimes give tables temporary shorter names called aliases.

The previous query could be written as:

Here, o refers to orders and c refers to customers, and once you understand what each letter stands for, aliases can make long queries easier to read.

Queries that summarize data

SQL can also calculate summaries.

Suppose you want to know how many confirmed orders exist:

COUNT(*) counts the matching rows.

To calculate the total value of confirmed orders:

SUM adds the matching values.

Other common operations include:

• AVG for an average
• MIN for the smallest value
• MAX for the largest value

These operations are one reason SQL is important beyond application engineering. The same stored data can support analytics, reporting, finance, and product decisions.

Reads versus writes

Database operations are often divided into reads and writes.

A readConcept · lights on your mapreadAn operation that retrieves existing information without intentionally changing it. SELECT is the primary one. Reads observe state; writes create it, which is why reads are cheaper to scale. retrieves existing information without intentionally changing it.

SELECT is the primary SQL command used for reads:

A writeConcept · lights on your mapwriteAn operation that changes the database’s stored state: INSERT adds a row, UPDATE changes one, DELETE removes one. Unlike a read, it creates new authoritative truth, which is why it needs more coordination and care. changes the database’s stored state.

Common write commands include:

• INSERT
• UPDATE
• DELETE

These commands correspond roughly to creating, modifying, and removing records.

INSERT

INSERT adds a new row.

For example:

This asks the database to create a new order row.

Before accepting the write, the database may check:

• Whether the ID is unique
• Whether customer 88 exists
• Whether required values are present
• Whether each value matches the column’s data type
• Whether other constraints are satisfied

UPDATE

UPDATE changes existing rows.

For example:

This means:

Find order 1045 and change its status to preparing.

The WHERE clause is especially important because without an appropriate filter, an update could affect every row in the table.

For example:

would attempt to change the status of all orders.

This is one reason writes require care.

DELETE

DELETE removes rows:

This asks the database to remove order 1045, though many products do not permanently delete important business records immediately.

An application may instead update a status:

This preserves the historical record while indicating that the order is no longer active, and the product’s business and legal requirements help determine which approach is appropriate.

Why writes require coordination

Reads and writes have different consequences because a read observes stored information while a write changes the source of truth.

That means writes often require the system to coordinate questions such as:

• Is the new data valid?
• Is another write happening simultaneously?
• Should this operation be allowed?
• What happens if part of it fails?
• Which caches or copies must be updated?
• Should an audit history be preserved?

This does not mean that every write is slower than every read. A simple write may be faster than a complicated analytical query.

The deeper distinction is:

Reads observe state. Writes create new authoritative state.

Because writes change the truth, errors can have lasting consequences.

Can reads be cached?

Reads are often easier to cache because they do not intentionally change the database.

For example, a public list of popular drinks might be stored in an application cache rather than recalculated for every request.

However, not every read can be cached safely.

A cached result may be inappropriate when:

• The data changes frequently
• The user needs the newest value
• The response contains private information
• Different users have different permissions
• A recent write must appear immediately
• The query depends on rapidly changing state

A bank balance cannot always be treated like a public menu.

Caching remains a tradeoff between speed and freshness, governed by the needs of the product.

Read-heavy and write-heavy systems

Many consumer applications perform more reads than writes. A user might view a product page many times before placing one order, and thousands of people may read a public post that was written once.

This can lead to a read-heavy workload.

Other systems may be write-heavy. Sensors, analytics pipelines, financial markets, and logging systems can generate enormous numbers of new records.

The balance between reads and writes affects architectural choices such as:

• Caching
• Database replicas
• Indexes
• Data partitioning
• Queueing
• Storage design

You will encounter these concepts in later modules.

For now, recognize that teams make these choices by studying their actual traffic patterns rather than assuming every product behaves the same way.

What is NoSQL?

Relational databases are not the only type of database.

NoSQLConcept · lights on your mapNoSQL databaseA broad category covering databases that do not rely exclusively on the traditional relational table model; the name is often interpreted as “not only SQL.” It includes several different models: document, key-value, wide-column, and graph databases. The best-known kind is the document database, which stores JSON-like documents; MongoDB is a widely recognized example. is a broad category covering databases that do not rely exclusively on the traditional relational table model.

The name is often interpreted as:

“Not only SQL”

rather than literally “no SQL.”

NoSQL includes several different database models, such as:

• Document databases
• Key-value databases
• Wide-column databases
• Graph databases

These categories organize and retrieve data in different ways, and they should not be treated as one single design.

Document databases

One well-known type of NoSQL database is the document database, which stores records as document-like structures, often resembling JSON. The most widely recognized document database is MongoDB.

An order might be stored as:

The order and its items can be stored together in one nested document, which differs from the relational approach, where the information might be separated across orders, order_items, customers, and products tables. Everything travels together.

Flexible does not mean structureless

Document databases are often described as schema-flexible, meaning two documents in the same collection may contain somewhat different fields.

For example, one customer document might contain:

while another contains:

This flexibility can make it easier to evolve data shapes.

However, document databases are not automatically structureless. Applications still expect particular fields and types, and many document databases support schema validation.

Without discipline, flexible records can become inconsistent and difficult to understand. The absence of a rigid relational schema does not eliminate the need for a data model.

Relational versus document design

A relational database emphasizes:

• Tables
• Relationships
• Constraints
• Joins
• Consistent schemas

A document database emphasizes:

• Self-contained documents
• Nested structures
• Flexible fields
• Retrieving related information together

Neither model is automatically superior.

A relational database may be a strong choice when:

• Relationships between entities are important
• Consistency rules must be enforced
• Data needs to be queried in many combinations
• Transactions connect several records
• The structure is reasonably well understood

A document database may be useful when:

• Data naturally forms self-contained documents
• Related information is usually retrieved together
• Records have varying shapes
• The structure changes frequently
• The application benefits from nested data

The correct decision depends on the product’s needs and access patterns.

Relational databases can store JSON too

The boundary is not absolute.

Modern relational databases such as PostgreSQL can store JSON documents inside columns, while document databases can enforce validation and model relationships.

Database systems increasingly borrow ideas from one another.

The choice is therefore rarely:

Perfectly rigid tables or completely unstructured blobs.

Instead, teams ask:

• What relationships exist?
• Which rules must be enforced?
• How will the data be queried?
• Which information changes together?
• How consistent must the data remain?
• How quickly will the model evolve?
• What expertise does the team have?

These are data-modeling and system-design questions, not debates with one universal winner.

Postgres or MongoDB?

When engineers discuss:

“Postgres or MongoDB?”

they may be comparing:

• Relational tables versus documents
• Joins versus nested data
• Strong relational constraints versus schema flexibility
• Different querying and scaling patterns
• Existing team knowledge and infrastructure

The actual decision is usually more nuanced than “strict versus flexible,” but those are part of the tradeoff.

Seeing the complete data flow

Imagine that the frontend sends an authenticated request to retrieve Erik’s confirmed orders.

The backend might execute:

The database follows the relationship between the tables, filters the relevant rows, and returns the requested fields.

The backend then serializes the result into JSON:

The database speaks in tables and query results, while the API commonly sends the result as JSON.

SQL and JSON play different roles in the same flow.

The mental model to remember

A query is a request for a database to retrieve or change information.

SQL is the language commonly used to work with relational databases.

SELECT chooses columns.

FROM identifies a table.

WHERE filters rows.

A JOIN combines related rows from multiple tables using a matching condition.

INSERT, UPDATE, and DELETE change stored data.

A read observes existing state. A write changes authoritative state.

Reads can sometimes be cached, but only when the product can tolerate the corresponding freshness, privacy, and permission tradeoffs.

NoSQL is an umbrella category covering several non-relational database models.

A document database stores document-like structures that often resemble JSON.

MongoDB is a widely used document database.

You should now be able to read a simple SQL query, follow a join between primary and foreign keys, and understand the basic tradeoff between relational tables and document-oriented storage.