DML: SELECT, INSERT, UPDATE, DELETE

1. What is DML?

• DML (Data Manipulation Language): Commands used to retrieve, insert, update, or delete data in a database.
• Key Commands:
  • SELECT
  • INSERT
  • UPDATE
  • DELETE

---

2. SELECT Command

• Purpose: Retrieves data from one or more tables.
• Syntax: SELECT column1, column2, ... FROM table_name WHERE condition;
• Examples:
  • Retrieve all columns: SELECT * FROM Students;
  • Retrieve specific columns: SELECT Name, Age FROM Students;
  • Filtered data with WHERE: SELECT * FROM Students WHERE Age > 18;
• Key Points:
  • Can use filters (WHERE), sorting (ORDER BY), and grouping (GROUP BY).
  • Supports aggregate functions like COUNT, SUM, AVG.

---

3. INSERT Command

• Purpose: Adds new records to a table.
• Syntax: INSERT INTO table_name (column1, column2, ...) VALUES (value1, value2, ...);
• Examples:
  • Insert a single row: INSERT INTO Students (ID, Name, Age) VALUES (1, 'John', 20);
  • Insert multiple rows: INSERT INTO Students (ID, Name, Age) VALUES (2, 'Jane', 22), (3, 'Mike', 19);
• Key Points:
  • Use column names for clarity.
  • If all columns are provided, column names can be omitted.

---

4. UPDATE Command

• Purpose: Modifies existing records in a table.
• Syntax: UPDATE table_name SET column1 = value1, column2 = value2, ... WHERE condition;
• Examples:
  • Update a specific record: UPDATE Students SET Age = 21 WHERE ID = 1;
  • Update multiple records: UPDATE Students SET Age = Age + 1 WHERE Age < 20;
• Key Points:
  • Use WHERE to avoid updating all rows.
  • If WHERE is omitted, all rows will be updated.

---

5. DELETE Command

• Purpose: Removes records from a table.
• Syntax: DELETE FROM table_name WHERE condition;
• Examples:
  • Delete specific records: DELETE FROM Students WHERE Age < 18;
  • Delete all records: DELETE FROM Students;
• Key Points:
  • Use WHERE to avoid deleting all rows.
  • Unlike DROP, DELETE retains the table structure.

---

Comparison Table

Command	Purpose	Effect
SELECT	Retrieves data	Returns data without modifying it.
INSERT	Adds new data	Inserts new rows into a table.
UPDATE	Modifies existing data	Changes values in existing rows.
DELETE	Removes data	Deletes rows from a table but keeps structure.

---

Exam Tips

1. Memorize syntax for each command.
2. Practice scenarios like filtering (WHERE) and updating multiple rows.
3. Remember: DELETE removes rows; DROP removes the entire table.
4. Understand the use of SELECT with filters and functions.

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

• X
• Instagram
• WhatsApp