SELECT, WHERE, ORDER BY 🔍

Master SQL in SSMS → Then Use in C# - Zero to Hero

Beginner Friendly 45 min read Lesson 4 of 13

Master SQL Commands in 2 Steps! 🎯

PART 1: Learn SELECT, WHERE, ORDER BY in SSMS (SQL Server Management Studio)
PART 2: Use SELECT, WHERE, ORDER BY in C# (Visual Studio)

Why Two Steps?
  • ✅ First learn what the commands do in SSMS
  • ✅ Then learn how to use them in C# code
  • ✅ This way you understand both sides!

PART 1 - SQL Server Management Studio (SSMS)

Learn SQL Commands in SSMS First!

SSMS Step 1: Create Our Sample Database

First, let's create a database and table to practice with.

Open SSMS and run this SQL:

-- ===== CREATE DATABASE =====
CREATE DATABASE SchoolDB;
GO

USE SchoolDB;
GO

-- ===== CREATE STUDENTS TABLE =====
CREATE TABLE Students (
    StudentId INT IDENTITY(1,1) PRIMARY KEY,
    FirstName NVARCHAR(50),
    LastName NVARCHAR(50),
    Age INT,
    City NVARCHAR(50),
    GPA DECIMAL(3,2),
    EnrolledDate DATE
);
GO

-- ===== INSERT SAMPLE DATA =====
INSERT INTO Students (FirstName, LastName, Age, City, GPA, EnrolledDate)
VALUES 
    ('John', 'Doe', 20, 'New York', 3.8, '2024-01-15'),
    ('Jane', 'Smith', 22, 'Boston', 3.5, '2024-02-20'),
    ('Bob', 'Johnson', 19, 'Chicago', 2.9, '2024-03-10'),
    ('Alice', 'Williams', 21, 'New York', 4.0, '2024-01-05'),
    ('Charlie', 'Brown', 23, 'Boston', 3.2, '2024-04-01'),
    ('Diana', 'Miller', 20, 'Chicago', 3.9, '2024-02-14'),
    ('Eve', 'Davis', 22, 'New York', 3.1, '2024-03-20'),
    ('Frank', 'Wilson', 21, 'Boston', 2.7, '2024-01-30'),
    ('Grace', 'Moore', 18, 'Chicago', 3.6, '2024-04-15'),
    ('Henry', 'Taylor', 24, 'New York', 3.3, '2024-02-28');
GO
✅ Done! You now have 10 students in your database. Let's start querying!
😂 Fun Joke: Why did the SQL developer name their database "SchoolDB"? Because they wanted to teach it a lesson! 😅

SSMS Step 2: SELECT - Getting Your Data

SELECT: This is how you ask the database for data. Like saying "Show me all students!"
💻 Basic SELECT

Type this in SSMS and press F5:

-- Get ALL columns from ALL students
SELECT * FROM Students;

✅ Result: All 10 students with all columns!

📌 * means "all columns"

💻 SELECT Specific Columns

Get only first name, last name, and age:

-- Get specific columns only
SELECT FirstName, LastName, Age
FROM Students;

✅ Result: Only 3 columns shown!

💡 Why this is useful: Less data = faster results!
🎯 SELECT DISTINCT

Get unique cities only:

-- Get unique cities (no duplicates)
SELECT DISTINCT City
FROM Students;

✅ Result: New York, Boston, Chicago

🎯 SELECT TOP

Get only top 3 students:

-- Get only 3 students
SELECT TOP 3 *
FROM Students;

✅ Result: First 3 students only!

🎯 SELECT AS (Alias)

Rename columns in results:

-- Rename columns
SELECT FirstName AS [First Name]
FROM Students;

✅ Result: Column shows as "First Name"

😂 Fun Joke: Why did the SELECT command go to the gym? Because it wanted to get in shape! (Get it? SELECT *? ...I'll stop 😅)

SSMS Step 3: WHERE - Filtering Your Data

WHERE: This is how you filter data. Like saying "Show me ONLY students from New York!"
💻 WHERE - Exact Match

Get students from New York:

-- Get only New York students
SELECT *
FROM Students
WHERE City = 'New York';

✅ Result: Only students from New York!

💻 WHERE - Greater Than

Get students older than 21:

-- Get students older than 21
SELECT *
FROM Students
WHERE Age > 21;

✅ Result: Students with age 22, 23, 24!

🎯 WHERE LIKE

Find names starting with 'J':

-- Names starting with J
SELECT *
FROM Students
WHERE FirstName LIKE 'J%';

✅ John, Jane

🎯 WHERE BETWEEN

Students age 20-22:

-- Age between 20 and 22
SELECT *
FROM Students
WHERE Age BETWEEN 20 AND 22;

✅ Students age 20, 21, 22

🎯 WHERE IN

Students from Boston or Chicago:

-- Cities in a list
SELECT *
FROM Students
WHERE City IN ('Boston', 'Chicago');

✅ All Boston and Chicago students

🎯 WHERE AND (Multiple Conditions)

New York students with GPA > 3.5:

-- Two conditions
SELECT *
FROM Students
WHERE City = 'New York'
AND GPA > 3.5;

✅ New York students with good grades!

🎯 WHERE OR (Multiple Conditions)

Students from New York OR GPA > 3.8:

-- Either condition
SELECT *
FROM Students
WHERE City = 'New York'
OR GPA > 3.8;

✅ All New York students OR students with high GPA

😂 Fun Joke: Why did the WHERE clause break up with the SELECT statement? Because it was too conditional! 😅

SSMS Step 4: ORDER BY - Sorting Your Data

ORDER BY: This is how you sort your data. Like saying "Show me students sorted by name!"
💻 ORDER BY - Ascending (A-Z)

Sort by last name alphabetically:

-- Sort A-Z
SELECT *
FROM Students
ORDER BY LastName ASC;

✅ Brown, Davis, Doe, Johnson, Miller, Moore, Smith, Taylor, Williams, Wilson

💻 ORDER BY - Descending (Z-A)

Sort by age (oldest first):

-- Sort Z-A
SELECT *
FROM Students
ORDER BY Age DESC;

✅ Oldest students first (24, 23, 22, etc.)

🎯 ORDER BY Multiple Columns

Sort by city then by name:

-- Sort by city, then name
SELECT *
FROM Students
ORDER BY City ASC, LastName ASC;

✅ Grouped by city, sorted by name

🎯 ORDER BY with WHERE

New York students, sorted by GPA:

-- Filter + Sort
SELECT *
FROM Students
WHERE City = 'New York'
ORDER BY GPA DESC;

✅ Only New York students, best GPA first

🎯 ORDER BY Column Number

Sort by the 2nd column:

-- Sort by column position
SELECT FirstName, LastName
FROM Students
ORDER BY 2 ASC;

✅ Sorted by LastName (the 2nd column)

😂 Fun Joke: Why did the ORDER BY command go to the gym? Because it wanted to get sorted! 😅

SSMS Step 5: SELECT + WHERE + ORDER BY Together

Master Combination: Use all three together for powerful queries!
💻 Complete Example
-- New York students, sorted by GPA
SELECT FirstName, LastName, GPA
FROM Students
WHERE City = 'New York'
ORDER BY GPA DESC;

✅ Result: Alice (4.0), John (3.8), Eve (3.1), Henry (3.3)

💻 More Examples
-- Students over 20, sorted by name
SELECT *
FROM Students
WHERE Age > 20
ORDER BY LastName ASC;

-- Boston students with GPA > 3.0, sorted by GPA
SELECT *
FROM Students
WHERE City = 'Boston'
AND GPA > 3.0
ORDER BY GPA DESC;
💡 Remember the Order:
  • 1️⃣ SELECT - What to show
  • 2️⃣ FROM - Which table
  • 3️⃣ WHERE - Filter first
  • 4️⃣ ORDER BY - Sort last

PART 2 - Using SQL Commands in C# (Visual Studio)

Now Let's Use SELECT, WHERE, ORDER BY in C#!

C# Step 1: Create Your WinForms Project

Now we'll use the same SQL commands from C#!
📁 Create Project
  1. Open Visual Studio
  2. Click "Create a new project"
  3. Search for "Windows Forms App"
  4. Name it "StudentManagerCSharp"
  5. Click Create
💻 Add NuGet Package
  1. Right-click project → Manage NuGet Packages
  2. Search for "Microsoft.Data.SqlClient"
  3. Click Install
✅ Ready! Now add the using statements: 
// Add these at the top of Form1.cs
using System.Data;
using Microsoft.Data.SqlClient;

// Add this inside your Form1 class
private string connectionString = 
    "Server=localhost;Database=SchoolDB;Trusted_Connection=True;TrustServerCertificate=True;";
😂 Fun Joke: Why did the C# developer love NuGet? Because it had all the packages they needed! (Get it? Packages? ...I'll stop 😅)

C# Step 2: SELECT - Getting Data in C#

Same SELECT command, now in C#! 
// ===== METHOD: Load All Students =====
// This uses the same SELECT command we learned in SSMS

private void LoadAllStudents()
{
    // 1️⃣ The SQL query (same as in SSMS!)
    string query = "SELECT * FROM Students";
    
    // 2️⃣ Connect and run the query
    using (var conn = new SqlConnection(connectionString))
    using (var adapter = new SqlDataAdapter(query, conn))
    {
        DataTable dt = new DataTable();
        adapter.Fill(dt);
        
        // 3️⃣ Show in the DataGridView
        dgvStudents.DataSource = dt;
    }
}
✅ Same as SSMS! The SQL command is identical, just wrapped in C# code.

C# Step 3: WHERE - Filtering in C#

Same WHERE command, now in C#! 
// ===== METHOD: Filter by City =====
// Uses WHERE City = '@City'

private void FilterByCity(string city)
{
    // 1️⃣ The SQL query with WHERE
    string query = "SELECT * FROM Students WHERE City = @City";
    
    // 2️⃣ Connect and add the parameter
    using (var conn = new SqlConnection(connectionString))
    using (var cmd = new SqlCommand(query, conn))
    {
        // 3️⃣ Add the parameter value
        cmd.Parameters.AddWithValue("@City", city);
        
        // 4️⃣ Run the query
        using (var adapter = new SqlDataAdapter(cmd))
        {
            DataTable dt = new DataTable();
            adapter.Fill(dt);
            dgvStudents.DataSource = dt;
        }
    }
}
🎯 WHERE with Age
// Students older than 20
string query = "SELECT * FROM Students WHERE Age > @Age";
cmd.Parameters.AddWithValue("@Age", 20);
🎯 WHERE with LIKE
// Names starting with J
string query = "SELECT * FROM Students WHERE FirstName LIKE @Name";
cmd.Parameters.AddWithValue("@Name", "J%");
✅ Important! Always use @Parameters to prevent SQL injection!

C# Step 4: ORDER BY - Sorting in C#

Same ORDER BY command, now in C#! 
// ===== METHOD: Sort by Name =====
// Uses ORDER BY LastName ASC

private void SortByName()
{
    // 1️⃣ The SQL query with ORDER BY
    string query = "SELECT * FROM Students ORDER BY LastName ASC";
    
    // 2️⃣ Run the query
    using (var conn = new SqlConnection(connectionString))
    using (var adapter = new SqlDataAdapter(query, conn))
    {
        DataTable dt = new DataTable();
        adapter.Fill(dt);
        dgvStudents.DataSource = dt;
    }
}
🎯 ORDER BY DESC
// Sort by age (oldest first)
string query = "SELECT * FROM Students ORDER BY Age DESC";
🎯 ORDER BY Multiple
// Sort by city, then name
string query = "SELECT * FROM Students ORDER BY City ASC, LastName ASC";
✅ Same as SSMS! The ORDER BY syntax is exactly the same.

C# Step 5: SELECT + WHERE + ORDER BY Together

Same combination, now in C#! 
// ===== METHOD: Filter AND Sort =====
// Uses WHERE + ORDER BY together

private void FilterAndSort(string city)
{
    // 1️⃣ The SQL query with WHERE and ORDER BY
    string query = "SELECT * FROM Students WHERE City = @City ORDER BY LastName ASC";
    
    // 2️⃣ Connect with parameter
    using (var conn = new SqlConnection(connectionString))
    using (var cmd = new SqlCommand(query, conn))
    {
        cmd.Parameters.AddWithValue("@City", city);
        
        // 3️⃣ Run the query
        using (var adapter = new SqlDataAdapter(cmd))
        {
            DataTable dt = new DataTable();
            adapter.Fill(dt);
            dgvStudents.DataSource = dt;
        }
    }
}
🏆 Master Combination! You've now used SELECT + WHERE + ORDER BY in both SSMS and C#!

Exercises: Test Your Skills!

1 Easy: SELECT All Students

Task: Write C# code to display all students from the database.

// SOLUTION (3 lines)
string query = "SELECT * FROM Students";
DataTable dt = GetData(query);
dgvStudents.DataSource = dt;
2 Medium: Filter by GPA

Task: Write C# code to show students with GPA > 3.5, sorted by GPA (highest first).

// SOLUTION (3 lines)
string query = "SELECT * FROM Students WHERE GPA > @GPA ORDER BY GPA DESC";
DataTable dt = GetData(query, "@GPA", 3.5);
dgvStudents.DataSource = dt;
3 Hard: Complete Search Form

Task: Create a WinForms form with search by city, age range, and sorting options.

// ===== COMPLETE SEARCH FORM =====

                                            private void btnSearch_Click(object sender, EventArgs e)
                                            {
                                            // Build the query dynamically
                                            string query = "SELECT * FROM Students WHERE 1=1";
    
                                            // Add filters based on user input
                                            if (!string.IsNullOrWhiteSpace(txtCity.Text))
                                            query += " AND City = '@City'";
    
                                            if (!string.IsNullOrWhiteSpace(txtMinAge.Text))
                                            query += " AND Age >= @MinAge";
    
                                            if (!string.IsNullOrWhiteSpace(txtMaxAge.Text))
                                            query += " AND Age <= @MaxAge";
    
                                            // Add sorting
                                            string sortColumn = cmbSortBy.SelectedItem?.ToString() ?? "LastName";
                                            string sortDirection = cmbSortDirection.SelectedItem?.ToString() ?? "ASC";
                                            query += $" ORDER BY {sortColumn} {sortDirection}";
    
                                            // Execute the query
                                            dgvStudents.DataSource = GetData(query);
                                            }
🏆 Congratulations! You've built a professional search form using SELECT, WHERE, and ORDER BY!

🎉 Mastery Summary

SELECT

SELECT * FROM Students

Get all data
WHERE

WHERE City = 'NY'

Filter data
ORDER BY

ORDER BY Name ASC

Sort data
Combined

SELECT * FROM Students WHERE City = 'NY' ORDER BY Name ASC

All 3 commands together!
In C#

DataTable dt = GetData(query);

Just 1 line to run any query!
In SSMS

SELECT * FROM Students WHERE City = 'NY' ORDER BY Name ASC

Same query, same result!
You Are Now a Master!

✅ You can use SELECT, WHERE, ORDER BY in both SSMS and C#

🎯 You're now a master of SELECT, WHERE, and ORDER BY in SQL Server and C#!

Previous Lesson Next Lesson
Lessons
Test Your Knowledge - Take Quiz