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:

person_id,first_name,last_name,phone_number,address,city
1,Asha,Rao,9876543210,12 Lake Road,Bengaluru
2,Ben,Thomas,9123456780,45 Park Street,Bengaluru
3,Carla,Shah,9988776655,18 Hill View,Mumbai
4,Dev,Mehta,9000012345,12 Lake Road,Bengaluru

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, explained with examples

1. Repeated information can become inconsistent

If we store a separate row for each phone number, Asha’s name and address appear multiple times. When she moves to a new address, updating only one row leaves two different addresses for the same person:

2. Files enforce no rules

Suppose every person must have a unique ID, a name, a valid phone number, and a city. A plain file doesn’t check these rules. It can contain duplicate IDs, missing names, or invalid phone numbers without reporting an error:

The id 1 is duplicated, unknown is not a phone number, Ben’s city is missing, and the third record has no name.

What a database provides

A database is an organised collection of information. The software used to store, search, update, and manage this information is called a Database Management System (DBMS). Examples include PostgreSQL, MySQL, Microsoft SQL Server, Oracle Database, and SQLite.

A DBMS makes it easier to manage large amounts of data. It helps you find records quickly, update information safely, allow multiple users to work with the same data, enforce rules to prevent invalid data, control who can access information, and recover from failures. When data is organised properly, it also helps reduce duplication and keep related information consistent.

The Relational Model

In a relational database, information is organised into tables. Each table stores one type of information, such as customers or orders. A table has rows and columns:

• A row represents one record, such as a single order.
• A column represents a specific detail about that record, such as the order ID, customer ID, or total amount.

For example, an orders table might look like this:

Customer and product details don’t need to be repeated in every order. Instead, we store them in separate tables and use IDs to connect the information.

For example, order 1001 contains product_id = 101 and customer_id = 1. Using these IDs, we can find out that Asha ordered a Wireless Mouse without storing her personal details or the product information again.

How tables connect: 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. It is the language we use to work with relational databases. SQL lets us retrieve, add, update, and delete information. When retrieving data, we describe what we want, rather than writing every step needed to find it.

For example, a SQL query can specify which columns to return, which table to read, and which conditions the rows must satisfy. The database figures out how to retrieve the data efficiently. This approach is called declarative programming.

Imperative vs. declarative: what’s the difference?

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

What does a SQL query return?

An important concept is that a SELECT query returns a result that looks like a table. For example, suppose we run a query to find products priced above 2,000. The result contains only the products that match our condition and the columns we requested. The original table remains unchanged. The query simply returns the information we asked for. As you learn more SQL, you’ll build queries that perform additional operations: filtering rows, combining related tables, calculating values, grouping records, and sorting results. The basic idea remains the same: start with data, describe what you need, and let SQL return the result.

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

What comes next

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

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