SQL DML Commands (Chapter 3)

Data Manipulation Language (DML) is a crucial aspect of SQL, allowing users to interact with databases through Create, Read, Update, and Delete (CRUD) operations. This concise guide explores DML basics and empowers readers to proficiently manipulate data, retrieve insights, and maintain data integrity using SQL’s powerful DML commands.

DML stands for “Data Manipulation Language” in SQL. Unlike DDL (Data Definition Language) which deals with the database’s structure, DML commands are used to manipulate the data within the database. These commands allow you to insert, update, delete, and retrieve data from database tables. The primary purpose of DML is to perform operations that affect the data content.

CRUD (Create, Read, Update, Delete) and DML are closely connected concepts in the context of databases and SQL. CRUD operations are the fundamental actions performed on data, while DML commands are the SQL statements used to perform those operations.

Common DML commands include:

• SELECT (Used to retrieve data from one or more tables in the database.)
• INSERT (Used to insert new rows (records) into a table.)
• UPDATE (Used to modify existing data in a table.)
• DELETE (Used to remove rows from a table.)

Let’s understand them in more depth.

SELECT

The “SELECT” command is used to retrieve data from one or more tables in a database. It is one of the most commonly used SQL statements and allows you to query and filter data based on specific conditions.

SELECT * FROM table_name  --> Retriving all data
-------------------------
SELECT column_name FROM table_name  --> Retriving specific col data
-------------------------
SELECT column1, column2, ... FROM table_name  --> Retriving multiple col data

Filtering Column using SELECT

SELECT * FROM table_name WHERE condition
---------------------------------------
Ex
SELECT * FROM student_table WHERE cgpa > 7.5

ALIAS | Renaming Column with SELECT

SELECT column_name AS alias_name
FROM table_name;
---------------------------------
SELECT cgpa AS 'grade' 
FROM student_table 

DISTINCT value from a Column

SELECT DISTINCT(col_name) FROM table_name; 

INSERT

The “INSERT” command is used to add new records (rows) into a table. It allows us to insert data into specific columns of a table.

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

UPDATE

The “UPDATE” command in MySQL is used to modify existing records in a table. It allows you to change the values of one or more columns in one or multiple rows based on specified conditions.

UPDATE table_name
SET column_name = value
WHERE condition;

Updating multiple columns

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

DELETE

The “DELETE” command in MySQL is used to remove rows from a table based on specified conditions. It allows you to delete one or multiple rows that meet certain criteria.

DELETE FROM table_name
WHERE condition;
-----------------------
EXAMPLE
DELETE FROM students                        
WHERE student_id = 2;

SQL Functions

SQL provides a wide range of built-in functions that allow you to perform various operations on data. These functions are categorized into several types, each serving a specific purpose. Here are some common SQL functions:

• COUNT(): Counts the number of rows or non-null values in a column.
• SUM(): Calculates the sum of values in a numeric column.
• AVG(): Calculates the average of values in a numeric column.
• MIN(): Finds the minimum value in a column.
• MAX(): Finds the maximum value in a column.
• ABS(): Returns the absolute value of a numeric value.
• ROUND(): Rounds a numeric value to the nearest whole number or a specified decimal position.
• CEIL() or CEILING(): Rounds up a numeric value to the nearest integer.
• FLOOR(): Rounds down a numeric value to the nearest integer.
• POWER() or POW(): Raises a numeric value to a specified power.

SELECT product_name, SUM(quantity * price_per_unit) AS total_sales
FROM sales
GROUP BY product_name;

These are just some of the common SQL functions. Different database management systems may offer additional proprietary functions or slight variations in the function names and syntax, but the above functions are widely supported in most SQL implementations.