SQL Data Manipulation (DML): INSERT, UPDATE, DELETE, and SELECT Queries

What is Data Manipulation Language (DML)? (In Simple Words)

While DDL sets up the empty structure and containers of a database (like tables and columns), DML (Data Manipulation Language) is what we use to actually put records into those tables, modify them, remove them, and read them back.

1. Database Change Operations: INSERT, UPDATE, and DELETE

A. Inserting Data (INSERT INTO)

There are two primary ways to insert data into a table:

-- Method 1: Specify both column names and values (Recommended best practice)
INSERT INTO Student (Roll_No, Student_Name, Email, Age, Dept_ID)
VALUES (101, 'Aman Verma', '[email protected]', 20, 1);

-- Method 2: Insert values in the exact positional order of table columns
INSERT INTO Student
VALUES (102, 'Priya Sharma', '[email protected]', 19, 'F', '2026-07-01', 2);

-- Method 3: Multi-row insertion in a single SQL statement
INSERT INTO Student (Roll_No, Student_Name, Age, Dept_ID) VALUES
(103, 'Rohan Mehta', 21, 1),
(104, 'Sneha Patel', 20, 2),
(105, 'Vikram Singh', 22, 1);

B. Modifying Existing Data (UPDATE)

The UPDATE statement changes values in existing rows. Always specify a WHERE clause, otherwise EVERY row in the entire table will be updated!

-- Example 1: Update the email of a specific student
UPDATE Student
SET Email = '[email protected]'
WHERE Roll_No = 101;

-- Example 2: Give a 10% salary increase to all employees in Department 10
UPDATE Employee
SET Salary = Salary * 1.10
WHERE Dept_ID = 10;

C. Removing Data (DELETE)

The DELETE FROM statement removes rows from a table. If you omit the WHERE clause, all rows in the table are deleted!

-- Example 1: Delete a specific student record
DELETE FROM Student
WHERE Roll_No = 105;

-- Example 2: Delete all students who have not completed admission
DELETE FROM Student
WHERE Admission_Date IS NULL;

2. Querying Data: The Fundamental SELECT Statement

The SELECT statement is the most widely executed statement in database computing.

Basic Syntax:

SELECT Column1, Column2, ...
FROM Table_Name
WHERE Condition;

Examples:

-- Select all columns from the Student table
SELECT * FROM Student;

-- Select specific columns and apply an alias (AS)
SELECT Student_Name AS Name, Email AS Student_Email
FROM Student;

-- Eliminate duplicate rows using DISTINCT
SELECT DISTINCT City FROM Student;

3. Filtering Rows with the WHERE Clause

The WHERE clause uses comparison and logical operators to filter records:

A. Comparison Operators (=, != or <>, <, <=, >, >=)

SELECT * FROM Student WHERE Age >= 20;

B. Logical Operators (AND, OR, NOT)

-- Both conditions must be met
SELECT * FROM Student 
WHERE Dept_ID = 1 AND Age > 19;

-- At least one condition must be met
SELECT * FROM Student 
WHERE City = 'Indore' OR City = 'Bhopal';

4. Special SQL Filtering Operators

A. The BETWEEN Operator (Range Testing)

Tests whether a value falls within an inclusive numerical or date range:

SELECT * FROM Student 
WHERE Age BETWEEN 18 AND 22;
-- Equivalent to: WHERE Age >= 18 AND Age <= 22

B. The IN Operator (Set Membership)

Tests whether a value matches any value in a specified list:

SELECT * FROM Student 
WHERE City IN ('Indore', 'Ujjain', 'Bhopal', 'Dewas');

C. The LIKE Operator (Pattern Matching with Wildcards)

Used to search for string patterns using two standard wildcards:

1. Percent sign (%): Matches zero, one, or multiple characters.
2. Underscore (_): Matches strictly one single character.

-- Finds all names starting with 'A' (e.g. Aman, Amit, Ananya)
SELECT * FROM Student WHERE Student_Name LIKE 'A%';

-- Finds all names ending with 'a' (e.g. Priya, Sneha)
SELECT * FROM Student WHERE Student_Name LIKE '%a';

-- Finds all names containing 'sh' anywhere (e.g. Sharma, Vishal)
SELECT * FROM Student WHERE Student_Name LIKE '%sh%';

-- Finds names with exactly 4 letters starting with 'A' (e.g. Amit, Aman)
SELECT * FROM Student WHERE Student_Name LIKE 'A___';

D. Testing for NULL Values (IS NULL / IS NOT NULL)

You cannot use = NULL because in SQL, NULL represents an unknown state, and comparing anything to NULL yields UNKNOWN!

-- Correct way to find records with missing data:
SELECT * FROM Student WHERE Email IS NULL;

-- Correct way to find records with valid data:
SELECT * FROM Student WHERE Email IS NOT NULL;

💡 DAVV Exam Preparation Tips

5-Mark Question: “Explain pattern matching in SQL using the LIKE operator. Differentiate between % and _ wildcards with examples.”

• Define LIKE for pattern searching.
• Explain % (0 or more characters) with 'A%' and '%gmail.com'.
• Explain _ (exactly 1 character) with 'A___' (4-letter name).

10-Mark Question: “Explain Data Manipulation Language (DML) statements: INSERT, UPDATE, and DELETE. Write SQL queries using WHERE, BETWEEN, IN, LIKE, and IS NULL operators on an EMPLOYEE table.”

• Give clear syntax and examples for INSERT INTO, UPDATE ... SET ... WHERE, and DELETE FROM ... WHERE.
• Write out 5 distinct query examples showing:
  1. Salary range using BETWEEN
  2. Department filtering using IN
  3. Name pattern using LIKE
  4. Missing manager check using IS NULL.