Understanding SQL Data Definition Language(DML)

What is Standard Query Language (SQL)

SQL is the standard language used to communicate with relational databases such as MySQL, PostgreSQL, Microsoft SQL Server, Oracle, and SQLite. Databases organize information into tables made up of rows and columns, making it easier to store and retrieve data efficiently.

Businesses use SQL to manage customer information, employee records, sales transactions, inventory, and many other types of data.

Data Manipulation Language (DML)

Data Manipulation Language (DML) consists of commands used to interact with the data stored in tables.

The most common DML commands include:

• SELECT – Retrieves data from a table.
• INSERT – Adds new records.
• UPDATE – Modifies existing records.
• DELETE – Removes records.

For example, retrieving all employees from a table:

SELECT *
FROM Employees;

These commands form the foundation of everyday database operations.

Essential SQL Keywords

SQL provides several keywords that make querying data more effective.

Some of the most commonly used include:

• FROM – Specifies the table.
• WHERE – Filters records.
• ORDER BY – Sorts results.
• DISTINCT – Removes duplicates.
• LIMIT or TOP – Restricts the number of rows returned.
• AS – Creates aliases for columns.

These keywords help retrieve information accurately and present it in a meaningful way.

Filtering Data with Operators

Filtering allows us to retrieve only the data that meets specific conditions.

Common comparison operators include:

• =
• >
• <
• >=
• <=
• <>

Logical operators such as AND, OR, and NOT allow multiple conditions to be combined.

Other useful operators include:

• BETWEEN
• IN
• LIKE

Example:

SELECT *
FROM Orders
WHERE Amount > 2000
AND Status = 'Completed';

Filtering is one of the most frequently used SQL skills because it helps answer specific business questions.

Combining Tables with SQL Joins

In most databases, information is stored across multiple tables. SQL joins make it possible to combine related data using a common key.

The four main joins are:

• INNER JOIN – Returns matching records from both tables.
• LEFT JOIN – Returns all records from the left table and matching records from the right.
• RIGHT JOIN – Returns all records from the right table and matching records from the left.
• FULL OUTER JOIN – Returns all records from both tables.

Joins are essential for creating comprehensive reports that combine customer, product, employee, and sales information.

Using CASE WHEN Statements

The CASE WHEN statement introduces conditional logic into SQL queries. It works similarly to an IF-ELSE statement in programming languages.

For example:

SELECT CustomerName,
       Amount,
       CASE
           WHEN Amount >= 5000 THEN 'High Value'
           WHEN Amount >= 2000 THEN 'Medium Value'
           ELSE 'Low Value'
       END AS Category
FROM Orders;

CASE WHEN is useful for categorizing data, assigning grades, creating customer segments, and generating business-friendly reports.

Row-Level Functions

Row-level functions operate on individual rows to clean and transform data.

Examples include:

• UPPER()
• LOWER()
• CONCAT()
• ROUND()
• LENGTH()
• TRIM()

These functions improve data consistency and make reports more readable.

Handling NULL Values

Missing data is common in real-world databases. SQL provides several ways to handle NULL values.

Useful functions include:

• IS NULL
• IS NOT NULL
• COALESCE()
• NULLIF()

Replacing missing values before analysis helps improve the accuracy of reports and calculations.

Working with Date and Time

Date and time functions simplify time-based analysis.

Examples include:

• CURRENT_DATE
• CURRENT_TIMESTAMP
• DATEDIFF()
• EXTRACT()
• DATE_FORMAT()

These functions help answer questions such as:

• How many orders were placed this month?
• How long did delivery take?
• Which customers have not purchased recently?

Time-based analysis is essential for tracking trends and business performance.

Using Subqueries

A subquery is a query nested inside another query.

For example, to find products priced above the average:

SELECT ProductName, Price
FROM Products
WHERE Price >
(
    SELECT AVG(Price)
    FROM Products
);

Subqueries make it possible to solve complex problems without manually calculating intermediate results.

Simplifying Queries with Common Table Expressions (CTEs)

Common Table Expressions (CTEs) improve the readability and organization of complex SQL queries.

Example:

WITH CustomerSales AS
(
    SELECT CustomerID,
           SUM(Amount) AS TotalSales
    FROM Orders
    GROUP BY CustomerID
)

SELECT *
FROM CustomerSales
WHERE TotalSales > 50000;

CTEs make large queries easier to understand, maintain, and debug.

Bringing It All Together

Imagine you're working as a Data Analyst for an e-commerce company. Management asks you to:

• Display completed orders only.
• Combine customer and order information.
• Categorize customers based on spending.
• Replace missing contact details.
• Calculate delivery time.
• Identify customers spending above the average.
• Generate a clean sales report.

Using filtering, joins, CASE WHEN, row-level functions, NULL handling, date functions, subqueries, and CTEs, you can answer all these questions with efficient and readable SQL queries.