Introduction to Databases and SQL

What is a database?

Before databases were widely used, applications often stored information directly in files. Imagine a telephone directory kept in a file named telephone_directory.csv:

For a small amount of information, a file works perfectly well: a program opens it, reads the contents, and searches for the record it needs. As the data grows and more applications and users work with it, several problems appear.

Why plain files struggle as data grows

Two problems, made concrete

Duplication drifts into conflict

Storing a row per phone number repeats Asha’s name and address. If she moves and only one row is updated, the file now disagrees with itself:

A relational database stores the person once and connects several phone numbers to that single record.

Files enforce no rules

Suppose every person must have a unique id, a name, a valid phone number, and a city. A plain file happily accepts none of that:

The id 1 is duplicated, unknown is not a phone number, Ben’s city is missing, and the third record has no name. A database’s constraints can require values, forbid duplicate identifiers, and restrict data types.

What a database provides

A database is an organised collection of related information. The software that stores, retrieves, updates, and protects it is a Database Management System, or DBMS: for example PostgreSQL, MySQL, Microsoft SQL Server, Oracle Database, or SQLite.

A DBMS helps applications store structured information, search large volumes quickly, update records safely, support many concurrent users, reduce duplication, enforce data-quality rules, maintain relationships, control access, and recover from failure.

Information organised into related tables

In the relational model, information lives in tables. Each table represents one kind of thing, holding rows (individual records) and columns (their attributes). An orders table might look like this:

Product and customer details do not need to be repeated inside every order. They live in their own tables, connected by shared identifiers.

Order 1001 stores product_id = 101 and customer_id = 1. Looking those up tells us Asha ordered a Wireless Mouse, without repeating her details or the product’s in every order row.

How the tables relate: keys

The three tables describe one business process, Customers → Orders → Products, linked through shared identifying columns called keys.

Thinking in rows and columns

Most questions become questions about tables, rows, columns, and relationships:

• Which orders have been paid? Filter orders by status.
• Which products cost more than 2,000? Filter products by price.
• Which products has Asha ordered? Combine customers, orders, and products.
• How much revenue did each product generate? Combine orders and products, compute an amount, and group by product.

What is SQL?

SQL (pronounced “S-Q-L” or “sequel”) stands for Structured Query Language, the language commonly used to work with relational databases. It lets you describe the information you want without spelling out every step to retrieve it.

SELECT product_id, product_name, price
FROM products
WHERE price > 2000;

The query states which columns you want, which table holds them, and which rows to include. The database decides how to run it efficiently. This is what it means to call SQL a declarative language.

What you can do with SQL

SQL does more than read data. It can filter, sort, combine, summarise, add, update, remove, and even define the structure of information in a relational database.

SELECT product_name, price
FROM products;

SELECT product_name, price
FROM products
WHERE price > 2000;

SELECT product_name, price
FROM products
ORDER BY price DESC;

SELECT o.order_id, c.customer_name, p.product_name
FROM orders AS o
JOIN customers AS c ON c.customer_id = o.customer_id
JOIN products  AS p ON p.product_id  = o.product_id;

SELECT status, COUNT(*) AS order_count
FROM orders
GROUP BY status;

INSERT INTO products (product_id, product_name, category, price)
VALUES (105, 'Laptop Sleeve', 'Accessories', 1200);

UPDATE orders
SET status = 'shipped'
WHERE order_id = 1002;

DELETE FROM orders
WHERE order_id = 1004;

CREATE TABLE products (
  product_id   INTEGER PRIMARY KEY,
  product_name VARCHAR(100),
  category     VARCHAR(50),
  price        DECIMAL(10, 2)
);

Because of this, SQL is used by software engineers, data analysts, data engineers, database administrators, data scientists, and business teams alike. The same core ideas appear across PostgreSQL, MySQL, SQL Server, Oracle, SQLite, Snowflake, BigQuery, and DuckDB; the exact syntax varies, but the relational concepts stay the same.

A SQL query returns another table

An important idea: the result of a query is itself a table. Running:

SELECT product_id, product_name, price
FROM products
WHERE price > 2000;

Nothing was removed from the database. The query produced a new table containing only the requested rows and columns. As queries grow, they follow the same shape: begin with one or more tables, filter unwanted rows, combine related tables, calculate new values, group similar rows, sort, and return a new table.

What comes next

We build SQL knowledge gradually, from the operations used in everyday queries toward more advanced analytical patterns.

1. 1Selecting columns and reading tables. SELECT … FROM …, aliases, removing duplicates, and the shape of a result.
2. 2Filtering rows with WHERE. Comparisons, AND/OR, IN, LIKE, BETWEEN, NULL, precedence, and date filters.
3. 3Sorting and limiting results. ORDER BY, ASC/DESC, LIMIT, and database-specific alternatives.
4. 4Calculations and expressions. Order totals, combining text, CASE categories, missing values, and type conversions.
5. 5Aggregate functions and grouping. COUNT, SUM, AVG, MIN, MAX, GROUP BY, and HAVING.
6. 6Joining related tables. INNER JOIN, LEFT JOIN, multi-table joins, missing matches, and duplicate rows.
7. 7Subqueries and CTEs. Scalar and correlated subqueries, EXISTS/NOT EXISTS, and WITH.
8. 8Window functions. Ranking, running totals, comparing to a previous row, and rolling averages.
9. 9Set operations. UNION, UNION ALL, INTERSECT, and EXCEPT.
10. 10Modifying data safely. Insert, update, delete, transactions, and not touching more rows than intended.
11. 11Designing reliable SQL queries. The mistakes that return believable but incorrect results: grouping, join fan-out, NULL, date boundaries, and type mismatches.

The goal is not only SQL that runs. It is SQL that expresses the business requirement clearly, returns the correct result, and stays understandable when someone reads it later.