Mastering Statements & Filtering

Mastering the SELECT Statement: Your Key to Data Retrieval

Welcome back, data enthusiasts! In this lesson, we explore the powerful SELECT statement — your gateway to extracting valuable insights from your database.

Understanding the SELECT Statement

The SELECT statement is your primary tool for retrieving data. Its basic structure is:

SELECT column1, column2, ...
FROM table_name;

-- Parts
-- SELECT   : Start a query to retrieve data
-- column(s): Choose which columns to return (use * to select all)
-- FROM     : Which table to read from
-- ;        : End of statement

Retrieving All Records

Get a complete overview of your table:

SELECT *
FROM data_table;

Retrieving Specific Details

Focus on just the fields you need (e.g., transaction amount and category):

SELECT amount, category
FROM data_table;

Helpful Extras

• Distinct values — remove duplicates: SELECT DISTINCT category FROM data_table;
• Sorting — most expensive first: SELECT * FROM products ORDER BY price DESC;
• Limiting — top results only: SELECT * FROM products ORDER BY price DESC LIMIT 5;
• Aliases — rename a column in results: SELECT amount AS total_amount FROM data_table;

Mastering the WHERE Clause: Filtering Data with Precision

The WHERE clause filters your results so only matching rows are returned.

Syntax

SELECT column1, column2
FROM table_name
WHERE condition;  -- only rows that satisfy the condition are returned

Comparison Operators

Filter based on numeric, text, or date comparisons:

-- Amount greater than 100
SELECT * FROM transactions
WHERE amount > 100;

-- Events in a date range (inclusive)
SELECT * FROM events
WHERE event_date BETWEEN '2023-01-01' AND '2023-12-31';

-- Exact match for a category
SELECT * FROM products
WHERE category = 'electronics';

Pattern Matching and Sets

-- Names containing "pro" (case sensitivity depends on collation)
SELECT * FROM products
WHERE name LIKE '%pro%';

-- Belongs to one of several categories
SELECT * FROM products
WHERE category IN ('electronics','home');

-- Missing values
SELECT * FROM customers
WHERE email IS NULL;  -- vs. IS NOT NULL

Tip: Strings should be quoted with single quotes. Use proper date formats for your database engine.

Organizing Data with the ORDER BY Clause

The ORDER BY clause sorts rows returned by a SELECT. Place it after WHERE (if present) to control result order.

Syntax

SELECT column1, column2
FROM table_name
WHERE condition
ORDER BY column_name ASC | DESC;  -- ASC = ascending (A→Z, 0→9), DESC = descending (Z→A, 9→0)

Sorting by a Single Column

Most-recent-first using a date/timestamp column:

SELECT *
FROM records
ORDER BY date DESC;  -- newest at the top

Sorting by Multiple Columns

Sort alphabetically by category, then newest-first within each category:

SELECT *
FROM records
ORDER BY category ASC, date DESC;

Ordering results makes trends clearer and lists more usable. Combine with filters and limits for dashboards.

More Resources

• ORDER BY clause (W3Schools)

Advanced Data Retrieval: Wildcards and Comparison Operators

Use pattern matching and comparisons to search flexibly and target precisely.

Wildcards (%) for Flexible Searching

Find records whose text contains a keyword anywhere:

SELECT *
FROM records
WHERE description LIKE '%keyword%';  -- any characters before/after "keyword"

Comparison Operators for Targeted Filtering

-- Amount exceeds 100
SELECT * FROM transactions
WHERE amount > 100;  -- also =, <, <=, >=

-- Filtering by date range (inclusive)
SELECT * FROM events
WHERE event_date BETWEEN '2023-01-01' AND '2023-12-31';

Combining Techniques for Powerful Queries

SELECT *
FROM products
WHERE name LIKE '%pro%'
  AND price >= 50;  -- find products with "pro" that cost 50 or more

More Resources

• SQL Wildcards

Logical Operators and Combining Techniques

Combine conditions for granular control. Parentheses help make intent unambiguous.

AND, OR, NOT

-- Adults in the US
SELECT * FROM users
WHERE age > 18 AND country = 'US';

-- Products with "pro" in the name AND price >= 50, OR category is electronics
SELECT * FROM products
WHERE (name LIKE '%pro%' AND price >= 50)
   OR category = 'electronics';

-- Exclude discontinued items
SELECT * FROM products
WHERE NOT discontinued;

Putting It All Together

-- Example: expensive recent orders by VIP customers
SELECT o.id, o.total_amount, o.created_at, c.name AS customer
FROM orders AS o
JOIN customers AS c ON c.id = o.customer_id
WHERE o.created_at >= '2024-01-01'
  AND o.total_amount >= 250
  AND (c.segment = 'VIP' OR c.country IN ('US','UK'))
ORDER BY o.created_at DESC
LIMIT 20;

More Resources

• SELECT statement (W3Schools)
• WHERE clause (W3Schools)
• SQLBolt — Select queries

← Back to Database Home

Movie Database Challenge 🎥

Build and query a simple database about movies and actors. This reinforces table design, data types, and SELECT + WHERE + ORDER BY skills.

1. Goal

Create two base tables (Actors and Movies) then run exploratory queries. Optionally add a linking table for casting.

2. Table Structures

-- Actors Table 🌟
-- id: unique ID
-- name: actor's name
-- age: actor's age
CREATE TABLE Actors (
  id INT PRIMARY KEY,
  name VARCHAR(120) NOT NULL,
  age INT
);

-- Movies Table 🎥
-- id: unique ID
-- title: movie name
-- year: release year
CREATE TABLE Movies (
  id INT PRIMARY KEY,
  title VARCHAR(160) NOT NULL,
  year INT NOT NULL
);

-- Optional many‑to‑many casting table
CREATE TABLE MovieCast (
  movie_id INT,
  actor_id INT,
  role VARCHAR(160),
  PRIMARY KEY (movie_id, actor_id),
  FOREIGN KEY (movie_id) REFERENCES Movies(id),
  FOREIGN KEY (actor_id) REFERENCES Actors(id)
);

3. Sample Data

INSERT INTO Actors (id, name, age) VALUES
  (1,'Emma Stone',35),
  (2,'Denzel Washington',69),
  (3,'Zendaya',28);

INSERT INTO Movies (id, title, year) VALUES
  (1,'La La Land',2016),
  (2,'The Equalizer',2014),
  (3,'Spider-Man: No Way Home',2021);

INSERT INTO MovieCast (movie_id, actor_id, role) VALUES
  (1,1,'Mia'),
  (2,2,'Robert McCall'),
  (3,3,'MJ');

4. Practice Queries

• All movies: SELECT * FROM Movies;
• Actors older than 40: SELECT name, age FROM Actors WHERE age > 40;
• Recent releases (>= 2016): SELECT title, year FROM Movies WHERE year >= 2016 ORDER BY year DESC;
• Join cast to movies:

  SELECT m.title, a.name, c.role
  FROM MovieCast c
  JOIN Movies m ON m.id = c.movie_id
  JOIN Actors a ON a.id = c.actor_id
  ORDER BY m.year DESC;

5. Stretch Challenges 🧠

• Add a genre column to Movies and query only action movies.
• List actors appearing in movies released after 2018.
• Count movies per actor (requires GROUP BY).
• Find actors with no casting entries yet (LEFT JOIN + WHERE c.actor_id IS NULL).

6. Tips

• Use meaningful primary keys (consider AUTO_INCREMENT in MySQL).
• Keep text columns sized reasonably (e.g., VARCHAR(160) for titles).
• Normalize: separate actors and movies; use a junction table for many‑to‑many relationships.
• Start with simple selects, then layer WHERE, ORDER BY, and JOIN progressively.

You now have a mini data playground. Expand it with ratings, genres, or awards and practice more complex queries!