SQL: From zero to hero

Hey dev, long time no see! I hope you’re doing well. Today, we’re going to talk about how to communicate with databases—specifically, those that use SQL. I hope you’re ready, because we’re about to start!

A bit of terminology

Let’s talk a little bit about how databases fit into the picture. Imagine we have a website — for example, Facebook. When you create a new account, your credentials, login, and password have to be stored somewhere, and that ‘somewhere’ is most likely a database.

A database is essentially storage for your data. It can hold usernames, addresses, ages, or even product information you see on e-commerce sites — all of this is stored in a database and dynamically loaded onto the page when a user requests it.

To interact with some databases, you need to use a specific language SQL (Structured Query Language).

SQL is a language that is used to manipulate data in relational databases

But here’s the thing: not all databases use SQL. Some of them take a different approach. Let’s check that out

SQL vs NoSQL

So, before we learn more about SQL, I want to mention that you can use this language only with relational databases. What does that mean? These databases use tables that store data in rows and columns, and we might have multiple tables that can be related to each other based on a certain column(s).

Example:
Imagine we have a table named Employee. It might look like this:

Employee

Department

In this case, the Employee table has a relationship with the Department table based on departmentId. In the Employee table, the departmentId column is called a foreign key, and in the Department table, it is called a primary key.

For example, in the Employee table we see that Anna has departmentId = 3. If we look it up in the Department table, we can see that department 3 corresponds to Sales.

In NoSQL databases, we have a completely different mechanism. Most of the time, data is stored in collections, such as an Employees collection, which contains multiple records with all their data.

User Collection (Example)

[
  {
    "userId": 1,
    "name": "Anna",
    "email": "anna@example.com",
    "department": {
      "departmentId": 3,
      "departmentName": "Sales"
    }
  },
  {
    "userId": 2,
    "name": "John",
    "email": "john@example.com",
    "department": {
      "departmentId": 2,
      "departmentName": "Marketing"
    }
  }
]

Every element inside {} is a separate record

When choosing a database, you should always pick the one that best fits your needs, most of the time it depends on the project. Sometimes NoSQL is the right choice, and other times SQL is the way to go. It all comes down to how your data is structured and how you plan to use it.

Examples of SQL databases:

👉 MySQL, 👉 MariaDB, 👉 PostgreSQL, 👉 Oracle, 👉 Microsoft SQL Server

Examples of NoSQL databases:

👉 MongoDB, 👉 Cosmos DB, 👉 Cassandra

SQL Sublanguages

SQL has five sublanguages, each containing commands or queries that perform related activities.

Here’s the list:

👉 Data Definition Language (DDL)

👉 Data Manipulation Language (DML)

👉 Transaction Control Language (TCL)

👉 Data Control Language (DCL)

👉 Data Query Language (DQL)

Let’s review each of them:

1. Data Definition Language (DDL) DDL contains commands related to creating or defining the structure of a database. This includes creating tables and defining their fields.

Example: CREATE TABLE, ALTER TABLE, DROP TABLE

2. Data Manipulation Language (DML) DML lets us manipulate the data inside tables, including inserting, updating, or deleting records.

Example: INSERT, UPDATE, DELETE

3. Data Control Language (DCL) DCL commands are responsible for allowing or restricting access to databases and tables. You can grant or revoke permissions for certain users.

Example: GRANT, REVOKE

4. Data Query Language (DQL) DQL includes commands that retrieve data from tables.

Example: SELECT

5. Transaction Control Language (TCL)

TCL may be a little tricky to wrap your head around. It lets us treat a set of commands as a single unit of work.

Common Commands

👉 Create Database

CREATE DATABASE MyDatabase;

👉 Creating a New Table

CREATE TABLE Users (
    userId INT PRIMARY KEY,
    name VARCHAR(50),
    email VARCHAR(100),
    age INT
);

👉 Inserting a New Record

INSERT INTO Users (userId, name, email, age)
VALUES (1, 'Anna', 'anna@example.com', 25);

👉 Retrieving Data from the Table

-- Get all users
SELECT * FROM Users;

-- Get users with age > 20
SELECT name, email FROM Users WHERE age > 20;

👉 Updating Data in the Table

-- Update Anna's email
UPDATE Users
SET email = 'anna.new@example.com'
WHERE userId = 1;

👉 Deleting Data from the Table

-- Delete a user
DELETE FROM Users
WHERE userId = 1;

Constraints and Clauses

One of the most important topics we need to discuss is constraints. There are different types of constraints, some of which we can use when creating a table—for example, NOT NULL, UNIQUE, PRIMARY KEY, and FOREIGN KEY

👉 NOT NULL ensures that a column cannot have a null value.

👉 UNIQUE ensures that records in the table are unique and do not repeat.

Another important concept is clauses that help you filter and organize data. Some of these include WHERE, ORDER BY, GROUP BY, DISTINCT, and others. Many of these clauses are used in SELECT statements to filter, sort, or group your data, which is extremely helpful when working with large datasets.

SQL flavors

As I already mentioned, there are many database servers (MySQL, and others), and depending on the company that created the database server, they may have slightly different SQL syntax. The differences are usually not huge, but MySQL and Microsoft SQL Server, for example, might have variations in their syntax.

It’s always a good idea to check the documentation. Most queries are similar across platforms, but if you are using something specific, the syntax might differ. Being aware of these differences can save you time and prevent errors.

There is so much to SQL that it’s impossible to cover everything in one article. I hope this article was helpful, and I will continue the series with more articles and exercises to help you master SQL.

Thank you for reading, and happy coding! 🚀