++++Go beyond SELECT * with aliases, DISTINCT, calculated columns, WHERE filters, LIKE, BETWEEN, IN, ORDER BY, and LIMIT.
7. SELECT Querying Deep Dive
SELECT Querying Deep Dive
SELECT is the command you will use most often. The early lessons used SELECT * to inspect a full table, but real queries usually choose specific columns, filter rows, sort results, and limit output.
Start with a sample table
CREATE TABLE customers (
customer_id INT PRIMARY KEY,
first_name VARCHAR(50),
last_name VARCHAR(50),
city VARCHAR(100),
country VARCHAR(50),
total_spend DECIMAL(10, 2)
);
INSERT INTO customers
VALUES
(1, "John", "Doe", "Springfield", "USA", 250.00),
(2, "Jane", "Smith", "Metropolis", "USA", 1200.50),
(3, "Emily", "Davis", "Gotham", "USA", 740.25),
(4, "Aarav", "Sharma", "Mumbai", "India", 980.00),
(5, "Sara", "Wilson", "Austin", "USA", 80.00);Select specific columns
Prefer explicit columns when you know what you need.
SELECT first_name, last_name, city
FROM customers;SELECT * is fine for quick exploration, but explicit columns make production queries clearer and reduce unnecessary data transfer.
Aliases
Aliases rename columns in the result.
SELECT
first_name AS first,
last_name AS last,
total_spend AS lifetime_value
FROM customers;You can also create calculated columns.
SELECT
first_name,
total_spend,
total_spend * 0.10 AS reward_credit
FROM customers;DISTINCT
DISTINCT removes duplicate result rows.
SELECT DISTINCT country
FROM customers;Use it when you need unique values, not when you are hiding duplicates caused by a broken join.
WHERE filters
WHERE keeps only rows that match a condition.
SELECT *
FROM customers
WHERE total_spend >= 500;Combine conditions with AND and OR.
SELECT *
FROM customers
WHERE country = "USA"
AND total_spend >= 500;
SELECT *
FROM customers
WHERE city = "Mumbai"
OR city = "Austin";Use parentheses when the logic mixes AND and OR.
SELECT *
FROM customers
WHERE country = "USA"
AND (city = "Austin" OR city = "Gotham");LIKE, BETWEEN, and IN
LIKE matches patterns. % means any number of characters.
SELECT *
FROM customers
WHERE last_name LIKE "S%";BETWEEN filters an inclusive range.
SELECT *
FROM customers
WHERE total_spend BETWEEN 100 AND 1000;IN checks whether a value appears in a list.
SELECT *
FROM customers
WHERE city IN ("Austin", "Mumbai", "Gotham");ORDER BY
Sort results with ORDER BY.
SELECT first_name, last_name, total_spend
FROM customers
ORDER BY total_spend DESC;Use multiple sort columns when ties matter.
SELECT *
FROM customers
ORDER BY country ASC, total_spend DESC;LIMIT
LIMIT returns only the first N rows after filtering and sorting.
SELECT first_name, last_name, total_spend
FROM customers
ORDER BY total_spend DESC
LIMIT 3;Always pair LIMIT with ORDER BY when you expect deterministic top-N results.