An overview of the most common commands in relational databases with SQL

Dec 1, 2024

In the previous chapter, we reviewed the origins and fundamentals of databases. We took a brief journey through the different types of databases, including relational ones. Now, we will dive into the core of working with relational databases: SQL (Structured Query Language), the universally accepted language for interacting with these systems.

SQL allows us to define, manipulate, and query data in relational databases. In this chapter, we will break down the most common SQL terms and their basic functionality, accompanied by progressive examples that will help clarify the concepts.

SQL is the de facto standard for working with relational databases. Whether you are using MySQL, PostgreSQL, Microsoft SQL ( MSSQL), or Oracle, the fundamental concepts and commands we will cover are universal. Understanding how SQL works will enable you to retrieve, analyze, and manipulate data with ease. Let’s start with a list of the most important terms you will encounter in this chapter.

SQL variables and data types

Before diving into commands, it is essential to understand the data types commonly available in relational databases. These types define the nature and constraints of the data you can store in your table columns.

Numeric

• INT / INTEGER: Whole numbers (positive or negative).
• SMALLINT: Smaller integers, consuming less memory.
• BIGINT: Large integers, useful for very large values.
• DECIMAL(p, s) / NUMERIC(p, s): Decimal numbers with defined precision and scale.
• FLOAT / REAL: Floating-point numbers.

Text

• CHAR(n): Fixed-length text strings.
• VARCHAR(n): Variable-length text strings.
• TEXT: Text strings of indefinite length (system-limited).

Date and time

• DATE: Date (year, month, day).
• TIME: Time (hours, minutes, seconds).
• DATETIME: Combined date and time.
• TIMESTAMP: Timestamp (may include time zone information depending on the implementation).

Boolean

• BOOLEAN: Values TRUE or FALSE (implementation-dependent).

Other data types

• BLOB: Binary large objects (used for images, videos, etc.).
• JSON: Data in JSON format (increasingly supported in modern engines like PostgreSQL and MySQL).
• UUID: Universally unique identifiers.

These data types are the foundation for designing efficient schemas and structuring data optimally in a relational database. Now that we are familiar with the available data types, let’s move on to the fundamental terms and commands in SQL.

Essential terms in SQL

Below, we group the most important SQL terms based on their primary purpose. These are common to all relational database engines, though syntax may vary slightly. They are as follows:

Data Query Language (DQL)

• SELECT: Extracts data from one or more tables in the database.
• FROM: Specifies the source of the data (table or views) for the query.
• WHERE: Filters rows based on a specific condition.
• ORDER BY: Sorts the query results in ascending or descending order.
• LIMIT: Restricts the number of rows returned by the query.
• GROUP BY: Groups rows that share common values in specified columns.
• HAVING: Filters groups of data after applying GROUP BY.
• JOIN: Combines rows from two or more tables based on a common condition.
  • INNER JOIN: Returns rows when there is a match in both tables.
  • LEFT JOIN: Returns all rows from the left table and matching rows from the right table.
  • RIGHT JOIN: Returns all rows from the right table and matching rows from the left table.
  • FULL JOIN: Returns all rows when there is a match in either table.
• UNION: Combines the results of two queries into a single dataset, removing duplicates.
• DISTINCT: Eliminates duplicate rows from the query results.

Data Manipulation Language (DML)

• INSERT: Adds new rows to a table.
• UPDATE: Modifies existing rows in a table.
• DELETE: Removes rows from a table.

Data Definition Language (DDL)

• CREATE: Creates new tables, databases, views, indexes, etc.
• ALTER: Modifies the structure of a table, such as adding, changing, or removing columns.
• DROP: Deletes tables, databases, or views.

Constraints and keys

• PRIMARY KEY: Uniquely identifies each row in a table.
• FOREIGN KEY: Defines a relationship between two tables.

In the following sections, we will dive deeper into some of the most fundamental commands (SELECT, FROM, WHERE, etc.), along with practical examples. We will use a local database with SQLite to illustrate these concepts clearly and concisely.

Exploring SQL commands with a practical example

To begin, we need a sample database. You can download the SQLiteBrowser tool from its official website or install it using your preferred package manager. Once installed, create a database named store.db and execute the following SQL script to create a sample table:

Creating a table

This command creates a table named Products with four columns: id, name, price, and category. The id column is the primary key of the table and will auto-increment with each new row inserted. The name, price, and category columns are of types TEXT, REAL, and TEXT, respectively, and cannot be null (NOT NULL). Additionally, we create another table named Categories to store product categories:

  CREATE TABLE Categories (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL
  );
  
  CREATE TABLE Products (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    price REAL NOT NULL,
    category_id INTEGER NOT NULL,
    FOREIGN KEY (category_id) REFERENCES Categories(id)
  );
    

Now that we have two sample tables, let’s see how to interact with them using data insertion and query commands.

Inserting data into the table

To insert data into the Products table, we use the INSERT INTO command. Below, we insert three rows of data into the table:

  INSERT INTO Categories (name) VALUES 
  ('Electronics'), 
  ('Furniture'), 
  ('Accessories');
  
  INSERT INTO Products (name, price, category_id) VALUES
  ('Laptop', 1200, 1),
  ('Smartphone', 800, 1),
  ('Headphones', 50, 3),
  ('Table', 150, 2),
  ('Chair', 85, 2),
  ('Charger', 20, 3),
  ('Monitor', 250, 1);
  

This adds five products to the table. To view them, execute a SELECT query to retrieve all the data from the table.

Querying data from the table

The SELECT query is the most commonly used command in SQL, allowing us to retrieve data from one or more tables based on a specific condition. In this case, we retrieve all the data from the Products table:

  SELECT * FROM Products;
  

We can choose to display only specific columns in the query. For example, if we only want to see the name and price of the products, we can do it as follows:

  SELECT name, price FROM Products;
      

We can also filter the results based on a specific condition. For example, if we want to see only the products in the Electronics category, we can add a WHERE clause to our query. Since we only have the category ID, we need to use the number corresponding to the category, which is 1 in this case:

  SELECT * FROM Products WHERE category_id = 1;
      

To view the name of the category instead of its ID, we can join the two tables using the JOIN clause. In this case, we use an INNER JOIN to combine rows from both tables based on the category_id column:

  SELECT p.name AS product_name, p.price, c.name AS category_name
  FROM Products p
  INNER JOIN Categories c ON p.category_id = c.id;
      

Now we can filter by category name instead of the category ID:

    
  SELECT p.name AS product_name, p.price, c.name AS category_name
  FROM Products p
  INNER JOIN Categories c ON p.category_id = c.id
  WHERE c.name = 'Electronics';
        

As we can see, by combining different SQL commands and clauses, we can efficiently retrieve, filter, and manipulate data when working with or accessing a relational database.

Sorting and limiting results

In addition to filtering results, we can also sort and limit them using the ORDER BY and LIMIT clauses. For example, if we want to view the products sorted by price from highest to lowest, we can do so as follows:

  SELECT * FROM Products ORDER BY price DESC;
      

If we only want to see the two most expensive products, we can add a LIMIT clause to our query:

  SELECT * FROM Products ORDER BY price DESC LIMIT 2;
      

Grouping and filtering data

The GROUP BY clause allows us to group rows that have common values in one or more columns. For example, if we want to group products by category and count how many products exist in each category, we can do it as follows:

    
  SELECT c.name AS category_name, COUNT(p.id) AS product_count
  FROM Products p
  INNER JOIN Categories c ON p.category_id = c.id
  GROUP BY c.name;
        

We can also filter the grouped data using the HAVING clause. For example, if we only want to see categories that have more than two products, we can add a HAVING condition to our query:

  SELECT c.name AS category_name, COUNT(p.id) AS product_count
  FROM Products p
  INNER JOIN Categories c ON p.category_id = c.id
  GROUP BY c.name
  HAVING COUNT(p.id) > 2;
        

Now, let’s look at how to update and delete data from the table. These are common operations that are crucial for maintaining data integrity and performing maintenance tasks.

Updating and deleting data

To update data in a table, we use the UPDATE command. For example, if we want to change the price of a product, we can do it as follows:

  UPDATE Products SET price = 900 WHERE name = 'Laptop';
        

To delete rows from a table, we use the DELETE command. For example, if we want to delete a product from the table, we can do it as follows:

  DELETE FROM Products WHERE name = 'Charger';
        

We have seen, in a practical way, how to use the most common SQL commands to interact with a relational database. These commands form the foundation for designing complex queries, manipulating data, and defining the structure of a database. As you become more familiar with these commands, you will be able to efficiently retrieve, analyze, and manipulate data.

Conclusion

In this chapter, we explored the most common SQL terms and their basic functionality. We covered data query commands ( SELECT, FROM, WHERE, etc.), data manipulation commands (INSERT, UPDATE, DELETE, etc.), and data definition commands (CREATE, ALTER, DROP, etc.). Additionally, we discussed constraints and keys (PRIMARY KEY, FOREIGN KEY) used to ensure data integrity in a relational database.