Advanced Data Manipulation
In addition to basic retrieval, SQL provides powerful Data Manipulation Language (DML) commands to insert new records, modify existing rows, and delete data safely.
Modifying Records: INSERT, UPDATE, DELETE
Let's start with a sample Customers table to see how DML commands manipulate its state:
| ID | CustomerName | City | Country |
|---|---|---|---|
| 1 | Alfreds | Berlin | Germany |
| 2 | Ana Trujillo | México D.F. | Mexico |
1. INSERT INTO
The INSERT INTO statement adds brand new rows to a table. You specify the columns you are filling, followed by the values in the exact same order.
INSERT INTO Customers (ID, CustomerName, City, Country)
VALUES ('3', 'Cardinal', 'Stavanger', 'Norway');| ID | CustomerName | City | Country |
|---|---|---|---|
| 1 | Alfreds | Berlin | Germany |
| 2 | Ana Trujillo | México D.F. | Mexico |
| 3 | Cardinal | Stavanger | Norway |
2. UPDATE and DELETE
The UPDATE statement modifies existing records, while DELETE completely removes them.
CRITICAL RULE: You must *always* use a WHERE clause! If you omit the WHERE clause, the database will update or delete EVERY single row in your table!
-- Update Ana's city
UPDATE Customers
SET City = 'Cancun'
WHERE ID = '2';
-- Delete Alfreds from the system
DELETE FROM Customers
WHERE ID = '1';| ID | CustomerName | City | Country |
|---|---|---|---|
| 2 | Ana Trujillo | Cancun | Mexico |
| 3 | Cardinal | Stavanger | Norway |
Handling Missing Data (NULLs)
NULL values represent missing, unknown, or undefined data.
Because NULL is mathematically "unknown", a database engine cannot evaluate it using standard comparison operators like = or !=. Asking the database WHERE City = NULL will silently fail or return zero rows.
IS NULL or IS NOT NULL operators.SELECT CustomerName
FROM Customers
WHERE City IS NULL;Pattern Matching (LIKE, IN, BETWEEN)
Often, you don't know the exact string you are looking for. SQL provides the LIKE operator alongside wildcards to search for patterns within text.
- The Percent Sign (%): Represents zero, one, or multiple characters. Example: `LIKE 'a%'` finds anything starting with 'a' (apple, ant, a).
- The Underscore (_): Represents exactly ONE single character. Example: `LIKE '_r%'` finds any string where the SECOND letter is 'r' (bread, tree, orbit).
-- Find values starting with 'a' and at least 3 characters long
SELECT *
FROM Customers
WHERE CustomerName LIKE 'a__%';
-- Find values within a specific list
SELECT *
FROM Customers
WHERE Country IN ('Germany', 'France', 'UK');
-- Find values inside an inclusive numeric range
SELECT *
FROM Products
WHERE Price BETWEEN 10 AND 20;Limiting Results
When dealing with millions of rows, you rarely want to return all of them. However, SQL implementations differ wildly on how to limit returned rows.
/* Microsoft SQL Server Syntax */
SELECT TOP 3 *
FROM Customers;
/* MySQL and PostgreSQL Syntax */
SELECT *
FROM Customers
LIMIT 3;
/* Oracle ANSI Standard Syntax */
SELECT *
FROM Customers
FETCH FIRST 3 ROWS ONLY;Aggregate Functions
Aggregate functions perform mathematical calculations on an entire column of values and crush them down into a single summary value.
| ProductID | ProductName | Price |
|---|---|---|
| 1 | Laptop | 1000 |
| 2 | Mouse | 50 |
| 3 | Keyboard | 150 |
SELECT
COUNT(ProductID) AS TotalItems,
MIN(Price) AS CheapestItem,
MAX(Price) AS ExpensiveItem,
SUM(Price) AS TotalInventoryValue,
AVG(Price) AS AveragePrice
FROM Products;| TotalItems | CheapestItem | ExpensiveItem | TotalInventoryValue | AveragePrice |
|---|---|---|---|---|
| 3 | 50 | 1000 | 1200 | 400 |
COUNT(*) and COUNT(column_name).
COUNT(*) simply counts the total number of physical rows in the table. However, COUNT(column_name) counts the number of values in that column while IGNORING all NULL values! If a column has 10 rows but 2 are NULL, COUNT(col) returns 8.Spot The Bug!
An intern is trying to update the city for a specific customer with ID 5, but the senior developer stopped them before they ran this query. Why?
UPDATE Customers
SET City = 'London';