DBMS SQL DML

DML (Data Manipulation Language)

A subset of SQL used to retrieve, insert, update, and delete data in a database.

SELECT

Retrieves data from one or more tables and returns it in a result set.

INSERT

Adds new records (rows) into a table.

UPDATE

Modifies existing data in a table.

DELETE

Removes records from a table.

WHERE Clause

Filters rows based on a condition before they are retrieved or affected.

HAVING Clause

Filters grouped records (used after GROUP BY) based on aggregate conditions.

LIKE Operator

Used for pattern matching with wildcards such as % (any characters) and _ (single character).

ORDER BY

Sorts query results in ascending (ASC) or descending (DESC) order.

GROUP BY

Groups rows with the same values into summary rows for aggregate functions.

Aggregate Function

Functions like COUNT(), SUM(), AVG(), MAX(), and MIN() that compute a single value from multiple rows.

Wildcard %

Represents any sequence of characters in a string pattern. Example: 'A%' matches "Alice", "Aaron".

Wildcard _

Represents a single character in a pattern. Example: 'J_n' matches "Jan", "Jon".

DELETE vs TRUNCATE

DELETE removes specific rows (can use WHERE); TRUNCATE removes all rows quickly without logging.

INSERT INTO SELECT

Copies rows from one table into another table.

UPDATE with calculation

Example: UPDATE emp SET salary = salary * 1.1; increases salary by 10%.

DISTINCT keyword

Removes duplicate rows from query results.

BETWEEN

Tests whether a value lies within a range. Example: salary BETWEEN 5000 AND 10000.

IN Clause

Checks if a value matches any value in a list. Example: department_id IN (10, 20, 30).