DEV Community: Shaquille Mburu

SQL Beyond SELECT: Understanding SQL Sublanguages.

Shaquille Mburu — Thu, 15 Oct 2026 22:57:13 +0000

Introduction.

Structured Query Language (SQL), like any language, has its own syntax and semantics. To make it easier to understand, SQL commands can be grouped by the type of operation they perform on a database.

These groups are commonly referred to as SQL sublanguages: DDL, DML, DQL, DCL, and TCL.

In this article, we'll use a supermarket as a running example to explore what each sublanguage does and how you use its commands in real-world database operations.

The Sunrise Supermarket analogy.

Picture walking into Sunrise Supermarket and noticing the products are organized into categories, shelves are stocked, prices are updated, sales are recorded, and employees have different levels of access to the information they need.

When a new product arrives, someone needs to add it to the system. When a product's price changes, the database needs to be updated. When the manager wants to know which products are selling the most, they need to query the data. When a cashier logs into the system, they should only have access to the information relevant to their role.

And sometimes, several things need to happen together.

Suppose a customer buys the last five bottles of a particular product. The system needs to record the sale and reduce the available stock. What happens if the sale is recorded successfully but the stock update fails?

To handle these transactions reliably, SQL groups commands with related responsibilities into sublanguages. In this article, we'll use Sunrise supermarket as a running example to understand each of these sublanguages, see the SQL commands associated with them, and see where they fit into real-world database operations.

1. Data Definition Language (DDL)

DDL handles the structure of the database. It creates database objects, such as schemas, tables, views, and indexes, modifies their structure, and removes them when no longer needed.

At Sunrise supermarket, before the first customer is served, attendants organize its shelves, record product information, and record their relationship to other parts of the system.

We'll start by creating a table using the CREATE TABLE command to store product information. We use constraints such as NOT NULL to define the structure and rules for the data.

CREATE TABLE products (
    product_id INT PRIMARY KEY,
    product_name VARCHAR(100) NOT NULL,
    category VARCHAR(50),
    price DECIMAL(10, 2) NOT NULL,
    stock_quantity INT NOT NULL DEFAULT 0,
    supplier VARCHAR(100)
);

As Sunrise supermarket evolves, there is a need to track expiry dates for perishable products. Instead of creating an entirely new table, we can modify the existing one using ALTER TABLE.

ALTER TABLE products
ADD COLUMN expiry_date DATE;

An existing column can be renamed using RENAME COLUMN.

ALTER TABLE products
RENAME COLUMN supplier TO supplier_name;

In the case where a column becomes redundant, it's dropped using DROP COLUMN.

ALTER TABLE products
DROP COLUMN expiry_date;

As part of the evolution of Sunrise supermarket, a temporary table was created to test a new inventory process. Once the table and its records are no longer needed, we can remove it using DROP TABLE.

DROP TABLE test_inventory;

On the other hand, a temporary table that was used to validate supplier data can be emptied of all its records while retaining the table using TRUNCATE TABLE.

TRUNCATE TABLE supplier_import_staging;

As Sunrise supermarket grows, its products table may contain thousands of records and needs to run queries more efficiently. Indexing helps locate matching records, and it's created using CREATE INDEX.

CREATE INDEX idx_products_name
ON products (product_name);

To remove an index that is no longer required, we use DROP INDEX.

DROP INDEX idx_products_name;

Suppose the manager frequently needs a list of products that are running low on stock. Instead of repeatedly writing the same query, we can create a view using CREATE VIEW.

CREATE VIEW low_stock_products AS
SELECT
    product_id,
    product_name,
    stock_quantity
FROM products
WHERE stock_quantity < 40;

The manager can then query the view like a table:

SELECT *
FROM low_stock_products;

2. Data Manipulation Language (DML)

While DDL creates the structure, DML is used to manage the data in the database.

Sunrise supermarket now has a products table. Every time a new product arrives, a supplier changes a price, or a product is discontinued, we need a way to add, modify, or remove the relevant records.

To add products to the table, we use INSERT INTO.

INSERT INTO products (
    product_id,
    product_name,
    category,
    price,
    stock_quantity,
    supplier
)
VALUES
    (102, 'Rice 2kg', 'Groceries', 280.00, 80, 'Grain Distributors'),
    (103, 'Laundry Detergent', 'Household', 450.00, 30, 'Clean Living Ltd'),
    (104, 'Milk 500ml', 'Dairy', 65.00, 100, 'Dairy Fresh Ltd');

If a supplier or product price changes, the table is updated using UPDATE.

UPDATE products
SET
    price = 370.00,
    supplier = 'Fresh Supplies Ltd'
WHERE product_id = 101;

Sunrise Supermarket might discontinue a product, and we remove the record using DELETE FROM.

DELETE FROM products
WHERE product_id = 103;

The WHERE filter is critical for finding the exact record; otherwise, all records can also be removed using:

DELETE FROM products;

Sunrise Supermarket receives a stock file from its supplier every morning. Some products already exist in the database, while others are new. Instead of checking each product manually, we can use MERGE to match incoming records against existing ones and decide whether to insert or update them. Let's have the supplier's latest stock information loaded into a staging table called incoming_products.

MERGE INTO products AS target
USING incoming_products AS source
ON target.product_id = source.product_id

WHEN MATCHED THEN
    UPDATE SET
        price = source.price,
        stock_quantity = source.stock_quantity

WHEN NOT MATCHED THEN
    INSERT (
        product_id,
        product_name,
        category,
        price,
        stock_quantity,
        supplier
    )
    VALUES (
        source.product_id,
        source.product_name,
        source.category,
        source.price,
        source.stock_quantity,
        source.supplier
    );

3. Data Query Language (DQL)

Sunrise supermarket now has products in its database, stock levels are being updated, and sales are being recorded, but how do we make use of all that data? This is where DQL comes in. It's primarily used to retrieve data.

Suppose the manager wants to find grocery products priced above KES 300, with the most expensive first? We'll use SELECT as the primary command, WHERE as the filter, and ORDER BY as the sorting clause.

SELECT product_name, price
FROM products
WHERE category = 'Groceries'
  AND price > 300
ORDER BY price DESC;

If an attendant wants to identify products priced above the average price of all products, they can do so by nesting one query within another.

SELECT product_name, price
FROM products
WHERE price > (
    SELECT AVG(price)
    FROM products
);

As queries become more complicated, nesting multiple subqueries can make them harder to read. Suppose the manager wants to see categories with an average product price above KES 200. A Common Table Expression (CTE) lets us define an intermediate result by using WITH.

WITH category_prices AS (
    SELECT
        category,
        AVG(price) AS average_price
    FROM products
    GROUP BY category
)
SELECT category, average_price
FROM category_prices
WHERE average_price > 200;

4. Data Control Language (DCL)

The Sunrise Supermarket database contains product details, inventory levels, supplier information, and sales records; however, not every employee needs access to all this information.

A cashier may need to view product prices, while an inventory manager needs to update stock quantities. The accountant may need access to sales records, while only authorized administrators should be able to change database permissions.

DCL manages access to database objects and controls what different users can do. Suppose the inventory manager needs to view products and update stock quantities.

GRANT SELECT, UPDATE
ON products
TO inventory_manager;

If a cashier is moved to another department and no longer needs access to the products table, the access can be revoked using REVOKE.

REVOKE SELECT
ON products
FROM cashier;

As Sunrise Supermarket expands, granting permissions needs to be handled more efficiently. A better approach is to create roles based on job responsibilities and assign permissions to those roles using CREATE ROLE.

CREATE ROLE cashier;

GRANT SELECT
ON products
TO cashier;

GRANT INSERT
ON sales
TO cashier;

The role is then assigned to a database user.

GRANT cashier
TO employee_101;

When another cashier joins, the same role can be assigned to that employee rather than repeating every permission grant and revoking the role from the previous employee.

REVOKE cashier
FROM employee_101;

5 Transaction Control Language (TCL)

When a customer buys five bottles of cooking oil at Sunrise Supermarket. Two things need to happen in the database:

A sales record must be created, and the product's stock quantity must be reduced by five.

These operations are related. If the sales record is saved but the stock quantity is not updated, the database may no longer accurately reflect the supermarket's inventory. Similarly, stock should not be reduced if the sale cannot be recorded.

TCL commands manage groups of database operations that should be treated as a single unit. The main commands are BEGIN, COMMIT, ROLLBACK, and SAVEPOINT.

If the price of each cooking oil bottle is KES 350, then the database is updated using:

BEGIN;

INSERT INTO sales (
    sale_id,
    product_id,
    quantity_sold,
    sale_amount
)
VALUES (
    1001,
    101,
    5,
    1750.00
);

UPDATE products
SET stock_quantity = stock_quantity - 5
WHERE product_id = 101;

COMMIT;

BEGIN starts the transaction, while COMMIT permanently saves the transaction. In case of an error while processing the sale, the uncommitted changes can be undone using ROLLBACK.

BEGIN;

INSERT INTO sales (
    sale_id,
    product_id,
    quantity_sold,
    sale_amount
)
VALUES (
    1002,
    101,
    5,
    1750.00
);

-- Suppose an error occurs before the transaction is completed.

ROLLBACK;

A transaction may contain several operations, and only one needs to be undone. Rather than rolling back the entire transaction, you can create a SAVEPOINT.

BEGIN;

UPDATE products
SET stock_quantity = stock_quantity - 5
WHERE product_id = 101;

SAVEPOINT after_stock_update;

-- An optional operation is attempted here.

-- If that operation fails or must be undone:
ROLLBACK TO SAVEPOINT after_stock_update;

-- Continue with other valid operations if appropriate.

COMMIT;

Final Thoughts

Understanding SQL sublanguages makes it easier to see how different database operations work together. From defining database structures with DDL and manipulating data with DML to retrieving information with DQL, controlling access with DCL, and managing transactions with TCL, each plays a distinct role in database management. Using the Sunrise Supermarket example, we've seen how these commands work together to maintain accurate, secure, and reliable data.

Bridging Local and Cloud Databases for Centralized Data Management

Shaquille Mburu — Mon, 21 Sep 2026 19:51:43 +0000

Introduction

Structured data (typically in rows and columns) resides in databases such as PostgreSQL or MySQL. These databases can be hosted locally or offered as a cloud-managed service. When data spans multiple databases or is hosted both locally and in the cloud, a universal data management system provides a centralized platform to handle this dynamic scenario. One of the most common centralized platforms is DBeaver. In this read, we'll set up PostgreSQL locally and as a cloud-managed service with Aiven, access both databases in DBeaver, and link them to Power BI.

The Setups.

Local PostgreSQL setup.

PostgreSQL is an open-source database that is largely contributed to by EnterpriseDB. You can download the installer from EDB shown below. Choose your operating system; in this case, the Windows installer, to start the download.

After completing the download, run the installer as an administrator to get to the setup welcome page.

Proceed to select the components for the installer. You can have all the components checked, or uncheck pgAdmin 4 and StackBuilder since we'll use DBeaver - the universal database management system.

pgAdmin is a PostgreSQL native Graphical User Interface (GUI) used to interact with PostgreSQL databases.

Without a GUI, database interaction can only happen with terminal shell commands using psql.

After selecting the components, you'll be prompted to select your preferred data directory. This directory stores all your database-related data, which can be left as the default.

The next step is choosing your superuser password. The default superuser is postgres. Ensure you provide a password you can easily remember, since it will be required to access the database.

After providing a password, select the port that routes traffic from the internet to the database. The default port is 5432, which can be left as is for a 1st time installation. In case you need to install a later version, you can assign port 5433, but this installation will be left at the default.

Proceed to the preinstallation summary, which details the elements selected and ready to be installed.

After checking the summary, proceed with the installation.

Once the installation is complete, the setup wizard prompts the launch of StackBuilder. This step is optional, as StackBuilder is a useful package manager if you intend to install database drivers or extensions. Click Finish to complete the setup.

Cloud PostgreSQL setup.

One of the cloud-managed PostgreSQL services available is Aiven. To get started, create an account by signing up through the Aiven console.

After signing up, proceed to Services in the left pane to create a new PostgreSQL service using the Create service button at the top right.

Proceed to select the PostgreSQL database.

Select the service tier and proceed to create the service.

The service will first build, then switch to running. This pane provides the credentials required to connect to other software, such as the database management system and visualization tools.

Universal database management system setup.

This system can be installed by downloading the installer from the DBeaver official site. The community version is open-source. Alternatively, it can be downloaded from the Microsoft Store and run the installer.

The installer will start by asking you to select the language.

Proceed to the welcome note and licence agreement.

After agreeing to the licence, you'll select the components and the installation directory.

Finally, proceed with the installation.

The Connections.

Connecting to the localhost and cloud-hosted service.

Launch DBeaver and create a new connection using the plug icon below the File button, select the PostgreSQL database, and proceed to the next pane.

By default, the host is set to localhost on port 5432, and the username is postgres. To complete the connection, enter your password.

As a best practice, test the connection before establishing the database connection.

To connect to the cloud-managed PostgreSQL, create a new connection and use the credentials provided by the Aiven service.

For the connection to be established, the host, port, database name, username, and password are required.

Test the connection before establishing it.

Once the connection is established, create a new script using the SQL editor to write SQL queries.

Connecting to Power BI

To establish a connection with Power BI, launch it and choose the PostgreSQL database using the Get Data button.

For a connection to localhost, the server IP address is 127.0.0.1 on port 5432 or 5433, and the database name is postgres.

The username is postgres; enter your password to connect.

Once the connection is established, access is granted to any existing tables in the database.

To connect to the cloud-managed service, use the hostname and port as the server IP and the database name as defaultdb.

Once the connection is established, access to the existing tables in the database is granted for further manipulation and visualization.

Final thoughts

Data stored across different sources should not remain confined to static, siloed systems. Instead, it should be accessible and transferable between specialized tools and applications, enabling users to manipulate, analyze, and transform the data through secure connections and connection strings.

Making sense of data through modelling relationships in Power BI

Shaquille Mburu — Fri, 17 Jul 2026 18:49:00 +0000

Before building dashboards, writing SQL queries, or creating reports, it's essential to organize data in a way that accurately represents real-world entities while capturing their interactions. This process is known as data modeling.

What is a data model?

Picture a three-storey building, and let's call it the database building. Let's label each floor - schema. Since each floor has a unique identity, pair it with its corresponding floor number, so that the first floor becomes schema-1. Each floor or schema has four apartments, labelled table, paired with its unique unit number to become table-1, table-2, and so forth. Each apartment unit or table has a different characteristic defined by size and number of rooms. For instance, schema-1 can have studio units, while schema-2 can have one-bedroom units of different floor areas. Similarly, related tables in a database are hosted in the same schema. This organizational blueprint is referred to as a data model.

How is the Schema structured?

A schema typically takes two structures, dictated the relationships of the tables inside it.

1. The Star Schema.

As the name suggests, it takes the shape of a star. In this setup, there is a central table called the facts table that has a relationship with all the other tables called the dimension tables. A fact table explains what happened, while the dimension table gives context of who, where, when.

2. The Snowflake Schema.

This schema takes the shape of a snowflake. The dimension tables can have related subcategories, which are not directly linked to the fact table.

How are these tables related?

Every table in a database typically includes a column that uniquely identifies each record. This column is referred to as the primary key (PK). Think of it as a unique ID that distinguishes one row from another.

When one table needs to reference information stored in another table, it includes that table's primary key as one of its own columns. This referenced column is known as a foreign key (FK). Foreign keys establish relationships between tables, allowing the database to connect related information while maintaining consistency across the data.

Once tables contain primary keys and foreign keys, the next step is to define the relationships between them. Relationships tell the database how tables are connected, enabling it to combine related information when queries are executed. Think of relationships as the roads that connect tables; without these relationships, retrieving meaningful insights from multiple tables would be difficult and inefficient. Typically, there are three types of relationships:

1. One-to-One Relationship

This relationship suggests that each record in a table is associated with a single record in another table. For instance, a student can have only one identity card and vice versa. However, this relationship is not common.

2. One-to-Many Relationship

This implies that a single record in one table can be associated with multiple records in another table, while each record in the second table relates to only one record in the first. For example, one school can have many students, but each student can only belong to one school.

3. Many-to-Many Relationship

As the name suggests, records in both tables can be associated with multiple records in the other table. For instance, a teacher can teach many students, and a student can be taught by many teachers.

How is data retrieved across tables?

After organizing data in tables and relating them, it is then retrieved based on different use cases using joins. A join combines rows from two or more tables based on a common column, a primary key, and its corresponding foreign key. There are five types:

1. Inner join

This join returns only the records that have matching values from both tables. For instance, the syntax to view students' names (from student's table) and the respective schools (from the schools' table) they attend is as follows:

SELECT
    s.student_name,
    sch.school_name
FROM student_table s
INNER JOIN school_table sch
ON s.school_id = sch.school_id;

2. Left join

This join returns all records from the left table and any matching records from the right table. If no match exists, the columns from the right table are returned as NULL. The syntax will return every student, even if their school information is missing.

SELECT
    s.student_name,
    sch.school_name
FROM student_table s
LEFT JOIN school_table sch
ON s.school_id = sch.school_id;

3. Right join

This join reverses the left join. It returns all records from the right table and matching records from the left table. The syntax will return every school, even if no student is assigned to it.

SELECT
    s.student_name,
    sch.school_name
FROM student_table s
RIGHT JOIN school_table sch
ON s.school_id = sch.school_id;

4. Full outer join

This join returns all records from both tables. Matching rows are combined, while non-matching rows contain NULL values for the missing side. The syntax returns every record from both the students and school tables, whether a match exists or not.

SELECT
    s.student_name,
    sch.school_name
FROM student_table s
FULL OUTER JOIN school_Table sch
ON s.school_id = sch.school_id;

5. Anti joins

This join returns records that do not have a matching record in another table. However, anti join is not a SQL native command and can be implemented using the WHERE and the NOT NULL clauses. The syntax is as follows:

SELECT
    s.student_name
FROM student_table s
LEFT JOIN school_table sch
ON s.school_id = sch.school_id
WHERE sch.school_id IS NULL;

Final Thoughts

Every dashboard and business application begins with a well-designed data model. Understanding how tables relate to one another and how SQL joins retrieve and combine the data across tables, bringing those relationships to life, is a fundamental skill in working with data.

Linux Fundamentals for Data Engineering

Shaquille Mburu — Thu, 25 Jun 2026 00:23:10 +0000

What is Data Engineering?

Data refers to a collection of raw observations. Engineering broadly refers to the art of building and in this case, using data as its raw material. Data engineering focuses on building systems that collect, move, clean and store data, making it available, reliable and useable.

Why Linux?

Linux is a Unix-like operating system (OS), meaning it borrows heavily from its philosophy of simplicity, modularity and code reusability. Its simplistic nature consumes minimal compute resources with superior performance making it widely adopted across servers, cloud infrastructure and big data technologies. This adoption allows native compatibility in engineering tasks making Linux a suitable choice for data engineers.

Linux in Action...

Let's look at a case of accessing a server to set up PostgreSQL database.

1. Server Access.

A remote server is accessed using a secure cryptographic network protocol called Secure Shell - ssh.
The syntax is:ssh <username>@<server_ip_address> while using a password, or ssh -i /path/to/private-key.pem <username>@<server_ip_address> in the case of public/private key pair.

2. User Access.

After accessing the server, start by updating the server using the command sudo apt update.

Create a new user using the command adduser <username>.

Verify the user was created using id <username>

To switch to the new user run the command su - <username>

Alternatively the new user can be accessed directly without going through root by running the command ssh <new_user>@<remote_ip_address>.

The new user can be granted administrative privileges by adding them to the super users (sudo) group, using the command sudo usermod -aG <group_name> <user_name>

3. Setting up PostgreSQL.

Start by checking if PostgreSQL is installed using the command psql --version. If it is not installed run these commands:

sudo apt update
sudo apt install postgresql postgresql-contrib

In this case, version 16 is installed.

Login as its super user - postgres by running the command sudo -i -u postgres. This command should be run from root or an account with adminstrative priviledges.
To access the PostgreSQL shell run the command psql.

Create a user using CREATE USER <username> WITH PASSWORD 'user_password'; and a database using CREATE DATABASE <database_name>;. Grant all access to the user by running the command GRANT ALL PRIVILEGES ON DATABASE <database_name> TO <username>;. Note: Ensure all lines are terminated with a semicolon (;) and correct spelling, otherwise it results in an error.

Confirm the creation of the database using \l.

To change the owner from postgres run the command ALTER DATABASE <database_name> OWNER TO <user_name>.

Confirm the owner was changed to the new user using \l.

To create a schema named staging, move into the database created using the command \c <database_name> or directly move from the postgres admin user using the command psql -U <username> -d <database_name> -h <host_name>. From the database run the command CREATE SCHEMA <schema_name>. List the schemas in the database using the \dn command.

4. Configuring for external tools and traffic.

Data can be uploaded using external tools like DBeaver. Before switching to DBeaver, postgresql needs to be configured to allow external tools and traffic to connect to the databases.

To allow external tools we run the command sudo vim /etc/postgresql/16/main/postgresql.conf. This opens the configuration file in the vim editor.

Edit the listeners to listen globally using the '*' symbol. To save and exit the editor press esc then :wq!.

To allow global traffic connections to the database run the command sudo vim /etc/postresql/16/main/pg_hba.conf to open the file in the vim editor.

Include a configuration line (the last line) which allows global traffic and enforce a password using either md5 or scram-sha-256 authentication. To save and exit the editor press esc then :wq!.

Restart the PostgreSQL service for the configuration changes to be effected.

Check the status of the postgresql service.

5. Uploading Data.

Switch to DBeaver to upload data and establish a connection to PostgreSQL.

To complete the connection, input the credentials created in the linux server.

Test the connection before establishing it. If the credentials match a connection test will pass.

After the connection is established, move to the created database, then select the schema to import data.

Browse the file to be imported.

Select the table mapping.

Complete the importation.

Conclusion

Linux forms the backbone of data engineering enabling native interaction with servers, cloud infrastructure and big data technologies. Its mastery enables engineers to design and build, robust and scalable systems to solve present day challenges.