Lecture 3: Data Query Language & DML Basics
The Art of Asking Questions to Your Data
đ Concept Overview
- DML vs DQL: We first learn to Create (INSERT), Update, and Delete data before we Query it (SELECT).
- Logical Flow: Written as
SELECT -> FROM -> WHERE, but interpreted asFROM -> WHERE -> SELECT. - Filtering is Key: The
WHEREclause reduces the dataset before processing. - NULL: Represents "Unknown". It is not zero, and it is not an empty string.
3.1 Modifying Data (DML)
Before we can read data, we need to have data. DML (Data Manipulation Language) handles this.
3.1.1 INSERT (Creating Data)
There are two main ways to insert data: specifying columns (safe) and shorthand (risky).
-- 1. Explicit Column Names (Best Practice)
INSERT INTO users (username, email, age)
VALUES ('john_doe', 'john@example.com', 25);
-- 2. Multiple Row Insertion (Faster)
INSERT INTO users (username, email, age) VALUES
('jane_doe', 'jane@example.com', 30),
('bob_smith', 'bob@example.com', 45);âšī¸ Auto-Increment Behavior
If an INSERT fails (e.g., duplicate email), the standard AUTO_INCREMENT counter
still increases. You might see gaps in your IDs (1, 2, 5, 6...). This is normal
behavior for performance reasons.
3.1.2 UPDATE (Modifying Data)
Changes existing data. Always use a WHERE clause!
-- Good: Updates one specific user
UPDATE users SET age = 26 WHERE id = 1;
-- â ī¸ DANGEROUS: Updates EVERY user in the table
UPDATE users SET age = 26;3.1.3 DELETE (Removing Data)
Removes specific rows. Like UPDATE, if you forget the WHERE clause, you wipe the table.
-- Good: Deletes one user
DELETE FROM users WHERE id = 1;
-- â ī¸ DANGEROUS: Deletes everyone
DELETE FROM users;3.2 The SELECT Statement
The SELECT statement allows us to retrieve specific data points from our tables.
đ§ Mental Model: The Filter Funnel
Imagine a giant funnel.
- Top (FROM): You dump the entire "Books" table into the funnel.
- Middle (WHERE): A filter grid blocks unwanted rows (e.g., only keep "Price < $10").
- Bottom (SELECT): You only pick the columns you want to see (e.g., just "Title").
3.2.1 Selecting Columns
It is better performance to select only what you need rather than everything (*).
| Query | Performance | Use Case |
|---|---|---|
SELECT * FROM users |
Slower (Fetches unused data) | Debugging, Ad-hoc checks. |
SELECT email FROM users |
Faster (Less network I/O) | Production code. |
3.2.2 DISTINCT
Removes duplicate rows from the result set.
-- If you have 5 users from 'USA' and 3 from 'UK':
SELECT country FROM users;
-- Result: USA, USA, USA, USA, USA, UK, UK, UK (8 rows)
SELECT DISTINCT country FROM users;
-- Result: USA, UK (2 rows)3.3 The Power of WHERE (Filtering)
The WHERE clause is the most important part of SQL for performance. It filters data
on the server before sending it to you.
3.3.1 Comparison Operators
| Operator | Meaning | Example |
|---|---|---|
= |
Equal to | age = 25 |
<> or != |
Not Equal to | age != 18 |
>, < |
Greater/Less than | price > 100 |
>=, <= |
Greater/Less or Equal | price <= 50 |
3.3.2 Logical Operators (AND, OR, NOT)
3.4 Advanced Operators
3.4.1 BETWEEN & IN
-- BETWEEN (Inclusive)
SELECT * FROM products WHERE price BETWEEN 10 AND 20;
-- Same as: price >= 10 AND price <= 20
-- IN (List Matching)
SELECT * FROM users WHERE id IN (1, 3, 5);
-- Same as: id = 1 OR id = 3 OR id = 53.4.2 LIKE (Pattern Matching)
%: Matches 0 or more characters._: Matches exactly one character.
-- Starts with 'A'
WHERE name LIKE 'A%';
-- Ends with 'gmail.com'
WHERE email LIKE '%gmail.com';
-- Second letter is 'a'
WHERE name LIKE '_a%';3.4.3 IS NULL (The Trap)
You cannot use = NULL. You must use IS NULL.
| Expression | Result | Reason |
|---|---|---|
NULL = NULL |
NULL (False) |
It's logical result is "Unknown". |
NULL IS NULL |
TRUE |
Correct way to check. |
3.5 Sorting and Limiting
ORDER BY sorts your results. LIMIT cuts them off.
Crucial Rule: LIMIT is always the last word in your query.
-- Top 5 most expensive products
SELECT * FROM products
ORDER BY price DESC
LIMIT 5;3.6 Teaching Notes
đ Instructor Guide
Key Concepts to Drill:
- The Funnel: Draw a funnel. 1 million rows enter at the top (FROM). The Filter (WHERE) blocks 99%. Only 1% reach the bottom (SELECT). This explains why WHERE is so fast/important.
- NULL Infection:
5 + NULL = NULL.'Hello' + NULL = NULL. NULL is like a cold virus; it infects everything it touches. - Safe Updates: Always run a
SELECT * FROM ... WHERE ...before you run anUPDATE ... WHERE ...to confirm you are targeting the right rows.
3.7 Debugging Workshop
1. Confusing Logic
I wanted inexpensive SciFi or Fantasy books, but I got expensive Fantasy books.
SELECT * FROM books
WHERE genre = 'SciFi' OR genre = 'Fantasy' AND price < 10;2. The Null Trap
I want books with no price.
SELECT * FROM books WHERE price = NULL;3. The Dangerous Update
I tried to change John's email, but I changed everyone's!
UPDATE users SET email = 'john@new.com';3.8 Practice Tasks
Task 1: The 'Gmail' Search
Scenario: You are building a search feature. You want to find all users whose email domain is 'gmail.com'.
Activity: Write a query using LIKE.
Show Answer
SELECT * FROM users WHERE email LIKE '%@gmail.com';Note: The % wildcard matches anything before the domain.
Task 2: Pagination
Scenario: You have 100 products. You want to show "Page 2", assuming 10 products per page. (So you need products 11-20).
Activity: Write the query using LIMIT and OFFSET.
Show Answer
SELECT * FROM products ORDER BY id LIMIT 10 OFFSET 10;LIMIT 10 gives the size of the page. OFFSET 10 skips the
first Page (1-10).
3.9 Quiz
đ Lecture 3 Quiz
Select the correct answer for each question. (8 Questions)