Let's talk about SQL

SQL stands for Structured Query Language, and in my opinion is one of the foundations of the modern web. Of course, we still have lots of sites and products that use non-relational database languages, as well as a lot of sites that still use old-school techniques for permanent data, for instance, this blog, which uses markdown to store every post.

In real life though, you need your apps to communicate with a db; in comes SQL. It allows developers and data analysts to store, retrieve, update, and manage data efficiently. In this article, I’ll cover the fundamentals of SQL, including basic queries and the concept of transactions.

---

What Is SQL?

SQL is used to communicate with databases such as PostgreSQL, MySQL, SQLite, and SQL Server. With SQL, you can perform operations like:

• Creating and modifying database structures (tables, schemas)
• Inserting, updating, and deleting data
• Querying data using powerful filtering and sorting capabilities
• Managing access and permissions

---

Basic SQL Queries

Let’s look at some common SQL commands that form the backbone of everyday database work.

1. “SELECT”: Retrieving Data

SELECT first_name, last_name
FROM users;

You can retrieve specific columns or use ”*” to select all:

SELECT * FROM users;

2. “WHERE”: Filtering Results

SELECT * FROM users
WHERE age > 18;

Operators you can use:

= != < > <= >=
AND OR NOT
LIKE IN BETWEEN

Example:

SELECT * FROM users
WHERE age BETWEEN 18 AND 30
  AND city = 'Mexico city';

3. “INSERT”: Adding Data

INSERT INTO users (first_name, last_name, age)
VALUES ('Johhny', 'Cash', 65);

4. “UPDATE”: Modifying Data

UPDATE users
SET age = 65
WHERE first_name = 'Johhny' AND last_name = 'Cash';

5. “DELETE”: Removing Data

DELETE FROM users
WHERE age < 18;

---

Sorting, Limiting, and Grouping

”ORDER BY”: Sorting Results

SELECT * FROM users
ORDER BY age DESC;

”LIMIT”: Restricting Rows

SELECT * FROM users
LIMIT 10;

”GROUP BY” and Aggregation

SELECT city, COUNT(*) AS user_count
FROM users
GROUP BY city;

---

Transactions in SQL

A transaction is a sequence of one or more SQL operations that are executed as a single unit of work. Transactions help maintain data integrity, especially when working with critical operations like transferring funds between accounts.

Properties of Transactions (ACID):

1. Atomicity – All operations in a transaction succeed or none do.
2. Consistency – The database remains in a valid state before and after the transaction.
3. Isolation – Concurrent transactions don’t interfere with each other.
4. Durability – Once committed, the changes are permanent.

Basic Transaction Syntax

BEGIN;

UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;

COMMIT;

If something goes wrong, you can roll it back:

ROLLBACK;

---

Best Practices

• Always use “WHERE” clauses to avoid accidental full-table updates or deletions.
• Test your queries with “SELECT” before running “UPDATE” or “DELETE”.
• Use transactions for multi-step operations that must succeed together.
• Normalize your data to reduce redundancy and improve integrity.

---

Conclusion

SQL is an essential skill for anyone working with data. By mastering basic queries and understanding how to use transactions, you lay the foundation for building secure, robust, and efficient database-driven applications.

Ther’s of course a lot to learn about SQL! Check out topics like joins, indexing, views, stored procedures, and database design patterns.