SQL Beyond SELECT: Understanding SQL Sublanguages
Explore the 5 SQL sublanguages including DDL, DML, DQL, DCL, and TCL through a practical Sunrise Supermarket database example.
Stock photo for illustration only, not from the actual event
- SQL commands are grouped by operations into SQL sublanguages.
- Sunrise Supermarket is used as a running example for real-world operations.
- Covers essential groups including DDL, DML, DQL, DCL, and TCL.
- Brings structure and reliability to database management and coding.
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.
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.
Stock photo for illustration only, not from the actual event
Categorizing SQL commands into sublanguages represents a fundamental separation of concerns in database architecture. It allows developers and administrators to cleanly separate structural definitions (DDL) from data manipulation (DML) and transaction controls, ensuring maintainable and secure database systems.
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.
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 using constraints such as NOT NULL.
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, rename columns, or drop redundant tables using ALTER TABLE, DROP TABLE, and TRUNCATE TABLE. On the other hand, Data Manipulation Language (DML) is used to manage the data itself within the established tables using INSERT INTO, UPDATE, and DELETE FROM commands with precise WHERE filters.
Source: Dev.to
Found something wrong in this article? Report an issue with this article
Comments
Leave a Comment