SELECT: columns, expressions, aliases, DISTINCT

Part of the free LogicWiz SQL & Databases course, Module 2: Your first queries. The queries and practice run in the app on real Postgres.

A company's placement form wants each student's name, CGPA and percentage (CGPA × 9.5). The table has no percentage column. Aarav and Meher need it anyway, by this evening.

You'll learn to

  • Pick exactly the columns you need, in your order
  • Compute new columns with expressions and name them with AS
  • Remove repeated rows with DISTINCT, and know what it really compares

Learn

Pick your columns

SELECT name, city, cgpa
FROM students;

List columns after SELECT, separated by commas. They come back in the order you list them, not the table's order. Real apps almost always name columns instead of using *: less data travels, and the query does not change meaning when someone adds a column.

Expressions make new columns

SELECT name, cgpa, cgpa * 9.5 AS percentage
FROM students;

cgpa * 9.5 is worked out for every row. Nothing is saved in the table; the column exists only in this answer. Look at Aditya: his CGPA is NULL (unknown), so his percentage is NULL too. Maths with an unknown gives an unknown.

Aliases with AS

AS percentage gives a column a name. Without it Postgres calls the column ?column?, which no app can use.

  • AS is optional: cgpa * 9.5 percentage also works, but AS reads better.
  • Names are case-insensitive and folded to lower case: AS Percentage comes out as percentage.
  • For spaces or capitals, use double quotes: AS "Final %".
  • Single quotes are for text values ('Delhi'), never for names.

SQL keywords are case-insensitive too; we write them in capitals only so they stand out.

DISTINCT: each value once

SELECT DISTINCT city
FROM students;

Twenty students, but only 13 cities. DISTINCT drops repeated rows from the answer. Without it you get 20 rows, with Delhi three times.

DISTINCT looks at the whole row

SELECT DISTINCT city, gender
FROM students;

Now Lucknow appears twice: (Lucknow, M) and (Lucknow, F) are different rows. DISTINCT removes rows that are equal in every selected column, not just the first one.

SELECT DISTINCT city, name does not mean "distinct cities, plus a name". It means distinct (city, name) pairs, and since every student has a different name you get all 20 rows back. DISTINCT is not a function on one column; it applies to the whole selected row.

MySQL quotes names with backticks: AS `Final %` . Postgres uses double quotes: AS "Final %". In MySQL, double quotes often mean a text value, so "Delhi" works there but is a column name in Postgres. Use single quotes for text and your SQL runs on both.

Interviewers ask: "What does SELECT DISTINCT a, b do?" (unique pairs), "Is AS required?" (no), and "Can I use a SELECT alias in WHERE?" (no in Postgres and MySQL, because WHERE runs before SELECT; you will see why in Module 4).

In one line

SELECT picks columns and computes expressions; AS names them; DISTINCT removes rows equal in every selected column.