SELECT Queries — The Foundation

The SELECT Statement

SELECT is the most important SQL statement — you'll use it in virtually every query you write. It retrieves data from one or more tables.

Basic Syntax

SELECT column1, column2, ...
FROM table_name;

• SELECT — specifies which columns to return
• FROM — specifies which table to read from

Sample Tables

We'll use these tables throughout this chapter:

-- employees table
CREATE TABLE employees (
    id            INTEGER PRIMARY KEY,
    name          TEXT    NOT NULL,
    department    TEXT    NOT NULL,
    salary        REAL    NOT NULL,
    hire_date     TEXT    NOT NULL,
    email         TEXT,
    manager_id    INTEGER
);

INSERT INTO employees VALUES
(1, 'Priya Sharma',  'Finance',    75000, '2022-03-15', 'priya@meritshot.com', 4),
(2, 'Raj Patel',     'Technology', 92000, '2021-07-01', 'raj@meritshot.com',   NULL),
(3, 'Meera Singh',   'Marketing',  68000, '2023-01-10', 'meera@meritshot.com', NULL),
(4, 'Arjun Nair',    'Finance',    81000, '2020-11-22', 'arjun@meritshot.com', NULL),
(5, 'Sunita Rao',    'Technology', 88000, '2022-09-05', 'sunita@meritshot.com', 2),
(6, 'Dev Kumar',     'Marketing',  72000, '2021-04-18', 'dev@meritshot.com',   3);

Selecting Specific Columns

Instead of retrieving all columns with *, name exactly what you need:

SELECT name, department
FROM employees;

Result:

name          | department
--------------+------------
Priya Sharma  | Finance
Raj Patel     | Technology
Meera Singh   | Marketing
Arjun Nair    | Finance
Sunita Rao    | Technology
Dev Kumar     | Marketing

Why Avoid SELECT *

Using SELECT * in production code is generally bad practice:

1. Performance — retrieves columns you don't need, wastes bandwidth
2. Fragility — if someone adds a column to the table, your query returns unexpected data
3. Readability — other developers can't see what data the query uses without checking the table schema

Rule: Use SELECT * only when exploring data interactively. In application code and reports, always list column names explicitly.

Column Aliases with AS

You can rename columns in your results using AS:

SELECT
    name        AS employee_name,
    department  AS dept,
    salary      AS annual_salary
FROM employees;

Result:

employee_name | dept       | annual_salary
--------------+------------+--------------
Priya Sharma  | Finance    | 75000
Raj Patel     | Technology | 92000
...

Aliases with Spaces

If your alias has spaces, wrap it in double quotes:

SELECT
    name   AS "Full Name",
    salary AS "Annual Salary (₹)"
FROM employees;

AS is Optional

The AS keyword is optional — these are identical:

SELECT name AS employee_name FROM employees;
SELECT name employee_name FROM employees;  -- works but less readable

Best practice: always include AS for clarity.

Expressions in SELECT

You can compute values directly in a SELECT clause:

Arithmetic

SELECT
    name,
    salary,
    salary * 1.10       AS salary_with_10pct_raise,
    salary / 12         AS monthly_salary,
    salary * 0.20       AS tax_estimate
FROM employees;

Result:

name         | salary | salary_with_10pct_raise | monthly_salary | tax_estimate
-------------+--------+-------------------------+----------------+--------------
Priya Sharma | 75000  | 82500                   | 6250           | 15000
Raj Patel    | 92000  | 101200                  | 7666.67        | 18400
...

String Concatenation

Combine text columns into a single value:

-- PostgreSQL / SQLite
SELECT
    name || ' (' || department || ')' AS name_with_dept
FROM employees;

-- MySQL
SELECT
    CONCAT(name, ' (', department, ')') AS name_with_dept
FROM employees;

Result:

name_with_dept
------------------------------
Priya Sharma (Finance)
Raj Patel (Technology)
Meera Singh (Marketing)

String Functions

Common built-in string functions:

SELECT
    name,
    UPPER(name)           AS name_upper,
    LOWER(department)     AS dept_lower,
    LENGTH(name)          AS name_length,
    SUBSTR(name, 1, 5)    AS first_5_chars   -- SQLite/PostgreSQL: SUBSTRING
FROM employees;

Result:

name         | name_upper    | dept_lower | name_length | first_5_chars
-------------+---------------+------------+-------------+--------------
Priya Sharma | PRIYA SHARMA  | finance    | 12          | Priya
Raj Patel    | RAJ PATEL     | technology | 9           | Raj P

Numeric Functions

SELECT
    salary,
    ROUND(salary / 12, 2)   AS monthly_rounded,
    ABS(-salary)             AS absolute_value,
    CEIL(salary / 1000)      AS thousands_ceiling,
    FLOOR(salary / 1000)     AS thousands_floor
FROM employees;

Literal Values

You can include constant values in SELECT:

SELECT
    name,
    'Meritshot Academy'  AS company,
    2026                 AS current_year,
    salary
FROM employees;

Result:

name         | company           | current_year | salary
-------------+-------------------+--------------+--------
Priya Sharma | Meritshot Academy | 2026         | 75000
Raj Patel    | Meritshot Academy | 2026         | 92000

The DISTINCT Keyword

DISTINCT removes duplicate rows from the result:

-- Without DISTINCT — shows all department values
SELECT department FROM employees;
-- Finance, Technology, Marketing, Finance, Technology, Marketing

-- With DISTINCT — shows each department only once
SELECT DISTINCT department FROM employees;

Result:

department
----------
Finance
Technology
Marketing

DISTINCT on Multiple Columns

When used with multiple columns, DISTINCT considers the combination of all listed columns:

SELECT DISTINCT department, manager_id
FROM employees;

This returns each unique (department, manager_id) pair — not each unique department or each unique manager_id separately.

NULL in SELECT

Columns with NULL values display as empty (or the word NULL, depending on your client):

SELECT name, manager_id
FROM employees;

name         | manager_id
-------------+-----------
Priya Sharma | 4
Raj Patel    |          ← NULL (no manager)
Meera Singh  |          ← NULL
Arjun Nair   |          ← NULL
Sunita Rao   | 2
Dev Kumar    | 3

Replacing NULL with COALESCE

COALESCE returns the first non-NULL value from a list:

SELECT
    name,
    COALESCE(manager_id, 0)  AS manager_id_or_zero,
    COALESCE(email, 'no email provided')  AS contact
FROM employees;

CASE Expressions

CASE works like an if/else inside a query:

SELECT
    name,
    salary,
    CASE
        WHEN salary >= 90000 THEN 'Senior'
        WHEN salary >= 75000 THEN 'Mid-level'
        ELSE 'Junior'
    END AS seniority
FROM employees;

Result:

name         | salary | seniority
-------------+--------+----------
Priya Sharma | 75000  | Mid-level
Raj Patel    | 92000  | Senior
Meera Singh  | 68000  | Junior
Arjun Nair   | 81000  | Mid-level
Sunita Rao   | 88000  | Mid-level
Dev Kumar    | 72000  | Junior

Simple CASE (Equality Checks)

SELECT
    name,
    department,
    CASE department
        WHEN 'Finance'    THEN 'FIN'
        WHEN 'Technology' THEN 'TECH'
        WHEN 'Marketing'  THEN 'MKT'
        ELSE 'OTHER'
    END AS dept_code
FROM employees;

Ordering the SELECT Clause

There is no guaranteed ordering of rows in SQL unless you add ORDER BY (covered in Chapter 4). SQL tables are conceptually unordered sets. Never rely on "natural" row order.

Practical Examples

Example 1: Employee Directory

SELECT
    id,
    name                              AS "Employee Name",
    department                        AS "Department",
    ROUND(salary / 12, 2)             AS "Monthly Salary",
    COALESCE(manager_id::TEXT, 'None') AS "Reports To"
FROM employees;

Example 2: Salary Summary per Person

SELECT
    name,
    salary                          AS annual,
    ROUND(salary / 12, 2)           AS monthly,
    ROUND(salary / 52, 2)           AS weekly,
    ROUND(salary * 0.30, 2)         AS estimated_tax,
    ROUND(salary * 0.70, 2)         AS net_after_tax
FROM employees;

Example 3: Grade Classification

SELECT
    name,
    salary,
    CASE
        WHEN salary > 90000 THEN 'Band A'
        WHEN salary > 75000 THEN 'Band B'
        WHEN salary > 65000 THEN 'Band C'
        ELSE 'Band D'
    END AS pay_band
FROM employees;

Common Mistakes

1. Selecting a column that doesn't exist

-- Wrong
SELECT employee_name FROM employees;
-- Error: column "employee_name" does not exist

-- Correct (the column is named "name")
SELECT name FROM employees;

2. Confusing column alias with original name in the same query

-- Wrong — cannot use alias in WHERE of same SELECT
SELECT salary * 1.1 AS new_salary
FROM employees
WHERE new_salary > 80000;  -- Error!

-- Correct — use a subquery or repeat the expression
SELECT salary * 1.1 AS new_salary
FROM employees
WHERE salary * 1.1 > 80000;

3. Arithmetic with NULL

-- Any arithmetic with NULL returns NULL
SELECT 100 + NULL;   -- returns NULL
SELECT salary + NULL AS result FROM employees;  -- all NULLs

Practice Exercises

1. Write a query to display only the name and email columns from the employees table.
2. Display each employee's name and their annual salary increased by 15%, aliased as new_salary.
3. Use a CASE expression to label employees: those in Finance as 'Accounts', Marketing as 'Outreach', Technology as 'Engineering'.
4. Display the unique departments from the employees table (no duplicates).
5. Show each employee's name and a new column display_label formatted as "Name — Department" (e.g., "Priya Sharma — Finance").

Summary

In this chapter you learned:

• SELECT column1, column2 FROM table retrieves specific columns
• Avoid SELECT * in production — name your columns explicitly
• AS creates aliases for columns in the result
• You can use arithmetic operators (+, -, *, /) directly in SELECT
• String functions: UPPER(), LOWER(), LENGTH(), SUBSTR()
• DISTINCT removes duplicate rows from results
• COALESCE(col, default) substitutes a value for NULL
• CASE WHEN ... THEN ... ELSE ... END adds conditional logic
• NULL propagates through arithmetic — always handle NULLs explicitly

Next up: Filtering with WHERE — learn to retrieve only the rows that match specific conditions.