Lesson 1: SELECT Fundamentals (Projection, Filtering, Sorting)

Section 3 — Lesson 1: SELECT Fundamentals (Projection, Filtering, Sorting)

Welcome to Section 3. In Section 2, you learned how databases are designed and protected:
tables, keys, constraints, relationships, transactions, normalization, and indexes.

Now we move into the daily language of DBAs: T-SQL. If you want to be job-ready, you must be able to:
read queries quickly, write correct queries safely, and understand what your query is asking SQL Server to do.

This lesson focuses on the most important statement in SQL: SELECT.
You will learn the fundamentals in a way that matches real DBA work:
projection (what columns you return),
filtering (which rows you return),
and sorting (how results are ordered).

Lesson description: You will learn the SELECT statement structure, how SQL Server processes it conceptually,
how to choose the right columns, how to filter safely using WHERE, how sorting works with ORDER BY,
and how to avoid common beginner mistakes that cause incorrect results and performance problems.
You will also practice with DBA-style examples (auditing, troubleshooting, validation, reporting).

1) The Purpose of SELECT (DBA View)

A SELECT query answers a question about your data. As a DBA, you use SELECT for:

• Validation: “Did the data load correctly?”
• Troubleshooting: “Which session is blocked?” “Which jobs failed?”
• Reporting support: “How many orders were created today?”
• Auditing: “Which accounts were created recently?”
• Operational checks: “What changed after the deployment?”

Professional standard: Your SELECT queries must be correct, readable, and safe.
Wrong queries lead to wrong decisions.

2) SELECT Structure (The Standard Pattern)

At its simplest, SELECT has this shape:

SELECT column_list
FROM   table_name
WHERE  filter_condition
ORDER BY sort_columns;

You will not always use every clause, but this pattern is the foundation.

3) Projection (Choosing the Columns You Return)

Projection means selecting which columns to return.
New learners often use SELECT *. DBAs avoid this in most real work.

Why DBAs avoid SELECT *

• Performance: returns unnecessary columns (more I/O, more network transfer).
• Stability: if the table changes (new column added), your result shape changes unexpectedly.
• Clarity: other people can’t tell what data you actually need.
• Security: you may accidentally expose sensitive fields (PII, tokens, hashes).

DBA best practice: explicitly list columns except for quick exploration in a safe environment.

Example: “Show basic customer identity”

SELECT
    CustomerID,
    FullName,
    Email,
    CreatedAt
FROM dbo.Customer;

Column aliasing (clean output)

Aliases improve readability for reports and troubleshooting.

SELECT
    CustomerID      AS CustomerId,
    FullName        AS CustomerName,
    Email           AS EmailAddress,
    CreatedAt       AS CreatedDate
FROM dbo.Customer;

DBA habit: write queries that another DBA can understand in 30 seconds.

4) FROM Clause (Where Data Comes From)

The FROM clause defines the source table (or view). DBAs always qualify objects clearly.

Use schema-qualified names

In SQL Server, prefer dbo.TableName (or the correct schema) to avoid ambiguity.

SELECT BookingID, ConfirmationCode, PickupAt
FROM dbo.Booking;

DBA note: In production, multiple schemas may exist (dbo, sales, security, etc.).
Schema-qualified queries are safer and clearer.

5) Filtering (WHERE) — Returning Only the Rows You Need

Filtering means selecting which rows to return based on conditions.
This is where correctness matters most.

Basic filtering

SELECT
    BookingID,
    ConfirmationCode,
    Status,
    PickupAt
FROM dbo.Booking
WHERE Status = 'CONFIRMED';

Multiple conditions (AND / OR)

Use parentheses with OR conditions. Without parentheses, you can return the wrong rows.

-- Confirmed bookings scheduled in the next 24 hours
SELECT
    BookingID,
    ConfirmationCode,
    Status,
    PickupAt
FROM dbo.Booking
WHERE Status = 'CONFIRMED'
  AND PickupAt >= SYSDATETIME()
  AND PickupAt <  DATEADD(HOUR, 24, SYSDATETIME());

IN (cleaner than many ORs)

SELECT
    BookingID,
    ConfirmationCode,
    Status
FROM dbo.Booking
WHERE Status IN ('NEW','CONFIRMED');

LIKE (pattern matching)

LIKE is useful, but remember that leading wildcards often prevent efficient index seeks.

-- Customers with emails in a specific domain
SELECT CustomerID, FullName, Email
FROM dbo.Customer
WHERE Email LIKE '%@example.com';

Filtering with NULL (critical DBA detail)

NULL is not equal to anything, not even another NULL. You must use IS NULL or IS NOT NULL.

-- Find customers missing phone numbers
SELECT CustomerID, FullName, Email
FROM dbo.Customer
WHERE Phone IS NULL;

DBA warning: Writing WHERE Phone = NULL returns no rows.
This is one of the most common beginner mistakes.

6) Sorting (ORDER BY) — How to Control Result Order

SQL tables have no guaranteed “natural order.” If you want results in a specific order, you must use ORDER BY.

Sort ascending (default)

SELECT BookingID, ConfirmationCode, PickupAt
FROM dbo.Booking
ORDER BY PickupAt;

Sort descending

SELECT BookingID, ConfirmationCode, PickupAt
FROM dbo.Booking
ORDER BY PickupAt DESC;

Sort by multiple columns

This is common in DBA reporting queries.

SELECT BookingID, ConfirmationCode, Status, PickupAt
FROM dbo.Booking
ORDER BY Status ASC, PickupAt DESC;

DBA performance note: ORDER BY can be expensive for large result sets because SQL Server may need to sort.
Good indexing can help when the ORDER BY matches the index key order.

7) The Logical Processing Order (DBA Clarity)

SQL is written in one order, but it is logically processed in another. This matters when you write correct queries.

• FROM (choose source)
• WHERE (filter rows)
• SELECT (choose columns)
• ORDER BY (sort the final output)

You will expand this later with GROUP BY and HAVING, but for now, memorize this sequence.
It prevents many confusing mistakes.

8) DBA-Grade Query Writing Rules (Simple, Professional, Safe)

1. List columns explicitly (avoid SELECT * in real work).
2. Use schema-qualified names (dbo.TableName).
3. Filter intentionally (WHERE clause matters for correctness and performance).
4. Use correct NULL logic (IS NULL / IS NOT NULL).
5. Sort only when needed (ORDER BY has cost).
6. Make it readable (indentation, column list formatting).

9) Practice (Write These and Check Your Results)

Use your lab tables (Customer, Booking, Service, Payment) and write these queries:

1. Return CustomerID, FullName, Email for all customers, ordered by CreatedAt descending.
2. Return bookings where Status is NEW or CONFIRMED, ordered by PickupAt ascending.
3. Return customers where Phone is NULL.
4. Return bookings scheduled in the next 24 hours (use SYSDATETIME and DATEADD).

DBA habit: After each query, ask: “Did I return only what I need? Are the results correct? Is it readable?”

Lesson Summary

• Projection: choose columns; avoid SELECT * for professional work.
• Filtering: use WHERE to return the right rows; handle NULL correctly.
• Sorting: ORDER BY controls output order; it can be expensive for large data.
• SQL logical order: FROM → WHERE → SELECT → ORDER BY.

Next Lesson: WHERE, ORDER BY, TOP, DISTINCT, and NULL Handling (DBA Accuracy Skills)

In the next lesson, we will go deeper into filtering and result control:
TOP for limiting output, DISTINCT for de-duplication, correct NULL handling patterns,
and professional “safe query” techniques DBAs use to avoid mistakes in production.