Build Your Own ASP.NET 3.5 Website Using C# & VB (58 page)

Speaking SQL

313

The Number of Affected Rows

As you can see in Figure 8.4
, SQL Server reports the number of records that have been affected by a certain query. This report doesn’t indicate that those records

were modified. Instead, the figure represents the number of rows that were read,

modified, deleted, or inserted by a certain query.

Let’s move on and take a look at some variations of the SELECT query. Then we’ll

see how easy it is to insert, modify, and delete items from the database using other

keywords.

Selecting Certain Fields

If you didn’t want to select all the fields from the database table, you’d include the

names of the specific fields that you wanted in place of the * in your query. For

example, if you’re interested only in the department names—not their IDs—you

could execute the following query:

FROM Departments

This statement would retrieve data from the Department field only. Rather than

specifying the *, which would return all the fields within the database table, we

specify only the fields that we need.

Selecting All Columns Using *

To improve performance in real-world development scenarios, it’s better to ask

only for the columns that are of interest, rather than using *. Moreover, even when

you need all the columns in a table, it’s better to specify them by name, to safeguard

against the possibility that future changes, which cause more columns to be added

to the table, affecting the queries you’re writing now.

It’s important to note that the order of the fields in a table determines the order in

which the data will be retrieved. Take this query, for example:

SELECT DepartmentID, Department

Licensed to [email protected]

314

Build Your Own ASP.NET 3.5 Web Site Using C# & VB

You could reverse the order in which the columns are returned with this query:

SELECT Department, DepartmentID

Executing this query would produce the result set shown in
Figure 8.5
. Figure 8.5. Retrieving department names and their IDs

Try it for yourself!

Selecting Unique Data with DISTINCT

Say you want to find out which cities your employees hail from. Most likely, a query

such as the one shown below would generate multiple results:

FROM Employees

If this query were applied to the Dorknozzle application, the same city location

would appear six times in the results—once for every employee in our database.

Figure 8.6 illustrates this point.

That’s not usually what we want to see in our results. Typically, we prefer to see

the
unique
cities in the list—a task that, fortunately enough, is easy to achieve.

Adding the DISTINCT keyword immediately after the SELECT clause extracts only

Licensed to [email protected]

Speaking SQL

315

Figure 8.6. Reading the employees’ cities

the unique instances of the retrieved data. Take a look at the following SQL statement:

SELECT
DISTINCT
City

This query will produce the result shown in Figure 8.7.

Figure 8.7. Selecting distinct cities

In this case, because only the City column was included within the SQL query,

unique instances within the City column were returned.

Note that the uniqueness condition applies to the whole of the returned rows. If,

for example, we asked for the name of each employee as well, all the rows would

be considered unique (because no two employees have the same name) and no row

would be eliminated by DISTINCT. To see for yourself, execute this query:

Licensed to [email protected]

316

Build Your Own ASP.NET 3.5 Web Site Using C# & VB

SELECT DISTINCT Name, City

The results of this code are pictured in Figure 8.8
. As we expected, the DISTINCT

clause doesn’t have any effect, since each row is unique.

Figure 8.8. Retrieving employees and cities

Row Filtering with WHERE

The WHERE clause is used in conjunction with SQL queries, including the SELECT

query, to deliver more refined search results based on individual field criteria. The

following example could be used to extract all employees that work in the Department whose ID is 6: SELECT Name, DepartmentID

WHERE DepartmentID = 6

This query returns the results shown below:

Name

DepartmentID

-------------------------------------------------------------Ted Lindsey

6

Shane Weebe

6

(2 row(s) affected)

Licensed to [email protected]

Speaking SQL

317

But wait! How do I know the name of the department with the ID of 6? Well, you

could use a similar query to find out. Try this:

FROM Departments

WHERE DepartmentID = 6

Executing this query reveals that the department with the ID of 6 is Engineering:

Department

-------------------------------------------------Engineering

(1 row(s) affected)

Selecting Ranges of Values with BETWEEN

There may be times when you’ll want to search within a database table for rows

that fall within a certain range of values. For instance, if you wanted to retrieve

from the Departments table all departments that have IDs between 2 and 5, you

could use the BETWEEN keyword like so:

SELECT DepartmentID, Department

WHERE DepartmentID BETWEEN 2 AND 5

As we requested, all departments whose IDs are between 2 and 5 are returned. Note

that the range is inclusive, so departments with IDs of 2 and 5 will also be retrieved.

Keep in mind that any conditions that use BETWEEN could easily be rewritten by

combining two “greater than or equal” and “less than or equal” conditions:

SELECT DepartmentID, Department

WHERE DepartmentID >= 2 AND DepartmentID <= 5

We could also use the NOT keyword before the BETWEEN keyword to specify all items

that fall outside the range, as follows:

Licensed to [email protected]

318

Build Your Own ASP.NET 3.5 Web Site Using C# & VB

SELECT DepartmentID, Department

WHERE DepartmentID NOT BETWEEN 2 AND 5

In this example, all rows whose DepartmentIDs are less than 2 or greater than 5 are

returned.

Matching Patterns with LIKE

As we’ve just seen, the WHERE clause allows us to filter results based on criteria that

we specify. The example we discussed earlier filtered rows by comparing two

numbers, but SQL also knows how to handle strings. For example, if we wanted to

search the company’s Employees table for all employees named Zak Ruvalcaba,

we'd use the following SQL statement:

SELECT EmployeeID, Username

WHERE Name = 'Zak Ruvalcaba'

However, we won’t see many such queries in reality. In real-world scenarios, most

record matching is done by matching the primary key of the table to some specific

value. When an arbitrary string such as a name is used (as in the example above),

it’s likely that we’re searching for data based on partially complete information.

A more realistic example is one in which we want to find all employees with the

surname Ruvalcaba. The LIKE keyword allows us to perform pattern matching with

the help of
wildcard characters
. The wildcard characters supported by SQL Server

are the percentage symbol (%), which matches any sequence of zero or more characters, and the underscore symbol (_), which matches exactly one character. If we wanted to find all names within our Employees table with the surname of

Ruvalcaba, we could modify the SQL query using a wildcard, as follows:

SELECT EmployeeID, Name

WHERE Name LIKE '%Ruvalcaba'

With this query, all records in which the Name column ends with Ruvalcaba are

returned, as shown below.

Licensed to [email protected]

Speaking SQL

319

EmployeeID Name

------------------------------------------------------------1

Zak Ruvalcaba

2

Jessica Ruvalcaba

(2 row(s) affected)

As we knew that the last name was Ruvalcaba, we only needed to place a wildcard

immediately before the last name. But what would happen if we didn’t know how

to spell the entire last name? That name
is
fairly difficult to spell! You could solve the problem by modifying your SQL statement to use two wildcards as follows:

SELECT EmployeeID, Name

WHERE Name LIKE '%Ruv%'

In this case, the wildcard is placed before and after the string Ruv. Although this

statement would return the same values we saw in the results table above, it would

also return any employees whose names (first or last) contain the sequence Ruv. As

SQL is case-insensitive, this would include the names Sarah Ruvin, Jonny Noruvitch,

Truvor MacDonald, and so on.

Using the IN Operator

We use the IN operator in SELECT queries primarily to specify a list of values that

we want to match in our WHERE clause. Let’s say we want to find all employees who

live in California, Indiana, and Maryland. You could write the following SQL

statement to accomplish this task:

SELECT Name, State

WHERE State = 'CA' OR State = 'IN' OR State = 'MD'