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;