SQL Beyond SELECT: Understanding SQL Sublanguages.

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.