Introduction
Structured Query Language (SQL) is essential for managing and manipulating data in relational databases. In this article, we explore two important categories of SQL commands—Data Definition Language (DDL) and Data Manipulation Language (DML)—along with practical operations such as CREATE, INSERT, UPDATE, DELETE, filtering with WHERE, and the use of CASE WHEN for data transformation.
- What DDL and DML Are (and Their Differences)
Data Definition Language (DDL) refers to SQL commands used to define and manage the structure of a database. These commands deal with creating, modifying, and deleting database objects such as tables.
Common DDL commands include
- CREATE – used to create new tables or databases
- ALTER – used to modify existing structures
- DROP– used to delete tables or databases
For example, using "CREATE TABLE" allows you to define columns, data types, and constraints for storing data.
Data Manipulation Language (DML), on the other hand, is used to manage the data within those structures. It focuses on inserting, updating, retrieving, and deleting records.
Common DML commands include:
- INSERT – adds new records
- UPDATE– modifies existing data
- DELETE – removes records
- SELECT– retrieves data
Key Difference:
DDL deals with the structure of the database, while DML deals with the data inside the database.
- Using CREATE, INSERT, UPDATE, and DELETE
In a typical database assignment, these commands are used as follows:
CREATE: I used this command to define tables such as students, subjects, or exam results. It involved specifying column names, data types (e.g., INTEGER, VARCHAR), and primary keys.
INSERT: This command was used to add records into the tables. For example, inserting student names, subject details, and exam scores into the respective tables.
UPDATE: I used UPDATE to modify existing records. For instance, correcting a student’s score or updating a subject name.
DELETE: This command helped remove unwanted or incorrect records from the database, such as deleting a student entry or clearing outdated data.
These commands are fundamental for maintaining accurate and up-to-date data in any database system.
- Filtering Data with WHERE
The WHERE clause is used in SQL to filter records based on specific conditions. It helps retrieve only the data that meets certain criteria.
Some commonly used operators include:
= (Equal to): Selects records that match a specific value
Example: "WHERE score = 80"> (Greater than): Selects values greater than a given number
Example: "WHERE score > 70"BETWEEN: Filters values within a range
Example: "WHERE score BETWEEN 50 AND 90"IN: Matches values within a list
Example: "WHERE subject IN ('Math', 'Science')"LIKE: Used for pattern matching
Example: "WHERE name LIKE 'J%'" (names starting with J)
The WHERE clause is powerful because it allows precise data retrieval, making queries more meaningful and efficient.
- How CASE WHEN Helps in Transforming Data The CASE WHEN statement in SQL is used to perform conditional logic within queries. It allows you to transform data by assigning values based on conditions.
For example, you can categorize student performance:
- If score ≥ 70 → “Pass”
- If score < 70 → “Fail”
This helps in:
- Creating calculated columns
- Categorizing or grouping data
- Improving readability of query results
CASE WHEN is especially useful in reports, where raw data needs to be interpreted into meaningful insights.
Conclusion
Understanding DDL and DML is crucial for working with databases effectively. While DDL defines the structure of the database, DML allows you to interact with the data itself. Commands like CREATE, INSERT, UPDATE, and DELETE form the backbone of database operations. Additionally, tools like the WHERE clause and CASE WHEN statement enhance your ability to filter and transform data, making SQL a powerful language for data management and analysis.
Top comments (0)