Your First SQL Query - SELECT Basics
Introduction
The SELECT statement is the most important command in SQL. It's how you retrieve data from databases, and you'll use it constantly—whether you're a data analyst, backend developer, or data scientist.
In this lesson, you'll master the fundamentals of SELECT queries and learn to retrieve exactly the data you need.
The SELECT Statement
At its core, SELECT answers one question: "What data do I want to see?"
Basic Syntax
SELECT column1, column2, ...
FROM table_name;
Your First Query
Let's use the users table we created:
SELECT * FROM users;
Output:
id | name | email | age
----+--------------+----------------------+-----
1 | Alice Smith | alice@example.com | 28
2 | Bob Jones | bob@example.com | 34
3 | Carol White | carol@example.com | 22
Breakdown:
SELECT *: Get all columnsFROM users: From the users table;: End of statement (required)
The Asterisk (*)
* means "all columns". It's quick for exploration, but in production code, specify columns explicitly:
-- Less specific (uses *)
SELECT * FROM users;
-- More specific (better)
SELECT id, name, email, age FROM users;
Why specify columns?
- Clearer intent
- Better performance (fetches only needed data)
- Protects against table schema changes
Selecting Specific Columns
Single Column
SELECT name FROM users;
Output:
name
--------------
Alice Smith
Bob Jones
Carol White
Multiple Columns
SELECT name, age FROM users;
Output:
name | age
---------------+-----
Alice Smith | 28
Bob Jones | 34
Carol White | 22
Order matters:
SELECT age, name FROM users; -- age first
SELECT name, age FROM users; -- name first
Column Aliases
Use AS to rename columns in output:
SELECT
name AS full_name,
age AS years_old
FROM users;
Output:
full_name | years_old
--------------+-----------
Alice Smith | 28
Bob Jones | 34
Carol White | 22
AS is optional (but recommended for clarity):
SELECT
name full_name,
age years_old
FROM users;
Use cases:
- Make column names more readable
- Avoid conflicts when joining tables
- Match expected output format
Expressions in SELECT
SELECT can perform calculations:
Math Operations
SELECT
name,
age,
age + 10 AS age_in_ten_years
FROM users;
Output:
name | age | age_in_ten_years
---------------+-----+------------------
Alice Smith | 28 | 38
Bob Jones | 34 | 44
Carol White | 22 | 32
String Operations
SELECT
name,
email,
UPPER(email) AS uppercase_email,
LENGTH(email) AS email_length
FROM users;
Output:
name | email | uppercase_email | email_length
---------------+----------------------+-----------------------+--------------
Alice Smith | alice@example.com | ALICE@EXAMPLE.COM | 17
Bob Jones | bob@example.com | BOB@EXAMPLE.COM | 15
Carol White | carol@example.com | CAROL@EXAMPLE.COM | 17
Concatenation
SELECT
name || ' (' || age || ' years old)' AS description
FROM users;
Output:
description
------------------------------
Alice Smith (28 years old)
Bob Jones (34 years old)
Carol White (22 years old)
Note: || is the concatenation operator in PostgreSQL.
DISTINCT - Removing Duplicates
Let's add more data to see duplicates:
INSERT INTO users (name, email, age)
VALUES
('Dave Brown', 'dave@example.com', 28),
('Eve Davis', 'eve@example.com', 34);
SELECT age FROM users;
Output:
age
-----
28
34
22
28 -- duplicate
34 -- duplicate
Using DISTINCT
SELECT DISTINCT age FROM users;
Output:
age
-----
22
28
34
DISTINCT removes duplicate rows from results.
DISTINCT with Multiple Columns
SELECT DISTINCT age, name FROM users;
Returns unique combinations of age and name (all rows are likely unique here).
Limiting Results
LIMIT Clause
Get only the first N rows:
SELECT * FROM users LIMIT 3;
Output:
id | name | email | age
----+--------------+----------------------+-----
1 | Alice Smith | alice@example.com | 28
2 | Bob Jones | bob@example.com | 34
3 | Carol White | carol@example.com | 22
Practical Uses
Pagination:
-- First page (rows 1-10)
SELECT * FROM users LIMIT 10;
-- Second page (rows 11-20)
SELECT * FROM users LIMIT 10 OFFSET 10;
Preview large tables:
-- See what data looks like
SELECT * FROM large_table LIMIT 5;
Comments in SQL
Single-line Comments
-- This is a comment
SELECT name FROM users; -- Get all names
Multi-line Comments
/*
This query retrieves user information
for our monthly report
*/
SELECT * FROM users;
Counting Rows
COUNT(*)
SELECT COUNT(*) FROM users;
Output:
count
-------
5
COUNT with DISTINCT
SELECT COUNT(DISTINCT age) FROM users;
Output:
count
-------
3
Counts unique ages.
Practical Examples
Example 1: Email Directory
SELECT
name AS "Full Name",
email AS "Email Address"
FROM users
ORDER BY name;
Note: Quoted aliases preserve case and spaces.
Example 2: Age Analysis
SELECT
DISTINCT age AS unique_age,
COUNT(*) AS user_count
FROM users
GROUP BY age
ORDER BY age;
(We'll cover GROUP BY in Module 5)
Example 3: Name Formatting
SELECT
UPPER(SUBSTRING(name, 1, 1)) ||
LOWER(SUBSTRING(name, 2)) AS formatted_name
FROM users;
Common Mistakes and How to Avoid Them
Mistake 1: Forgetting FROM
-- ❌ Wrong
SELECT name;
-- ✅ Correct
SELECT name FROM users;
Mistake 2: Forgetting Semicolon (in psql)
-- In psql, this will wait for more input
SELECT name FROM users
-- ✅ Add semicolon to execute
SELECT name FROM users;
Mistake 3: Typos in Column Names
-- ❌ Wrong (column doesn't exist)
SELECT username FROM users;
-- ✅ Correct
SELECT name FROM users;
Error message:
ERROR: column "username" does not exist
Mistake 4: Missing Quotes for String Aliases
-- ❌ Won't work if alias has spaces
SELECT name AS Full Name FROM users;
-- ✅ Use quotes
SELECT name AS "Full Name" FROM users;
Practice Exercises
Try these queries on your users table:
Exercise 1: Basic Selection
-- Get all emails
SELECT email FROM users;
Exercise 2: Multiple Columns
-- Get name and age
SELECT name, age FROM users;
Exercise 3: Aliases
-- Rename columns to "Person" and "Years"
SELECT name AS "Person", age AS "Years" FROM users;
Exercise 4: Expressions
-- Calculate age in months
SELECT name, age * 12 AS age_in_months FROM users;
Exercise 5: DISTINCT
-- Find all unique ages
SELECT DISTINCT age FROM users;
Exercise 6: LIMIT
-- Get the first 2 users
SELECT * FROM users LIMIT 2;
Exercise 7: Concatenation
-- Create a formatted string: "Alice Smith - alice@example.com"
SELECT name || ' - ' || email AS contact_info FROM users;
SQL Style Guidelines
Readable Formatting
Good:
SELECT
id,
name,
email,
age
FROM users;
Also Good (for simple queries):
SELECT name, age FROM users;
Avoid:
select id,name,email,age from users;
Keywords in Uppercase
-- ✅ Conventional
SELECT name FROM users;
-- ✅ Also valid
select name from users;
-- Choose one style and be consistent
What We Learned
- ✅ SELECT retrieves data from tables
- ✅ * selects all columns (use specific columns in production)
- ✅ AS creates column aliases
- ✅ Expressions allow calculations in SELECT
- ✅ DISTINCT removes duplicates
- ✅ LIMIT restricts number of rows returned
- ✅ COUNT() counts rows
Next Steps
You now know how to SELECT data from tables. But what if you only want specific rows? In the next lesson, we'll learn the WHERE clause—how to filter data based on conditions.
Get ready to ask precise questions of your data!