SELECT, WHERE and ORDER BY Basics – SQL Server | Lesson 16

Published / updated4 Oct 2026

Module 4 · Lesson 16Beginner30 minInterview:FMSA

Before this lesson: Sample Data and 10,000 Students with INSERT – SQL Server

Introduction

The SMS database is full: 10,012 students and over 700,000 rows. Now you start asking it questions. Every report, screen, and API in this course begins with a query.

This lesson covers the core of every query: choosing columns, filtering rows, and sorting the result. It ends with the two statements that change data, UPDATE and DELETE, and a safe way to use them.

By the end of this lesson, you will be able to:

• Read data with SELECT and FROM, and rename columns with AS.
• Filter rows with WHERE, using =, <>, BETWEEN, IN, LIKE, and IS NULL.
• Sort with ORDER BY, and limit rows with TOP.
• Remove repeated rows with DISTINCT.
• Change data with UPDATE and DELETE, checked first and inside a transaction.

Why this matters

The school asks questions all day: which students were born in 2011, or what is Rajesh Sharma's phone number? Each question is a SELECT.

The office also fixes mistakes: a wrong phone number, an attendance mark entered for the wrong day. An UPDATE without the right filter can change every row in a table in one second. This lesson teaches the habit that prevents that.

The concept

A query is a question you ask the database. SELECT is the statement for reading. Its simplest form names the columns and the table:

Example

SELECT FirstName, LastName
FROM dbo.Students;

Think of copying from a school register onto a blank page:

• SELECT decides which columns you copy.
• WHERE decides which rows you copy.
• ORDER BY decides the order you write them in.

Example: the order SQL Server runs a query

You write the clauses in one order, but SQL Server works through them in another:

Written order	Run order	Clause	Job
1	3	SELECT	Pick and name the columns
2	1	FROM	Choose the table
3	2	WHERE	Keep only matching rows
4	4	ORDER BY	Sort the result

This explains a common error. A column alias made with AS in SELECT does not exist yet when WHERE runs:

Example

SELECT DateOfBirth AS BornOn
FROM dbo.Students
WHERE BornOn > '2015-01-01';

Output

Msg 207, Level 16, State 1, Line 1
Invalid column name 'BornOn'.

ORDER BY runs after SELECT, so ORDER BY BornOn works. In WHERE, use the real column name, DateOfBirth.

Example: comparison operators

Operator	Meaning	SMS example
=	Equal	Gender = 'F'
<>	Not equal	Status <> 'Present'
>, <, >=, <=	Greater or smaller	MarksObtained >= 35
BETWEEN a AND b	From a to b, both included	DateOfBirth BETWEEN '2011-01-01' AND '2011-12-31'
IN (...)	Equal to any value in a list	Occupation IN (N'Teacher', N'Engineer')
LIKE	Matches a text pattern	LastName LIKE N'Sh%'
IS NULL	Has no value	Occupation IS NULL

Combine conditions with AND (both must be true) and OR (either can be true).

In LIKE patterns, % means "any text, even none", and _ means "exactly one character". N'Sh%' matches Sharma and Sheikh.

Example: NULL needs IS NULL

NULL means "unknown", so Occupation = NULL is never true, not even when the occupation is NULL. This query finds nothing:

Example

SELECT COUNT(*) AS Matches
FROM dbo.Guardians
WHERE Occupation = NULL;

The result is Matches = 0, even though six guardians have no occupation. Always use IS NULL or IS NOT NULL. COUNT(*) counts rows; you will use it properly in Lesson 18.

Apply it to the SMS

Save the full script below as queries\016-select-basics.sql, then run one query at a time: select its lines with the mouse and press F5. SSMS runs only the selected text.

Example: choose, filter, sort

The 12 students you know have Ids 1 to 12, so WHERE Id <= 12 keeps them apart from the 10,000 generated students:

Example

SELECT
    AdmissionNumber,
    FirstName,
    LastName,
    DateOfBirth AS BornOn
FROM dbo.Students
WHERE Id <= 12
ORDER BY LastName, FirstName;

AdmissionNumber	FirstName	LastName	BornOn
ADM-2021-0004	Zara	Ali	2015-12-11
ADM-2017-0005	Rohan	Das	2011-06-17
ADM-2021-0006	Kiara	Fernandes	2015-10-19
ADM-2017-0004	Meera	Iyer	2011-11-05
ADM-2021-0003	Vihaan	Kapoor	2015-09-02
ADM-2021-0005	Arnav	Mishra	2015-05-28
ADM-2017-0002	Diya	Patel	2011-08-23
ADM-2021-0002	Ananya	Reddy	2015-02-14
ADM-2017-0001	Aarav	Sharma	2011-04-12
ADM-2021-0001	Advika	Sharma	2015-07-21
ADM-2017-0006	Sana	Sheikh	2011-03-09
ADM-2017-0003	Kabir	Singh	2011-01-30

ORDER BY LastName, FirstName sorts by last name, and by first name when last names are equal: Aarav comes before Advika Sharma.

Example: several conditions

Girls born in 2011:

Example

SELECT FirstName, LastName, DateOfBirth
FROM dbo.Students
WHERE Id <= 12
  AND Gender = 'F'
  AND DateOfBirth BETWEEN '2011-01-01' AND '2011-12-31'
ORDER BY DateOfBirth;

FirstName	LastName	DateOfBirth
Sana	Sheikh	2011-03-09
Diya	Patel	2011-08-23
Meera	Iyer	2011-11-05

Example: IN, LIKE and IS NULL

Guardians who work as teachers or engineers, using IN:

FirstName	LastName	Occupation
Faisal	Ali	Engineer
Anjali	Mishra	Teacher
Rajesh	Sharma	Engineer
Simran	Singh	Teacher

Students whose last name starts with "Sh", using LIKE N'Sh%': Aarav Sharma, Advika Sharma, and Sana Sheikh.

Guardians whose occupation is unknown, using IS NULL: six rows, from Nadia Ali to Ayesha Sheikh.

Example: TOP and DISTINCT

TOP (3) keeps only the first three rows after sorting. The three youngest students:

Example

SELECT TOP (3) FirstName, LastName, DateOfBirth
FROM dbo.Students
WHERE Id <= 12
ORDER BY DateOfBirth DESC;

FirstName	LastName	DateOfBirth
Zara	Ali	2015-12-11
Kiara	Fernandes	2015-10-19
Vihaan	Kapoor	2015-09-02

DESC sorts from highest to lowest; the default, ASC, is lowest first. Without ORDER BY, TOP returns whichever rows SQL Server finds first, which can change.

SELECT DISTINCT Status FROM dbo.AttendanceRecords returns each attendance status once: Absent, Late, Leave, Present. Four rows out of more than 600,000.

Show queries/016-select-basics.sql

queries/016-select-basics.sql

-- Lesson 16: SELECT basics. Read-only: it changes nothing.
-- The 12 students you know have Ids 1 to 12 (Lesson 15).
USE SchoolDb;
GO

-- 1. Every column, every row.
SELECT *
FROM dbo.Subjects;

-- 2. Chosen columns, a renamed column, a filter, and a sort.
SELECT
    AdmissionNumber,
    FirstName,
    LastName,
    DateOfBirth AS BornOn
FROM dbo.Students
WHERE Id <= 12
ORDER BY LastName, FirstName;

-- 3. Several conditions: girls born in 2011.
SELECT FirstName, LastName, DateOfBirth
FROM dbo.Students
WHERE Id <= 12
  AND Gender = 'F'
  AND DateOfBirth BETWEEN '2011-01-01' AND '2011-12-31'
ORDER BY DateOfBirth;

-- 4. IN: guardians who work as teachers or engineers.
SELECT FirstName, LastName, Occupation
FROM dbo.Guardians
WHERE Occupation IN (N'Teacher', N'Engineer')
ORDER BY LastName, FirstName;

-- 5. LIKE: last names that start with "Sh".
SELECT FirstName, LastName
FROM dbo.Students
WHERE Id <= 12
  AND LastName LIKE N'Sh%'
ORDER BY LastName, FirstName;

-- 6. IS NULL: guardians whose occupation is not known.
SELECT FirstName, LastName
FROM dbo.Guardians
WHERE Occupation IS NULL
ORDER BY LastName;

-- 7. TOP with ORDER BY: the three youngest of the 12 students.
SELECT TOP (3) FirstName, LastName, DateOfBirth
FROM dbo.Students
WHERE Id <= 12
ORDER BY DateOfBirth DESC;

-- 8. DISTINCT: each attendance status once.
SELECT DISTINCT Status
FROM dbo.AttendanceRecords
ORDER BY Status;
GO

Example: safe UPDATE and DELETE

UPDATE changes values in existing rows. DELETE removes rows.

Both use a WHERE to say which rows. Without a WHERE, they change every row in the table.

The safe habit has three steps:

1. Run a SELECT with the exact WHERE you plan to use. Check the rows.
2. Start a transaction, then run the UPDATE or DELETE with the same WHERE.
3. Check the result and the row count, then COMMIT to keep it, or ROLLBACK to undo it.

This practice script fixes Rajesh's phone number, soft-deletes Sana (Lesson 14), and deletes one attendance record. It ends with ROLLBACK, so your data stays unchanged:

queries/016-update-delete.sql

-- Lesson 16: safe UPDATE and DELETE.
-- Practice runs inside a transaction and is rolled back, so your data does not change.
USE SchoolDb;
GO

BEGIN TRANSACTION;

-- 1. Correct a phone number. First SELECT with the same WHERE, then UPDATE.
SELECT Id, FirstName, LastName, Phone
FROM dbo.Guardians
WHERE Id = 1;

UPDATE dbo.Guardians
SET Phone = '9876549999',
    UpdatedAt = SYSUTCDATETIME()
WHERE Id = 1;

SELECT Id, FirstName, LastName, Phone
FROM dbo.Guardians
WHERE Id = 1;

-- 2. Soft delete a student (Lesson 14): mark the row, keep the data.
UPDATE dbo.Students
SET IsDeleted = 1,
    DeletedAt = SYSUTCDATETIME()
WHERE AdmissionNumber = 'ADM-2017-0006';

SELECT COUNT(*) AS VisibleFixedStudents
FROM dbo.Students
WHERE Id <= 12
  AND IsDeleted = 0;

-- 3. Delete one attendance record entered by mistake.
DELETE FROM dbo.AttendanceRecords
WHERE EnrollmentId = 17
  AND AttendanceDate = '2026-07-03';

SELECT @@ROWCOUNT AS RowsDeleted;  -- rows changed by the last statement

ROLLBACK TRANSACTION;  -- undo all three changes

-- Everything is back as it was.
SELECT Phone FROM dbo.Guardians WHERE Id = 1;
GO

What you see, in order:

Step	Result
Phone before	9876540101
Phone after UPDATE	9876549999
Visible fixed students after soft delete	11
RowsDeleted	1
Phone after ROLLBACK	9876540101

@@ROWCOUNT returns how many rows the last statement changed. If you expected 1 and see 600,036, you roll back before anyone notices. In real work, you replace ROLLBACK with COMMIT only after checking.

Common mistakes

1. UPDATE or DELETE without WHERE. Every row changes. Fix: write the WHERE first, test it with SELECT, and work inside a transaction.
2. = NULL instead of IS NULL. It never matches. Fix: use IS NULL.
3. Using a SELECT alias in WHERE. The alias does not exist yet. Fix: repeat the real column name in WHERE.
4. TOP without ORDER BY. You get random-looking rows. Fix: always sort.
5. SELECT * in application code. It returns columns you do not need and breaks when columns change. Fix: list the columns.

In real projects

From 16 years of .NET projects

Early in my career, I watched a colleague run UPDATE Customers SET Status = 'Closed' on production, meaning to add a WHERE on the next line. Forty thousand customers were closed in under a second. The restore took the rest of the day.

Since then, I follow one rule on any shared database: BEGIN TRANSACTION first, then the change, then a SELECT to check, and COMMIT only when the numbers are what I expected. On production, I go further: data fixes are written as scripts, reviewed by a second person, and run by the release process, not typed by hand.

Best practices

• List the columns you need instead of SELECT *.
• Always pair TOP with ORDER BY.
• Use IS NULL and IS NOT NULL for missing values.
• Test every UPDATE and DELETE with a SELECT using the same WHERE.
• Change data inside a transaction, and check @@ROWCOUNT before COMMIT.

Recap

• SELECT picks columns, FROM picks the table, WHERE picks rows, ORDER BY sorts.
• SQL Server runs FROM and WHERE before SELECT, so aliases are not usable in WHERE.
• BETWEEN, IN, LIKE, and IS NULL cover most filters.
• TOP limits rows after sorting; DISTINCT removes repeats.
• UPDATE and DELETE need a tested WHERE and a transaction.

Homework

Library module task: List the fiction books that are not deleted, sorted by title, with their author and published year.

A correct result looks like: two rows, "Malgudi Days" and "The Jungle Book".

Show the solution

library-assignment/016-library-select.sql

-- Library homework, Lesson 16. Read-only.
USE SchoolDb;
GO

-- Fiction books, by title.
SELECT Title, Author, PublishedYear
FROM dbo.Books
WHERE Category = N'Fiction'
  AND IsDeleted = 0
ORDER BY Title;
GO

Quiz

1. Which clause does SQL Server process first?

   1. SELECT
   2. FROM
   3. ORDER BY
   4. TOP
   Show answer

   Answer: B. FROM FROM runs first, then WHERE, then SELECT, then ORDER BY.

2. Which condition finds guardians with no occupation recorded?

   1. Occupation = NULL
   2. Occupation = ''
   3. Occupation IS NULL
   4. Occupation LIKE NULL
   Show answer

   Answer: C. Occupation IS NULL NULL is never equal to anything; use IS NULL.

3. What does LastName LIKE N'Sh%' match?

   1. Only 'Sh'
   2. Names ending in 'sh'
   3. Names starting with 'Sh'
   4. Names containing exactly three letters
   Show answer

   Answer: C. Names starting with 'Sh' % means any text, so Sh% is Sh followed by anything.

4. What does SELECT TOP (3) ... ORDER BY DateOfBirth DESC return?

   1. The three oldest students
   2. The three youngest students
   3. Any three students
   4. Students born in three different years
   Show answer

   Answer: B. The three youngest students DESC puts the latest dates of birth first, which are the youngest students.

5. Before running an UPDATE on real data, what should you do?

   1. Remove the WHERE to be safe
   2. Run a SELECT with the same WHERE and use a transaction
   3. Restart SQL Server
   4. Use SELECT *
   Show answer

   Answer: B. Run a SELECT with the same WHERE and use a transaction Check exactly which rows will change, then change them where you can still roll back.

Interview questions

Questions are grouped by experience level. Click a question to see a model answer. All questions: SQL Server interview questions.

Fresher (0-2 yrs)

F In what order does SQL Server process the clauses of a SELECT query? concept

Logically: FROM (and joins), WHERE, GROUP BY, HAVING, SELECT, ORDER BY, then TOP. That is why a column alias defined in SELECT cannot be used in WHERE, but can be used in ORDER BY.

See the example · Permalink

F Why does WHERE Occupation = NULL return no rows? concept

NULL means unknown, and comparing anything with unknown gives unknown, which is never true. So = NULL matches nothing, even rows that are NULL. Use IS NULL or IS NOT NULL instead.

See the example · Permalink

F What do the wildcards % and _ mean in LIKE? coding

% matches any text of any length, including none, and _ matches exactly one character. LastName LIKE N'Sh%' matches Sharma and Sheikh; LIKE '_A' matches any two-character value ending in A.

See the example · Permalink

Mid (3-5 yrs)

M Why should TOP always be used with ORDER BY? concept

Without ORDER BY, SQL Server returns whichever rows it reaches first, and that can change between runs, after an index change, or under load. TOP (3) ... ORDER BY DateOfBirth DESC always returns the three youngest students.

See the example · Permalink

M Why is SELECT * discouraged in application code? concept

It returns columns the code does not need, which wastes network and memory and can stop SQL Server from using a narrower index. It also changes silently when someone adds a column, which can break code that reads columns by position. List the columns you need.

See the example · Permalink

M How do you safely run an UPDATE or DELETE on important data? coding

Run a SELECT with the exact WHERE first and check the rows. Then start a transaction, run the change with the same WHERE, check @@ROWCOUNT and the data, and only then COMMIT; otherwise ROLLBACK.

See the example · Permalink

Senior (6-9 yrs)

S Someone ran an UPDATE without a WHERE clause on production. What do you do? scenario

Stop further changes to that table, find out exactly what changed and when, and restore the correct values: from a backup restored to a separate database, from audit data, or from a point-in-time restore. Communicate early with the business. Afterwards, change the process so it cannot happen again.

How to answer: Give a calm sequence: contain, assess, recover, communicate, prevent. Explain the recovery options and their trade-offs (restoring the whole database loses later work, so restore a copy and repair only the affected rows). Close with prevention: reviewed scripts, transactions, and limited write access to production.

See the example · Permalink

S Why can a search with LIKE N'%sharma%' be slow on a large table? concept

A pattern that starts with % cannot use an index seek, because the matching text could be anywhere in the value; SQL Server has to scan every row. A pattern like N'Sharma%' can seek. For true "contains" search on large tables, use full-text search or a search index.

How to answer: Explain index seeks vs scans in one sentence, then show the two patterns. Discuss the options for a student search screen: starts-with search, full-text search, or a separate search service, and the cost of each.

See the example · Permalink

Architect / Lead (10+ yrs)

A How would you design the process for data fixes on a production database? design

Data fixes are written as scripts, reviewed by a second person, tested on a copy of production, and run by the release process with a transaction and row-count checks. The script and its result are stored, so every change is traceable. Nobody types changes directly into production.

How to answer: Describe the workflow (request, script, review, test, approved run, record) and the controls (permissions, transactions, expected row counts, backups). Mention the trade-off: urgent fixes take minutes longer, which is cheap compared with one bad manual update.

See the example · Permalink

Resources

• Code for this lesson: git tag lesson-16-select-basics (code repository coming soon)
• Files: queries/016-select-basics.sql, queries/016-update-delete.sql, library-assignment/016-library-select.sql
• Video: coming soon

What's next

One table answers simple questions. Real questions need several tables together. Lesson 17: Joins

Want live sessions, code reviews, and interview preparation? Join the next batch at Sahasra Technologies.