Lesson 2 of 17 · First Queries
SELECT and FROM
Learn the SQL SELECT statement. Pick the columns you want, rename them with AS, calculate new values and write queries that are easy to read.
- 8 min read
- Beginner
- Reviewed
- How to select only the columns you need
- How to rename a column in the result with AS
- How to calculate new values inside a query
- How to lay out SQL so it is easy to read
The two words in every query
Almost every query you will ever write starts with the same two keywords.
SELECTsays which columns you want.FROMsays which table they come from.
In the last lesson you used SELECT * to get every column. Usually you want only a few.
Choosing columns
List the column names after SELECT, separated by commas.
SELECT name, country FROM students;
The result has only the two columns you asked for, in the order you wrote them. Swap them and the result swaps too.
SELECT country, name, age FROM students;
Why not always use SELECT *?
SELECT * is handy when you are exploring a table for the first time. In real work, name your columns:
- A real table may have fifty columns. You rarely need them all.
- Fetching less data is faster.
- The query documents itself: a reader can see exactly what it returns.
Renaming columns with AS
AS gives a column a different name in the result. The new name is called an alias. The table itself is not changed.
SELECT name AS student_name, country AS home_country FROM students;
Aliases make results clearer, especially for calculated columns.
Calculating new values
A query can do arithmetic. Each course has a length in hours. This query also shows it in minutes.
SELECT title, hours, hours * 60 AS minutes FROM courses;
The minutes column does not exist in the table. The database works it out for each row when the query runs.
You can use +, -, * and /. One thing to watch: dividing two whole numbers gives a whole number.
SELECT 7 / 2 AS whole_division, 7 / 2.0 AS decimal_division;
7 / 2 gives 3, because both numbers are integers. Write one of them with a decimal point to get 3.5.
Joining text
The || operator glues pieces of text together. Text values are written in single quotes.
SELECT name || ' is from ' || country AS sentence FROM students;
Look at the last row of the result: it is NULL. One student has no country on record, and joining anything to a missing value gives a missing value. You will learn how to handle that in the lesson on NULL.
Writing readable SQL
SQL does not care about capital letters or line breaks. These two queries do exactly the same thing.
select name, age from students; SELECT name, age FROM students;
The second style is the one professionals use:
- Keywords in capitals (
SELECT,FROM), names in lowercase. - Each clause on its own line.
- A semicolon at the end of every query.
Comments
Two dashes start a comment. The database ignores the rest of the line.
-- Show how long each course takes SELECT title, hours -- hours is the planned study time FROM courses;
Common mistakes
Select the title and category of every course. Rename category to topic in the result.
-- Show two columns from the courses table: -- the title, and the category renamed to "topic"
What does AS do in SELECT name AS student_name FROM students; ?
Summary
SELECT column1, column2 FROM table;returns only the columns you name.ASrenames a column in the result.- You can calculate values with
+,-,*and/, and join text with||. - Text values go in single quotes.
- Write keywords in capitals and put each clause on its own line.
Next: choosing which rows you want.
Finished reading? Tick it off to track your progress.