SQL SELECT Clause ¶

The SELECT clause specifies what data we are selecting.

• Select all attributes

SELECT * [...]

• Select some attributes (id, name, age, and gender)

SELECT id, name, age, gender [...]

• No duplicate results (each line once)

SELECT DISTINCT name [...]

• Rename output columns

SELECT name as 'Customer name' [...]

• Select an expression

SELECT 5 [...]
SELECT LEFT(name, 1) as 'First letter' [...]

-- concatenate Ms/Mr/none and the name
SELECT CONCAT((CASE gender
                WHEN 'Woman' THEN 'Ms. '
                WHEN 'Man' THEN 'Mr. '
                ELSE '' -- default
        END), name) as 'Name'
FROM customer

• Simple calculation

SELECT age+1 [...]

• Functions

SELECT SUM(age) [...]; -- 18+24
SELECT AVG(age) [...]; -- (18+24)/2
SELECT MIN(age) [...]; -- 18
SELECT MAX(age) [...]; -- 24
SELECT COUNT(*) [...]; -- 2 = number of tuples

• You may add DISTINCT (no duplicates) or ALL (default, allow duplicates)

SELECT COUNT(DISTINCT age) FROM customer; -- 2 (no duplicates ages)
SELECT SUM(DISTINCT age) FROM customer; -- 18 + 24
SELECT SUM(ALL age) FROM customer; -- same as SUM(age)