Lesson 1
Basic SQL Queries
Module 2 closed with a promise: VirtualBookShelf's Customer and Inventory tables now live on a connected SQL Server 2025 database, reachable through both ADO.NET and Entity Framework Core, and the only thing left was writing and running SQL statements directly rather than only reading them in a lesson. Module 3 is where that happens. This first lesson sets up what's ahead: what a database actually is, where this course's SQL statements get written and run, and the four basic operations the rest of this module builds around.
It's worth pausing on why this matters even though Module 2 already produced working code that reads and writes VirtualBookShelf's data. Everything built so far, the ADO.NET `SqlCommand` objects, the EF Core `DbContext`, has been quietly relying on SQL underneath without asking you to write it directly. This module closes that gap. By the time it ends, nothing SQL Server does will be hidden behind a framework; you'll know exactly what statement gets sent, because you'll have typed one just like it yourself.
You will need to use SQL statements to query and modify the database in the VirtualBookShelf.com Web site. This module focuses on how to write some of the most basic queries. When you finish, you will be able to do the following:
- Use the SQL
SELECT statement to retrieve all the rows in a table
- Modify a
SELECT statement to parameterize data search criteria
- Sort, modify, add, and delete records in a table
Later in this module, you will write and run each of these statements yourself against VirtualBookShelf's own Customer and Inventory tables. You already have everything that requires: Module 2 built the connection, through ADO.NET and Entity Framework Core, that every one of these statements will travel through.
What Is a Database?
A database is one or more large, structured sets of persistent data, usually associated with software that updates and queries that data. "Structured" means the data is organized in a specific, predictable pattern rather than scattered arbitrarily, which is what makes it possible to store, search, and manage efficiently. "Persistent" means the data survives after the program that created it closes; it isn't held only in memory and lost when the application stops running.
A simple database might be a single file containing many records, each with the same set of fields. A more useful way to think about it, though, is in terms of collections: a database contains distinct collections of related data, VirtualBookShelf's customer records and its book inventory, for example, kept separate from each other so that searching for a book's price never means wading through unrelated customer data. A database stores these collections in tables, the subject Module 2, Lesson 2 covered in detail.
The definition also mentions software that updates and queries that data. Real-life examples of that software, a database management system, or DBMS, include Microsoft SQL Server 2025, PostgreSQL, MySQL, Oracle Database, and Microsoft Access, the same five systems Module 2, Lesson 3 compared directly. People often call these programs "databases" themselves, but strictly speaking, they're the software that manages one or more databases; VirtualBookShelf's actual database, its specific collections of Customer and Inventory data, is what SQL Server 2025 manages on this course's behalf.
Inside a database, the structure that makes everything searchable comes down to tables, rows, and columns, the same vocabulary Module 2, Lesson 2 introduced when VirtualBookShelf's schema was first designed. A table holds one kind of thing, Customer rows all describe customers, Inventory rows all describe books, and every row in a table has the same columns, even when a particular row's value for one of those columns is missing. A column also carries a data type, `nvarchar` for text, `int` for whole numbers, `decimal` for currency, which is what lets SQL Server catch a mistake, trying to store text where a price belongs, before it ever reaches the data VirtualBookShelf depends on.
This also explains why a database and the application built on top of it are two separate things, even though people sometimes talk about them as one. VirtualBookShelf the Razor Pages application can be stopped, rebuilt, and redeployed without touching a single row in Customer or Inventory, because the data and the code that reads it live in different places, connected only by the SQL statements this module teaches.
SQL Describes What You Want, Not How to Get It
One habit worth building early: SQL is a declarative language, not a procedural one. A procedural approach, the kind the legacy VBScript version of VirtualBookShelf relied on, walks through data step by step: open a `Recordset`, call `MoveNext` in a loop, check a field's value on each pass, and decide by hand whether to keep or skip that row. SQL doesn't work that way. A statement like SELECT Title, Price FROM Inventory WHERE QuantityOnHand > 0; simply states the condition, rows where QuantityOnHand is greater than zero, and leaves SQL Server to decide how to find them.
That difference matters more than it might first appear. SQL Server's query optimizer can choose a faster way to satisfy a declarative request, using an index instead of scanning every row, for instance, without anyone having to rewrite the statement itself. A hand-written procedural loop has no equivalent flexibility; it does exactly what it was told, row by row, every time. Classic ADO's own `Recordset` object, the technology Module 2, Lesson 4 contrasted with modern `Microsoft.Data.SqlClient`, worked exactly this way: navigation methods like `MoveNext` told the program precisely how to step through rows one at a time, which is procedural thinking even when the underlying data came from a database.
It's also why Entity Framework Core's LINQ queries can translate into efficient SQL at all: LINQ is declarative in the same way, describing the desired result rather than a sequence of steps, which is what makes `context.Inventory.Where(b => b.QuantityOnHand > 0)` and the raw SQL version functionally the same request. Learning to think declaratively here, in plain SQL, is what makes both ADO.NET's parameterized commands and EF Core's LINQ expressions feel like the same skill rather than two unrelated ones.
Where This Course Writes and Runs SQL
Module 2, Lesson 6 already named the tool this course uses for exactly this: Azure Data Studio, connected to VirtualBookShelf's SQL Server 2025 database using the same connection details stored in `appsettings.json`. Every statement in this module can be typed directly into a query window there and run immediately against VirtualBookShelf's real data, no Visual Studio project, no Razor Page, and no C# code required.
That's worth being direct about, because it's easy to assume SQL only matters once it's wrapped inside application code. It doesn't. Module 2's conclusion page already laid the same query, finding every book currently in stock, side by side in three forms:
-- Raw SQL
SELECT Title, Author, Price FROM Inventory WHERE QuantityOnHand > 0;
// ADO.NET
using var cmd = new SqlCommand(
"SELECT Title, Author, Price FROM Inventory WHERE QuantityOnHand > 0;", connection);
using var reader = cmd.ExecuteReader();
// Entity Framework Core
var inStock = context.Inventory
.Where(b => b.QuantityOnHand > 0)
.ToList();
All three ultimately send the identical SQL statement to SQL Server. Learning to write that statement directly, the way this module does, is what makes the ADO.NET and EF Core versions make sense rather than feeling like separate, unrelated skills. The LINQ expression is EF Core generating a
SELECT statement on your behalf; this module is where you learn to write that same statement yourself, by hand, in Azure Data Studio, before any C# code is involved at all.
VirtualBookShelf's Tables, Ready to Query
Every example in this module runs against the same two tables Module 2 already built and connected:
CREATE TABLE dbo.Customer
(
CustomerID int IDENTITY(1,1) NOT NULL,
FirstName nvarchar(100) NOT NULL,
LastName nvarchar(100) NOT NULL,
Email nvarchar(255) NOT NULL,
Phone nvarchar(20) NULL,
DateJoined date NOT NULL DEFAULT (GETDATE()),
CONSTRAINT PK_Customer PRIMARY KEY (CustomerID)
);
CREATE TABLE dbo.Inventory
(
BookID int IDENTITY(1,1) NOT NULL,
Title nvarchar(200) NOT NULL,
Author nvarchar(150) NOT NULL,
ISBN nvarchar(20) NOT NULL,
Price decimal(10,2) NOT NULL,
QuantityOnHand int NOT NULL DEFAULT (0),
CONSTRAINT PK_Inventory PRIMARY KEY (BookID)
);
Nothing about this schema changes in this module. The point of writing SQL by hand here isn't to design new tables, Module 2, Lesson 2 already did that, it's to practice reading from and writing to the tables that already exist, the same two tables every lesson since has queried, updated, and bound against.
Two details in that schema are worth keeping in view while writing this module's statements. First, `CustomerID` and `BookID` are each marked `IDENTITY(1,1)`, meaning SQL Server assigns their values automatically, starting at 1 and incrementing by 1 for every new row; an `INSERT` statement against either table never supplies a value for that column directly. Second, each table's `PRIMARY KEY` constraint, `PK_Customer` and `PK_Inventory`, guarantees that column's value is unique across every row, which is exactly what makes a statement like `WHERE CustomerID = 3` reliable: there is never more than one row it could match.
The Four Basic Operations, Previewed
Four SQL statements cover nearly everything this module, and this course's data access code, actually does to a table. Module 2, Lesson 5 already introduced the syntax for each one against the Customer table; this module is where you'll write and run versions of your own, so it's worth having all four in view before the hands-on lessons begin.
SELECT retrieves rows without changing anything: SELECT FirstName, LastName, Email FROM Customer; This lesson's first objective, retrieving every row in a table, starts here, and the next lesson writes this exact kind of statement against VirtualBookShelf's own data.
INSERT adds a new row: INSERT INTO Customer (FirstName, LastName, Email, Phone) VALUES ('Jane', 'Doe', 'jane.doe@example.com', '555-0147'); This is how a new customer record, or a new book in Inventory, actually gets created.
UPDATE changes existing rows: UPDATE Customer SET Phone = '555-0199' WHERE CustomerID = 3; the same statement Module 2, Lesson 7 compared directly against Entity Framework Core's change-tracking approach to the identical update.
DELETE removes rows: DELETE FROM Customer WHERE CustomerID = 7; Note the WHERE clause here is doing the same job it does in a SELECT or an UPDATE, narrowing which rows a statement actually affects, which matters enormously with DELETE, since a DELETE statement with no WHERE clause at all removes every row in the table.
This lesson's second and third objectives, parameterizing a search and sorting, modifying, adding, and deleting records, are really refinements of these same four statements: a parameterized search is a
SELECT with a
WHERE clause whose value comes from user input rather than being typed directly into the statement, and sorting is an
ORDER BY clause added onto a
SELECT, both covered in Lesson 5's "Anatomy of a Query" breakdown.
"Parameterized" is worth sitting with for a moment, because it's doing real work beyond convenience. If a customer searches VirtualBookShelf for a book by author name, the application can't simply paste whatever the customer typed directly into a
SELECT statement's
WHERE clause; a value built that way can be crafted to change what the statement actually does, a vulnerability called SQL injection. A parameterized query keeps the statement's structure fixed,
WHERE Author = @AuthorName, and sends the customer's input separately as a value for
@AuthorName, never as text that becomes part of the SQL itself. Both ADO.NET's `SqlCommand.Parameters` collection and EF Core's LINQ queries parameterize automatically behind the scenes; this module's next lessons make that same discipline visible in plain SQL, before either framework hides it again.
A Few Common Questions
Do I need to memorize the exact SQL syntax for all four statements before this module's hands-on lessons begin? No. This lesson previews the shape of each statement so the rest of this module has something concrete to build on, not so you can recite it from memory. The following lessons walk through each statement's syntax in detail, against VirtualBookShelf's own data, with room to practice.
Is Azure Data Studio the only way to run these statements? No, it's simply the tool this course uses because it's free, lightweight, and connects directly to SQL Server 2025 using the same connection details already configured in Module 2. SQL Server Management Studio works equally well for the exercises in this module, and so, eventually, would any client capable of sending SQL to SQL Server over a TDS connection.
If ADO.NET and EF Core can already query the database, why does this module teach raw SQL separately? Because both of them are, underneath, generating and sending exactly the SQL this module teaches. Module 2, Lesson 7 made this explicit: EF Core's LINQ queries only generate correct, efficient SQL when the person writing the LINQ understands what SQL statement it should turn into. Writing these four statements directly, without a layer of C# syntax on top, is what makes that understanding solid.
What happened to the "SQL Query Designer Control" mentioned in older versions of this course? It doesn't factor into this course's actual stack. The closest real historical feature, Visual InterDev's Data Environment query designer, belonged to the Classic ASP era Module 2, Lesson 6 already replaced with `appsettings.json` and dependency injection. This course builds queries by writing SQL directly, then by ADO.NET, then by Entity Framework Core, exactly the progression Module 2 already walked through.
Will every SQL statement in this module run successfully on the first try? Not necessarily, and that's fine. SQL Server rejects a statement with a typo in a column name or a missing clause rather than guessing at what was meant, the same behavior Module 2, Lesson 5 described. A rejected statement with a clear error message is a normal, expected part of learning to write SQL directly, not a sign that something is broken.
Why does SQL injection matter in a course that uses parameterized queries everywhere already? Because the habit has to be deliberate, not accidental. It's entirely possible to write a `SqlCommand` or a raw SQL statement that concatenates user input directly into the query text, which reintroduces the exact vulnerability parameters exist to close. Understanding why `WHERE Author = @AuthorName` is safe and `WHERE Author = '" + userInput + "'"` isn't, is the point of writing parameterized SQL by hand in this module, rather than only ever seeing it already handled inside a framework.
Does VirtualBookShelf need a third table before this module's exercises make sense? No. Every statement in this module, and every lesson that follows in Module 3, works entirely within the Customer and Inventory tables Module 2 already built and connected. Nothing here depends on an orders or transactions table that doesn't exist yet.
What's Ahead
This lesson set up what the rest of Module 3 builds on: a working definition of a database, a tool, Azure Data Studio, for running SQL directly against VirtualBookShelf's connected SQL Server 2025 database, and a preview of the four statements, SELECT, INSERT, UPDATE, and DELETE, this module spends its remaining lessons on.
In the next lesson, SELECT statements that retrieve all rows from a single table will be written, starting with VirtualBookShelf's own Customer table and the exact kind of query Module 2's lessons have been pointing toward since Lesson 5 first introduced SQL itself.
From there, this module moves through the remaining objectives in order: narrowing a SELECT with a parameterized WHERE clause, adding an ORDER BY clause to control sort order, and finally writing the INSERT, UPDATE, and DELETE statements that add, change, and remove records. Each lesson runs its examples against Azure Data Studio, connected to the same VirtualBookShelf database every Module 2 lesson already built toward, so nothing about the environment changes between lessons, only the statements being written in it.
