Build Your Own ASP.NET 2.0 Web Site Using C# & VB, Second Edition

SQL is an international standard, and almost all database products, including

SQL Server, Oracle, DB2, and so on, support the standard to a certain degree.

The dialect of SQL supported by SQL Server is named Transact-SQL (or T-SQL).

This chapter cannot begin to cover all there is to know on the subject, but we

hope it will provide you with an introduction to beginning and advanced SQL

concepts.

In this chapter, we’ll learn:

❑

the basic SQL commands

❑

the expressions that SQL supports

❑

the most important SQL functions

❑

how to perform table joins and subqueries

❑

how to create stored procedures

This may sound like a lot, but you’re certain to enjoy it! Let’s get started.

Reading Data from a Single Table

Information that’s contained within a database is useless unless we have a way

of extracting it. SQL is that mechanism; it allows quick but sophisticated access

to database data through the use of queries. Queries pose questions to the

database server, which returns the answer to your application.

Table 8.1. Sample contents from the Employees table

CityUsernameNameDep'tIDEmployeeID

(Primary

Key)

San DiegozakZak Ruvalcaba51

San DiegojessicaJessica Ruvalcaba92

San DiegotedTed Lindsey63

San DiegoshaneShane Weebe64

San DiegodavidDavid Levinson95

San DiegogeoffGeoff Kim16

294

Chapter 8: Speaking SQL

For example, imagine that you’re trying to extract the information shown in

Table 8.1 from the Employees table of the Dorknozzle database.

How do we make this kind of data available to our web site? The first step is to

learn how to read this data using SQL. Then, in the next chapter, we’ll learn to

access the data from ASP.NET web applications.

In the following sections, we’ll learn to write queries that will let us view existing

data, insert new data, modify existing data, and delete data. Once you’ve learnt

how to write these fundamental SQL queries, the next step is to put everything

together, and to build the web forms with which your users will interact.

Let’s begin: first up, open SQL Server Management Studio. Visual Web Developer

can also be used to test SQL queries, but SQL Server Management Studio is

slightly easier to use for our purposes. Log in to your SQL Server instance, and

select the Dorknozzle database in the Object Explorer pane, as illustrated in Fig-

ure 8.1.

Figure 8.1. Using SQL Server Management Express

Having selected the Dorknozzle database, go to File > New > Database Engine

Query, or simply click the New Query button on the toolbar. A new query window,

like the one shown in Figure 8.2, should open in the right-hand pane.

295

Reading Data from a Single Table

Figure 8.2. A new query window

In the query window, type your first command:

SELECT Name

FROM Employees

Click the Execute button, or press F5. If everything works as planned, the result

will appear similar to Figure 8.3.

Figure 8.3. Executing a simple query

Nice work! Now that we’ve taken our first look at SQL, let’s talk more about

SQL queries.

296

Chapter 8: Speaking SQL