SQL Databases Explained: From Basics to Modern Systems

In today’s world, data drives everything. From social media platforms to banking systems, databases form the backbone of modern applications. But what exactly is a database, and how do we manage one? Let’s break it down.

What is a Database?

A database is a collection of data stored in a structured format that can easily be accessed, managed, and updated. Think of it as a digital filing system — instead of sifting through papers, we use databases to store and retrieve information quickly and efficiently.

Database Management System (DBMS)

A Database Management System (DBMS) is a software application that allows us to control, organize, and manage databases within a project. It acts as the middle layer between users (or applications) and the data itself, ensuring security, consistency, and performance.

DBMS can be broadly classified into two main categories:

1. Relational Database Management Systems (RDBMS)
2. NoSQL / Non-Relational Database Management Systems

Relational Database Management Systems (RDBMS)

In RDBMS, data is stored in tables, and these tables are connected to each other through relationships. Each table represents a real-world entity — for example, a “Customers” table may store customer details, while an “Orders” table tracks purchase history.

The power of RDBMS lies in its use of SQL (Structured Query Language), which allows us to query, insert, update, and manage data with precision.

Some of the most widely used RDBMS include:

• MySQL
• Microsoft SQL Server
• Oracle Database

RDBMS are ideal for applications where data consistency and structured relationships are critical, such as banking, e-commerce, and enterprise systems.

NoSQL / Non-Relational Database Management Systems

Unlike relational systems, NoSQL databases don’t rely on traditional tables with rows and columns. Instead, they use flexible data models that can handle:

• Unstructured data (e.g., social media posts, logs)
• Semi-structured data (e.g., JSON, XML)
• Complex data formats (e.g., graphs, key-value pairs)

This flexibility makes NoSQL databases especially powerful for modern applications that need to scale rapidly and handle massive, diverse datasets.

Popular NoSQL databases include:

• MongoDB (document-oriented)
• Cassandra (wide-column store)
• Neo4j (graph database)

NoSQL is often used in big data, real-time analytics, and applications requiring high scalability like social networks and IoT systems.

we’ll walk through the fundamental SQL concepts, using examples from MySQL. By the end, you’ll have a clear understanding of how to retrieve, filter, and manipulate data. Let’s get started!

Setting the Context: The USE Statement

Before running queries, we usually specify which database to work with.

USE sql_store;

This tells MySQL that all queries should be run against the sql_store database.

A Simple Query: SELECT

The SELECT statement is the workhorse of SQL—it allows you to retrieve data from a table.

Example:

SELECT *
FROM customers
WHERE state = 'CA'
ORDER BY first_name
LIMIT 3;

Explanation:

• * selects all columns.
• WHERE filters rows (in this case, customers in California).
• ORDER BY sorts results by first name.
• LIMIT restricts the output to just 3 rows.

SQL is not case-sensitive (e.g., SELECT and select are the same). In MySQL, every statement ends with a semicolon (;).

Adding Notes: Comments in SQL

Just like in programming, comments make your queries easier to understand.

-- This is a comment and it won’t get executed

Comments are ignored by the SQL engine but help developers and collaborators.

Expressions in SELECT

SQL allows you to perform calculations inside your queries.

SELECT (points * 10 + 20) AS discount_factor
FROM customers;

Here, we calculate a new value called discount_factor using the points column.

Order of Operations

SQL follows the standard math precedence:

1. Parentheses
2. Multiplication / Division
3. Addition / Subtraction

Removing Duplicates: DISTINCT

To return only unique values:

SELECT DISTINCT state
FROM customers;

This will list each state only once, even if multiple customers live there.

Filtering Data: WHERE Clause

The WHERE clause filters records based on conditions.

Comparison Operators

• > Greater than
• >= Greater than or equal
• < Less than
• <= Less than or equal
• = Equal
• <> or != Not equal

Example:

SELECT *
FROM customers
WHERE points > 1000;

Logical Operators: AND, OR, NOT

Logical operators let us combine conditions.

• AND → Both conditions must be true

SELECT *
FROM customers
WHERE birthdate > '1990-01-01' AND points > 1000;

• OR → At least one condition must be true

SELECT *
FROM customers
WHERE birthdate > '1990-01-01' OR points > 1000;

• NOT → Negates a condition

SELECT *
FROM customers
WHERE NOT (birthdate > '1990-01-01');

The IN Operator

Instead of multiple OR conditions, IN makes queries cleaner:

SELECT *
FROM customers
WHERE state IN ('VA', 'NY', 'CA');

This selects customers in Virginia, New York, or California.

The BETWEEN Operator

To filter a range:

SELECT *
FROM customers
WHERE points BETWEEN 100 AND 200;

This includes both boundary values (100 and 200).

Pattern Matching with LIKE

LIKE is useful for finding text patterns.

SELECT *
FROM customers
WHERE first_name LIKE 'b%';

Explanation:

• % matches any number of characters.
• _ matches exactly one character.

Example:

• 'b%' → names starting with “b” (like Ben, Bella).
• '_a%' → names where the second character is “a”.

Advanced Pattern Matching with REGEXP

Regular expressions (REGEXP) provide more powerful text matching.

Get Waruni Gunasena’s stories in your inbox

Join Medium for free to get updates from this writer.

Remember me for faster sign in

Example:

SELECT *
FROM customers
WHERE first_name REGEXP '^a';

This returns names starting with “a”.

Useful Regex Patterns

• ^ → start of a string
• $ → end of a string
• | → logical OR
• [abc] → match any character in brackets
• [a-d] → match a range of characters

More Examples

• Ends with “ey” or “on”:

WHERE first_name REGEXP 'ey$|on$';

• Starts with “my” OR contains “se”:

WHERE first_name REGEXP '^my|se';

• Contains “b” followed by “r” or “u”:

WHERE first_name REGEXP 'b[ru]';

Handling Missing Data: IS NULL

In databases, sometimes values are missing or unknown. These are represented as NULL.

Example:

SELECT *
FROM customers
WHERE phone IS NULL;

This query retrieves all customers who don’t have a phone number stored.

Sorting Results: ORDER BY

By default, SQL returns rows in no particular order. The ORDER BY clause lets us sort results.

Example:

SELECT *
FROM customers
ORDER BY state, first_name DESC;

Explanation:

• First, sort customers by state in ascending order.
• Then, within each state, sort by first_name in descending order.

Limiting Results: LIMIT

Sometimes we don’t want all the rows. The LIMIT clause restricts how many rows are returned.

-- Return only 3 customers
SELECT *
FROM customers
LIMIT 3;

We can also skip rows and then fetch a set number of results:

-- Skip 6 customers and return 3
SELECT *
FROM customers
LIMIT 6, 3;

This is useful for pagination in applications.

Combining Data: Joins

One of SQL’s most powerful features is the ability to combine data from multiple tables.

Inner Joins

An inner join returns rows where there is a match in both tables.

SELECT *
FROM customers c
JOIN orders o
  ON c.customer_id = o.customer_id;

Only customers who have placed orders will appear in this result.

Outer Joins

Sometimes we want to see all customers — even if they don’t have any orders. That’s where outer joins come in.

SELECT *
FROM customers c
LEFT JOIN orders o
  ON c.customer_id = o.customer_id;

This ensures all customers are returned, even those without orders. The missing values from the orders table will be shown as NULL.

Using the USING Clause

If the column names in both tables are the same, we can simplify joins with USING:

SELECT *
FROM customers c
JOIN orders o
  USING (customer_id);

This makes the query cleaner and easier to read.

Cross Joins

A cross join pairs every row in one table with every row in another table.

SELECT *
FROM colors
CROSS JOIN sizes;

If there are 5 colors and 3 sizes, this will produce 15 combinations. Cross joins are often used for generating combinations (e.g., product variations).

Combining Results: UNION

The UNION operator merges results from multiple queries into a single result set.

SELECT name, address
FROM customers
UNION
SELECT name, address
FROM clients;

Key Notes:

• Both queries must have the same number of columns.
• Column data types should be compatible.
• By default, UNION removes duplicates. Use UNION ALL if you want to keep them.

Inserting Data: INSERT

We can add new rows to a table with the INSERT statement.

Insert a single record:

INSERT INTO customers(first_name, phone, points)
VALUES ('Mosh', NULL, DEFAULT);

Insert multiple records in one statement:

INSERT INTO customers(first_name, phone, points)
VALUES
  ('Mosh', NULL, DEFAULT),
  ('Bob', '1234', 10);

Using DEFAULT inserts the default value for that column (if one is defined).

Updating Data: UPDATE

The UPDATE statement modifies existing records.

UPDATE customers
SET points = points + 50
WHERE customer_id = 1;

This query increases the points of the customer with customer_id = 1 by 50.Always use a WHERE clause—otherwise, all rows will be updated.

Deleting Data: DELETE

The DELETE statement removes records from a table.

DELETE FROM customers
WHERE customer_id = 1;

This deletes the customer with customer_id = 1.Like with UPDATE, always be careful. Running DELETE FROM customers; without a WHERE will remove all rows in the table!