Picking columns with SELECT
The first query everyone writes, and the habits that make it a good one.
In this lesson 4 sections
Every SQL query answers one question: which data do I want back? SELECT names the columns, FROM names the table.
SELECT opportunity_id, name, stage, amount
FROM opportunities;That returns four columns for every row in opportunities.
The sample tables
Every lesson in this track uses the same tables from a small sales CRM. They match the SQL field guide, so you can move between the two.
| Table | One row is | Columns you’ll use most |
|---|---|---|
accounts |
one customer company | account_id, name, industry, segment, region |
opportunities |
one sales deal | opportunity_id, account_id, owner_id, name, stage, amount, lead_source, created_at, close_date, closed_at, is_won |
users |
one sales rep | user_id, name, manager_id |
contacts |
one person at a customer | contact_id, account_id, name, email, title, created_at |
opportunity_history |
one stage change on a deal | history_id, opportunity_id, stage, changed_at |
A deal is open until it closes, so closed_at and is_won are empty (NULL) on open deals.
The SQL sandbox at the bottom of every lesson has these tables loaded with sample data. Run the examples there as you read.
Why not SELECT *?
SELECT * returns every column. It’s fine for poking around, but in real work:
- It pulls data you don’t need, which is slower and, on cloud data warehouses that charge for the data they scan, costs more.
- Your results change shape when someone adds a column.
- Readers can’t tell which columns you actually care about.
Name the columns you need.
Renaming columns
Use AS to give a column a clearer name in the results:
SELECT name AS deal_name, amount AS deal_value
FROM opportunities;Snowflake notes
Snowflake stores each column separately, so a query only reads the columns it names. SELECT * reads every column, which takes longer and uses more compute credits. On a wide table, naming three columns instead of fifty can cut a query’s cost sharply.
Try it
Return only the name and industry columns from accounts.
Write it in the sandbox below, then press Check answer.
SQL sandbox
Run queries against the sample CRM tables (accounts, opportunities,users, contacts and opportunity_history), right in your browser. Nothing you run leaves your computer.