SQL SELECT Basics: Get Data Out of a Table (Beginner Tutorial)
The SELECT statement is how you get data out of any table. Learn SELECT star, picking columns, renaming them with AS, and adding a calculated column, on a sample online shop database.
Step by step
Select
- Get data out. SELECT is how you get data out of a table in SQL. The examples here use a sample database for an online shop.
- The customers table. We start with the customers table. The column list on the left shows its columns, such as first_name, last_name, email and city.
Select star
- SELECT star. Type SELECT * FROM customers; on 2 lines. The * means every column, FROM names the table, and the semicolon marks the end of the query. Run it. You get every column for every customer, which is handy for a first look at a table.
SELECT * FROM customers; - Forty rows. The result has 40 rows. Reports rarely need every column, and on a big table SELECT * can be slow because it pulls back data you don't need.
Pick columns
- Pick columns. Instead, name the columns you need. Replace the * with first_name, last_name, city, with a comma between each name but none after the last one. Run it. You get the same 40 customers, but only 3 columns, which is much easier to read.
first_name, last_name, city
Rename with AS
- Rename with AS. Column names from a database can be hard to read. AS gives a column a new name in the result, so change last_name to last_name AS surname. Then run the query again.
last_name AS surname - The new name. The column header now says surname. The customers table itself didn't change; only this result uses the new name.
Calculated column
- Calculate. SELECT can also calculate. Clear the editor and query the products table: unit_price * 1.2 gives the price with 20% tax, and AS with_tax names the new column. Run it. The result has 24 rows, one per product, each with its unit_price and its with_tax price.
SELECT product_name, unit_price, unit_price * 1.2 AS with_tax FROM products; - Price with tax. The with_tax column isn't stored in the database. SQL works it out each time you run the query, so it always uses the current unit_price.
Recap
- SELECT in four moves. In short: SELECT * shows every column, naming columns keeps the result tidy, AS renames them, and you can calculate new ones. The next step is filtering rows with WHERE.
Common mistakes
- A comma after the last column. SELECT first_name, last_name, city, FROM customers; gives a syntax error in most databases (such as MySQL, PostgreSQL, SQL Server and SQLite). Put commas only between column names so your query works everywhere.
- A missing comma between 2 columns. SELECT first_name last_name FROM customers; often runs without an error, but SQL reads last_name as a new name for first_name. You get 1 column called last_name full of first names, so check every gap has a comma.
- Typing the column name as it sounds. first name with a space is not the same as first_name, and the query fails. Copy the exact names from the column list, underscores included.
- Thinking AS renames the column in the table. AS only changes the header in this one result. The table still has a column called last_name, so other queries must still use that name.
When a business analyst uses this
During user acceptance testing of a checkout screen, a business analyst can run SELECT product_name, unit_price, unit_price * 1.2 AS with_tax FROM products; and compare the with_tax column against the prices the screen shows. Any product that doesn't match goes in the defect log before sign-off.
Questions people ask
What is a basic SELECT statement in SQL?
It is SELECT, then the columns you want, then FROM and the table name, like SELECT first_name, last_name, city FROM customers;. Use * instead of column names to get every column.
What are some simple SQL SELECT examples?
SELECT * FROM customers; shows every column, and SELECT last_name AS surname FROM customers; renames a column in the result. SELECT product_name, unit_price * 1.2 AS with_tax FROM products; adds a calculated column.
How do WHERE and ORDER BY fit into a SELECT query?
They both go after FROM. WHERE keeps only the rows that meet a condition, and ORDER BY sorts the result. When you use both, WHERE comes first.
