Starting here? This lesson is part of a full-length tutorial in using SQL for Data Analysis. Check out the beginning.
Basic syntax: SELECT and FROM
There are two required ingredients in any SQL query:
FROM—and they have to be in that order.
SELECT indicates which columns you’d like to view, and
FROM identifies the table that they live in.
Let’s start by looking at a couple columns from the housing unit table:
To see the results yourself, copy and paste this query into Mode’s Query Editor and run the code. If you already have SQL code in the Query Editor, you’ll need to paste over or delete the query that was there previously. If you simply copy and paste this query below the previous one, you’ll get an error—you can only run one
SELECT statement at a time.
Try it out.
So what’s happening in the above query? In this case, the query is telling the database to return the
west columns from the table
tutorial.us_housing_units. (Remember that when referencing tables, the table names have to be preceded by the name of user who uploaded it.) When you run this query, you’ll get back a set of results that shows values in each of these columns.
Note that the three column names were separated by a comma in the query. Whenever you select multiple columns, they must be separated by commas, but you should not include a comma after the last column name.
If you want to select every column in a table, you can use
* instead of the column names:
Now try this practice problem for yourself:
Write a query to select all of the columns in the
tutorial.us_housing_units table without using
Note: Practice problems will appear in boxes like the one above throughout this tutorial.
When you’ve completed the above practice problem, check your answer by clicking “See the answer.” You’ll land in Mode’s Report View—a cleaned-up view of a query meant for sharing queries and results. You can learn more about the report view on Mode’s help site, but for now, here’s what you need to know:
- Following the link will bring you to the Report page of the Report View. The results should look like the results your query produced.
If your results don’t match (or even if they do), you can check out the answer query by clicking the “Details” link at the top of the Report View first:
Then, click “SQL” in the sidebar on the left:
If you’re feeling particularly proud of your work, you can share this report with anyone by clicking the “Share” menu in the Query Editor and clicking Copy. Paste the link wherever you want people to see it.
Copy the URL in the share menu and send to all your friends!
Before you publish, you might want to add a title and description to let people know what you’ve made. To do this, click “Untitled Report”:
Then add a title and description for your work. If you want everyone on Mode to be able to see it, click the “Publish” toggle. Even if you choose not to publish to everyone, you can still save the report and send the link to your friends as described above—it just won’t be discoverable by the rest of the Mode community.
What actually happens when you run a query?
When you run a query, what do you get back? As you can see from running the queries above, you get a table. But that table isn’t stored permanently in the database. It also doesn’t change any tables in the database—
tutorial.us_housing_units will contain the same data every time you query it, and the data will never change no matter how many times you query it. Mode does store all of your results for future access, but
SELECT statements don’t change anything in the underlying tables.
You might have noticed that the
SELECT and `FROM’ commands are capitalized. This isn’t actually necessary—SQL will understand these commands if you type them in lowercase. Capitalizing commands is simply a convention that makes queries easier to read. Similarly, SQL treats one space, multiple spaces, or a line break as being the same thing. For example, SQL treats this the same way it does the previous query:
SELECT * FROM tutorial.us_housing_units
It also treats this the same way:
While most capitalization conventions are the same, there are several conventions for formatting line breaks. You’ll pick up on several of these in this tutorial and in other people’s work on Mode. It’s up to you to determine what formatting method is easiest for you to read and understand.
While we’re on the topic of formatting, it’s worth noting the format of column names. All of the columns in the
tutorial.us_housing_units table are named in lower case, and use underscores instead of spaces. The table name itself also uses underscores instead of spaces. Most people avoid putting spaces in column names because it’s annoying to deal with spaces in SQL—if you want to have spaces in column names, you need to always refer to those columns in double quotes.
If you’d like your results to look a bit more presentable, you can rename columns to include spaces. For example, if you want the
west column to appear as
West Region in the results, you would have to type:
SELECT west AS "West Region"
Without the double quotes, that query would read ‘West’ and ‘Region’ as separate objects and would return an error. Note that the results will only return capital letters if you put column names in double quotes. The following query, for example, will return results with lower-case column names.
SELECT west AS West_Region,
south AS South_Region
Sharpen your SQL skills
Write a query to select all of the columns in
tutorial.us_housing_units and rename them so that their first letters are capitalized.
Find the report but not the SQL?
Once you’ve got the report open, see the SQL powering it by clicking on “View Details” at the top, then clicking “SQL” in the sidebar on the left.