Your first SELECT
The SELECT statement is the foundation of every database query. It retrieves data from a table. Let's start with the simplest form and build up.
Q1: Select all columns (SELECT *)
To see every column from every row in the customers table, use SELECT *. The LIMIT clause restricts the result to just 10 rows:
โโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโ
1 Jake Martin jake.martin58@email.com Seattle WA 2023-11-18 1291.30
2 Tyler White tyler.white614@email.com Denver CO 2024-12-29 3188.11
3 Sneha Reddy sneha.reddy842@email.com Miami FL 2024-03-17 4736.27
4 Isabella Thomas isabella.thomas483@email.com Chicago IL 2024-10-06 852.83
5 Mason Lee mason.lee814@email.com Seattle WA 2025-01-17 4102.27
(10 rows)
What this does:
SELECT *โ Returns all columns (name, email, city, state, joined_date, lifetime_spend).FROM customersโ Looks in the customers table.LIMIT 10โ Returns only the first 10 rows (useful for large tables).
Q2: Select specific columns only
Usually, you don't need every column. Pick the ones you care about:
โโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโ
Jake Martin jake.martin58@email.com Seattle
Tyler White tyler.white614@email.com Denver
Sneha Reddy sneha.reddy842@email.com Miami
Isabella Thomas isabella.thomas483@email.com Chicago
Mason Lee mason.lee814@email.com Seattle
(100 rows)
Why this matters: Selecting only the columns you need makes queries faster and results easier to read. In production databases with millions of rows, this can save significant time and memory.
Filtering with WHERE
The WHERE clause filters rows based on conditions. Only rows matching the condition are returned. Let's explore comparison operators, string matching, and logical operators.
Q3: WHERE with comparison (greater than)
Find all customers who have spent more than $500:
โโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโ
Jake Martin Seattle 1291.30
Tyler White Denver 3188.11
Sneha Reddy Miami 4736.27
Isabella Thomas Chicago 852.83
Mason Lee Seattle 4102.27
(93 rows)
Comparison operators: > (greater than), < (less than), >= (greater or equal), <= (less or equal), = (equal), != or <> (not equal).
Q4: WHERE with string matching (equals)
Find all customers from Austin:
โโโโโโโโโโโโโโโโโโโโโโโโโโโโ
Lucas White Austin
Alexander Harris Austin
Mason Wilson Austin
Priya Thompson Austin
Alexander Taylor Austin
Sofia Lee Austin
Isabella White Austin
Charlotte Martin Austin
Mia Wilson Austin
Charlotte Chen Austin
(12 rows)
String matching: Text values go in single quotes. WHERE city = 'Austin' finds exact matches. PostgreSQL is case-sensitive, so 'austin' won't match 'Austin'.
Q5: WHERE with AND/OR logic
Combine multiple conditions. Find customers from Seattle who spent more than $300:
โโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโ
Jake Martin Seattle 1291.30
Mason Lee Seattle 4102.27
Charlotte Anderson Seattle 1011.74
Logan Iyer Seattle 1416.33
Arjun Anderson Seattle 3918.94
Tyler Chen Seattle 3930.41
Alexander Morrison Seattle 2194.86
Noah Johnson Seattle 4174.70
Alexander Wilson Seattle 599.39
Arjun Iyer Seattle 3491.22
(18 rows)
Logical operators: AND requires both conditions to be true. OR requires at least one condition to be true. Use parentheses for complex logic: WHERE (city = 'Seattle' OR city = 'Denver') AND lifetime_spend > 300.
Sorting with ORDER BY
The ORDER BY clause sorts results. By default, it sorts ascending (AโZ, 0โ9). Use DESC for descending order.
Q6: ORDER BY ascending (ASC)
Sort customers by lifetime spend from lowest to highest:
โโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโ
Sofia Patel 33.12
Emma Martin 35.97
Liam Taylor 52.78
Isabella Patel 168.05
Ella Morrison 300.18
Ella Thompson 337.77
Arjun Miller 344.21
Emma Iyer 519.64
Alexander Wilson 599.39
Amelia Taylor 625.20
(100 rows)
ASC is the default: You can omit ASC and just write ORDER BY lifetime_spend. The result is the same.
Q7: ORDER BY descending + LIMIT (top N)
Find the top 5 biggest spenders:
โโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโ
Arjun Morrison 4878.23
Liam Wilson 4815.29
Sneha Reddy 4736.27
Liam Cruz 4735.59
Ava Jackson 4674.29
(5 rows)
Combining ORDER BY + LIMIT: This is a powerful pattern. Sort descending and limit to N to get the "top N" results. Perfect for dashboards showing top customers, top products, etc.
Q8: ORDER BY multiple columns
Sort by city first (AโZ), then by lifetime_spend (highโlow) within each city:
โโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโ
Sofia Lee Austin 4653.42
Isabella White Austin 4595.89
Harper Kim Austin 4301.84
Lucas White Austin 3296.96
Charlotte Martin Austin 3063.89
Charlotte Chen Austin 2742.17
Priya Thompson Austin 2358.34
Alexander Harris Austin 2157.48
Mason Wilson Austin 2144.78
Mia Wilson Austin 1755.45
(100 rows)
Multi-column sorting: Columns are sorted in order. PostgreSQL first sorts by city (ascending), then within each city, sorts by lifetime_spend (descending). This is perfect for hierarchical data.
DISTINCT and COUNT
Remove duplicate values and count rows efficiently.
Q9: DISTINCT โ remove duplicates
How many different cities do we have customers in? Use DISTINCT to remove duplicate city names:
โโโโโโโโโโโ
Austin
Chicago
Denver
Miami
Portland
Seattle
(6 rows)
DISTINCT: Returns only unique values. Without it, this query would return all 100 customer cities (with many duplicates). With DISTINCT, we see the 6 unique cities.
Q10: COUNT โ count rows
How many customers do we have in total?
โโโโโโโโโโโโโโโโ
100
COUNT(*): Counts every row. COUNT(*) is the fastest way to count all rows. We'll see more counting patterns when we cover aggregates.
Aliasing with AS
Rename columns in the output using AS. This makes results clearer and easier to understand.
Q11: Rename columns with AS
Alias name โ customer_name and lifetime_spend โ total_spent. Then filter and sort:
โโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโ
Arjun Morrison 4878.23
Liam Wilson 4815.29
Sneha Reddy 4736.27
Liam Cruz 4735.59
Ava Jackson 4674.29
Mia Wilson 4659.22
Sofia Lee 4653.42
Isabella White 4595.89
Lucas Davis 4575.04
Tyler Jackson 4388.76
(96 rows)
Why use aliases? They make query results more readable and are essential when building reports or APIs. In production, you can also use column aliases in filters: WHERE total_spent > 200 (though this only works in certain situations; WHERE typically uses original column names).
Challenge
Write a query that finds: The 10 most recent customers (by joined_date), showing only their name, email, city, and joined_date. Sort newest first, and rename joined_date to signup_date in the output.
You'll need:
- SELECT the columns you want (with alias for joined_date)
- FROM customers
- ORDER BY joined_date in descending order (newest first)
- LIMIT 10
The query structure: SELECT ... FROM customers ORDER BY ... DESC LIMIT 10;
Test Your Knowledge
You just ran your first queries.
Join the Best AI Program: The AI-Era Data Engineer โ 5 months, fully online, with placement assistance. Week 1 is free.
Explore The AI-Era Data Engineer โ