Which of these is not a DML command?

In the context of SQL, To determine which command is not a DML command, it is very important to understand first what is the basic difference between Data manipulation language and Data definition language.

  DDL(DATA MANIPULATION LANGUAGE)

Commands which are used in manipulating the data within the database table are called DML. These command includes SELECT, INSERT, UPDATE AND DELETE

  DDL (DATA DEFINITION LANGUAGE)

The commands are used to define and manage the database structure. These commands include CREATE, ALTER, DROP, TRUNCATE

Given the list of commands, CREATE is one of the commands that doesn’t belong to the category of DML as it is a DDL command.

Code example

DML command

SELECT

Retrieve employee names and their respective department names

SELECT e.first_name, e.last_name, d.department_name, AVG(s.salary) as average_salary
FROM employees e
JOIN departments d ON e.department_id = d.department_id
JOIN salaries s ON e.employee_id = s.employee_id
GROUP BY e.first_name, e.last_name, d.department_name
ORDER BY average_salary DESC;
INSERT
Insert multiple new employees into the ‘employees’ table
INSERT INTO employees (employee_id, first_name, last_name, department_id)
VALUES 
    (102, ‘Jane’, ‘Smith’, 1),
    (103, ‘Michael’, ‘Brown’, 2),
    (104, ‘Emily’, ‘Davis’, 3);
UPDATE
Update the department of employees who work under a specific manager
UPDATE employees
SET department_id = (
    SELECT department_id
    FROM departments
    WHERE manager_id = 201
)
WHERE department_id = (
    SELECT department_id
    FROM departments
    WHERE manager_id = 301
);
DELETE
Delete employees who have not been assigned a project
DELETE e
FROM employees e
LEFT JOIN project_assignments pa ON e.employee_id = pa.employee_id
WHERE pa.project_id IS NULL;
DDL COMMAND
CREATE
Create a new ‘employees’ table with constraints
CREATE TABLE employees (
    Employee_id INT PRIMARY KEY,
    First_name VARCHAR(50) NOT NULL,
    Last_name VARCHAR(50) NOT NULL,
    Department_id INT,
    Hire_date DATE,
    Salary DECIMAL(10, 2),
    CONSTRAINT fk_department
        FOREIGN KEY (department_id) 
        REFERENCES departments(department_id)
);
Create a new ‘departments’ table
CREATE TABLE departments (
    Department_id INT PRIMARY KEY,
    Department_name VARCHAR(50) NOT NULL,
    Manager_id INT
);
ALTER
Add a new column to the ‘employees’ table
ALTER TABLE employees
ADD COLUMN email VARCHAR(100);
Modify an existing column to change its data type
ALTER TABLE employees
MODIFY COLUMN salary DECIMAL(15, 2);
DROP
Drop the ‘project_assignments’ table if it exists
DROP TABLE IF EXISTS project_assignments;
TRUNCATE TABLE
Remove all rows from the ‘salaries’ table but keep the table structure
TRUNCATE TABLE salaries;