DML: SELECT, INSERT, UPDATE, DELETE

Commands used to retrieve, add, modify, or delete data in a database.

Main Commands:

  • SELECT → Retrieve data
  • INSERT → Add data
  • UPDATE → Modify data
  • DELETE → Remove data

1. SELECT (Retrieve Data)

Syntax:

SELECT column1, column2 FROM table_name WHERE condition;

Examples:

  • All columns → SELECT * FROM Students;
  • Specific columns → SELECT Name, Age FROM Students;
  • With condition → SELECT * FROM Students WHERE Age > 18;

Key Points:

  • Filtering → WHERE
  • Sorting → ORDER BY
  • Grouping → GROUP BY
  • Aggregate functions → COUNT, SUM, AVG

2. INSERT (Add Data)

Syntax:

INSERT INTO table_name (col1, col2, ...) VALUES (val1, val2, ...);

Examples:

  • Single row → INSERT INTO Students (ID, Name, Age) VALUES (1, 'John', 20);
  • Multiple rows →
    INSERT INTO Students (ID, Name, Age) VALUES (2, 'Jane', 22), (3, 'Mike', 19);

Key Points:

  • Always mention column names (clear & safe).
  • If inserting all columns, column names can be skipped.

3. UPDATE (Modify Data)

Syntax:

UPDATE table_name SET col1 = val1, col2 = val2 WHERE condition;

Examples:

  • Update one row → UPDATE Students SET Age = 21 WHERE ID = 1;
  • Update many rows → UPDATE Students SET Age = Age + 1 WHERE Age < 20;

Key Points:

  • Always use WHERE (otherwise all rows get updated!).

4. DELETE (Remove Data)

Syntax:

DELETE FROM table_name WHERE condition;

Examples:

  • Delete some rows → DELETE FROM Students WHERE Age < 18;
  • Delete all rows → DELETE FROM Students;

Key Points:

  • Use WHERE carefully (otherwise all rows get deleted).
  • Table structure remains (unlike DROP).

Quick Comparison

CommandPurposeEffect
SELECTRetrieve dataReturns rows (no change in table)
INSERTAdd dataInserts new rows
UPDATEModify dataChanges existing rows
DELETERemove dataDeletes rows (table stays)

MCQ

Q1: What does DML stand for?
A. Data Modification Language
B. Data Manipulation Language
C. Data Management Language
D. Data Migration Language

Answer: B

Q2: Which of the following is NOT a DML command?
A. SELECT
B. INSERT
C. UPDATE
D. CREATE

Answer: D

Q3: What is the purpose of the SELECT command?
A. To retrieve data from one or more tables.
B. To modify data in a table.
C. To delete records from a table.
D. To create a new table.

Answer: A

Q4: Which of the following is the correct syntax to select all columns from a table named Employees?
A. SELECT * FROM Employees;
B. SELECT ALL FROM Employees;
C. SELECT ALL COLUMNS FROM Employees;
D. FETCH ALL FROM Employees;

Answer: A

Q5: What does the WHERE clause in a SELECT statement do?
A. Specifies the table to select data from.
B. Filters rows based on a specified condition.
C. Groups rows based on a column.
D. Orders rows in ascending or descending order.

Answer: B

Q6: Which SQL command retrieves the total number of rows in a table?
A. SELECT COUNT(*) FROM table_name;
B. SELECT SUM(*) FROM table_name;
C. SELECT TOTAL FROM table_name;
D. SELECT ROWS(*) FROM table_name;

Answer: A

Q7: What does the INSERT command do?
A. Updates existing rows in a table.
B. Deletes rows from a table.
C. Adds new rows to a table.
D. Modifies the structure of a table.

Answer: C

Q8: Which of the following is a valid INSERT command?
A. INSERT INTO Students (Name, Age) VALUES ('John', 20);
B. ADD INTO Students (Name, Age) ('John', 20);
C. INSERT INTO Students VALUES Name, Age ('John', 20);
D. INSERT INTO Students (Name, Age) ('John', 20);

Answer: A

Q9: If you omit column names in an INSERT command, what must you do?
A. Use a DEFAULT clause.
B. Provide values for all columns in the table.
C. Only insert values into NOT NULL columns.
D. Provide values for primary key columns only.

Answer: B

Q10: What is the main purpose of the UPDATE command?
A. To add new data to a table.
B. To delete data from a table.
C. To modify existing rows in a table.
D. To retrieve data from a table.

Answer: C

Q11: Which of the following is a valid UPDATE statement?
A. UPDATE Students SET Age = 21 WHERE ID = 1;
B. UPDATE Students MODIFY Age = 21 WHERE ID = 1;
C. MODIFY Students SET Age = 21 WHERE ID = 1;
D. CHANGE Students SET Age = 21 WHERE ID = 1;

Answer: A

Q12: What happens if you use an UPDATE statement without a WHERE clause?
A. Only the first row is updated.
B. The operation fails with an error.
C. All rows in the table are updated.
D. The UPDATE statement is ignored.

Answer: C

Q13: What is the main purpose of the DELETE command?
A. To delete a table’s structure and data.
B. To delete specific rows from a table.
C. To delete all columns from a table.
D. To delete duplicate rows from a table.

Answer: B

Q14: How can you delete all rows from a table but keep its structure intact?
A. DELETE * FROM table_name;
B. TRUNCATE TABLE table_name;
C. DROP TABLE table_name;
D. DELETE FROM table_name;

Answer: D

Q15: Which of the following is TRUE about the DELETE command?
A. It deletes the entire table, including its structure.
B. It cannot use a WHERE clause.
C. It deletes rows but retains the table structure.
D. It always deletes all rows from a table.

Answer: C

Q16: Which combination of DML commands is needed to retrieve, insert, and update data?
A. SELECT, INSERT, UPDATE
B. CREATE, DELETE, SELECT
C. DROP, INSERT, SELECT
D. SELECT, DELETE, ALTER

Answer: A

Q17: Which of the following is the correct order for using WHERE in SELECT, UPDATE, and DELETE commands?
A. Always optional.
B. Mandatory for SELECT, optional for UPDATE and DELETE.
C. Optional for all three commands.
D. Optional for SELECT, mandatory for UPDATE and DELETE when targeting specific rows.

Answer: D

Q18: Which statement about DML commands is FALSE?
A. SELECT can use aggregate functions like SUM and COUNT.
B. INSERT can add multiple rows in a single command.
C. UPDATE can modify data in multiple rows at once.
D. DELETE removes the table’s structure along with its data.

Answer: D

Exam Tips

  1. Understand the syntax of each command.
  2. Learn when and how to use WHERE to target specific rows.
  3. Know the difference between DELETE and DROP.
  4. Remember that TRUNCATE deletes all rows but keeps the structure, while DELETE can be selective.