GyanDesk

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)

✅ MCQs

🔶 SECTION A — BASIC DEFINITIONS

1. SELECT, INSERT, UPDATE, DELETE belong to:

A. DDL
B. DML
C. TCL
D. DCL
Answer: B

2. DML stands for:

A. Data Managing Language
B. Data Manipulation Language
C. Direct Memory Language
D. Data Model Language
Answer: B

3. Which DML command is used to read data?

A. INSERT
B. SELECT
C. UPDATE
D. DELETE
Answer: B

4. INSERT command is used to:

A. Add new data
B. Modify data
C. Delete data
D. Fetch data
Answer: A

5. UPDATE command is used to:

A. Remove rows
B. Change existing data
C. Change table structure
D. Insert new table
Answer: B

6. DELETE command is used to:

A. Remove rows
B. Remove columns
C. Remove table
D. Remove database
Answer: A

🔶 SECTION B — SELECT COMMAND

7. To select all columns from a table, we use:

A. SELECT ALL FROM table;
B. SELECT * FROM table;
C. GET * FROM table;
D. SHOW table;
Answer: B

8. To select specific columns:

A. SELECT columns ONLY table;
B. SELECT column1, column2 FROM table;
C. SELECT FROM column1 column2;
D. GET column1 column2;
Answer: B

9. To remove duplicates:

A. DISTINCT
B. UNIQUE
C. FILTER
D. SPECIAL
Answer: A

10. WHERE clause is used to:

A. Sort data
B. Filter rows
C. Rename rows
D. Delete table
Answer: B

11. ORDER BY clause is used to:

A. Filter data
B. Sort data
C. Delete data
D. Insert rows
Answer: B

12. ORDER BY default sorting is:

A. Descending
B. Ascending
C. Random
D. None
Answer: B

13. To sort in descending order:

A. ORDER BY col DOWN
B. ORDER BY col DESC
C. SORT col DESCENDING
D. DESC col
Answer: B

14. LIKE operator is used for:

A. Exact matching
B. Pattern matching
C. Logical operations
D. Math operations
Answer: B

15. To count total rows:

A. SUM()
B. COUNT()
C. TOTAL()
D. NUMBER()
Answer: B

🔶 SECTION C — INSERT COMMAND

16. Syntax of INSERT:

A. INSERT INTO table;
B. INSERT INTO table VALUES(…);
C. INSERT table INTO VALUES(…);
D. INTO INSERT values(…);
Answer: B

17. INSERT without column names requires:

A. Default values
B. All values in order
C. Null values
D. Primary Key only
Answer: B

18. To insert values into specific columns:

A. INSERT table VALUES;
B. INSERT INTO table (col1, col2) VALUES(…);
C. SELECT INTO table;
D. ADD INTO table;
Answer: B

19. INSERT allows:

A. Adding multiple rows
B. Adding one row only
C. Adding only numeric rows
D. Adding rows without values
Answer: A (Using INSERT INTO ... SELECT)

20. INSERT INTO … SELECT is used to:

A. Insert data from another table
B. Create new table
C. Modify table
D. Delete table
Answer: A

🔶 SECTION D — UPDATE COMMAND

21. UPDATE command always requires:

A. FROM clause
B. SET clause
C. GROUP BY clause
D. SUM clause
Answer: B

22. To update all rows:

A. UPDATE table SET col = value;
B. UPDATE table;
C. UPDATE table WHERE ALL;
D. UPDATE table CHANGE;
Answer: A

23. To update specific rows:

A. UPDATE table;
B. UPDATE table SET col=val WHERE condition;
C. UPDATE table WHERE col=set;
D. MODIFY table SET col;
Answer: B

24. UPDATE without WHERE updates:

A. No rows
B. Only first row
C. All rows
D. Last row
Answer: C

25. Which clause is essential to avoid accidental updates?

A. BY
B. HAVING
C. WHERE
D. GROUP
Answer: C

🔶 SECTION E — DELETE COMMAND

26. DELETE FROM table deletes:

A. Table structure
B. Selected rows
C. Column
D. Schema
Answer: B

27. DELETE without WHERE deletes:

A. No rows
B. First row only
C. All rows
D. Rows with NULL
Answer: C

28. DELETE vs TRUNCATE:

A. Both delete structure
B. TRUNCATE deletes structure
C. DELETE keeps structure, TRUNCATE keeps structure but deletes data
D. DELETE removes table
Answer: C

29. DELETE is a:

A. DDL command
B. DML command
C. TCL command
D. System command
Answer: B

30. DELETE is:

A. Auto commit
B. Requires COMMIT
C. Cannot be rolled back
D. Permanent
Answer: B

🔶 SECTION F — WHERE, AND, OR, LIKE

31. Which operator is used for conditional filtering?

A. WHERE
B. SET
C. USING
D. MERGE
Answer: A

32. AND operator returns rows where:

A. At least one condition is true
B. All conditions are true
C. None are true
D. Values are NULL
Answer: B

33. OR operator returns rows where:

A. At least one condition is true
B. All conditions must be true
C. None are true
D. Only numeric values
Answer: A

34. LIKE ‘A%’ means:

A. Starts with A
B. Ends with A
C. Contains A
D. A somewhere
Answer: A

35. LIKE ‘%A’ means:

A. Starts with A
B. Ends with A
C. Contains A
D. No A
Answer: B

🔶 SECTION G — BANKING SCENARIO

36. Display all customers with balance > 5000:

A. SELECT * FROM Customer;
B. SELECT * FROM Customer WHERE Balance > 5000;
C. SELECT ALL Balance;
D. GET Customer;
Answer: B

37. Add new customer entry:

A. INSERT INTO Customer VALUES(…);
B. ADD CUSTOMER(…);
C. CREATE ROW Customer;
D. MODIFY Customer;
Answer: A

38. Update loan status to ‘Closed’:

A. CLOSE Loan;
B. UPDATE Loan SET Status=’Closed’;
C. DELETE Loan SET Status;
D. MODIFY Loan;
Answer: B

39. Delete all inactive accounts:

A. DELETE Account;
B. DELETE FROM Account WHERE Status=’Inactive’;
C. DROP Account;
D. REMOVE Account;
Answer: B

40. List accounts sorted by balance:

A. SELECT * BY Balance;
B. ORDER Account;
C. SELECT * FROM Account ORDER BY Balance;
D. SORT Account;
Answer: C

🔶 SECTION H

41. SELECT * returns:

A. All columns
B. All rows
C. All records
D. Both A and C
Answer: D

42. INSERT INTO table DEFAULT VALUES inserts:

A. NULL values
B. Default values
C. No values
D. Values from other table
Answer: B

43. UPDATE… WHERE… affects:

A. All rows
B. No rows
C. Specific rows
D. Columns only
Answer: C

44. DELETE is stored in:

A. Undo buffer
B. Redo buffer
C. Log file
D. Data dictionary
Answer: A

45. INSERT… SELECT copies:

A. Only structure
B. Only data
C. Data from another table
D. Constraints only
Answer: C

🔶 SECTION I — TRICKY QUESTIONS

46. SELECT without WHERE returns:

A. No rows
B. Only 1 row
C. All rows
D. Error
Answer: C

47. INSERT without VALUES is:

A. Allowed
B. Not allowed
C. Allowed only for NULL
D. Allowed only for DEFAULT
Answer: B

48. UPDATE without SET:

A. Runs normally
B. Errors out
C. Deletes rows
D. Runs partially
Answer: B

49. DELETE FROM table WHERE 1=1 means:

A. No rows deleted
B. First row deleted
C. All rows deleted
D. Last row deleted
Answer: C

50. DELETE FROM table WHERE 1=2 means:

A. No rows deleted
B. All rows deleted
C. Last row deleted
D. First row deleted
Answer: A

🔶 SECTION J — JOINS & SELECT (Extra Important)

51. SELECT with JOIN is used to:

A. Insert data
B. Delete data
C. Fetch data from multiple tables
D. Drop tables
Answer: C

52. SELECT COUNT(*) FROM table gives:

A. Count of rows
B. Count of columns
C. Count of NULLs
D. Count of Primary Keys
Answer: A

53. SELECT MIN(Salary) returns:

A. Highest Salary
B. Average Salary
C. Lowest Salary
D. Sum of Salary
Answer: C

54. SELECT MAX(Balance) returns:

A. Lowest balance
B. Highest balance
C. All balances
D. Only positive
Answer: B

🔶 SECTION K — AGGREGATE FUNCTIONS

55. COUNT(*) counts:

A. Only non-null rows
B. Only numeric
C. All rows
D. Only distinct rows
Answer: C

56. SUM() works on:

A. Numeric columns
B. String columns
C. NULL columns
D. Tables
Answer: A

57. AVG() returns:

A. Total
B. Average
C. Highest
D. Lowest
Answer: B

58. GROUP BY is used with:

A. SUM()
B. COUNT()
C. AVG()
D. All of the above
Answer: D

🔶 SECTION L — ADVANCED

59. DELETE FROM table WHERE col IS NULL deletes:

A. All rows
B. Rows where col is NULL
C. No rows
D. Columns
Answer: B

60. UPDATE table SET col = col + 100 is used for:

A. Increasing value
B. Decreasing value
C. Resetting
D. Sorting
Answer: A

61. SELECT IN operator is used for:

A. Multiple filtering values
B. Sorting
C. Updating
D. Grouping
Answer: A – SELECT * FROM Employees WHERE Department IN (‘HR’, ‘Finance’, ‘IT’);

62. DELETE FROM table WHERE col IN (10,20) removes:

A. No rows
B. Rows with value 10 or 20
C. All rows
D. NULL rows only
Answer: B

63. UPDATE with multiple columns:

A. Not allowed
B. Allowed
C. Requires DELETE
D. Requires CREATE
Answer: B

64. SELECT BETWEEN 100 AND 500 returns:

A. Values 100–500
B. Values >500
C. Values <100
D. All rows
Answer: A

65. UPDATE using WHERE AND condition updates:

A. Records that satisfy both conditions
B. Either condition
C. All rows
D. No rows
Answer: A

66. DELETE is:

A. Permanent
B. Reversible (with ROLLBACK)
C. Cannot be undone
D. Auto-commit
Answer: B

67. SELECT with ALIAS is used to:

A. Rename column
B. Delete data
C. Insert data
D. Drop table
Answer: A

68. INSERT fails when:

A. PK duplicates
B. CHECK fails
C. NOT NULL fails
D. All
Answer: D

69. SELECT with WHERE col LIKE ‘%bank%’ shows:

A. Names ending with bank
B. Starting with bank
C. Containing word bank
D. Only bank
Answer: C

70. ORDER BY with two columns sorts by:

A. First column only
B. Both columns
C. Second column
D. None
Answer: B