DML and queries

The essentials

DML commands change the data in tables, and SELECT retrieves it. Most exam questions ask you to write a query or predict which rows it returns.

Key points

• INSERT INTO Student VALUES (1, 'Asha', '2008-05-14'); inserts values for all columns in order. INSERT INTO Student (RollNo, Name) VALUES (2, 'Ravi'); fills only named columns; the rest get NULL or their default.
• UPDATE Student SET Marks = Marks + 5 WHERE Class = 12; changes values in matching rows. Without WHERE, every row is updated.
• DELETE FROM Student WHERE Marks < 33; removes matching rows. Without WHERE, all rows are removed.
• SELECT column list FROM table WHERE condition; SELECT * returns all columns.
• DISTINCT removes duplicate values: SELECT DISTINCT City FROM Student;
• Aliases: SELECT Name AS StudentName …
• Relational operators: =, <, >, <=, >=, <> (or !=). Logical operators: AND, OR, NOT.
• BETWEEN 50 AND 80 includes both 50 and 80.
• IN ('Delhi', 'Pune') matches any value in the list.
• LIKE with wildcards: % means any number of characters (including none); _ means exactly one character. Name LIKE 'A%' finds names starting with A; Name LIKE '_a%' finds names with a as the second letter.
• IS NULL / IS NOT NULL test for NULL. A condition such as Marks = NULL never matches.
• ORDER BY Marks DESC sorts results; ASC (ascending) is the default.

Common mistakes

• Using = NULL instead of IS NULL.
• Forgetting that BETWEEN is inclusive at both ends.
• Forgetting that UPDATE or DELETE without WHERE affects every row.