Velamuvi AIAcademy

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
In this lesson you will learn
  • 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.

  • SELECT says which columns you want.
  • FROM says 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.

Try it yourselfSQL
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.

Try it yourselfSQL
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.

Try it yourselfSQL
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.

Try it yourselfSQL
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.

Try it yourselfSQL
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.

Try it yourselfSQL
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.

Try it yourselfSQL
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.

Try it yourselfSQL
-- Show how long each course takes
SELECT title, hours   -- hours is the planned study time
FROM courses;

Common mistakes

Practice exercise

Select the title and category of every course. Rename category to topic in the result.

Your solutionSQL
-- Show two columns from the courses table:
-- the title, and the category renamed to "topic"
Knowledge check

What does AS do in SELECT name AS student_name FROM students; ?

Summary

  • SELECT column1, column2 FROM table; returns only the columns you name.
  • AS renames 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.