What Is SQL? Understanding Its Purpose, Origins, and How to Use It

Most modern applications, from simple blogs to massive social networks store and retrieve data using a language called SQL.

In this article, you’ll learn:

• What SQL is and why it was created
• How relational databases organize information
• A concrete example with two tables
• How to run real SQL queries, including JOINs
• Why SQL is still the standard after 50+ years

Let’s dive in.

What Is SQL?

SQL (Structured Query Language) is a declarative programming language created for managing relational databases, databases built around tables, rows, and relationships between them.

SQL allows you to:

• Create tables
• Insert and modify data
• Query data using filters
• Join related data from multiple tables
• Enforce constraints like unique emails or foreign keys

SQL is used in many popular relational database systems. Here are some examples:

• PostgreSQL: advanced open-source relational database with strong SQL standards support.
• MySQL: widely used in web applications, known for speed and simplicity.
• MariaDB: community-driven fork of MySQL with enhanced performance features.
• SQLite: a lightweight, file-based database used in mobile apps, browsers, and embedded systems.
• Microsoft SQL Server: enterprise-level commercial database with strong tooling on Windows and cloud environments.
• Fully managed cloud SQL services such as:
  • Amazon RDS
  • Google Cloud SQL
  • PlanetScale
    These platforms (Amazon RDS, Google CLoud SQL, PlanetScale) handle backups, scaling, updates, and maintenance while still giving you the power of SQL.

Why SQL Was Created

In the 1970s at IBM, researchers Edgar F. Codd and Donald Chamberlin were working on a better way to store and retrieve data. At the time, databases were hierarchical or network-based, and interacting with them was:

• Complex
• Error-prone
• Tightly coupled to physical storage
• Hard to query flexibly

To solve this, they introduced the relational model, which organizes data into tables with well-defined relationships.

SQL was created because:

1. Developers needed a simple, readable language to query data.
2. Businesses needed consistent rules for storing relationships.
3. Data needed to be accessed without knowing internal storage details.

The result?
SQL became the standard in 1986 (ANSI SQL), and nearly every major database today is still built around it.

Understanding Tables, Columns, and Relationships

In a relational database:

• A table is like a spreadsheet (rows + columns)
• A row represents a single record
• A column represents a property
• A primary key uniquely identifies each row
• A foreign key links one table to another

Let’s look at a simple example involving users and orders.

Example Schema: Users and Orders

Table 1: users

Table 2: orders

Relationship

• Each order belongs to a user (orders.user_id → users.id).
• A user can have multiple orders (one-to-many relationship).

Creating the Tables (SQL Example)

CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  name TEXT NOT NULL,
  email TEXT UNIQUE NOT NULL
);

CREATE TABLE orders (
  id SERIAL PRIMARY KEY,
  user_id INTEGER NOT NULL REFERENCES users(id),
  product TEXT NOT NULL,
  price NUMERIC(10,2) NOT NULL
);

This creates two strongly-typed relational tables with a foreign key constraint. But what "strongly-typed" means? In SQL databases like PostgreSQL and MySQL, each column has a strict data type:

• Strings must be strings
• Numbers must be numbers
• Dates must be dates
• Wrong types cause errors

This prevents mistakes such as:

• Saving "twenty dollars" into a numeric column
• Saving 123 into an email field
• Saving invalid dates

SQLite is different: it is type-flexible (“dynamic typing”), meaning it enforces types less strictly. PostgreSQL is the most strongly typed of the major databases.

Table Creation Explained Line by Line

Table 1: users

CREATE TABLE users (

Explanation: This starts the creation of a new table named users.

• Works in: PostgreSQL, MySQL, MariaDB, SQLite, SQL Server
• “Table" = structured set of rows and columns in the database.

  id SERIAL PRIMARY KEY,

Explanation:

• id → the column name.

• SERIAL → auto-incrementing integer. PostgreSQL-specific.

  • Equivalent in other engines:

    • MySQL: INT AUTO_INCREMENT
    • SQLite: INTEGER PRIMARY KEY AUTOINCREMENT

• PRIMARY KEY → ensures uniqueness and identifies each row.

  name TEXT NOT NULL,

Explanation:

• name → column to store text.
• TEXT → variable-length text (string).
• NOT NULL → cannot be empty.

Works in: PostgreSQL, MySQL, MariaDB, SQLite (but SQL Server uses VARCHAR(MAX) instead of TEXT).

  email TEXT UNIQUE NOT NULL

Explanation:

• email → stores the user’s email address.
• TEXT → text type.
• UNIQUE → prevents duplicate emails.
• NOT NULL → required field.

Works in: PostgreSQL, MySQL, MariaDB, SQLite (noting differences in TEXT type internals).

);

Explanation: Closes the table definition.

Table 2: orders

CREATE TABLE orders (

Starts a new table named orders. Works in all major engines.

  id SERIAL PRIMARY KEY,

Same meaning as in users table:

• Auto-incrementing primary key
• PostgreSQL-specific type (SERIAL)
• MySQL uses INT AUTO_INCREMENT
• SQLite uses INTEGER PRIMARY KEY AUTOINCREMENT

  user_id INTEGER NOT NULL REFERENCES users(id),

Explanation:

• user_id → a number linking the order to a row in users.
• INTEGER → whole number.
• NOT NULL → every order must belong to a user.
• REFERENCES users(id) → foreign key constraint.

Foreign key meaning:

• Ensures user_id must match a real id in the users table.
• If a user is deleted, the database will enforce the relationship according to foreign key rules.

Engine compatibility:

• PostgreSQL: Fully supported.
• MySQL/MariaDB: Works, but requires storage engine = InnoDB.
• SQLite: Supported only when foreign key support is enabled (PRAGMA foreign_keys = ON;).

  product TEXT NOT NULL,

Stores the name of the purchased product.

• TEXT type → works differently across engines but always stores strings.
• Required (NOT NULL).

  price NUMERIC(10,2) NOT NULL

Explanation:

• price → the product’s cost.

• NUMERIC(10,2) → fixed precision number with:

  • up to 10 digits total
  • 2 digits after the decimal

This is ideal for money because it avoids floating-point errors.

Engine compatibility:

• PostgreSQL: Perfect support.
• MySQL: DECIMAL(10,2) is equivalent.
• SQLite: Accepts it but stores internally as TEXT, INT, or REAL (dynamic typing).

);

Ends the table.

Inserting the Example Data

Now that our tables are created, let’s insert the sample data we showed earlier. We will not manually provide any id values, the database will generate them automatically using the SERIAL (auto-increment) type.

Inserting the users Data

INSERT INTO users (name, email)
VALUES
  ('Alice', 'alice@example.com'),
  ('Bob', 'bob@example.com'),
  ('Caroline', 'caroline@example.com');

Insert Into users Explained Line by Line

INSERT INTO users (name, email)

• INSERT INTO users We are adding new rows to the users table.

• (name, email) We only provide name and email, not id.

  Why not provide the ID? Because the id column uses a serial/auto-incrementing type (PostgreSQL’s SERIAL), the database will create IDs automatically:

• First user → id = 1

• Second user → id = 2

• Third user → id = 3

This is predictable across PostgreSQL, MySQL, MariaDB, and SQLite (using their respective auto-increment mechanisms).

VALUES
  ('Alice', 'alice@example.com'),
  ('Bob', 'bob@example.com'),
  ('Caroline', 'caroline@example.com');

• VALUES introduces a list of rows to insert.
• Each ( ... ) group corresponds to one new row.
• The database will assign IDs automatically in the order these rows appear.

Resulting users table after insertion:

Even though we didn’t write any IDs manually, the auto-increment mechanism ensures this ordering.

Inserting the orders Data

To insert orders, we do need to provide user_id, because an order must belong to a specific user.

Even though we did not manually set the user IDs, we still know what they are:

• Alice → ID 1
• Bob → ID 2
• Caroline → ID 3

These were generated automatically in the order of insertion, and auto-increment always assigns IDs sequentially unless rows are deleted.

Here is the insert:

INSERT INTO orders (user_id, product, price)
VALUES
  (1, 'Coffee Mug', 12.99),
  (1, 'Hoodie', 39.99),
  (2, 'Stickers', 4.99);

Insert Into orders Explained Line by Line

INSERT INTO orders (user_id, product, price)

• We are inserting new rows into the orders table.

• We specify:

  • user_id → which user the order belongs to
  • product → name
  • price → cost

Notice again: we do not provide id (orders.id), because it auto-increments.

VALUES
  (1, 'Coffee Mug', 12.99),
  (1, 'Hoodie', 39.99),
  (2, 'Stickers', 4.99);

• (1, 'Coffee Mug', 12.99)

  • This order belongs to user 1 (Alice).

• (1, 'Hoodie', 39.99)

  • Another order for Alice.

• (2, 'Stickers', 4.99)

  • Order for user 2 (Bob).

  How do we know which user has which ID? Because:

1. The users were inserted first.

2. Auto-increment IDs always assign sequential integers.

3. Therefore:

   • First inserted user → ID 1
   • Second inserted user → ID 2
   • Third inserted user → ID 3

This makes referencing users by ID completely predictable.

Final Resulting Tables

users

orders

Querying Data with SQL

Here are SQL queries we can run on the two tables we defined earlier.

1. Select all users

SELECT * FROM users;

Result:

2. Select users with a filter

SELECT * FROM users
WHERE email LIKE '%alice@example.com';

What this means:

• LIKE is a SQL operator used to compare text using patterns.
• % is a wildcard that matches any number of characters, including zero characters.

So:

• '%alice@example.com' means: “Match any text that ends with alice@example.com.”

This will return the row where the user’s email is exactly:

alice@example.com

but it would also match values like:

• test-alice@example.com
• hello.alice@example.com
• anythingalice@example.com

if they existed.

Result:

This query returns only the user whose email matches the filter.

3. Insert a new order

INSERT INTO orders (user_id, product, price)
VALUES (2, 'T-Shirt', 29.99);

Resulting orders table after insertion:

• The new row has id = 4, automatically generated.
• user_id = 2 links this order to Bob.

JOINs: A Powerful Part of SQL

JOINs let you query related data across multiple tables.

Let’s explore them using our users and orders tables.

Example 1: INNER JOIN (most common)

Get every order with the user who made it.

SELECT users.name, orders.product, orders.price
FROM orders
INNER JOIN users
  ON orders.user_id = users.id;

Result :

Bob now appears twice, because of the new order inserted earlier:

INSERT INTO orders (user_id, product, price)
VALUES (2, 'T-Shirt', 29.99);

Example 2: LEFT JOIN

Get all users, even those with no orders.

SELECT users.name, orders.product
FROM users
LEFT JOIN orders
  ON users.id = orders.user_id;

Result :

Caroline appears with null because she has no orders.

Example 3: Aggregate query

Total money spent by each user:

SELECT users.name, SUM(orders.price) AS total_spent
FROM users
LEFT JOIN orders ON users.id = orders.user_id
GROUP BY users.name;

Result :

Explanation

• Alice: 12.99 + 39.99 = 52.98
• Bob: 4.99 + 29.99 = 34.98
• Caroline: no orders → NULL

Where to Learn More

• PostgreSQL Documentation
• MySQL Reference Manual
• SQL Tutorial – W3Schools
• SQLite Documentation

Final Thoughts

SQL has been around for more than 50 years, yet it still powers the world’s most important applications, because it’s:

• Declarative
• Consistent
• Predictable
• Scalable
• Backed by decades of research

Once you understand how tables and joins work, SQL becomes one of the most powerful tools in your programming toolbox.