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:

sql
customer_id   name           email                 city      state   joined_date   lifetime_spend
โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€
        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:

sql
name           email                 city
โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€
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:

sql
name           city      lifetime_spend
โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€
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:

sql
name           city
โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€
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:

sql
name           city      lifetime_spend
โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€
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:

sql
name           lifetime_spend
โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€
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:

sql
name           lifetime_spend
โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€
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:

sql
name           city      lifetime_spend
โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€
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:

sql
city
โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€
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?

sql
total_customers
โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€
            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:

sql
customer_name      total_spent
โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€
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.

Show hint

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

Question 1 of 5
What does SELECT * do?
Question 2 of 5
Which clause filters rows in a query?
Question 3 of 5
What is the default sort order for ORDER BY?
Question 4 of 5
How do you get only the top 5 results?
Question 5 of 5
What does DISTINCT do?
0
/5
Africa & India's Best AI Academy
๐Ÿค– AI-first ๐ŸŽ“ Placement Assistance

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 โ†’