6. SQL DML-Functions

DML Functions in SQL

DML (“Data Manipulation Language”) functions are special functions provided by SQL to perform operations on data within the database. These functions can be used in various SQL statements such as SELECT, INSERT, UPDATE, and DELETE to manipulate and retrieve data effectively.

In this chapter, we will explore some of the most commonly used DML functions in SQL. After basic table operations, these commands are essential for working with data in a relational database.

The approach follows the well known CRUD paradigm:

• Create - Inserting new data into tables using the INSERT statement.
• Read - Retrieving data from tables using the SELECT statement.
• Update - Modifying existing data in tables using the UPDATE statement.
• Delete - Removing data from tables using the DELETE statement.

The example table we will use throughout this chapter is as follows:

PostgreSQL

CREATE TABLE students (
  student_id   SERIAL PRIMARY KEY,
  first_name   VARCHAR(50),
  last_name    VARCHAR(50),
  email        VARCHAR(320) UNIQUE NOT NULL,
  birth_date   DATE,
  class        VARCHAR(10)
);

MariaDB / MySQL

CREATE TABLE students (
  student_id INT PRIMARY KEY AUTO_INCREMENT,
  first_name VARCHAR(50),
  last_name  VARCHAR(50),
  email      VARCHAR(320) UNIQUE NOT NULL,
  birth_date DATE,
  class      VARCHAR(10)
);

Insert Data

A database table can be populated with data using the INSERT INTO statement. The basic syntax is as follows:

INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...) [, (value1, value2, value3, ...), ...];

Example - Inserting a new student into the students table:

INSERT INTO students (first_name, last_name, email, birth_date, class)
VALUES ('Alice', 'Kingsleigh', '[email protected]', '2010-12-17', '4A');

If not all values are known at the time of insertion, the columns can be omitted, and the database will assign default values (e.g., NULL or auto-incremented IDs):

Example - Inserting a new student with only the mandatory email field:

INSERT INTO students (first_name, last_name, email)
VALUES ('Alice', 'Kingsleigh', '[email protected]');

You may also rearrange the order of columns in the INSERT statement as long as the values correspond correctly.

Example - Inserting a new student with rearranged columns:

INSERT INTO students (email, last_name, first_name, class)
VALUES ('[email protected]', 'Kingsleigh', 'Alice', '4A');

Finally, you can insert multiple rows in a single INSERT statement by separating the value sets with commas:

Example - Inserting multiple students at once:

INSERT INTO students (first_name, last_name, email, birth_date, class)
VALUES
  ('Alice', 'Kingsleigh', '[email protected]', '2010-12-17', '4A'),
  ('Edwin', 'Hatter', '[email protected]', '2011-05-23', '4B'),
  ('Reginald', 'White-Rabbit', '[email protected]', '2010-08-15', '4A'),
  ('Quennie', 'Heart', '[email protected]', '2010-11-30', '4B');

Read Data

To retrieve data from a database table, the SELECT statement is used. The basic syntax is as follows:

SELECT column1, column2, column3, ...
FROM table_name
[WHERE condition];

Note

This is the most basic form of the SELECT statement. More advanced features such as joins, aggregations, and subqueries will be covered in later chapters.

Data retrieval is a vast topic, and this section serves as an introduction to the fundamental concepts.

Example - Selecting all students:

SELECT student_id, first_name, last_name, email, birth_date, class
FROM students;

If you want to select all columns from a table, you can use the asterisk (*) wildcard:

SELECT *
FROM students;

The WHERE clause allows you to filter the results based on specific conditions.

Example - Selecting students from class ‘4A’:

SELECT *
FROM students
WHERE class = '4A';

The WHERE clause can also use various operators such as =, <>, <, >, <=, >=, LIKE, IN, BETWEEN, EXISTS, NOT EXISTS, and logical operators like AND, OR, and NOT to create complex conditions.

Example - Selecting students born after January 1, 2011, in class ‘4B’:

SELECT *
FROM students
WHERE birth_date > '2011-01-01' AND class = '4B';

Example - Selecting students with last names starting with ‘K’:

SELECT *
FROM students
WHERE last_name LIKE 'K%';

Example - Selecting student’s name and email whose name is not ‘Alice’:

SELECT first_name, last_name, email
FROM students
WHERE first_name <> 'Alice';

Example - Selecting email addresses of students which are in class 4A or 4B:

SELECT email
FROM students
WHERE class IN ('4A', '4B');

Example - Selecting all students born in the year 2010:

SELECT *
FROM students
WHERE birth_date BETWEEN '2010-01-01' AND '2010-12-31';

Table aliases

In the examples above, we have used the full table name students in the FROM clause. However, when working with more complex queries, especially those involving multiple tables, it can be helpful to use table aliases to simplify the query and improve readability.

A table alias is a temporary name given to a table in a SQL query. It is defined using the AS keyword (though AS is optional in most cases). Once an alias is defined, you can refer to the table using the alias instead of the full table name.

Example - Using a table alias for the students table:

SELECT s.first_name, s.last_name, s.email
FROM students AS s
WHERE s.class = '4A';

In this example, we have assigned the alias s to the students table. This allows us to refer to the columns of the students table using the alias s, making the query more concise and easier to read.

`EXISTS` and `NOT EXISTS`

The EXISTS operator is used to check if a subquery returns any rows. It returns TRUE if the subquery returns one or more rows, and FALSE if it returns no rows.

This involves subqueries, which are nested queries that can be used to perform more complex data retrieval. We will cover subqueries in a later chapter, but here is a simple example to illustrate the concept:

Select all students who are enrolled in a course with the ID 101:

SELECT *
FROM students s
WHERE EXISTS (
  SELECT 1
  FROM enrollments e
  WHERE e.student_id = s.student_id AND e.course_id = 101
);

Select all students who are not enrolled in any course:

SELECT *
FROM students s
WHERE NOT EXISTS (
  SELECT 1
  FROM enrollments e
  WHERE e.student_id = s.student_id
);

Sorting Results with ORDER BY

By default, a database may return rows in any order — there is no guarantee that they come back the way you inserted them. To get a predictable order, add an ORDER BY clause at the end of your query.

SELECT first_name, last_name, birth_date
FROM students
ORDER BY last_name;

By default, sorting is ascending (ASC): A → Z for text, small → large for numbers, oldest → newest for dates. To reverse it, use DESC (descending).

Example - Youngest student first (most recent birth date on top):

SELECT first_name, last_name, birth_date
FROM students
ORDER BY birth_date DESC;

You can also sort by several columns. The result is sorted by the first column; rows that share the same value are then sorted by the next column.

Example - Sort by class first, and within each class alphabetically by last name:

SELECT class, last_name, first_name
FROM students
ORDER BY class ASC, last_name ASC;

Removing Duplicates with DISTINCT

Sometimes the same value appears in many rows, but you only want to see each value once. The keyword DISTINCT removes duplicate rows from the result.

Example - Without DISTINCT, every student’s class is listed, so “4A” and “4B” appear many times:

SELECT class
FROM students;

Example - With DISTINCT, each class is listed only once:

SELECT DISTINCT class
FROM students;

DISTINCT looks at all selected columns together. SELECT DISTINCT class, first_name keeps every unique combination of class and first name, not just unique classes.

Limiting the Number of Rows with LIMIT

Tables can hold thousands or even millions of rows. Often you only want to see a few — for example the “top 3”, or just a quick peek at the data. The LIMIT clause restricts how many rows are returned.

Example - Return at most 5 students:

SELECT *
FROM students
LIMIT 5;

LIMIT is especially useful together with ORDER BY to answer “top N” questions.

Example - The three youngest students:

SELECT first_name, last_name, birth_date
FROM students
ORDER BY birth_date DESC
LIMIT 3;

LIMIT across dialects

LIMIT works in PostgreSQL, MariaDB, and MySQL. To skip rows (for example to show “page 2” of a result), you can add OFFSET:

SELECT * FROM students
ORDER BY last_name
LIMIT 10 OFFSET 10;   -- rows 11–20

Some other systems use a different syntax (for example, Microsoft SQL Server uses SELECT TOP 5 ...). Always check the documentation of the database you are using.

Update Data

To modify existing data in a database table, the UPDATE statement is used. The basic syntax is as follows:

UPDATE table_name
SET column1 = value1, column2 = value2, ...
[WHERE condition];

Example - Updating a student’s email address:

UPDATE students
SET email = '[email protected]'
WHERE student_id = 1;

You can update more than one row at a time by specifying a condition in the WHERE clause which matches multiple records.

Example - Updating the class of all students born before 2011 to ‘3A’:

UPDATE students
SET class = '3A'
WHERE birth_date < '2011-01-01';

Also more than one column can be updated in a single UPDATE statement.

Example - Updating both the first name and last name of a student:

UPDATE students
SET first_name = 'Alicia', last_name = 'King', email = '[email protected]'
WHERE student_id = 1;

Delete Data

To remove data from a database table, the DELETE statement is used. The basic syntax is as follows:

DELETE FROM table_name
[WHERE condition];

Example - Deleting a student by their ID:

DELETE FROM students
WHERE student_id = 1;

You can delete multiple rows by specifying a condition in the WHERE clause that matches multiple records.

Example - Deleting all students from class ‘4A’:

DELETE FROM students
WHERE class = '4A';

Caution

Be very careful when using the DELETE statement without a WHERE clause, as it will remove all rows from the specified table.

Example - Deleting all records from the students table:

DELETE FROM students;

SQL Functions

So far we have read values exactly as they are stored. But SQL can also transform values while it reads them. A function takes one or more input values and returns a new value. Functions can be used almost anywhere you can write a column name — most often in the SELECT list (to change how a value is displayed) or in the WHERE clause (to filter on a calculated value).

The original data in the table is never changed by these functions — only the result of the query is affected.

Functions differ between database systems

Function names are one of the biggest differences between SQL dialects. The examples below show the most common names. If a function does not work in your system, look up its equivalent in the documentation — the idea is almost always the same.

Text Functions

These functions work on strings (text).

Function	Description	Example	Result
UPPER(text)	Converts to upper case	UPPER('Alice')	'ALICE'
LOWER(text)	Converts to lower case	LOWER('Alice')	'alice'
LENGTH(text)	Number of characters	LENGTH('Alice')	5
TRIM(text)	Removes leading/trailing spaces	TRIM(' hi ')	'hi'
SUBSTRING(text, start, length)	Extracts part of a string	SUBSTRING('Database', 1, 4)	'Data'

Joining (concatenating) two strings is so common it has its own operator. In PostgreSQL, MariaDB, and MySQL you can use ||; many systems (and the SQL standard) also offer the CONCAT(...) function:

-- Display the full name as a single column
SELECT first_name || ' ' || last_name AS full_name
FROM students;

-- The same using CONCAT (works in MariaDB/MySQL and PostgreSQL)
SELECT CONCAT(first_name, ' ', last_name) AS full_name
FROM students;

Example - Find students whose last name starts with “k”, regardless of upper or lower case:

SELECT first_name, last_name
FROM students
WHERE LOWER(last_name) LIKE 'k%';

Numeric Functions

These functions work on numbers.

Function	Description	Example	Result
ROUND(num, digits)	Rounds to a number of decimals	ROUND(3.14159, 2)	3.14
ABS(num)	Absolute value (removes the sign)	ABS(-7)	7
CEIL(num)	Rounds up to the next integer	CEIL(4.1)	5
FLOOR(num)	Rounds down to the next integer	FLOOR(4.9)	4
MOD(a, b)	Remainder of a division	MOD(10, 3)	1

Example - Show each product price rounded to whole euros (using a products table):

SELECT name, ROUND(price, 0) AS rounded_price
FROM products;

Date and Time Functions

These functions help you work with dates — for example, to read the year out of a date, or to get today’s date.

Function	Description
CURRENT_DATE	Today’s date
NOW()	Current date and time
EXTRACT(YEAR FROM date)	Reads a part (year, month, day) out of a date

Reading the year out of a date is a good example of a dialect difference:

-- PostgreSQL (SQL standard)
SELECT first_name, EXTRACT(YEAR FROM birth_date) AS birth_year
FROM students;

-- MariaDB / MySQL (shorter helper function)
SELECT first_name, YEAR(birth_date) AS birth_year
FROM students;

Example - Find all students who were born in the year 2010:

-- PostgreSQL
SELECT first_name, last_name
FROM students
WHERE EXTRACT(YEAR FROM birth_date) = 2010;

-- MariaDB / MySQL
SELECT first_name, last_name
FROM students
WHERE YEAR(birth_date) = 2010;

Functions and performance

Wrapping a column in a function inside a WHERE clause (for example WHERE YEAR(birth_date) = 2010) forces the database to calculate the function for every single row. For small, school-sized tables this is no problem at all. For very large tables there are often faster alternatives (such as WHERE birth_date BETWEEN '2010-01-01' AND '2010-12-31'), but readability comes first while you are learning.

This is only a small selection

The functions shown here are just a tiny fraction of what SQL offers. Each database system ships with many more — for string handling, math, dates, JSON, and much more. When you need something specific, look it up in the official reference for your system:

• PostgreSQL: Functions and Operators
• MariaDB: Built-in Functions

---

This concludes our introduction to data manipulation in SQL. You can now insert, read (including sorting with ORDER BY, removing duplicates with DISTINCT, and limiting results with LIMIT), update, and delete data — and reshape values on the fly with built-in functions. In the following chapters, we will explore more advanced SQL features and techniques for working with relational databases.

Learning Outcomes: What you should be able to do after this chapter

After completing this chapter, students should be able to:

• Name: name the DML commands INSERT, SELECT, UPDATE and DELETE.
• Describe: describe the structure of a SELECT query and the effect of the DML commands.
• Apply: insert, query, update and delete data and apply SQL functions (string, date and numeric functions).
• Analyze: analyze the effect of an UPDATE or DELETE statement without a WHERE clause.
• Evaluate: assess which SQL function is suitable for a given data preparation task.