SQL and databases for beginners: the path from theory to practice

What is a database and why do you need it?

Imagine that you are creating an application for managing a library. You need to store information about books, readers, and issues. You can, of course, use text files or JSON, but what happens when there are thousands of data? How can you quickly find all the books by a certain author? How do you ensure that two operations do not change the same data at the same time?

A database solves these problems. It is specialized software designed for the efficient storage, retrieval, and management of large volumes of structured information.

Relational databases: the foundation of the industry.

The most common type of database is relational (or SQL-based). They organize data in tables similar to Excel tables, where:

Table — is a set of related data (for example, the "Users" table)

Line is a separate record (specific user)

Column is a record attribute (name, email, registration date)

Key is a unique record identifier

The beauty of relational databases is that tables can be linked together. For example, the Orders table can refer to the Users table, creating a relationship between the data.

SQL: the language of communication with the database.

SQL (Structured Query Language) is the language you use to "talk" to a database. It is surprisingly readable and logical. Here are the basic operations that every developer starts with:

Creating a table

CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Here we created a table with four fields, defined data types and added restrictions (NOT NULL means that the field is required, UNIQUE — that the values should not be repeated).

Data insertion

INSERT INTO users (username, email) 
VALUES ('alex_dev', 'alex@example.com');

Reading data

SELECT * FROM users WHERE username = 'alex_dev';

Data update

UPDATE users 
SET email = 'newemail@example.com' 
WHERE username = 'alex_dev';

Data deletion

DELETE FROM users WHERE id = 5;

These four operations are often called CRUD (Create, Read, Update, Delete) — the basis for working with any database.

Case study: blog

Let's create a simple structure for a blog:

-- Таблица авторов
CREATE TABLE authors (
    id INTEGER PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    bio TEXT
);

-- Таблица постов
CREATE TABLE posts (
    id INTEGER PRIMARY KEY,
    author_id INTEGER,
    title VARCHAR(200) NOT NULL,
    content TEXT,
    published_at TIMESTAMP,
    FOREIGN KEY (author_id) REFERENCES authors(id)
);

-- Таблица комментариев
CREATE TABLE comments (
    id INTEGER PRIMARY KEY,
    post_id INTEGER,
    author_name VARCHAR(100),
    content TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (post_id) REFERENCES posts(id)
);

Pay attention to FOREIGN KEY — this creates a link between the tables. Now we can make interesting queries:

-- Получить все посты конкретного автора с количеством комментариев
SELECT 
    p.title, 
    p.published_at,
    COUNT(c.id) as comment_count
FROM posts p
LEFT JOIN comments c ON p.id = c.post_id
WHERE p.author_id = 1
GROUP BY p.id, p.title, p.published_at
ORDER BY p.published_at DESC;

Selecting the first database

To get started, I recommend one of these options:

SQLite - ideal for training. It does not require server installation, the entire database is one file. Great for small projects and prototypes.

PostgreSQL — a powerful industrial DBMS with rich functionality. Free, with excellent documentation and a huge community.

MySQL/MariaDB — also popular solutions widely used in web development.

Practical tips for beginners.

Start small. Create a simple database for a personal project — a habit tracker, a to-do list, a movie collection. Practice is more important than theory.

Use GUI tools. Programs like DBeaver, TablePlus, or built-in tools will help you visualize the database structure and debug queries.

Study JOINs gradually. Joining tables may seem difficult, but it is a key concept. Start with INNER JOIN, then master LEFT JOIN.

Think about performance from the very beginning. Create indexes on the fields you often search for data. An index is like a table of contents in a book, it speeds up the search by thousands of times.

Data normalization is your friend. Do not duplicate information in different tables. If the author's data is repeated in each post, it is a signal that a separate table of authors is needed.

Examine the transactions. They guarantee that a series of operations will either be completed in full or not at all. This is critical for financial transactions and other important actions.

Next steps.

After mastering the basics of SQL, many directions will open up for you. You can immerse yourself in query optimization, study NoSQL databases (MongoDB, Redis), get acquainted with ORM (Object-Relational Mapping) like SQLAlchemy or Prisma, which allow you to work with databases through objects in the code.

Databases are not just a technology, they are a way of thinking about the structure of information. The deeper you understand how to organize and extract data efficiently, the more powerful applications you can create. Start today with a simple SELECT query, and in a few months you will be designing complex data schemes for real projects.

In Codice we make programming training fun and easy to understand: we have interesting courses with tasks that help you improve your skills step by step.

And we also have an active Telegram channel, where we discuss cool ideas, share experiences and analyze tasks together — learning becomes not only useful, but also fun.